主页 / user-25964

Vitaly Karasik DevOps's questions

Martin Hope
Vitaly Karasik DevOps
Asked: 2017-01-14 02:43:26 +0800 CST
  • 0

我的客户在 Ubuntu16.04 上运行 MySQL 5.7.16 作为 Wordpress 站点的后端。大量繁重、糟糕的 SQL 语句。通过启用 MySQL 缓存，我能够显着改善情况，慢查询日志现在几乎是空的。但是 - 某些查询不使用缓存，我不明白为什么。SQL 使用两个非常静态的表，所以它不是缓存失效。知道我该如何调试吗？

TIA，维塔利

1) SHOW STATUS LIKE "qcache%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1700     |
| Qcache_free_memory      | 49555584 |
| Qcache_hits             | 17960465 |
| Qcache_inserts          | 6928571  |
| Qcache_lowmem_prunes    | 1850967  |
| Qcache_not_cached       | 924283   |
| Qcache_queries_in_cache | 13303    |
| Qcache_total_blocks     | 28441    |
+-------------------------+----------+

2）SQL示例：

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID 
FROM wp_posts  
  LEFT JOIN wp_postmeta 
    ON ( wp_posts.ID = wp_postmeta.post_id )  
  LEFT JOIN wp_postmeta AS mt1 
    ON (wp_posts.ID = mt1.post_id 
    AND mt1.meta_key = '_pagefrog_fbia_status' ) 
WHERE 1=1  
  AND (     ( wp_postmeta.meta_key = '_pagefrog_fbia_status' 
          AND wp_postmeta.meta_value = '1' )   
       OR     mt1.post_id IS NULL   
       OR   ( wp_postmeta.meta_key = '_pagefrog_fbia_status' 
          AND wp_postmeta.meta_value = '' ) ) 
  AND wp_posts.post_type = 'post' 
  AND ((wp_posts.post_status = 'publish')) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 10

附加物

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 | 83886080 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |    
+-----------------------------+----------+
mysql query-cache