MySQL Query Cache: yes or no for a large platform for creating sites

Penetrating through so much information and conflicting MySQL query cache tips can be really huge and it's hard to say how it relates to my use case.

We have a large, customizable platform running on the LAMP stack that hosts many websites. Each website has its own content stored in a row of the table. For the most part, they are all in the same table. I read that all cached queries are invalid every time the table is updated, but I also read conflicting things about it.

Say someone visits website A and its contents are downloaded from the database and cached in the process. Another person visits right after, and the site loads faster for them because the data has been cached. Now the contents of website B are being changed, which is a row in the same table as website A. Are all cached data from website A now invalid? And if so, will we see an increase in performance by completely disabling the query cache?

I read about setting up the query cache and again, very overwhelming. I tried a few things, but it's hard to say what effect they had. Here is the current insert from MySQLTunerscript:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.62-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 1977)
[--] Data in InnoDB tables: 384K (Tables: 16)
[!!] Total fragmented tables: 33

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 1h 30m 33s (53M q [122.486 qps], 1M conn, TX: 125B, RX: 13B)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 8.1G global + 5.5M per thread (500 max threads)
[OK] Maximum possible memory usage: 10.8G (46% of installed RAM)
[OK] Slow queries: 0% (2K/53M)
[OK] Highest usage of available connections: 5% (28/500)
[OK] Key buffer size / total MyISAM indexes: 8.0G/1.2G
[OK] Key buffer hit rate: 99.7% (1B cached / 3M reads)
[!!] Query cache efficiency: 16.2% (6M cached / 41M selects)
[!!] Query cache prunes per day: 2869188
[OK] Sorts requiring temporary tables: 0% (11 temp sorts / 609K sorts)
[OK] Temporary tables created on disk: 0% (11K on disk / 2M total)
[OK] Thread cache hit rate: 99% (28 created / 1M connections)
[!!] Table cache hit rate: 1% (1K open / 88K opened)
[OK] Open file limit used: 3% (2K/65K)
[OK] Table locks acquired immediately: 99% (41M immediate / 41M locks)
[OK] InnoDB data size / buffer pool: 384.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_limit (> 6M, or use smaller result sets)
    query_cache_size (> 96M)
    table_cache (> 1024)

Thanks in advance for any advice.

+3
source share
2 answers

The answer will be very yes.

, query_cache_wlock_invalidate, , , MyISAM.

, 16% , , MyISAM , -. ! - , , - 128 , / SHOW GLOBAL STATUS LIKE '%qcache%' Qcache_free_memory: , , , InnoDB; , , , , .

0
0

All Articles