Home » Other » General » Database consolidation vs. stacking
| Database consolidation vs. stacking [message #562237] |
Mon, 30 July 2012 11:38  |
Frank Naude
Messages: 4587
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  |
 |
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 |
John Watson
Messages: 8962
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 |
Frank Naude
Messages: 4587
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
|
|
|
|
Goto Forum:
Current Time: Fri Dec 27 05:52:00 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
