Slow insert performance in mysql innodb table for solr indexing

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

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

StackExchange Database Administrators Q#21935, answer score: 3