Home » Other » General » Database consolidation vs. stacking
Database consolidation vs. stacking [message #562237] Mon, 30 July 2012 11:38 Go to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

Do you guys have any guidelines / experiences w.r.t DB stacking and consolidation.

I assume database consolidation to mean:

Put several smaller databases into a single large database. Use one schema per application, otherwise use Oracle's VPD to create virtual private databases. Good candidates are those that do not require unique init.ora/spfile parameters or public privileges. Large systems that were logically split and operated on several systems due to resource constraints can now be reconsolidated.

I assume database stacking to mean:

Put different databases on the same server / host. Every system will get it's own database. Try to run all on the same version, but if you cannot, create multiple Oracle Homes. Use instance caging for databases that behave erratically (bad neighbours). Split databases into logical failure groups to prevent a single server from taking down an entire data centre. Split critical databases - and databases with an application component on the same server - off to dedicated servers.

Possible benefits:
- Run database on fewer cores (a cost saving)
- Fewer physical database servers to manage and maintain
- Simplified backup infrastructure
- Easier to monitor a smaller set of DB servers
- Run on "bare metal" (no visualization) with undiluted access to CPU, memory, I/O and networking

Drawbacks:
- A failure on one server can impact several applications
- Have to notify several users / service managers if a server fails.
- Server maintenance requires you to schedule outages for all databases on it.

Any ideas / thoughts / links would be greatly appreciated.

Best regards.
Frank
Re: Database consolidation vs. stacking [message #562262 is a reply to message #562237] Mon, 30 July 2012 18:38 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Frank,

I look at the physical and logical reads and writes to determine the heavily used databases.
Only lightly used databases can be combined without any impact on the user community.
ENWEBP1P > @dba_hist_seg_stat_PHYSICAL_WRITES_DELTA_LAST_7_DAYS_BY_DAILY_TOTALS.sql

DATE                        TOTAL_DAILY_PHYSICAL_WRITES
--------------------------- ---------------------------
2012-07-22 Sunday                                 70642
2012-07-23 Monday                                225542
2012-07-24 Tuesday                               222868
2012-07-25 Wednesday                             333195
2012-07-26 Thursday                              330667
2012-07-27 Friday                                428706
2012-07-28 Saturday                              291015
2012-07-29 Sunday                                254012
2012-07-30 Monday                                349055

ENWEBP1P > list
  1  select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
  2  sum(b.PHYSICAL_WRITES_DELTA) total_daily_physical_writes
  3  from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
  4  where b.SNAP_ID >(select max(SNAP_ID)-24*8 from sys.wRM$_SNAPSHOT) and a.object_id=b.OBJ#
  5  and b.PHYSICAL_WRITES_DELTA>0
  6  and c.instance_number=(select instance_number from v$instance)
  7  and c.snap_id=b.snap_id
  8  group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
  9* order by 1,2
 
ENWEBP1P > @dba_hist_seg_stat_PHYSICAL_READS_DELTA_LAST_7_DAYS_BY_DAILY_TOTALS.sql

DATE                        TOTAL_DAILY_PHYSICAL_READS
--------------------------- --------------------------
2012-07-22 Sunday                            170911530
2012-07-23 Monday                            263308013
2012-07-24 Tuesday                           174729822
2012-07-25 Wednesday                         632010796
2012-07-26 Thursday                          790794830
2012-07-27 Friday                            768039087
2012-07-28 Saturday                          704181967
2012-07-29 Sunday                            892866560
2012-07-30 Monday                            571080002
                            --------------------------
sum                                         4967922607

ENWEBP1P > list
  1  select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
  2  sum(b.PHYSICAL_READS_DELTA) total_daily_physical_reads
  3  from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
  4  where b.SNAP_ID >(select max(SNAP_ID)-24*8 from sys.wRM$_SNAPSHOT) and a.object_id=b.OBJ#
  5  and b.PHYSICAL_READS_DELTA>0
  6  and c.instance_number=(select instance_number from v$instance)
  7  and c.snap_id=b.snap_id
  8  group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
  9* order by 1,2

ENWEBP1P > @dba_hist_seg_stat_LOGICAL_READS_DELTA_LAST_7_DAYS_BY_DAILY_TOTALS.sql

DATE                        TOTAL_DAILY_LOGICAL_READS
--------------------------- -------------------------
2012-07-22 Sunday                          2296380288
2012-07-23 Monday                          6735376288
2012-07-24 Tuesday                         6773254864
2012-07-25 Wednesday                       6708916048
2012-07-26 Thursday                        7811527024
2012-07-27 Friday                          6992427760
2012-07-28 Saturday                        6737133952
2012-07-29 Sunday                          7134351152
2012-07-30 Monday                          6345428960

ENWEBP1P > list
  1  select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
  2  sum(b.logical_READS_DELTA) total_daily_logical_reads
  3  from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
  4  where b.SNAP_ID >(select max(SNAP_ID)-24*8 from sys.wRM$_SNAPSHOT) and a.object_id=b.OBJ#
  5  and b.logical_READS_DELTA>0
  6  and c.instance_number=(select instance_number from v$instance)
  7  and c.snap_id=b.snap_id
  8  group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
  9* order by 1,2


Alan

[Updated on: Mon, 30 July 2012 18:39]

Report message to a moderator

Re: Database consolidation vs. stacking [message #562404 is a reply to message #562237] Wed, 01 August 2012 06:12 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I dread the words "server consolidation". Some people use it to mean database consolidation, and others to mean what you call database stacking, which is a term I haven't heard of before but intend to use in future.

My thoughts, in no particular order:
1. I believe that the performance issues of VPD (too much parsing and function evaluation) have been largely fixed, if you can set up your policies as static shared, or least context sensitive. Partitioning must be important.
2. I always try to have one OH per database, even though it means many OHs on the machine, because of making applying patch sets much safer if you do it one DB at a time, though of course it's more work.
3. Stacking should work very well with GI clusterware and policy managed databases. If it is possible to put all the machines in one cluster, then you can rely on Uncle Oracle to balance the instances across the nodes. This would probably mean using ACFS for the OHs so that they will automount, and being very clever with the server pools to ensure an appropriate distribution of instances. I don't think you would need RAC licences. This really should work, and if you are moving to new hardware, this would be a wonderful opportunity to try it.
4. Fault tolerance: again, server pools! the SCAN listeners will take care of reconnecting clients to wherever the instances happen to be, TAF session failover no problem. So no problem for users if you lose a node.
5. With regard to requiring fewer CPUs and licences overall, I'm totally confused by this. As I understand it, you can run a single SE licence on a SPARC T3 with 128 cores. That is brilliant for stacking, we have a client running 8 databases on a one CPU T3 machine using logical domains. But I think it's different for EE licences. If I understood licensing, I'ld be a salesman (and rich) instead of a technician (and broke).
6. Any sort of consolidation should give the chance to migrate everything to ASM. I am totally convinced that the performance is far superior to any RAID, if you align your file extents with your segment extents, and have all the DBs sharing one huge disc group.
7. This wouldn't be consolidation onto Exadata, by any chance? If so, I can talk about that for hours.....

Just sort of random thoughts.
John.
Re: Database consolidation vs. stacking [message #562503 is a reply to message #562404] Wed, 01 August 2012 13:16 Go to previous message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
I agree, EXADATA is probably the Rolls-Royce of server consolidation platforms. Maybe even ODA (Oracle Database Appliance) to some extent. Although ODA is by no means a mini-EXADATA (no infiniband, no hybrid columnar compression, etc.) For now, I'm more concerned with consolidation on non-Oracle hardware.

One should probably also consider "soft virtualization" options like with Oracle VM and VMWare. OVM supports "Oracle recognized" hard partitioning. Some argue that VMware can do the same with DRS. However, getting Oracle to recognize/acknowledge it is proving difficult.

Other issues with VMware is the Oracle support, product certification and licensing issues. Arguments why this shouldn't be an issue sound credible, but are a bit too risky for many organisation. Besides, virtualization doesn't contain the VM and operating system sprawl. It also introduces virtualization overhead and why should you pay for virtualization if you don't really have to.

For me, database stacking (or instance caging if you must) stands out as an attractive option to explore. It's simple to implement, "free of charge" and provide theoretical savings by sharing/overcommitting CPU resources:

SQL> alter system set cpu_count = N;
SQL> alter system set resource_manager_plan = 'default_plan';

Best regards.
Frank
Previous Topic: Oracle Life Science
Next Topic: Multidimensional data model
Goto Forum:
  


Current Time: Fri Mar 29 05:53:21 CDT 2024