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.

MysqlTuner Suggestions

Discussion in 'Workarounds and Optimization' started by hostatree, Mar 24, 2011.

  1. hostatree

    hostatree Member

    Joined:
    May 6, 2009
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Hello, we have a user hosted on an XEN vps. He has use to 8 XEON cores, and 3gb ram. He runs a site that gets some good traffic and a lot of mysql connections on his 20gb mysql db. We have tried a lot of things to help his server run better, but we give up. Does anyone have any suggestions?
     
  2. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    Have you tried mysqltuner.pl and tuning-primer.sh

    Can you post their results?
     
  3. hostatree

    hostatree Member

    Joined:
    May 6, 2009
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    query_cache_size (>= 8M)
    sort_buffer_size (> 1M)
    read_rnd_buffer_size (> 256K)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)

    But when we add them, the load increases.
     
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    You would need to provide your current /etc/my.cnf settings along with the full output from mysqltuner.pl in order to receive valid assistance. In all instances, the existing /etc/my.cnf variables are needed and, without the full output from mysqltuner.pl, we don't even know your MySQL version. Some variables differ depending if you are using MySQL 5.0 or MySQL 5.1, and I am unable to provide advice unless I have that information.

    Next, you might have reached a point on your machine that tweaking variables won't further help in reducing load or memory. At that point, you need to begin looking at the slow query log and might even need to enable the general log to see which database(s) are the culprits for the usage. If you are at that point on the machine, this script helps to parse the log files to track the 10 highest database users and processes:

    http://hackmysql.com/mysqlsla

    I discuss how to install and use this script in my MySQL Optimization Presentation that I did at the cPanel conference last year:

    http://www.cpanel.net/videos/mysql-optimization/

    Finally, simply because MySQL might show high CPU doesn't mean it might be causing the load. It could be one specific account for some other script or process. While you only mention one site initially, I'm not certain if there might be more than one site on the machine and more than one database on the machine. If there really is only one database, then the log parsing will help to see the slow queries from that database and what needs fixed in it. You might need to index some of the queries that are taking a long time, or bring in a database administrator to help re-code it.
     
  5. hostatree

    hostatree Member

    Joined:
    May 6, 2009
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    #./mysqltuner.pl

    >> MySQLTuner 1.2.0 - 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
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 684M (Tables: 117)
    [--] Data in InnoDB tables: 16K (Tables: 1)
    [!!] Total fragmented tables: 1

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

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 48m 56s (9K q [3.294 qps], 761 conn, TX: 18M, RX: 1M)
    [--] Reads / Writes: 76% / 24%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (46% of installed RAM)
    [!!] Slow queries: 6% (646/9K)
    [OK] Highest usage of available connections: 10% (51/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/284.8M
    [OK] Key buffer hit rate: 98.1% (77M cached / 1M reads)
    [!!] Query cache is disabled
    [!!] Sorts requiring temporary tables: 251% (7K temp sorts / 2K sorts)
    [!!] Temporary tables created on disk: 47% (621 on disk / 1K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 12% (64 open / 515 opened)
    [OK] Open file limit used: 2% (105/4K)
    [!!] Table locks acquired immediately: 84%
    [OK] InnoDB data size / buffer pool: 16.0K/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Optimize queries and/or use InnoDB to reduce lock wait
    Variables to adjust:
    query_cache_size (>= 8M)
    sort_buffer_size (> 1M)
    read_rnd_buffer_size (> 256K)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)






    # cat /etc/my.cnf
    [mysqld]
    set-variable = max_connections=500
    log-slow-queries
    safe-show-databas
    tmpdir = /home/mysqltemp
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Please revise the /etc/my.cnf to the following values:

    Code:
    [mysqld]
    log-slow-queries=/var/lib/mysql/slow.log
    max_connections=500
    max_heap_table_size=40M
    query_cache_limit=1M
    query_cache_size=8M
    query_cache_type=1
    read_rnd_buffer_size=512K
    sort_buffer_size=2M
    table_cache=80
    thread_cache_size=4
    tmp_table_size=40M
    tmpdir = /home/mysqltemp
    First off, I removed safe-show-databases because that is now the default and not needed in /etc/my.cnf (since you have it misspelled, I'm wondering if you did copy and paste the content b/c I cannot believe that having it spelled as safe-show-databas misspelling wouldn't cause MySQL to balk on re-starting).

    Next, I added the path to the log for log-slow-queries as MySQL 5.0 cannot have that set blank to my knowledge. You need to set the path and then create the file, which you would then do with these steps:

    Code:
    cd /var/lib/mysql
    touch slow.log
    chown mysql:mysql slow.log
    chmod 660 slow.log
    Next, I changed the max_connections setting to be the right syntax. Yours had the old syntax.

    Finally, I added the variables suggested by mysqltuner.pl to those that I would use as slightly increased values when suggested. You would need to restart MySQL after doing the above steps, then you do need to give these 24 hours to run to see the results. The last run of mysqltuner.pl wasn't 24 hours between MySQL restarts per the output it provided:

     
  7. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    @cPanelTristan

    Very educating video!
     
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Thanks! I suppose I should explain that my first name was Danielle (Dani) when I made the video and I'm now going by Tristan. Long story, but again thanks :)
     
  9. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    Tristan, you are good in presenting technical information in a way that is really easy to understand. And I personally like how you are teaching how to fish, rather than giving a fish. This video should really be a sticky post on itself.

    TIL = MaxClients should be equal or more than mysql max_connections. I can't belive I have never known that in the 7 years I have had servers. Yet it is very logical!

    Do you have or know such a video/pdf/site with simular style explanation on how to optimize Apache? Do you know tools like mysqltuner.pl and tuning-primer.sh and other your mentioned for apache?


    p.s The graph in the PDF file is incorrect. Load 10, 11 and 6 are all on the same. Line.
    http://www.cpanel.net/2010_Slides/MySQL_Optimization.pdf
     
  10. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I don't know any Apache optimization presentations similar to the MySQL optimization one. I was actually slated to do an Apache optimization presentation as a backup one for the conference, but never had to give it. I might be giving one this year at the conference.
     
  11. hostatree

    hostatree Member

    Joined:
    May 6, 2009
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Hi guys, really appreciate the help! We applied those tweaks, and currently doing everything we can to keep the load below 200. So we will provide the results of mysqltuner in 12 more hours. We installed Tunner_Sh and got these results:

    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 10 sec.
    You have 462 out of 6342 that take longer than 10 sec. to complete
    Your long_query_time seems to be fine

    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See MySQL :: MySQL 5.0 Reference Manual :: 6.5 Point-in-Time (Incremental) Recovery Using the Binary Log

    WORKER THREADS
    Current thread_cache_size = 4
    Current threads_cached = 0
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine

    MAX CONNECTIONS
    Current max_connections = 500
    Current threads_connected = 340
    Historic max_used_connections = 340
    The number of used connections is 68% of the configured maximum.
    Your max_connections variable seems to be fine.

    INNODB STATUS
    Current InnoDB index space = 16 K
    Current InnoDB data space = 16 K
    Current InnoDB buffer pool free = 95 %
    Current innodb_buffer_pool_size = 8 M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory

    MEMORY USAGE
    Max Memory Ever Allocated : 1.03 G
    Configured Max Per-thread Buffers : 1.46 G
    Configured Max Global Buffers : 25 M
    Configured Max Memory Limit : 1.49 G
    Physical Memory : 3.86 G
    Max memory limit seem to be within acceptable norms

    KEY BUFFER
    Current MyISAM index space = 284 M
    Current key_buffer_size = 7 M
    Key cache miss rate is 1 : 84
    Key buffer free ratio = 0 %
    You could increase key_buffer_size
    It is safe to raise this up to 1/4 of total system memory;
    assuming this is a dedicated database server.

    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 8 M
    Current query_cache_used = 967 K
    Current query_cache_limit = 1 M
    Current Query cache Memory fill ratio = 11.81 %
    Current query_cache_min_res_unit = 4 K
    Your query_cache_size seems to be too high.
    Perhaps you can use these resources elsewhere
    MySQL won't cache query results that are larger than query_cache_limit in size

    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current read_rnd_buffer_size = 512 K
    On average 5 sort merge passes are made per sort operation
    You should raise your sort_buffer_size
    You should also raise your read_rnd_buffer_size

    JOINS
    Current join_buffer_size = 132.00 K
    You have had 0 queries where a join could not use an index properly
    Your joins seem to be using indexes properly

    OPEN FILES LIMIT
    Current open_files_limit = 2500 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine

    TABLE CACHE
    Current table_cache value = 80 tables
    You have a total of 135 tables
    You have 342 open tables.
    Current table_cache hit rate is 21%
    , while 427% of your table cache is in use
    You should probably increase your table_cache

    TEMP TABLES
    Current max_heap_table_size = 40 M
    Current tmp_table_size = 40 M
    Of 497 temp tables, 37% were created on disk
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    Note! BLOB and TEXT columns are not allow in memory tables.
    If you are using these columns raising these values might not impact your
    ratio of on disk temp tables.

    TABLE SCANS
    Current read_buffer_size = 128 K
    Current table scan ratio = 33596 : 1
    You have a high ratio of sequential access requests to SELECTs
    You may benefit from raising read_buffer_size and/or improving your use of indexes.

    TABLE LOCKING
    Current Lock Wait ratio = 1 : 2
    You may benefit from selective use of InnoDB.
    If you have long running SELECT's against MyISAM tables and perform
    frequent updates consider setting 'low_priority_updates=1'
    If you have a high concurrency of inserts on Dynamic row-length tables
    consider setting 'concurrent_insert=2'.
     
  12. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I'd presume that this script also uses MySQL information since the last restart, so it really makes little sense to use the values it has output at this time. Most of what it is saying is that you are fine for your existing settings you have now anyway. Run both of them after the 24 hour timeframe to get the results then.
     
  13. hostatree

    hostatree Member

    Joined:
    May 6, 2009
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    So we have made all the changes suggested in this thread. It seems to have not helped and actually maybe made things worse. So we have reverted back to the mysql default settings to help keep his site working. Below are the recommended changes before we did this though, ohh and we upgraded his VPS from 3gb to 6GB and it still was saying we should add more ram...


    ----------------------------------------------
    >> MySQLTuner 1.1.2 - 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
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 684M (Tables: 117)
    [OK] Total fragmented tables: 0

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 47m 26s (3K q [1.308 qps], 646 conn, TX: 6M, RX: 633K)
    [--] Reads / Writes: 82% / 18%
    [--] Total buffers: 138.0M global + 18.4M per thread (500 max threads)
    [!!] Maximum possible memory usage: 9.1G (156% of installed RAM)
    [OK] Slow queries: 5% (210/3K)
    [!!] Highest connection usage: 90% (452/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/281.2M
    [OK] Key buffer hit rate: 99.8% (91M cached / 176K reads)
    [OK] Query cache efficiency: 56.3% (1K cached / 2K selects)
    [OK] Query cache prunes per day: 0
    [!!] Sorts requiring temporary tables: 38% (67 temp sorts / 175 sorts)
    [!!] Temporary tables created on disk: 43% (98 on disk / 223 total)
    [!!] Thread cache hit rate: 24% (490 created / 646 connections)
    [OK] Table cache hit rate: 83% (511 open / 609 opened)
    [OK] Open file limit used: 13% (638/4K)
    [!!] Table locks acquired immediately: 50%
    [!!] Connections aborted: 12%

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Reduce or eliminate persistent connections to reduce connection usage
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Optimize queries and/or use InnoDB to reduce lock wait
    Your applications are not closing MySQL connections properly
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    max_connections (> 500)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    sort_buffer_size (> 8M)
    read_rnd_buffer_size (> 2M)
    tmp_table_size (> 70M)
    max_heap_table_size (> 70M)
    thread_cache_size (> 4)
     
  14. k-planethost

    k-planethost Well-Known Member

    Joined:
    Sep 22, 2009
    Messages:
    199
    Likes Received:
    4
    Trophy Points:
    18
    Location:
    Athens Greece
    dont expect from this script to do magic to myscl
    i have a default configuration max clients etc and 3-4 things more the most of net resourses that i used for triggering myscl server with the results of myscltuner after the restart the myscl server was crashing.
    apache optimizing will do some things also. personally max clients are equal with mysclconnections
    large scale forums community sites etc that may you have on the box you should get them on a vps or move them away.
    Of course that is a hasle with customers that pay 4 eyros per month and they think that they can use all resources of the server
    nice video with this http://hackmysql.com/mysqlsla can you see which table of a customer forum as an example cause load to the server?
     
  15. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    If optimizing MySQL doesn't help with the load or assist, then you need to follow the additional directions I provided initially:

    You might have one database or databases causing the issue that will become apparent if you use mysqlsla on the slow query log and enable the general log to see what databases there are the issue. If you aren't able to follow the steps to track down the user or cause, which might be a database that needs to be removed, moved, or improved, then I would highly suggest hiring someone who handles MySQL database administration to perform the task for you.
     
Loading...

Share This Page