MySQL High I/O. Wrong configuration? Since I switched from MyISAM to InnoDB I have a high I/O.



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

[mysqld]
bind-address = 127.0.0.1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

# CACHES AND LIMITS #
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

thread_concurrency=4
query_cache_size = 16M

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

#disable federated by default
skip-federated

innodb_file_per_table=1
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

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


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