MySQL Write performance bottleneck

MySQL & MariaDB Other MySQL® Questions
1

MySQL Config: table_cache =1000
thread_cache =60
max_heap_table_size =8192M
join_buffer_size =256K
sort_buffer_size =256K
query_cache_size =512M
query_cache_limit =512M
key_buffer_size =8000M
bulk_insert_buffer_size =2000M
myisam_sort_buffer_size =8000M
tmp_table_size =8192M
myisam_repair_threads =5
innodb_open_files =10000
open_files_limit =10000
concurrent_insert =2
#innodb
innodb_flush_log_at_trx_commit =2
innodb_support_xa =0
innodb_buffer_pool_size =128G
innodb_buffer_pool_instances =8
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_log_buffer_size=256M
innodb_thread_concurrency=8
innodb_file_per_table innodb_stats_on_metadata =0
innodb_io_capacity=10000

mysql>[COLOR=#00008B]SELECT EVENT_NAME, SUM_TIMER_WAIT [COLOR=#00008B]FROM events_waits_summary_global_by_event_name [COLOR=#00008B]where event_name [COLOR=#00008B]not[COLOR=#00008B]like’%innodb%'[COLOR=#00008B]ORDER[COLOR=#00008B]BY SUM_TIMER_WAIT [COLOR=#00008B]DESC LIMIT 20;±------------------------------------------------------±----------------+| EVENT_NAME | SUM_TIMER_WAIT |±------------------------------------------------------±----------------+| wait/synch/cond/sql/MDL_context::COND_wait_status |247003180269200|| wait/synch/mutex/mysys/KEY_CACHE::cache_lock |225136157662400|| wait/io/[COLOR=#00008B]file/myisam/dfile |112586106594800|| wait/synch/cond/mysys/my_thread_var::suspend |36942967073600|| wait/io/[COLOR=#00008B]file/sql/[COLOR=#00008B]load|28575680068400|| wait/synch/mutex/sql/HA_DATA_PARTITION::LOCK_auto_inc |15671510821200|| wait/synch/rwlock/myisam/MYISAM_SHARE::key_root_lock |7580580832000|| wait/io/[COLOR=#00008B]file/sql/global_ddl_log |759675521600|| wait/io/[COLOR=#00008B]file/myisam/kfile |478606412800|| wait/synch/mutex/sql/LOCK_open |317343598000|| wait/io/[COLOR=#00008B]file/sql/FRM |281702406000|| wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock |237524998400|| wait/synch/mutex/mysys/THR_LOCK::mutex |148067737600|| wait/synch/cond/myisam/MI_SORT_INFO::cond |53355489600|| wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data |40690076800|| wait/io/[COLOR=#00008B]file/sql/slow_log |32342937200|| wait/io/[COLOR=#00008B]file/sql/[COLOR=#00008B]partition|32000921600|| wait/synch/cond/mysys/IO_CACHE_SHARE::cond |20865078800|| wait/synch/mutex/mysys/THR_LOCK_open |18582940800|| wait/synch/mutex/sql/THD::LOCK_thd_data |15040836800|±------------------------------------------------------±----------------+

mysql>[COLOR=#00008B]SELECT EVENT_NAME, SUM_TIMER_WAIT [COLOR=#00008B]FROM events_waits_summary_global_by_event_name [COLOR=#00008B]where event_name [COLOR=#00008B]like’%innodb%'[COLOR=#00008B]ORDER[COLOR=#00008B]BY SUM_TIMER_WAIT [COLOR=#00008B]DESC LIMIT 20;±----------------------------------------------±----------------+| EVENT_NAME | SUM_TIMER_WAIT |±----------------------------------------------±----------------+| wait/io/[COLOR=#00008B]file/innodb/innodb_data_file |161894926356400|| wait/synch/mutex/innodb/log_sys_mutex |78901379729200|| wait/io/[COLOR=#00008B]file/innodb/innodb_log_file |54154967108000|| wait/synch/mutex/innodb/log_flush_order_mutex |31350108798800|| wait/synch/rwlock/innodb/btr_search_latch |29658726116800|| wait/synch/mutex/innodb/buf_pool_mutex |26124486124000|| wait/synch/mutex/innodb/kernel_mutex |9542359283600|| wait/synch/mutex/innodb/trx_undo_mutex |4636908475600|| wait/synch/mutex/innodb/autoinc_mutex |2176569546400|| wait/synch/mutex/innodb/fil_system_mutex |1580851583600|| wait/synch/mutex/innodb/dict_sys_mutex |764196921200|| wait/synch/mutex/innodb/flush_list_mutex |493146643200|| wait/synch/mutex/innodb/ibuf_mutex |415302790000|| wait/synch/rwlock/innodb/dict_table_stats |346864396800|| wait/synch/mutex/innodb/mutex_list_mutex |341627459200|| wait/synch/rwlock/innodb/checkpoint_lock |263200847600|| wait/synch/mutex/innodb/rw_lock_list_mutex |247598360000|| wait/synch/mutex/innodb/trx_doublewrite_mutex |84526368000|| wait/synch/mutex/innodb/rseg_mutex |76199049600|| wait/synch/mutex/innodb/ibuf_bitmap_mutex |12117140800|±----------------------------------------------±----------------+

2

Hi archak;

I see you have innodb_io_capacity set to 10k. Is that something you’ve tested and found to be the max? Can’t speak for your specific drive config, but that seems low. Another one I would try is setting innodb_thread_concurrency to 0 to not limit the concurrency. Not sure what you’ve tried for innodb_write_io_threads, but I would try something around 8 if you have not gone that high. I would also look at innodb_purge_threads, and try setting that to 1 if you are on 5.5.x and maybe something larger like 2-4 if you are on 5.6.x.

I’d start with that and see where you go. The key will be to make small changes and really see the impact, as if you change to much at once you won’t have any idea what’s what (which I’m sure you are well aware of by this point).

-Scott

3

Thank you for your response Scott. We did try

4

Hi Archak;

I’d also try upping your innodb_io_capacity if you have not already; that will still likely be your biggest tuning knob.

As for your wait times, your largest one, wait/io/file/innodb/innodb_data_file, is waiting on an InnoDB tablespace file, which makes sense as it sounds like you are doing all of your heavy inserts into one table. So the fact that everything is happening that one table will hurt you.

Another possible option to test is setting innodb_autoinc_lock_mode = 2, but it has a lot of implications that make it a bit dangerous so research carefully:
[url]http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-configurable.html[/url]

You also mentioned the table had a primary key; does it have any other indexes or foreign keys? those would definitely slow down inserts, but you likely already took that into account.

-Scott

5

I did try increasing

So far, it seems like spawning multiple processes to load data (using LOAD DATA INFILE) seems to increase the ingestion rate (not linearly but substantially) but when running a single process seems to cap out at 20K rows (average innodb insertion rate over an hour). What I am trying to understand is why is limiting at 20K, what could be the bottleneck. What else do you recommend I should look at, I feel the multiple process approach is just a patch that will mask the real problem and the come back to bite us later on.