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.

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:

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

share|improve this question
add comment

1 Answer

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
add comment

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.