How to set query_cache_size and join_buffer_size in my.cnf file?

Question

I have been monitoring my server the past two days and realised that 60% of my temp tables are stored on the hhd instead of memory. Also I ran MySQL Performance Tuning Primer script that adviced me to set the query_cache_size.

Now, to do this, I believe that I will need to set the query_cache_size and join_buffer_size in my.cnf

Unfortunately, I do not have any experience with editing the my.cnf file so I am hoping that someone can kindly assist me here. How do I set it, and what should I set it to?

my.cnf currently:

[mysqld]
set-variable = max_connections=500
safe-show-database
log_slow_queries = /var/log/mysql/log-slow-queries.log
slow_query_log = 1
long_query_time = 2
log-queries-not-using-indexes

Solution

The possible values (and other information) for the config are available in the MySQL reference guide. So, for example, if I wanted to add the two parameters you specified, your /etc/my.cnf config would resemble the following:

[mysqld]
set-variable = max_connections=500
safe-show-database
log_slow_queries = /var/log/mysql/log-slow-queries.log
slow_query_log = 1
long_query_time = 2
log-queries-not-using-indexes

# (default: 0)
thread_cache_size = 4
# Replaces table_cache in 5.1.3 (default: 64)
table_open_cache = 128
# (default: 0), disables cache
query_cache_size = 8388608

The two values I used were the defaults that MySQL uses if they are not specified. Be sure to specify your own values here!

UPDATE: MySQL's tech resources also has a great article on understanding the cache.

Comments

[LittleSnitch]

Thanks for the answer, is there any way that I can find what values I should set them to? It is obvious that they are set to too low currently.

[Andrew M.]

I added a great article to my answer, but you're definitely going to have to feel it out. Basically, you can check how many hits and misses you have on your query cache, and then tweak accordingly. It also depends on how much memory you have available.

[LittleSnitch]

Well im running it on a Xeon 7550 server with 32gb ram. I installed MySQLTuner and it told me to do this changes: query_cache_size (>= 8M) thread_cache_size (start at 4) table_cache (> 64) ... How do I add these changes to my.cnf? Could you kindly add these adjustments to your answer? That would be very helpful buddy.

[Andrew M.]

They have been added, but you will need to tweak them after monitoring them for performance.

[LittleSnitch]

Thank you very much, I will have a read through links to understand it better.