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.

Advise with mysql optimization

Discussion in 'Workarounds and Optimization' started by johnburk, Nov 6, 2010.

  1. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    With one of the servers I have a strange problem with the optimization of mysql.

    Specially with

    query_cache_size and table_cache

    According to mysqltuner:


    Code:
    >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.91-community-log
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 253M (Tables: 1771)
    [--] Data in InnoDB tables: 208K (Tables: 13)
    [!!] Total fragmented tables: 2
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 4h 24m 22s (17M q [169.407 qps], 140K conn, TX: 2B, RX: 56M)
    [--] Reads / Writes: 77% / 23%
    [--] Total buffers: 843.0M global + 5.2M per thread (300 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.3G (41% of installed RAM)
    [OK] Slow queries: 0% (3/17M)
    [OK] Highest usage of available connections: 28% (87/300)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/98.3M
    [OK] Key buffer hit rate: 100.0% (706M cached / 344K reads)
    [OK] Query cache efficiency: 88.2% (13M cached / 15M selects)
    [!!] Query cache prunes per day: 40200
    [OK] Sorts requiring temporary tables: 0% (12 temp sorts / 172K sorts)
    [OK] Temporary tables created on disk: 10% (29K on disk / 288K total)
    [OK] Thread cache hit rate: 99% (87 created / 140K connections)
    [!!] Table cache hit rate: 0% (106 open / 20K opened)
    [OK] Open file limit used: 0% (170/20K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    [OK] InnoDB data size / buffer pool: 208.0K/1.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increasing the query_cache size over 128M may reduce performance
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 512M) [see warning above]
        table_cache (> 4096)
    
    The advise is to increase query_cache_size and table_cache. Yet it advises the same with a query_cache_size of 1024M and a table_cache of 128K!

    Here is my.cnf

    Code:
    [mysqld]
    delayed_insert_timeout=600
    flush_time=14400
    innodb_buffer_pool_size=1M
    join_buffer_size=2M
    key_buffer_size=128M
    local-infile=0
    log_slow_queries=/var/log/mysql-slow.log
    max_allowed_packet=60M
    max_heap_table_size=200M
    myisam_sort_buffer_size=6M
    open_files_limit=20K
    query_cache_limit=32M
    query_cache_min_res_unit=2048
    query_cache_size=512M
    read_buffer_size=1M 
    read_rnd_buffer_size=1M 
    safe-show-database
    set-variable = max_connections=300
    sort_buffer_size=1M
    table_cache=4K
    thread_cache_size=128
    thread_concurrency=4
    tmp_table_size=200M
    wait_timeout=1200
    interactive_timeout=2400
    query_cache_type=1
    
    [isamchk]
    key_buffer_size = 128M
    sort_buffer_size = 128M
    read_buffer_size = 2M
    write_buffer_size = 2M
    
    [myisamchk]
    key_buffer_size = 128M
    sort_buffer_size = 128M
    read_buffer_size = 2M
    write_buffer_size = 2M
    
    The server has 6GB of ram and although in the last 24h there have been a max of 87 connections. On a monthly basis the max connection reaches 200 to 260.

    I am doing something wrong, because the mysqltuner advise seems strange to me. Perhaps it is the flush, but I cannot figure it out.

    What do you guys think?
     
  2. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    Change it to:

    Table_Cache does not need a value after the end, If anything use M and not K, 4K is tiny, Probably an avater or something...

    query_cache_size
    This is extremely high, I reduced this in the above.. so just make the changes and restart MySql server then run the tuner again, Roughly 24 hours later.
     
  3. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    Thank you Gary,

    I see you changed

    query_cache_size=32M
    table_cache=4096

    Reducing the query_cache size create a lot of prunes per day. I have already tried running for 48h with 32M, 64M, 128M, 256M, 512M and 1024M and yet mysqltuner advises more.


    Most tutorials and optimization websites I read do not recommend a table_cache higher than 8096. Yet mysqltuner advises more. I have even tried 102400, yet it asks for more.
     
  4. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    It does not tell you to increase, It says what to adjust !

    Look at what it says:

    And you tried 1024MB, I would say thats a little extreme.

    It's like a circle, you increase 1 element then the next one needs increasing and such, Your best of starting fom minimal and working its way up, My my.cnf took over a week to get fully tuned, Its one thing that can take a long time to complete but by putting in high values will cause problems with others.
     
    #4 GaryT, Nov 6, 2010
    Last edited: Nov 6, 2010
  5. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    Actually the advise of mysqltuner is

    query_cache_size (> 512M)

    So increase > it to more than 512M

    With 32M within 1h and 22m I have

    Query cache prunes per day: 832337

    Mysql needs more cache to reduce number of prunes.
     
  6. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    Its telling you it's at large so why increase ? lol

    If your going against the app why use it as you cleary do not want to do what it suggests.. So Ill say no more now.
     
  7. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    I though it advised to increase the cache, but warns that >128M reduces performance.


    Do you another way to reduce query cache prunes per day without increasing the query_cache to >128mb?
     
  8. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    The warning.

    your setting:

    Thats what I would call, EXTREMELY HIGH , I would not go higher than 120M

    Also remove:

    This setting does not exist in Linux.

    Comment out:

    For memory consumption

    re-run your mysqltuner.pl and post the stats.
     
    #8 GaryT, Nov 8, 2010
    Last edited: Nov 8, 2010
  9. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    I changed query_cache_size to 120M and removed the rest as adviced.

    It has not been 24h yet, but here are my stats

    Code:
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 253M (Tables: 1741)
    [--] Data in InnoDB tables: 208K (Tables: 13)
    [!!] Total fragmented tables: 4
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 20h 51m 38s (9M q [140.104 qps], 61K conn, TX: 2B, RX: 3B)
    [--] Reads / Writes: 74% / 26%
    [--] Total buffers: 507.0M global + 4.6M per thread (300 max threads)
    [OK] Maximum possible memory usage: 1.8G (32% of installed RAM)
    [OK] Slow queries: 0% (6/6M)
    [OK] Highest usage of available connections: 26% (80/300)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/98.7M
    [OK] Key buffer hit rate: 100.0% (1B cached / 144K reads)
    [OK] Query cache efficiency: 84.4% (5M cached / 6M selects)
    [!!] Query cache prunes per day: 353858
    [OK] Sorts requiring temporary tables: 0% (23 temp sorts / 115K sorts)
    [OK] Temporary tables created on disk: 17% (43K on disk / 245K total)
    [OK] Thread cache hit rate: 99% (80 created / 61K connections)
    [!!] Table cache hit rate: 5% (114 open / 2K opened)
    [OK] Open file limit used: 0% (174/20K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 208.0K/1.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 120M)
        table_cache (> 8196)
    
    Andy advise on how I can reduce the Query cache prunes per day?
     
  10. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    And this is your current my.cnf ?

    Update to:

     
  11. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    I used your my.cnf and it has been 28h and there are still a lot of

    Code:
    [!!] Query cache prunes per day: 232646
    Tuning-primer says

    Code:
    However, 235154 queries have been removed from the query cache due to lack of memory
    [COLOR="DarkRed"]Perhaps you should raise query_cache_size[/COLOR]
    
    Most advice to limit query_cache to 128M are based on servers with 1GB ram. With 6GB of ram 256M should not be an issue. However I think my problem with this server is probably due to bad coding. So I'm going to look in to that.

    Anyone else have another point of view regarding tips to reduce query cache prunes per day?
     
  12. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    I don't get why you use flush, And if you need to why have:

    Thats, "Over The Top" is the word to decribe it ?

    Same goes with:

    Thiscan cause issues alone.

    I would honestly consider removing those.

    does not exist on linux ! Correct me if I'm wrong.

    Try the following:

     
Loading...

Share This Page