Mysql – query_cache tuning for a database server to keep db from slowing

MySQL

I have a db server that that is a cloud slice of 512mb ram and 20gb disk. I wanted to make this run as fast as i can since it's the db for a magento insall on the app server. I turned on the query_cache showing

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 268435456 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.02 sec)

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 3         |
| Qcache_free_memory      | 175387552 |
| Qcache_hits             | 388427    |
| Qcache_inserts          | 41409     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 4912      |
| Qcache_queries_in_cache | 29960     |
| Qcache_total_blocks     | 60045     |
+-------------------------+-----------+
8 rows in set (0.01 sec)

So what happens it that after about 3-5 day the site slows down. now if I go and restart mysqld it speeds back up, but it seems to me that is not a good thing adn that i have the query_cache not set to it's right way. Any idea what is can do to not only stop it from needing the restart but to tune the server for the best results i can. Side note it's CentOs 6.0 and only thing installed is deafult and mysql with the app server making requests from the same datacenter via it's eht1.

any ideas on how to best set this up? thank you -Jeremy

EDIT
So i figured maybe some other info here may help

so if i get it right i have a 99.999% hit ratio

mysql> show status like 'qcache_hits';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Qcache_hits   | 388427 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 1     |
+---------------+-------+
1 row in set (0.00 sec)

qcache_hits / (qcache_hits + com_select) which gives us 0.99999. So its big enough?

Slice details

Technical Details 
RAM: 512 MB 
Disk Space: 20 GB 
Bandwidth In: 0.04 GB 
Bandwidth Out: 0.04 GB

my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

query_cache_size = 524288000
query_cache_type = 1
query_cache_limit=1048576

log-slow-queries=/var/log/mysqlslowqueries.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

UDPADTED INFO

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 268435456 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 5         |
| Qcache_free_memory      | 264569784 |
| Qcache_hits             | 10826     |
| Qcache_inserts          | 2706      |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 260       |
| Qcache_queries_in_cache | 1508      |
| Qcache_total_blocks     | 3118      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql> show status like 'qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 11027 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW global status LIKE '%com_select%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 2987  |
+---------------+-------+
1 row in set (0.00 sec)

So the current ratio is 0.786% .. still ok?

retuned

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 524288000 |
| query_cache_type             | DEMAND    |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)

to see if that is better

Best Answer

You are not calculating your number of selects correctly. Use 'show global status like '%com_select%' '. The way you did it you just get the select count from your session (which is why it is 1).

That being said, why do you think the slowness issue is related to query cache?

UPDATE 2012-09-06 10:10 EDT

Looking your latest updated info, you have query_cache_limit set to 1048576 (1M). This limits any result set to 1M. If you retrieve anything bigger, it will simply not be cached. While you have have query_cache_size set to 104857600 (100M), this only allows for 100 cached results set in a perfect world. If you perform hundreds of queries, fragmentation will come about rather quickly. You also have 4096 (4K) as the minimum size result set. Unfortunately, mysql has no internal mechanism for defragmenting the query cache.

If you must have the query cache and you have so much RAM, you could execute the following:

SET GLOBAL query_cache_size = 0;
SELECT SLEEP(60);
SET GLOBAL query_cache_size = 1024 * 1024 * 1024;

in order to purge the query cache. You lose all cached results, so run these lines during off-peak hours.

I would also assign the following:

query_cache_size = 1G
query_cache_limit = 8M

That leaves 23G of RAM. I would raise the following:

innodb_buffer_pool_size = 12G
key_buffer_size = 4G

That leaves 7G. This should be adequate for OS and DB Connections.

Keep in mind that the key buffer caches only MyISAM index pages, while the InnoDB Buffer Pool caches data and indexes.

One more recommendation: upgrade to MySQL 5.5 so you can configure InnoDB for multiple CPU and multiple threads for read/write I/O.

See my earlier posts on using MySQL 5.5 in conjunction with accessing multiple CPUs for InnoDB

Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Sep 20, 2011 : Multi cores and MySQL Performance
Jun 19, 2011 : How do I properly perform a MySQL bake-off?

UPDATE 2012-09-06 14:56 EDT

My method for clearing the query cache is rather extreme because it hoses cached data and forms a completely different segment of RAM. As you did point out in your comment, FLUSH QUERY CACHE (as you suggested) or even RESET QUERY CACHE would be better. For clarification, when I said "no internal mechanism," I meant exactly that. Defragmentation is needed and has to be done manually. It would need to be crontab'd.

If you do DML (INSERTs, UPDATEs, DELETEs) on InnoDB more often than on MyISAM, I would say remove the query cache altogether, which I said in the beginning.

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.