Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 Dec 12 '12 at 12:26
    
if you do decide to create new related posts please copy a link to the new posts in this post. –  JM Hicks Dec 12 '12 at 12:28
add comment

1 Answer

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

OBSERVATION #3

Please Change the Following :

share|improve this answer
    
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. –  user10619 Aug 3 '12 at 10:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.