MySQL / Fusion IO Configuration Question

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.

This is similar to the question I answered two months ago : MySQL using too much CPU

FusionIO tends to be CPU aggressive. You will have to fight back by

• Tuning InnoDB

• more read threads (innodb_read_io_threads), perhaps 16

• more write threads (innodb_write_io_threads), perhaps 16

• raising or lowering innodb_io_capacity to match IOPs of your FusionIO device

• raise the Log Buffer (innodb_log_buffer_size) to 1G

• raise the Log File (innodb_log_file_size) to 128M

• Converting as much of your MyISAM to InnoDB as possible because changes to .MYD are cached in the OS memory rather than any MySQL buffers

I would also lower max_connections to 2000 unless you know for a certainty to will get over 4500 simultaneous DB Connections. If you really need 5000, upgrade the VM's RAM to 48GB.

Give it a Try !!!

StackExchange Database Administrators Q#48698, answer score: 2