MySQL Forums
Forum List  »  Performance

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
5099
January 27, 2014 10:11AM
1764
January 28, 2014 06:46PM
1186
January 30, 2014 08:31PM
1336
January 31, 2014 08:31PM
1057
February 07, 2014 11:00AM
1062
February 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.