Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

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 !!!

share|improve this answer
    
Thanks Rolando. I do remember that thread and things have gotten way better with those config changes from then. I do know for a fact that I hit about 2500 simultaneous connections during peak periods which is why I set it to 5000 to give me some growing room. I'll tune a little more with the info you gave me here and in the last thread. –  Logan Best Aug 25 '13 at 22:03
    
Wow, you are the same person from the first question. If these answers really helped, please mark them accepted by clicking the check mark. –  RolandoMySQLDBA Aug 25 '13 at 22:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.