Created: 2007-01-25
Last modified: 2007-05-30




Database Performance and Scalability with db_STRESS Benchmark @T2000






by Dimitri








Ces informations sont données à titre indicatif et n'engagent pas Sun Microsystems.

Table of contents



1. PREFACE
Started as a simple database engines comparison, this benchmark quickly out-passed initial test perimeter and was transformed into more general database scalability study. This is the Part-I.

Following benchmark results covering mixed subjects, combining:
     - MySQL MyISAM and InnoDB
     - PostgreSQL
     - Oracle 10g
     - ZFS (Sol10 11/06)
     - UFS buffered/forcedirectio
     - T2000
     - etc

This study was full of re-bound event and crazy surprises. I'm presenting all events here as they happened in chronological order, finding by finding, with all fixed and potential problems. Final results are very positive, but I always was most interested to know *how* people reaching the best possible performance level (being on the same initial conditions as others) - rather just a final numbers... Hope information presented in this document will be also useful for others and, if it'll wake up any new ideas - I'll be even more happy! :)

Have a fun reading! Any feedback - welcome!

Rgds,
-Dimitri


2. Benchmark Platform
Initially we simple bet: will T2000 (1200Mhz UST1 8cores) be able to keep the same workload (database) as V490 (4CPU US4+ 1800Mhz (bi-cores)), or not?.. The only real way to know is to test by ourselves :)) As there was no V490 server around, V890 with 4CPU disabled was ok. Then, to avoid any storage speculations, each server got connected 2x SE3510 boxes. One SE3510 configured in RAID5 and another one in RAID1...

3. Benchmark Overview

4. MySQL Benchmark
Just few points here before going further:

     - Interesting parameters to check:
          - thread_concurrency= ?? (post-note: very sensible for MyISAM)
          - innodb_thread_concurrency= ?? (post-note: very important for InnoDB)
          - innodb_file_io_threads= ?? (post-note: did not help)
          - innodb_flush_method= O_DIRECT (and avoid flush call, post-note: did not help)
          - innodb_flush_log_at_tx_commit= 0 (mixed with UFS Direct I/O = ON may be still acceptable)
          - tx isolation = READ-COMMITTED (post-note: did not help)
          - innodb_locks_unsafe_for_binlog (post-note: deprecated)

     - Absolutely to test MyISAM databases at least once with UFS Direct=ON to see its true writing capacity



5. PostgreSQL Benchmark
It was a long time I did not run any test on PostgreSQL, and it was a good occasion to replay the same test scenario on the latest PgSQL-v8.2.3. As well I was encouraged by the same benchmark article (http://tweakers.net/reviews/649/6) to see by myself how well PostgreSQL scalability is...



6. Oracle Benchmark
Speaking about databases, may we miss Oracle?.. No! :)))
But may we speak about?... No! :(((

[skip] Oracle results are removed due Oracle license restrictions [/skip]


7. SUMMARY-1 : db_STRESS@UFS
Why these graphs instead of final table with avg TPS grouped by test, etc. Well, as you may see on graphs - database performance is rarely staying on the same activity even generating workload is. So, saying just we have 1500 TPS in avg is only part of truth if TPS level jumping all the time between 1000 and 2000...

8. FINAL SUMMARY: db_STRESS@UFS - Apples to Apples
Due some further findings as well once have clear idea about deadlock origin, I've decided to replay ALL tests within the same test conditions and using the same injector code. The results are quite surprising, specially for MySQL...

9. Suspended Questions: More lights...
Few questions were still suspended or unclear at the end of tests, and before to free used hardware for another project, I got a time to run some additional tests to get more lights on following topics:

Well, time was limited, so there in no full answers :))