Index tuning, application tuning, query re-write, partitioning, sub-partitioning, upgrade storage, faster CPU, multi-threaded programming, add more CPU, faster fiber channel controller, faster SATA3 controller
So what's next feasible approach.... Oracle Exadata storage server? Too expansive. Need something cheaper? Let's try few units of Solid State Disk (SSD) devices and combine them with database partitioning feature.
Regardless what kind of application, e.g. SAP ERP, SAP BusinessObject, Oracle Database, Oracle WebLogic, Genesys suites, Oracle Hyperion reports, you will always encounter situation where different tuning strategies are required. In term of best ROI (return of investment), following are common consideration
1. Availability of product and database expertise. Even if available, cost is the next consideration. Often they are costly
2. Timeline. They may not understand overall hardware, application, custom design, business function. It takes minimum a week for them to pick up
3. Workflow. Lots of time spend in big corporation to go through the workflow to provision major hardware change, or upgrade
4. Tuning time. Although DBA may suggest various tuning option, there are cases where DBA can't tell precisely which option will work best
5. User testing time. After each tuning, often QA or live users will test it. Time and cost involve to get them involve, especially overtime
6. Maganement cost. At least one manager needs to involve to coordinate for meeting, discussion, management update, etc. Another cost to consider
7. Scalability. When product, database, and servers are max out, yet limited by regular storage capacity, SSD technolgy is the last to consider. Often, everyone you speak to will propose running everything in SSD drive. This is very costly option
This is another tuning strategy fit in between hardware upgrade and database tuning.
Most of the databases has partitioning (MS SQL Server, DB2, MySQL), and sub-partition (Oracle) feature. This idea is to buy small amount of SSD drive to keep most frequently access data
I will use Genesys CTI application with Oracle database in this article. If there is any interest in other product I indicated above, I can expand it to cover other applications. There are too many applications (IIS, web portal, reporting, ETL, ERP) that I can cover, but would like to use one as an example
This design is
1. Create at leave 2 partitions. For Oracle, sub-partitions can be used for fine grain space allocation
2. One partition to keep frequently use data. Another partition to keep older than 7 days data
3. Recommend to have a pair of tablespaces for each major tables for each database
4. For databases in Table List #1 and #2, keep all the dbf files in SSD drive
5. Partitions which keep current data store will created in tablespace which has dbf files resides in SSD drive
6. Partitions which keep old data store will create in tablespace which has dbf files resides in regular drive, e.g. SATA, SAS, SCSI, etc
7. Create a weekly job which merge current data partition into old partition
8. Create new partition to keep current data with indexes, if applicable. This can be created 1 week or 1 month earlier. Note that it will takes up initial extend space. For data warehouse database, it could be 300 MB big
9. Ensure database backup with archive log exists and tested monthly. SSD drive will degrade depending on usage
10. To further improve recovery time, write RMAN backup script to backup the tablespaces in following sequence, SYSTEM, tablespaces holding current data, the rest of the tablespaces
11. To further improve recovery time, keep 1 copy of RMAN compressed backup on disk. If have Oracle Standard Edition, then use gzip to compress after backup completed
Therefore, the application and reports will enjoy following benefits:
1. System tables will always have fast performance
2. Day to day transaction will be very smooth
3. Intraday, or 7 days reports will available immediately
4. Data mart will able to crunch 7 days transactional date at 10x the speed
5. If SSD corrupted due to material aging after 3 years (let's say), and needs database recovery, it can recover from disk, which is very fast. Oracle allows to retore only the corrupted dbf files and respective archive log. Recovery is in 1 minutes for 2 GB file
6. Internal database engine transaction will be very smooth, which indirectly improve other application's database performance
Table List #1
MS SQL Server database
1. master
2. tempdb
3. config
4. datamart
5. any custom report database
Table List #2
Oracle database
1. datamart - selective tables
2. config - Keep everything in SSD drive if possible, else keep USERS tablespace in SSD. If other tablespace is used to store Genesys data, then use it
3. any custom report database
4. For each database, stores
4.1. Tablespace SYSTEM
4.2. Tablespace TEMP
4.3. Tablespace UNDOTBS
4.4. Online redo log
5. OCS - selective tables
For OCS Outbound Contact database,
A.
Create 2 partitions for each calling list. Use call_time to split data between 2 partitions, which its tablespace design as follow:
1. If call_time null, store in SSD drive
2. If call_time < 7 days, store in SSD drive
3. Others store in regular disk
B.
Store gsw_donotcall_list in tablespace which resides in SSD drive. Partitioning is optional. If need to partition, then use TIME_STAMP column
C.
Store gsw_req_log in 2 partitions as well. Partition by TIME_STAMP column
D.
If OCS history file (.rep) is captured and loaded into database table Calling_List_History (or any name), store in 2 partitions. Partition by LOG_TIME
1. log_time within 7 days stores in SSD drive
2. Others store in regular disk
For CCA database, or DATAMART
E.
Keeps following tables in tablespaces residing in SSD drive. No need partition
- AGG_COLUMN
- BASIC_STAT
- CHUNK_LOAD_ERR_LOG
- CHUNK_LOG
- COMP_STAT
- COMP_STAT_CATEGORY
- COMP_TO_BASIC_STAT
- CONFIG_SERVER
- DM_PROPERTY
- ERROR_CHUNK
- FOLD_TEMP_TO_COMP
- FOLD_TO_COMP_STAT
- FOLDER_TEMPLATE
- OBJ_TO_LAYOUT
- OBJ_TO_OBJ
- OBJECT
- OUTCOME_AGG_COLUMN
- PENDING_AGG
- PURGING_RULES
- REP_TO_TAB
- REPORT_FOLDER
- REPORT_LAYOUT
- REPORT_TABLE
- REPORT_VIEW
- SEQUENCES
- SOURCE
- STAT_PARAM
- STAT_TO_PAR
- STATISTIC
- TAB_INFO_TYPE
- TIME_COLUMN
- TIME_FUN_PARAM
- TEMP_TFUN_PAR_VAL
- TIME_FUN_PARAM_VAL
- TIME_FUNCTION
- TIME_ZONE
- VIEW_AGG_COLUMN
- VIEW_TEMP_AGG_COL
- VIEW_TEMP_TIME_COL
- VIEW_TEMPLATE
- VIEW_TIME_COLUMN
- All O_nnn_OBJ_DIM
- All S_nnn_STAT_DIM
Partition following tables into 2, one stores in SSD, another in regular disk
1. LOGIN - by TIME, which is sec since 1970-01-01
2. PURGING_LOG - by PURGE_START_TIME
3. QINFO - by STARTTIME
4. REP_REBUILD_LOG - by LAST_TIME_KEY
5. STATUS - by STARTTIME
6. R_nnn_STAT_RES - by TIMEKEY
7. T_nnn_TIME_DIM - by TIMEKEY
Create a database or shell script to move current data to 7 days old partition, which resides in regular disk
User coordination:
1. Inform user for weekly maintenance. Minimum 1 hr
2. Create an alert, or e-mail trigger into this script to notify on failure
3. Ensure archive log backup take place immediately after the activity to free up Fast Recovery Area (FRA) area
Option 1: Technical step
1. Create a control table which keeps track of partition name, table name, subpartition name, creation date, merge date, time_format, range1, range2, retention, is_current, is_archive
2. Base on control table, determine partition name which keeps current data (is_current=1), and old data (is_archive=1)
3. ALTER TABLE T_1_TIME_DIM MARGE PARTITIONS T_1_201143, T_1_2008 INTO T_1_2011 COMPRESS
4. ALTER TABLE four_seasons MODIFY PARTITION quarter_two REBUILD UNUSABLE LOCAL INDEXES. Skip if use global index
5. Dynamically add new partition for next month based on values from control table. Syntax to add base on week 33th of year 2011
ALTER TABLE T_1_TIME_DIM ADD PARTITION T_1_201133 VALUES LESS THAN ('20110814')
6. Update control table to indicate partition merged, and set is_current to new partition
7. This option can enable compressed table and index, which will reduce the database size, and saving to storage cost
Option 2: Technical step
1. Don't merge partition, but instead move the physical dbf files from SSD drive to regular disk drive
2. This is much faster process because it does not need to copy the data using database, but keep existing content of dbf file
3. This approach will indirectly introduce 52 partition per partition tables per year
4. If housekeeping is prefer to reduce managing so many partitions, then a quarterly merging activity can be scripted with similar logic as above
5. Prepare to move the partition resides in SSD with tablespace name T_1_201133, and dbf file T_1_201133_01.dbf
alter system checkpoint;
alter system switch logfile;
alter system checkpoint;
alter tablespace T_1_201133 offline;
6. Move file to regular disk in /u01 from SSD disk in /u02
mv /u01/datamart/T_1_201133_01.dbf /u02/datamart/
6. Rename dbf file in database
alter database rename file '/u01/datamart/T_1_201133_01.dbf' to '/u02/datamart/T_1_201133_01.dbf';
7. Perform recovery, if database is in archive log mode
recover tablespace T_1_201133;
8. Bring dbf file online
alter tablespace T_1_201133 online;
9. Typical 7200 rpm disk can copy at 30 MB/s, while 15000 rpm disk can copy at 110 MB/s. In SAN configuration, they may able to archive 150 MB/s
No comments:
Post a Comment