Slow insert performance in mysql innodb table for solr indexing

Question

I have a Slave server (MySQL 5.5) having innodb tables. This server is being used for SOLR indexing. Few procedures used to fill 12 million data into 9 tables. The insertion speed is too slow.

Following are the InnoDb parameters.

| innodb_adaptive_flushing | ON |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_instances | 10 |
| innodb_buffer_pool_size | 12884901888 |
| innodb_change_buffering | all |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 0 |
| innodb_flush_method | |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_io_capacity | 200 |
| innodb_large_prefix | OFF |
| innodb_lock_wait_timeout | 180 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 104857600 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 90 |
| innodb_old_blocks_time | 100 |
| innodb_open_files | 300 |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 32 |
| innodb_read_io_threads | 64 |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_strict_mode | OFF |
| innodb_support_xa | OFF |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 32 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 1.1.8 |
| innodb_write_io_threads | 64 |

The OS is linux and physical memory is 20GB.

Please help.

Thanks Kuldeep

• JM Hicks: maybe you might try breaking the problem up into smaller parts (individual procedures & tables) and prioritizing your analysis by which ones take the most time, and then posting in more detail, in separate posts, on that smaller part of the problem. You posted helpful information about your server configuration but, if you have the ability to consider optimizations of procedure code, server calls, or indexing, these factors often have as well a very significant performance impact.
• JM Hicks: if you do decide to create new related posts please copy a link to the new posts in this post.

Answer 1

OBSERVATION #1

Larger transactions need a larger log buffer

Please expand innodb_log_buffer_size to 32M because as the MySQL Documentation says

The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB.

A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

OBSERVATION #2

You have innodb_thread_concurrency set to 32. It should be 0. It allows infinite concurrency. Even Percona Server has innodb_thread_concurrency set to 0 by default.

I have written about this in the past

• Is the CPU performance relevant for a database server? (Apr 26, 2012)
• Multi cores and MySQL Performance (Sep 20, 2011)
• Possible to make MySQL use more than one core? (Sep 12, 2011)
• About single threaded versus multithreaded databases performance (May 26, 2011)

OBSERVATION #3

Please Change the Following :

• innodb_flush_method to O_DIRECT. This may alleviate the OS of caching responsibility
• innodb_io_capacity to 5000 (You can set this to other value dynamically with SET GLOBAL innodb_io_capacity = 5000; or any other value)
• innodb_change_buffering to inserts to help InnoDB focus on INSERTs

• user10619: Actually transaction does not involve any BLOB kind of data. but filling 12 million data equally distributed into 9 tables it takes more than 24 hours.