Posted by Yasufumi |
For the my previous post, there was comment to suggest to test db_STRESS benchmark on XtraDB by Dimitri. And I tested and tuned for the benchmark. I will show you the tunings. It should be also tuning procedure for general heavy writing workloads.
At first, <tuning peak performance>. The next, <tuning purge operation> to stabilize performance and to avoid decreasing performance.
<test condition>
Server:
PowerEdge R900, Four Quad Core E7320 Xeon, 2.13GHz, 32GB Memory, 16X2GB, 667MHz
db_STRESS:
32 sessions, RW=1, dbsize = 1000000, no thinktime
XtraDB: (mysql-5.1.39 + XtraDB-1.0.4-current)
innodb_io_capacity = 4000
innodb_support_xa = false
innodb_file_per_table = true
innodb_buffer_pool_size = 16G
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 128M
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_flush_method = O_DIRECT
(the followings are XtraDB specific general settings)
innodb_ibuf_active_contract = 1
innodb_adaptive_flushing = false
innodb_adaptive_checkpoint = estimate
<tuning peak performance>
At first, tuning the peak performance to use CPU and IO resource more effectively. To avoid mutex/lock contentions are good to use more CPU resource of many CPUs.
This graph shows the peak performance in tps of db_STRESS.
At current settings, "base" in the graph is the perfomance. We can confirm the mutex/lock contention roughly by the SEMAPHORES sction of SHOW INNODB STATUS output.
"xx-lock on RW-latch at 0x7f2ff40a3dc0 created in file dict/dict0dict.c line 1627"
It is index->lock, viewing the source file (and it may be HISTORY table). This is the lock for each index tree. We may be able to disperse the lock using by the partitioning of MySQL. Added the following clause to the HISTORY table definition.
"PARTITION BY HASH(REF_OBJECT) PARTITIONS 16"
Now the performance became to "+partitioned" in the graph. Looking the SEMAPHORES section again,
"has waited at handler/ha_innodb.cc line 7275 for 0.0000 seconds the semaphore:
X-lock on RW-latch at 0xd30320 created in file dict/dict0dict.c line 623"
may be the line which appears for the most times (it is dict_operation_lock). It may be partition specific lock contention. The current XtraDB has the variable to tune the contention.
innodb_stats_update_need_lock = 0 (default 1)
It skip the updating statistics which needs the lock. (it only affects for "Data_free:" value of TABLE STATS). And the performance became "+skip_stats" in the graph. Then, the next contention at SEMAPHORES section is...
"Mutex at 0x1b3e3e78 created file trx/trx0rseg.c line 167"
may be remarkable (it is rseg->mutex). The mutex is for each rollback segments, so we can increase the rsegs to solve the contention problem. XtraDB can increase the rseg.
innodb_extra_rsegments = 64 (affects to initialization of InnoDB)
Recreated database files with the parameter. Then the performance became "+rsegs64". At last, the next contention may be "Mutex at 0x28ce8e0 created file srv/srv0srv.c line 982". It is kernel_mutex, currently we don't have proper solution for that. The setting seems to be enough for now.
<tuning purge operation>
Next, looking the sequential result in more long term.
The next problem is "History list length" growing to huge size. The value is the number of entries in rollback segment. The entries are used for consistent reading of the older transactions. They can be removed when any transactions doesn't refer the entry. This removing operation for the entries is called "purge" in InnoDB. The purge operation should be done enough on time, because the huge history list affects to performance.
Basically, the purging is done by master_thread (general background thread of InnoDB). The huge history list makes the purge operation slow, and it interferes with the other tasks of the master_thread (e.g. flushing dirty blocks, treating insert buffer, etc...). Dimitri implemented a purge_thread to devote to the purging, and also XtraDB has similar purge_thread. Though it seems to make the throughput stabilize, it is not enough still for heavy update workloads. A single purge_thread on one CPU is not enough for updates from user threads on the all of other CPUs.
XtraDB can increase the purge_threads from the next release.
innodb_use_purge_thread = 4
seems to be enough for this workload on the server.
The first graph of followings is sequential throughput [tps] up to 3500 sec.
The next is tracking the "History list length" at the same time.
"Norm 1.0.4": Normal InnoDB Plugin 1.0.4 without XtraDB specific options
"xtra p_t 0": XtraDB 1.0.4-new (no purge_thread)
"xtra p_t 1": XtraDB 1.0.4-new (single purge_thread similar to Dimitri's)
"xtra p_t 4": XtraDB 1.0.4-new (4 purge threads)
The graphs show...
- The purge thread (> 0) helps to stabilize the throughput greatly.
- Increasing the purge threads can suppress the strong growing of the hitory list
- The adaptive checkpoint "estimate" needs the purge_thread... (than the adaptive_flushing does)
And the last 300secs' average tps are...
"Norm 1.0.4": 5725.47
"xtra p_t 0": 4699.33
"xtra p_t 1": 7130.3
"xtra p_t 4": 9118 (about 60%up from Normal Plugin 1.0.4)
In the end, the faster and more stable performance of db_STRESS benchmark is obtained by these tunings of XtraDB.
-----------------------------------------
(Added 2009.10.29)
<FAQ: Is XtraDB slower than Plugin?>
I'd like to say "no" to this question. We have been adding many tuning options to XtraDB. But they are effective not for all cases, sometimes the performance may get worse because of "not proper" or "too much value". We should choose the options correctly. XtraDB is based on InnoDB Plugin and we can set XtraDB same to InnoDB Plugin at least. The following graphs are results of XtraDB and Plugin with same options and same database.
top-left (same condition to above graphs):
innodb_flush_log_at_trx_commit = 2
innodb_doublewrite = true
top-right:
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = true
bottom-left:
innodb_flush_log_at_trx_commit = 2
innodb_doublewrite = false
bottom-right:
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = false
It seems that XtraDB is not slower than Plugin here at least.
We can start tuning based on these performances using XtraDB specific options!
Why do you make XtraDB slower than Plugin? 
del.icio.us
digg