The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

small vps mysql optimization cannot minimize query cache prunes

Discussion in 'Workarounds and Optimization' started by amaliaf, Oct 18, 2012.

  1. amaliaf

    amaliaf Registered

    Joined:
    Oct 13, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    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

    Code:
    -------- 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

    Code:
    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.
     
    #1 amaliaf, Oct 18, 2012
  2. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    66
    cPanel Access Level:
    Root Administrator
    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. :)
     
    #2 srpurdy, Oct 23, 2012
    Last edited: Oct 23, 2012
(You must log in or sign up to post here.)
Loading...
Similar Threads - small mysql optimization
  1. Vincent-hg

    Cpanel SVG logo is to small (css rule)

    Vincent-hg, Nov 5, 2016, in forum: User Experience
    Replies:
    2
    Views:
    470
    cPanelMichael
    Nov 7, 2016
  2. UmairSYS

    Help with a small Script

    UmairSYS, Oct 30, 2016, in forum: Workarounds and Optimization
    Replies:
    4
    Views:
    259
    cPanelMichael
    Oct 31, 2016
  3. stingerman

    Backup File Size Is Small

    stingerman, Oct 3, 2016, in forum: Database Discussions
    Replies:
    1
    Views:
    358
    cPanelMichael
    Oct 6, 2016
  4. albatroz

    UID of script is smaller than min_uid

    albatroz, Mar 2, 2016, in forum: General Discussion
    Replies:
    1
    Views:
    849
    cPanelMichael
    Mar 2, 2016
  5. Droidism20011

    MySQL -> MariaDB security questions

    Droidism20011, Jul 20, 2017 at 2:21 PM, in forum: Security
    Replies:
    2
    Views:
    59
    cPanelMichael
    Jul 21, 2017 at 11:24 AM

Share This Page