Skip to main content
Database Administrators

Return to Revisions

5 of 8
added 418 characters in body
Quantum
Quantum
  • 115
  • 4

query_cache tunning for a database server to keep db from slowing

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
Quantum
  • 115
  • 4