Best MySQL cache settings for 8gb RAM dedicated MySQL server using only InnoDB (5gb database)

Question

I'm a pretty big noob when it comes to setting up MySQL for performance. And honestly I'm not worried about the fine tuning to squeeze every last bit of performance out of MySQL, but I do know that the most important thing to do that provides some of the best results is setting up caches/buffers correctly.

I've tried to keep things simple by using only InnoDB as a storage engine. And I do have a dedicated server for MySQL. It has 8gb of RAM, how should I be allocating that to maximize performance? I'd like to be able to fit my entire database into memory for the best performance. The database is about 5gb. Is this possible?

How much memory should I allocate to the query cache? How much to the InnoDB buffer pool? How much for the rest of the computer (i.e. non MySQL related processes)? Etc.

Since I'm not using MyISAM I don't really need to put a lot of memory in the key cache correct?

Solution 1

This is hard without knowing much about the database itself. There are a few tools you should be aware of;

• mysqltuner.pl Github
• MySQL Tuning Primer Github

About storing the entire database in memory; Any queries that are doing changes on the database will remain open until the write is performed on the disk. The only thing that can avoid the disk to be a bottleneck, is a disk-controller with a write cache.

I would start with the following changes from the defaults:

key_buffer_size = 128M
thread_stack = 128K
thread_cache_size = 8
table_cache = 8192
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M

innodb_buffer_pool_size = 4G 

# This is crucial to avoid checkpointing all the time:
innodb_log_file_size = 512M

# If you have control on who consumes the DB, and you don't use hostnames when you've set up permissions - this can help as well.
skip_name_resolve

Then I'd see how things go, and try different things based on (among other things) the output of the tools mentioned above. I would also make sure to graph trends with a monitoring tool, such as Munin or Cacti, to see what kind of workload I'm actually dealing with. Personally, I have great experience with the MySQL-plugins provided with Munin.

Solution 2

IMHO you should be able to go with

innodb_buffer_pool_size=5G

That would be 62.5% of RAM with a sufficient amount of RAM for the Server OS plus memory for DB Connections

@kvisle recommended using mysqltuner.pl. That script is excellent for judging the amount of RAM to dedicate to join_buffer_size, sort_buffer_size, read_buffer_size, and read_rnd_buffer_size. Those 4 buffers added together are multiplied by max_connections. That answer is added to static buffers (innodb_buffer_pool_size + key_buffer_size). The combined sums are reported. If that combined sum exceeds 80% of RAM, that is when you must lower those buffer sizes. mysqltuner.pl will be very helpful in this regard.

Since all you data is InnoDB, you can make key_buffer_size (key cache buffer for MyISAM indexes) very low (I recommend 64M).

Here is a post I made in the DBA StackExchange to calculate a recommended size of innodb_buffer_pool_size.

UPDATE 2011-10-15 19:55 EDT

If you know you will have 5GB of data, then my first recommendation was OK. However, I forgot to add one thing:

[mysqld]
innodb_buffer_pool_size=5G
innodb_log_file_size=1280M

The log file size must be 25% of the InnoDB Buffer Pool

UPDATE 2011-10-16 13:36 EDT

The 25% rule is based strictly on using two log files. While it is possible to use multiple innodb log files, two usually works best.

Others have expressed using 25%

• http://lists.mysql.com/mysql/203879
• http://teaching-2.cs.uml.edu/MySQLdocs/MySQL_Manual/InnoDB_start.html
• http://www.issociate.de/board/post/395589/innodb_buffer_pool_size._How_to_setup_with_more_than_5G_?.html

However, in all fairness, someone from the original InnoBase Oy company expressed not using the 25% rule because of having larger InnoDB Buffers Pool.

Naturally, the 25% rule cannot work when having huge amounts of RAM. In fact, the largest innodb_log_file_size allowed using only 2 log files is 2047M, since the combined size of the log file must be less than 4G (4096M)

CASE IN POINT : One of my employer's clients has a DB server with 192GB RAM. There is no way to have 48G log files. I simlpy use the max file size for an innodb log file, 2047M. @Kvisle's comment on my answer simply gives a link stating you do not have to limit yourself to two log files. If you have N log files, they cannot total 4G. My 25% rule is just in a perfect world (DB Server with 8GB or less).

Comments

[billmalarky]

Thanks I'll give this a shot and see if I can learn something new.

[Ziron5]

@billmalarky Welcome to serverfault! :) Remember to upvote or mark the question as correct, if you're satisfied with the answer you got.

[RolandoMySQLDBA]

+1 for recommending mysqltuner.pl !!!

[billmalarky]

@Kvisle Thanks for the welcome! I've been around stackoverflow for a while (well... a few months that is...) but I'm new to serverfault. I actually host with voxel.net and their promanaged hosting (IE system support) is really great but I figured it would be appropriate to seek an outside view as well so I didn't sound like a complete tool when talking with them about this.

[billmalarky]

@Kvisle Also, I'll mark a correct answer but I do want this to stew for a bit to get a good brainstorm.

[billmalarky]

Thanks, I'll definitely be using mysqltuner.pl. However, I must ask, will that script still be accurate if my database is empty right now? I have all the tables and schema set up correctly, but no actual data. I used the 5gb as an example size that I would use on my current dedicated server (that is, once the DB gets any larger than that I will probably switch to a better dedicated server). Currently the database is empty (it is a brand new site that hasn't launched) but I want to tune mysql to handle scaling well pre-launch.

[RolandoMySQLDBA]

Updated my answer !!!

[Ziron5]

I want to comment on the "Must be 25% of the InnoDB Buffer Pool". Because it's not 100% true. Read dev.mysql.com/doc/refman/5.0/en/… for a more elaborate answer. Performance wise, it helps a lot to increase the size a bit from the default, but you don't have to max it.

[billmalarky]

Rolando, thank you for the help. I've bookmarked this thread as a reference. Also, I'm using that mysqltuner script now.

[billmalarky]

@Kvisle It only took me like a year, but I took your advice and marked this answer correct! Took me so long because I just plum forgot to do it, and just got a notification on this question that brought me back today.

[Zimano]

Wanted to add that changing innodb_log_file_size does not do much depending on how much is logged. The my.ini even says above that option: "As it is flushed once per second anyway, it does not make sense to have it very large"