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:
[
[OK] Currently running supported MySQL version 5.1.62-cll
[OK] Operating on 64-bit architecture
[
[
[
[!!] Total fragmented tables: 33
[OK] All database users have passwords assigned
[
[
[
[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
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.
source
share