I am doing load testing on my application using MySQL
server for the same. Here I am putting load of 400 users per hour for doing something on application. I have done some MySQL
tuning at my.conf
file.
I am using one application server (4GB RAM with dual core processor) and one database server (4GB RAM with dual core processor) and MySQL
version is 5.5.31-0ubuntu0.12.04.2-log
(Ubuntu).
Here are my settings:
bind-address = *
max_connect_errors=10000
myisam-recover = BACKUP
max_connections = 200
max_user_connections = 100
table_open_cache = 1000
table_definition_cache = 1000
tmp_table_size = 256M
flush_time = 0
key_buffer = 64M
max_allowed_packet = 32M
thread_stack = 192K
thread_cache_size = 16M
back_log = 1000
query_cache_limit = 64M
query_cache_size = 128M
connect_timeout = 15
interactive_timeout = 300
wait_timeout = 10
max_binlog_size = 100M
innodb_buffer_pool_size=2048MB
innodb_io_capacity=1000
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_additional_mem_pool_size=16M
innodb_lock_wait_timeout = 300
log_slow_queries=ON
long_query_time=3.000000
query_cache_type=OFF
slow_query_log=ON
log_output=TABLE
open_files_limit = 4000
max_allowed_packet = 16M
[isamchk]
key_buffer = 16M
One more point for about 30 mins when I see processlist it show me 13-20 connections but suddenly after 30 mins it increase to 140 and server become to unresponsive please help me to get rid from this.