数据库服务器的query_cache优化以防止db减慢

Question

我有一个db服务器，它是一个512 db内存和20 db磁盘的云片。我想尽快运行，因为它是应用服务器上magento的db。我打开了query_cache显示

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)

那么，在大约3-5天之后，网站的速度会减慢。现在，如果我重新启动mysqld，它会加速，但在我看来，这不是件好事，而且我的query_cache没有设置为正确的方式。任何想法都可以做什么，不仅可以阻止它需要重新启动，而且调优服务器的最好的结果，我可以。另外请注意，它是CentOS6.0，唯一安装的是失聪和mysql，应用服务器通过它的eht1从同一个数据中心发出请求。

关于如何最好地设置这个问题有什么想法吗？谢谢-Jeremy

编辑，所以我想也许这里的一些其他信息可能会有所帮助

所以如果我做对了，我有99.999%的命中率

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)，给我们0.99999。这么说够大了？

切片细节

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

数据发布信息

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)

所以电流比是0.786%。还好吗？

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)

看看这是否更好

Answer 1

您没有正确计算所选的数量。使用“显示全局状态”(如“%com_select%”)。通过这样做，您只需要从会话中获得select计数(这就是为什么它是1)。

话虽如此，您为什么认为慢度问题与查询缓存有关？