1

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

Improve this question

1 Answer 1

Reset to default
0

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?

Improve this answer
4
  • I was thinking that the slowing down was due to the cache cause after a fresh restart of mysqld everthing is speedy for the next couple of days. As we move close to, lets say the thrid day, things slow down to a crawl and simple restarting that service brings back the speed. I have to put my fingure somewhere to start, and with the fact that this server slice is just a basic CentOs install with the added install of the mysql only that putting my fingure on the cache would be the first. It's a gut thing, not saying it's the right cause to the issue.
    – Quantum
    Commented Jul 3, 2012 at 19:59
  • Does turning off the query cache cause the same problem right away? Is there a correlation between the query cache becoming full and slowness? When things are slow do you see high cpu or i/o activity? Is there swapping happening? Anyting in your error log for that time? What does the processlist look like when things are slow? It may very well be the query cache, but a lot of things can cause slowness. I would recommend gathering as much info as you can during a normal ops and compare it to behavior when it is slow. Perhaps that will point you towards the problem.
    – drogart
    Commented Jul 3, 2012 at 20:27
  • So truning off the cache seems to speed it up.. with a cache like that who need speed :D .. so i made a few changes noted above.. I don't see high i/o or cup atm but I'll try to pin that info you asked about when it really slows down again.
    – Quantum
    Commented Jul 4, 2012 at 0:04
  • Oh.. so drogart, your right, some time the db start readying right and the app server is the one hanging. I has a memory leak and what was going on is that it was just holding up the db server an dmade it look like the root when it was not. thanks for pointing out that idea.
    – Quantum
    Commented Jul 13, 2012 at 22:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.