MySQL Forums :: Performance :: Slow insert rate in mysql innodb


Advanced Search

Slow insert rate in mysql innodb
Posted by: Dhinakaran Arumugam ()
Date: January 27, 2014 10:11AM

Im using MySQL server 5.5.27, for Linux (x86_64) using readline 5.1. Only one of the table heavily used to dump data. It has 3 indexes and paritioned on timestamp.
Inserts are done from java code, batch inserts, 3000 records per second. But, while monitoring mysql server using any profiling application, we could see inserts rate are not going beyond 2000 per second. Eventually application starts dropping records. Below are the VM configuration and my.cnf. Using InnoDB. I have tried all the configuration tuning, but could not increase insert rate more than 2000 inserts.
Kindly suggest will any configiuration change help to increase performacne of mysql server.

(Note, in same VM i'm running Java application which has 2G of ram allocated for JVM)
VM configuration is 32GB RAM, 4 CPUs. Here is my.cnf
[mysqld]

innodb_buffer_pool_size=24G
bulk_insert_buffer_size=3G
thread_cache_size=15000

thread_stack = 512K
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M
binlog_cache_size = 128K
query_cache_size = 64M
query_cache_limit = 512K
max_connections = 1000
max_connect_errors = 100
concurrent_insert = 2
connect_timeout = 30
max_allowed_packet = 32M
max_heap_table_size = 1G
bulk_insert_buffer_size = 1G
tmp_table_size = 1G
table_cache = 512
table_open_cache = 512
key_buffer = 1M
key_buffer_size = 128M
innodb_write_io_threads = 64
innodb_read_io_threads = 64
innodb_io_capacity = 2000000
#innodb_file_io_threads = 16
#innodb_log_files_in_group = 3
innodb_buffer_pool_instances = 8
innodb_additional_mem_pool_size = 4M
innodb_flush_log_at_trx_commit = 2

transaction-isolation=READ-COMMITTED
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=8
innodb_log_buffer_size=4M


max_prepared_stmt_count = 100000
lower_case_table_names=1
expire_logs_days=1
innodb_support_xa = 0
log_warnings=5

Options: ReplyQuote


Subject Views Written By Posted
Slow insert rate in mysql innodb 4701 Dhinakaran Arumugam 01/27/2014 10:11AM
Re: Slow insert rate in mysql innodb 1619 Rick James 01/28/2014 06:46PM
Re: Slow insert rate in mysql innodb 1310 Dhinakaran Arumugam 01/29/2014 11:49PM
Re: Slow insert rate in mysql innodb 1120 Rick James 01/30/2014 08:31PM
Re: Slow insert rate in mysql innodb 977 Dhinakaran Arumugam 01/30/2014 11:50PM
Re: Slow insert rate in mysql innodb 1230 Rick James 01/31/2014 08:31PM
Re: Slow insert rate in mysql innodb 813 Dhinakaran Arumugam 02/06/2014 07:28AM
Re: Slow insert rate in mysql innodb 1004 Rick James 02/07/2014 11:00AM
Re: Slow insert rate in mysql innodb 852 Dhinakaran Arumugam 02/11/2014 10:04PM
Re: Slow insert rate in mysql innodb 982 Rick James 02/12/2014 07:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.