small vps mysql optimization cannot minimize query cache prunes

[amaliaf]

I have a small vps with 768MB ram. No matter how much I increase query cache size I cannot minimize Query cache prunes per day and furthermore it continuously recommends to increase the table cache as well. Almost all tables in database are InnoDB.

I would really appreciate any suggestion to resolve this problem. I have tried many different my.cnf configurations but I keep having the same problem.

The output of mysqltuner is shown below

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.62-cll
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 7M (Tables: 1)
[--] Data in InnoDB tables: 178M (Tables: 341)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 46

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 9h 37m 44s (65M q [90.854 qps], 196K conn, TX: 100B, RX: 20B)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 538.0M global + 7.2M per thread (100 max threads)
[!!] Maximum possible memory usage: 1.2G (167% of installed RAM)
[OK] Slow queries: 0% (1K/65M)
[OK] Highest usage of available connections: 79% (79/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.4M
[OK] Key buffer hit rate: 99.9% (3M cached / 2K reads)
[OK] Query cache efficiency: 92.5% (59M cached / 64M selects)
[!!] Query cache prunes per day: 368690
[OK] Sorts requiring temporary tables: 0% (8 temp sorts / 981K sorts)
[OK] Temporary tables created on disk: 10% (159K on disk / 1M total)
[OK] Thread cache hit rate: 99% (79 created / 196K connections)
[!!] Table cache hit rate: 15% (1K open / 6K opened)
[OK] Open file limit used: 0% (13/3K)
[OK] Table locks acquired immediately: 99% (12M immediate / 12M locks)
[OK] InnoDB data size / buffer pool: 178.3M/196.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Increasing the query_cache size over 128M may reduce performance
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 196M) [see warning above]
    table_cache (> 1024)

The settings in my.cnf are shown below

max_connections=100
query_cache_size=128M
query_cache_limit = 148M
wait_timeout = 10
interactive_timeout = 50
table_cache = 1K
table_definition_cache = 4K
open_files_limit = 3K
innodb open files = 3K
long_query_time = 5
max_connections=90
innodb_buffer_pool_size = 196M
key_buffer_size=16M
key_buffer=16M
tmp_table_size=128M
max_heap_table_size = 128M

I had previously set in my.cnf increased values for query cache size = 196M and table_cache = 2048 but I still had the same problem.

Finally, I always get the recommendation to run optimize tables because my tables are fragmented but since they are InnoDB no matter how many times I run optimize tables I still have fragmented tables.

 

[srpurdy]

Don't use innodb

I know it's not exactly clear, but innodb is really only useful for many inserts or updates, and even than it's not ideal, and is very memory demanding. For a small VPS it's not really a good idea, and you seem to a large majority of Reads based on your mysqltuner report.

If the queries in the cache get invalidated too quickly this will increase your prunes every time. how your application is doing queries? is it well optimized? With a large set of data like you have 178M if the data changes the query is no longer valid from the cache.

For example though you could have say 25 queries all mixed and getting information, but you could maybe do those 25 queries in 2 or 3 if you write the application efficiently.

So the question is also are you having performance issues?

As for the table_cache you can try battling the table_cache and move it up to 6K but from performance tests it's proven to be a futile exercise. Your actually better off just ignoring it. and setting to 64 (lower than the 1K) you have now. This will drop your table_cache to 0 but it's actually faster. Only time it's useful to fight the table cache is if you can realistically get it to work.

You might also want to try adding this line.

query_cache_min_res_unit=2K //default is 4K

This will allow smaller queries into the cache. Otherwise they might not get cached at all and could reduce fragmentation.

But it's more likely the application isn't coded efficiently.

So what I would do to try to remove as many redundant queries as possible by combining them. Using multi-dimensional arrays in memory is a handy way of doing this, with more complicated queries and after all that hard work, see how much difference it makes.