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

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?

Answer

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

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.