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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Since I switched from MyISAM to InnoDB I have a high I/O.

I/O stats

The writes is a flat line, it seems there is a limit for them. This is my current mysql configuration:

bind-address =

tmp_table_size                 = 512M
max_heap_table_size            = 512M
query_cache_size               = 16M
query_cache_limit              = 1M
thread_cache_size              = 8
open_files_limit               = 8292
key_buffer                     = 512M
table_cache                    = 512M
sort_buffer_size               = 4M
read_buffer_size               = 4M
read_rnd_buffer_size           = 16M
table_open_cache               = 512M

query_cache_size = 16M


#disable federated by default

innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 128M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50


I have 2 cpu's and 4GB RAM. My database is around 35GB. What can I do to optimize my configuration? Buy more resources?

Let me know if I need te add more information about my server or configuration.

share|improve this question

Definitely more ram. Regardless how you configure it - and your cache limits are way too low configured - on a 4gb machine as dedeicated db server you never will have more than maybe 3.5gb cache. With a 35gb database - unless it is mostly data you dont like - that is a pretty crappy relationship (10:1) which means the cache wont really help a lot. Again - unless your data is mostly stale and rarely used.

With a databbase that small - and 35gb is quite tiny - I would try to keep 100% in cache.

But in your current case.... you only have 512mb cache permission, if I read that right. That is REALLY tiny given the database size.

Disclaimer: I am a SqlServer guy - not MySql, but physics and math is the same regardless of program. I regularly deal with non-small databases (100-1000gb) and up to very large databases - my record size is 21tb.

share|improve this answer

Your Answer


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.