I need a little configuration optimization. I think what I have is pretty good but feel like there's still room for improvement.
Current Config
[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#innodb
innodb_log_file_size = 256M
innodb_log_buffer_size = 32M
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout=50
innodb_file_per_table
innodb_buffer_pool_size=16G
innodb_buffer_pool_instances=8
innodb_io_capacity=10000
#eliminating double buffering
innodb_flush_method = O_DIRECT
flush_time=86400
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
#tmpdir=/dev/shm
#tmpdir=/usr/tmpfs
skip-name-resolve
#skip-locking
#safe-show-database
query_cache_limit=4M
query_cache_size=256M
sort_buffer_size=8M
read_rnd_buffer_size=1M
max_connections=5000
interactive_timeout=60
wait_timeout=300
connect_timeout=30
thread_cache_size=32
key_buffer=124M
tmp_table_size=4096M
max_heap_table_size=256M
join_buffer=16M
max_connect_errors=2000
table_cache=2048
thread_concurrency=12
long_query_time=5
log-slow-queries=/var/log/mysql-slow.log
#table_definition_cache=384
max_allowed_packet=1024M
#server-id=20
#log-bin=mysql-bin
#expire_logs_days=10
event_scheduler=ON
#master-host =
#master-user =
#master-password =
#master-port = 3306
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Dedicated Database server specs
- VMWare VM
- Dual Quad Xeon X5680 @ 3.33GHz (8 vCPU)
- 24gb RAM
- Fusion IO mounted on /var/lib/mysql
- Rough total of 500 tables between all databases
I know this kind of question really kind of requires eyes on the database server but I will gladly append information to this question as needed in order to get a much better configuration.
Thanks for the help in advance.
