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

itemprop="text">

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 rel="noreferrer">Munin or rel="noreferrer">Cacti, to see what kind of workload I'm actually dealing
with. Personally, I have great experience with the MySQL-plugins provided with
Munin.