Thursday May 29, 2008

Optimizing Oracle DSS operations with CMT based servers

This entry continues the Throughput Computing Series to show how a typical DSS operation can be optimized with CMT based servers. The "Create as Select" and "Insert into as Select" operations are quite common in DSS and OLTP environments as well. Unless parallelism is specified, Oracle will single thread these operations. To achieve optimal throughput, these operations can use parallel query and DML operations.

Results

I created a 20GB table on a T5240 server to serve as the source for the "Create as Select" (CAS) operations. The parallelism of the CAS operation was increased until the IO subsystem was maxed out. This resulted in a drop from 25 minutes with no parallelism to 2 minutes 40 seconds with 8 threads...thats nearly a 10x speedup by simply using parallelism built into Oracle!



This server was configured with just two HBAs, one for each the source and destination tables. This limited throughput of CAS operations to 127MB/sec, or one HBA. With this IO configuration, it took only 8 threads to reach maximum throughput. You should experiment to achieve maximum throughput of your IO configuration. If you suspect your IO configuration is not performing up to speed, look into doing some IO micro benchmarking to find the maximum throughput outside of Oracle. A topic for a later discussion :)

SQL syntax

The following shows how to use parallel DML and parallel query.
           ## Create as Select ##
           ##
           SQL> alter session enable parallel dml;
                
           SQL> create table abc
                parallel (degree 32)
                as
                select /\*+ parallel(gtest, 32) \*/ \* from gtest;
    
    
           ## Insert as Select ##
           ##
           SQL> alter session enable parallel dml;
            
           SQL> insert /\*+ parallel(abc,32) \*/
                into abc
                select /\*+ parallel(gtest,32) \*/ \* from gtest;
    
    

Wednesday May 21, 2008

Optimizing Oracle index create with CMT based servers

One of the most common ways to improve SQL performance is the use of indexes. While Oracle does have a wide variety of indexes available, these tests focus on the most commonly used B-tree index. On large tables it is important to ensure indexes get created in a timely fashion, that is why Oracle introduced several features to decrease index creation time:
  • "unrecoverable"

    This feature prevent the logging of intermediate steps of the index creation process. There is really no value to logging of intermediate steps. Index creation should be thought of as an atomic process - if it fails, you can always start over. If you create indexes as "unrecoverable" they won't be recoverable until a backup is performed on the target tablespace.

  • "parallel"

    This simply uses parallel query/dml to speed the creation of indexes.
The following index create statement shows how to use the "parallel" and "unrecoverable" features for index creation.
      create index gtest_c1 on gtest(idname)
      pctfree 30  parallel 64 tablespace glennf_i unrecoverable;
      

Results

The following test created an non-unique index on varchar(32) column of a 20GB table. Parallelism was increased from 1->64 in order to use the available IO bandwidth. With parallelism of 1 index creation took 34 minutes, while with parallelism of 64 it took only 3 minutes and 45 seconds!



These tests use the same configuration as previous posts regarding Oracle in the Throughput Computing series.

Wednesday May 14, 2008

Parallelizing Oracle backup with RMAN on CMT based servers

A backup window is important to keep in check to ensure time for batch and on-line work. With Oracle RMAN there are several ways to keep backups flowing smoothly. This example shows how you can use multiple channels and parallelism to increase the throughput of backup to the maximum of your IO configuration.

Results

This graph shows scaling in MB/sec based on the # of channels in use. The term "channels" used by Oracle does not have any relation to actual "physical" channels. In Oracle RMAN terms, a channel is simply a "connection" to a database for which to backup data. Data files are assigned to "connections" in a round-robin fashion so as to utilize all connections as evenly as possible.



By configuring a parallelism of 20 with RMAN, I was able to increase throughput from 5->80 MB/sec. Single threaded performance was limited to 5MB/sec mainly due to the high CPU component that comes with using "COMPRESSED" backups. The way to maximize IO throughput with COMPRESSION is to simply add more streams.

RMAN commands to achieve parallelism

I used the following commands to create 20 backup "channels" for RMAN. Notice that they configured to use the same directory, just with different file formats.

RMAN> configure channel 1 device type disk format
     '/o6s_data/GLENNF/d2/backup_db_c1%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024 M;
RMAN> configure channel 2 device type disk format
     '/o6s_data/GLENNF/d2/backup_db_c2%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024 M;
...
...
RMAN> configure channel 20 device type disk format
     '/o6s_data/GLENNF/d2/backup_db_c20%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024 M;

After creating these channels, you must tell RMAN how to connect to these channels:

RMAN> configure channel 1 DEVICE TYPE DISK CONNECT '/as sysdba';
RMAN> configure channel 2 DEVICE TYPE DISK CONNECT '/as sysdba';
...
...
RMAN> configure channel 20 DEVICE TYPE DISK CONNECT '/as sysdba';

Next, you need to tell RMAN to use disk parallelism of 20:

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE 
      TO COMPRESSED BACKUPSET PARALLELISM 20;

Finally, let's issue the backup command:

RMAN> BACKUP TABLESPACE GLENNF_RMAN;

Starting backup at 09-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=966 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=952 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=940 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=938 devtype=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: sid=939 devtype=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: sid=969 devtype=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: sid=961 devtype=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: sid=963 devtype=DISK
allocated channel: ORA_DISK_9
channel ORA_DISK_9: sid=953 devtype=DISK
allocated channel: ORA_DISK_10
channel ORA_DISK_10: sid=970 devtype=DISK
allocated channel: ORA_DISK_11
channel ORA_DISK_11: sid=920 devtype=DISK
allocated channel: ORA_DISK_12
channel ORA_DISK_12: sid=943 devtype=DISK
allocated channel: ORA_DISK_13
channel ORA_DISK_13: sid=968 devtype=DISK
allocated channel: ORA_DISK_14
channel ORA_DISK_14: sid=929 devtype=DISK
allocated channel: ORA_DISK_15
channel ORA_DISK_15: sid=960 devtype=DISK
allocated channel: ORA_DISK_16
channel ORA_DISK_16: sid=931 devtype=DISK
allocated channel: ORA_DISK_17
channel ORA_DISK_17: sid=927 devtype=DISK
allocated channel: ORA_DISK_18
channel ORA_DISK_18: sid=957 devtype=DISK
allocated channel: ORA_DISK_19
channel ORA_DISK_19: sid=958 devtype=DISK
allocated channel: ORA_DISK_20
channel ORA_DISK_20: sid=964 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00068 name=/oracle/O6S/sapraw/glenn1
channel ORA_DISK_1: starting piece 1 at 09-MAY-08
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00069 name=/oracle/O6S/sapraw/glenn2
channel ORA_DISK_2: starting piece 1 at 09-MAY-08
channel ORA_DISK_3: starting compressed full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
input datafile fno=00070 name=/oracle/O6S/sapraw/glenn3
channel ORA_DISK_3: starting piece 1 at 09-MAY-08
channel ORA_DISK_4: starting compressed full datafile backupset
channel ORA_DISK_4: specifying datafile(s) in backupset
input datafile fno=00071 name=/oracle/O6S/sapraw/glenn4
channel ORA_DISK_4: starting piece 1 at 09-MAY-08
channel ORA_DISK_5: starting compressed full datafile backupset
channel ORA_DISK_5: specifying datafile(s) in backupset
input datafile fno=00072 name=/oracle/O6S/sapraw/glenn5
channel ORA_DISK_5: starting piece 1 at 09-MAY-08
channel ORA_DISK_6: starting compressed full datafile backupset
channel ORA_DISK_6: specifying datafile(s) in backupset
input datafile fno=00073 name=/oracle/O6S/sapraw/glenn6
channel ORA_DISK_6: starting piece 1 at 09-MAY-08
channel ORA_DISK_7: starting compressed full datafile backupset
channel ORA_DISK_7: specifying datafile(s) in backupset
input datafile fno=00074 name=/oracle/O6S/sapraw/glenn7
channel ORA_DISK_7: starting piece 1 at 09-MAY-08
channel ORA_DISK_8: starting compressed full datafile backupset
channel ORA_DISK_8: specifying datafile(s) in backupset
input datafile fno=00075 name=/oracle/O6S/sapraw/glenn8
channel ORA_DISK_8: starting piece 1 at 09-MAY-08
channel ORA_DISK_9: starting compressed full datafile backupset
channel ORA_DISK_9: specifying datafile(s) in backupset
input datafile fno=00076 name=/oracle/O6S/sapraw/glenn9
channel ORA_DISK_9: starting piece 1 at 09-MAY-08
channel ORA_DISK_10: starting compressed full datafile backupset
channel ORA_DISK_10: specifying datafile(s) in backupset
input datafile fno=00077 name=/oracle/O6S/sapraw/glenn10
channel ORA_DISK_10: starting piece 1 at 09-MAY-08
channel ORA_DISK_11: starting compressed full datafile backupset
channel ORA_DISK_11: specifying datafile(s) in backupset
input datafile fno=00078 name=/oracle/O6S/sapraw/glenn11
channel ORA_DISK_11: starting piece 1 at 09-MAY-08
channel ORA_DISK_12: starting compressed full datafile backupset
channel ORA_DISK_12: specifying datafile(s) in backupset
input datafile fno=00079 name=/oracle/O6S/sapraw/glenn12
channel ORA_DISK_12: starting piece 1 at 09-MAY-08
channel ORA_DISK_13: starting compressed full datafile backupset
channel ORA_DISK_13: specifying datafile(s) in backupset
input datafile fno=00080 name=/oracle/O6S/sapraw/glenn13
channel ORA_DISK_13: starting piece 1 at 09-MAY-08
channel ORA_DISK_14: starting compressed full datafile backupset
channel ORA_DISK_14: specifying datafile(s) in backupset
input datafile fno=00081 name=/oracle/O6S/sapraw/glenn14
channel ORA_DISK_14: starting piece 1 at 09-MAY-08
channel ORA_DISK_15: starting compressed full datafile backupset
channel ORA_DISK_15: specifying datafile(s) in backupset
input datafile fno=00082 name=/oracle/O6S/sapraw/glenn15
channel ORA_DISK_15: starting piece 1 at 09-MAY-08
channel ORA_DISK_16: starting compressed full datafile backupset
channel ORA_DISK_16: specifying datafile(s) in backupset
input datafile fno=00083 name=/oracle/O6S/sapraw/glenn16
channel ORA_DISK_16: starting piece 1 at 09-MAY-08
channel ORA_DISK_17: starting compressed full datafile backupset
channel ORA_DISK_17: specifying datafile(s) in backupset
input datafile fno=00084 name=/oracle/O6S/sapraw/glenn17
channel ORA_DISK_17: starting piece 1 at 09-MAY-08
channel ORA_DISK_18: starting compressed full datafile backupset
channel ORA_DISK_18: specifying datafile(s) in backupset
input datafile fno=00085 name=/oracle/O6S/sapraw/glenn18
channel ORA_DISK_18: starting piece 1 at 09-MAY-08
channel ORA_DISK_19: starting compressed full datafile backupset
channel ORA_DISK_19: specifying datafile(s) in backupset
input datafile fno=00086 name=/oracle/O6S/sapraw/glenn19
channel ORA_DISK_19: starting piece 1 at 09-MAY-08
channel ORA_DISK_20: starting compressed full datafile backupset
channel ORA_DISK_20: specifying datafile(s) in backupset
input datafile fno=00087 name=/oracle/O6S/sapraw/glenn20
channel ORA_DISK_20: starting piece 1 at 09-MAY-08
channel ORA_DISK_2: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c2O6S_S_81_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:58
channel ORA_DISK_3: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c3O6S_S_82_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:58
channel ORA_DISK_4: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c4O6S_S_83_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:58
channel ORA_DISK_9: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c9O6S_S_88_P_1_T_654270133 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_9: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_11: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c11O6S_S_90_P_1_T_654270133 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_11: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_12: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c12O6S_S_91_P_1_T_654270133 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_12: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_13: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c13O6S_S_92_P_1_T_654270133 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_13: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_18: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c18O6S_S_97_P_1_T_654270134 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_18: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_20: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c20O6S_S_99_P_1_T_654270135 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_20: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_10: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c10O6S_S_89_P_1_T_654270133 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_10: backup set complete, elapsed time: 00:00:58
channel ORA_DISK_16: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c16O6S_S_95_P_1_T_654270134 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_16: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_1: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c1O6S_S_80_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:00
channel ORA_DISK_5: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c5O6S_S_84_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_5: backup set complete, elapsed time: 00:01:00
channel ORA_DISK_14: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c14O6S_S_93_P_1_T_654270134 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_14: backup set complete, elapsed time: 00:00:58
channel ORA_DISK_7: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c7O6S_S_86_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_7: backup set complete, elapsed time: 00:01:01
channel ORA_DISK_8: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c8O6S_S_87_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_8: backup set complete, elapsed time: 00:01:01
channel ORA_DISK_6: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c6O6S_S_85_P_1_T_654270132 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:01:04
channel ORA_DISK_15: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c15O6S_S_94_P_1_T_654270134 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_15: backup set complete, elapsed time: 00:01:02
channel ORA_DISK_17: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c17O6S_S_96_P_1_T_654270134 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_17: backup set complete, elapsed time: 00:01:02
channel ORA_DISK_19: finished piece 1 at 09-MAY-08
piece handle=/o6s_data/GLENNF/d2/backup_db_c19O6S_S_98_P_1_T_654270135 tag=TAG20080509T134205 comment=NONE
channel ORA_DISK_19: backup set complete, elapsed time: 00:01:01
Finished backup at 09-MAY-08

Configuration

  • T5240 - "Maramba" CMT based server
    • 2 x UltraSPARC T2 Plus @ 1.4GHz
    • 128GB memory
    • 2 x 1GB fiber channel HBA
    • 1 x 6140 Storage array with 1 lun per channel.
  • Software
    • Solaris 10 Update 5
    • Oracle 10.2.0.3
    • CoolTools

Monday May 12, 2008

Optimizing Oracle Schema Analyze with CMT based servers

A common observation regarding Niagara based servers is that system maintenance or database administration tasks can run slower than previous generations of Sun servers. While single-threaded performance may be less, these maintenance tasks are often able to be parallelized, especially using a database engine as mature as Oracle. Take for instance the task of gathering schema statistics. Oracle offers many options on how to gather schema statistics, but there are a few ways to reduce overall gather statistics time:
  • Increased Parallelism
  • Reduced Sample Size
  • Concurrency
Oracle has written many articles in metalink which discuss sample size and the various virtues. There have also been many volumes written on optimizing the Oracle cost based optimizer (CBO). Jonathan Lewis of who is a member of the famous Oaktable network has written books and multiple white papers on the topic. You can read these for insight into the Oracle CBO. While a reasonable sample size or the use of the "DBMS_STATS.AUTO_SAMPLE_SIZE" can seriously reduce the gather statistics times, I will leave that up to you to choose the sample size the produces the best plans.

Results

The following graph shows the total run time in seconds of a "GATHER_SCHEMA_STATS" operations at various levels of parallelism and sample size on a simple schema of 130GB. All tests were run on a Maramba T5240 with a 6140 array and two channels.

GATHER_SCHEMA_STATS parallelism and sample_size


Note that if higher levels of sampling are required, parallelism can help to significantly reduce the overall runtime of the GATHER_SCHEMA_STATS operation. Of course a smaller sample size can be employed as well.

GATHER_SCHEMA_STATS options

SQL> connect / as sysdba

-- Example with 10 percent with parallel degree 32
--
SQL> EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'GLENNF', 
     ESTIMATE_PERCENT=>10, 
     DEGREE=>32, 
     CASCADE=>TRUE);

-- Example with AUTO_SAMPLE_SIZE and parallel degree 32
--
SQL> EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'GLENNF', 
     ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, 
     DEGREE=>32, 
     CASCADE=>TRUE);

Note that you must have "parallel_max_servers" set to at least the level of parallelism desired for the GATHER_SCHEMA_STATS operation. I typically set it higher to allow for other parallel operations to get servers.

        SQL> alter system set parallel_max_servers = 128;

Finally, you can easily run a schema collect on multiple schema's concurrently and in parallel by issuing GATHER_SCHEMA_STATS from multiple sessions and ensuring the level of parallelism is set high enough to accommodate.

Configuration

  • T5240 - "Maramba" CMT based server
    • 2 x UltraSPARC T2 Plus @ 1.4GHz
    • 128GB memory
    • 2 x 1GB fiber channel HBA
    • 1 x 6140 Storage array with 1 lun per channel.
  • Software
    • Solaris 10 Update 5
    • Oracle 10.2.0.3
    • CoolTools
  • Schema
      SQL> Connected.
      SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4  
      OWNER	 TABLE_NAME	NUM_ROWS       MB
      -------- ------------ ---------- --------
      GLENNF	 B2	       239826150    38560
      GLENNF	 B1	       237390000    32110
      GLENNF	 S2		 4706245      750
      GLENNF	 S4		 4700995      750
      GLENNF	 S5		 4699955      750
      GLENNF	 S7		 4698450      750
      GLENNF	 S8		 4706435      750
      GLENNF	 S9		 4707445      750
      GLENNF	 S10		 4700905      750
      GLENNF	 S3		 4706375      750
      GLENNF	 GTEST		 4706170      750
      
      OWNER	 TABLE_NAME	NUM_ROWS       MB
      -------- ------------ ---------- --------
      GLENNF	 S6		 4700980      750
      GLENNF	 S1		 4705905      710
      HAYDEN	 HTEST		 4723031      750
      
      14 rows selected.
      
      SQL>   2    3    4  
      OWNER	 INDEX_NAME	NUM_ROWS       MB
      -------- ------------ ---------- --------
      GLENNF	 B1_I2	       244841720    11623
      GLENNF	 B2_I2	       239784800    11451
      GLENNF	 B1_I1	       248169793     8926
      GLENNF	 B2_I1	       241690170     8589
      GLENNF	 S6_I2		 4790380      229
      GLENNF	 S3_I2		 4760090      227
      GLENNF	 S2_I2		 4693120      226
      GLENNF	 S5_I2		 4688230      224
      GLENNF	 S8_I2		 4665695      223
      GLENNF	 S4_I2		 4503180      216
      GLENNF	 S1_I2		 4524730      216
      
      OWNER	 INDEX_NAME	NUM_ROWS       MB
      -------- ------------ ---------- --------
      GLENNF	 S9_I2		 4389080      211
      GLENNF	 S10_I2 	 4364885      209
      GLENNF	 S7_I2		 4357240      208
      GLENNF	 S2_I1		 4972635      177
      GLENNF	 S3_I1		 4849660      174
      GLENNF	 S6_I1		 4830895      174
      GLENNF	 S9_I1		 4775830      171
      GLENNF	 S7_I1		 4772975      169
      GLENNF	 S5_I1		 4648410      168
      GLENNF	 GTEST_C1	 4686790      167
      GLENNF	 S1_I1		 4661605      166
      
      OWNER	 INDEX_NAME	NUM_ROWS       MB
      -------- ------------ ---------- --------
      GLENNF	 S4_I1		 4626965      166
      GLENNF	 S10_I1 	 4605100      164
      GLENNF	 S8_I1		 4590735      163
      
      25 rows selected.
      

Wednesday Jan 16, 2008

Throughput computing series: System Concurrency and Parallelism

Most environments have some open source SW that is used as part of the application stack. Depending on the packages, this can take a fair amount of time to configure and compile. To speed the install process, parallelism can easily be used to take advantage of the throughput of CMT servers.

Let us consider the following five open source packages:
  • httpd-2.2.6
  • mysql-5.1.22-rc
  • perl-5.10.0
  • postgresql-8.2.4
  • ruby-1.8.6

The following experiments will time the installation of these packages in both a serial, parallel, and concurrent fashion.

Parallel builds

After the "configure" phase is complete, these packages are all compiled using gmake. This is where parallelism within each job can be used to speed the install process. By using the "gmake -j" option, the level of parallelism can specified for each of the packages. This can dramatically improve the overall compile time as seen below.

compile time \*without\* concurrency
    • Jobs were ran in a serial fashion but with parallelism within the job itself.
    • 79% reduction in compile time at 32 threads/job.

Concurrency and Parallelism

The build process for the various packages are not each able to be parallelized perfectly. In fact, the best overall gain of any of the packages is 6x out of 32. This is where concurrency comes into play. If we start all the compiles at the same time and use parallelism as well, this further reduces the overall build time.

compile times with concurrency and parallelism
    • All 5 jobs were run concurrently with 1 and 32 (threads/job).
    • 88% overall reduction in compile time from serial to parallel with concurrency.
    • 42% reduction in compile time over parallel jobs ran serially.

Load it up!

Hopefully, this helps to better describe how to achieve better system throughput through parallelism and concurrency. Sun's CMT servers are multi-threaded machines which are capable of a high level of throughput. Whether you are building packages from source or installing pre-build packages, you have to load up the machine to see throughput.

Monday Jan 14, 2008

Throughput computing series: Parallel commands (pbzip2)

In this installment of the throughput computing series, I will explore how to get parallelism from the system point of view. The system administrator who first begins to configure the system will start forming impressions from the moment the shrink wrap comes off the server. First impressions and potential parallel options will be explored in this entry.

Off with the shrink-wrap... on with the install

Unfortunately, most installation processes involve a fair number of single-threaded procedures. As mentioned before, the CMT processor is designed to be a processor that optimizes the overall throughput of a server - often to the detriment of single threaded processes. There are several schools of thought on this one. First is, why bother - the install process happens but once and it really doesn't matter. That is true for most typical environments. But the current trend toward grid computing and virtualization makes "time to provision" often a critical factor. To help speed provisioning, there are some things that can be done by using parallelized commands and concurrency.

pbzip2 to the rescue

A very common time-consuming part of provisioning is the packing/unpacking of SW packages. Commonly, gzip or bzip is used to unpack data and packages, but this is not a parallel program. Fortunately, there is a parallel version of bzip that has been made available. "pbzip2" allows you to specify the level of parallelism in order to speed the compression/decompression process.

I spent a little time experimenting with the pbzip program after repeated interactions that always seemed to come back to "gzip" performance. I decided to do some quick benchmarks with pbzip2 using both the T2000(8core@1.4GHz) and v20z(AMD 2cores@2.2GHz).

pbzip2 benchmark

The setup used a 135M text file. This file was the trade_history.txt created using the egen program distributed by the tpc council for the TPC-E benchmark. This file was compressed using the following simple test script:
    #!/bin/ksh
    
    for i in 1 2 4 8 16 32
    do
      print "pbzip2 compress: ${i} threads\\n" 
      timex pbzip2 -p${i} small.txt
      print "pbzip2 decompress: ${i} threads\\n" 
      timex pbzip2 -d -p${i} small.txt.bz2
    done
    
    
T2000 pbzip2 throughput T2000 pbzip2 throughput


At lower thread counts, the v20z with two AMD cores does better. This is expected since the AMD x64 processor is optimized single-threaded performance. But you can see as you crank up the thread count, the T2000 starts to really shine. This demonstrates my main point that to push massive throughput within a single application, you need lots of threads and parallelism.

    ...The next entry will explore how concurrency and parallelism can help improve build times.

Tuesday Jan 08, 2008

Throughput computing series: Defining Throughput Computing

This is the first installment in a series of entries that discuss different aspects of throughput computing. This series aims to improve the understanding of how SPARC CMT servers can be utilized to increase business throughput. Let's start with a definition.

What is throughput computing?

Oxford American defines "throughput" as:
    "The amount of materials or items passing through a system or process"
In computer terms, throughput computing is the amount of "work" that can be done in a given period of time. Things like "orders per second", "paychecks per hour", "queries per second", "webpages per minute",... are all metrics of throughput. These measures help define the amount of work a system can complete in a given period of time.

Misguided throughput metrics

  • Latency or Response time is not a throughput metric.
  • CPU % is not a throughput metric.
  • IO wait% is definitely not a throughput metric... or anything other than a measure of idle time :)
  • The "Load average" of a system is not a measure of throughput. OK... you get the idea.

Job level parallelism

Job level parallelism is about taking a single job and breaking it into multiple pieces. Say you have 10,000 letters to put stamps on. If it takes 3 seconds per letter, you would need 30,000 seconds or more than 8 hours to complete the task. Now consider you are a teacher and you bring the letters to class. There are 20 students in the class so each student will place stamps on 500 letters. With only 500 letters to complete per student, the job can be done in only 1500 seconds or 25 minutes.

In terms of throughput, one person processes one letter every 3 seconds or 60/3 = 20 letters per minute... and a class of 20 students can process 20\*20 = 400 letters per minute.

Concurrency

Concurrency comes from running multiple jobs or applications together on a system. A job may be single-threaded or use multiple threads of execution as discussed above. These jobs need not be related or even from the same application. To further increase concurrency, virtualization is often used to run multiple concurrent OS images on the same machine in-order to take advantage of modern multi-threaded systems.

Putting it all together with Chip Multi-Threading

Denis Sheanan sums up Sun's throughput computing initiative in his paper on CMT as:
    "Sun’s Throughput Computing initiative represents a new paradigm in system design, with a focus toward maximizing the overall throughput of key commercial workloads rather than the speed of a single thread of execution. Chip multi-threading (CMT) processor technology is key to this approach, providing a new thread-rich environment that drives application throughput and processor resource utilization while effectively masking memory access latencies."
The salient point is that you must have an application that has multiple threads of execution in-order to take advantage of CMT. Multiple threads of execution could come from a single job that has been parallelized or from multiple jobs of different types that run concurrently.

Resources

Monday Jan 07, 2008

Throughput computing series... getting the most of your SPARC CMT server.

I was thinking about the development of a CMT throughput benchmark, but it occurred to me that there are many \*good\* examples of throughput already out with the benchmarks we publish... just look at the bmseer postings on the Recent T2 results and the long line of performance records on the T2000.

The biggest disconnect with CMT servers is a misunderstanding of throughput and multi-threaded applications. I made a posting last year which touched on some initial impressions, but I thought it would be a good idea to dig in further.

This entry is to kick off a series of postings that explore different aspects of throughput computing in a CMT environment. The rough outline is as follows:
    Overview
    • Definition of Throughput computing, multi-threading, and concurrency.
    Explore system parallelism
    • Unix commands and parallel options
    • Concurrent builds/compiles.
    • Configuring the system for parallelism
    Configuring applications for parallelism
    • Concurrency vs multi-threading
    • Single-Threaded jobs
    Database parallelism with Oracle
    • Parallel loader and datapump
    • Index build parallelism
    • Concurrent processing in Oracle
    • Configuring Oracle for CMT servers
About

This blog discusses performance topics as running on Sun servers. The main focus is in database performance and architecture but other topics can and will creep in.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
News

No bookmarks in folder

Blogroll

No bookmarks in folder