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'm currently struggling with an MySQL Community Server 5.5.27 when inserting large numbers of rows into a table. We're running a 24 core Intel Xeon machine with an SSD software raid array. I did some optimizations in the InnoDB engine however I'm not having any luck with this. Currently running 1000 inserts takes about one minute to complete. As an example I'm pasting one of the inserts right here:

insert into pays(account,amount,pays_type, pays_date)values('TEST1234','123.12','TEST','2013-08-18');

I'm now running them in a transaction however that doesn't take any less than previous. Here are my current InnoDB variables:

+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 16777216               |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_instances    | 1                      |
| innodb_buffer_pool_size         | 53687091200            |
| innodb_change_buffering         | inserts                |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| 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  | 2                      |
| innodb_flush_method             | O_DIRECT               |
| innodb_force_load_corrupted     | OFF                    |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_large_prefix             | OFF                    |
| innodb_lock_wait_timeout        | 900                    |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 134217728              |
| innodb_log_file_size            | 268435456              |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 95                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 37                     |
| innodb_old_blocks_time          | 0                      |
| innodb_open_files               | 300                    |
| innodb_purge_batch_size         | 20                     |
| innodb_purge_threads            | 0                      |
| innodb_random_read_ahead        | OFF                    |
| innodb_read_ahead_threshold     | 56                     |
| innodb_read_io_threads          | 4                      |
| 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               | ON                     |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 0                      |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_native_aio           | ON                     |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 1.1.8                  |
| innodb_write_io_threads         | 4                      |
+---------------------------------+------------------------+

Below is the output from innodb status:

=====================================
130827 16:21:17 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 29 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 449259 1_second, 449258 sleeps, 44771 10_second, 1585 background, 1585 flush
srv_master_thread log flush and writes: 450861
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 403616, signal count 11625155
Mutex spin waits 54892232, rounds 34425387, OS waits 182759
RW-shared spins 3049147, rounds 7630454, OS waits 101966
RW-excl spins 1568770, rounds 16322062, OS waits 109616
Spin rounds per wait: 0.63 mutex, 2.50 RW-shared, 10.40 RW-excl
------------
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
670250 OS file reads, 16298342 OS file writes, 1049910 OS fsyncs
0.21 reads/s, 16384 avg bytes/read, 44.72 writes/s, 2.93 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 2406, seg size 2408, 31556 merges
merged operations:
 insert 36457, delete mark 186, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 106248889, node heap has 23130 buffer(s)
91197.17 hash searches/s, 3925.04 non-hash searches/s
---
LOG
---
Log sequence number 2358539707628
Log flushed up to   2358539697712
Last checkpoint at  2358538947912
0 pending log writes, 0 pending chkp writes
7906723 log i/o's done, 21.41 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 54945382400; in additional pool allocated 0
Dictionary memory allocated 4773737
Buffer pool size   3276799
Free buffers       2288715
Database pages     964954
Old database pages 356189
Modified db pages  510
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3010, not young 0
0.03 youngs/s, 0.00 non-youngs/s
Pages read 669662, created 382041, written 8188995
0.21 reads/s, 0.28 creates/s, 22.79 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 964954, unzip_LRU len: 0
I/O sum[1114]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 5690, id 140166740838144, state: sleeping
Number of rows inserted 111113612, updated 357096878, deleted 83276610, read 9212573076
6.83 inserts/s, 3.93 updates/s, 0.00 deletes/s, 95904.62 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Is there anything wrong with my configuration that would explain this behavior?

share|improve this question
1  
How much RAM does the machine have? And have you tried inserting (1000s) rows in one Insert statement? –  ypercube Aug 27 '13 at 15:41
    
My machine has 100GB of ram with an innodb-pool set to 54.95 gigabytes. Yes, I've tried without luck. Can you spot anything wrong with my current configuration? –  ralexandru Aug 28 '13 at 7:58
    
So, a single INSERT table VALUES (..), (..), ...(..); statement that inserts 1000 rows takes 1 minute? How did you try that? Through command line, GUI or with a program you wrote? And how much time does it take to insert more (say 1M) rows (with 1000 Insert statements)? –  ypercube Aug 28 '13 at 8:01
    
I've run the script using a single statement and everything is done in less than a second. –  ralexandru Aug 28 '13 at 10:44

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.