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.

Sql tuner advise

Discussion in 'Workarounds and Optimization' started by Gauravk, Feb 2, 2012.

  1. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    I am new to sql tuning based on some online videos. Can anyone comment on the results below are good, bad or worst. I was at 1 gb ram for 7 months and recently increased to 2 gb. Is this increase was needed?

    My max connection ever spiked to 23 in last few months of monitoring, and server generally run 20 PHPBB forum and 1 drupal site.


    my.cnf present settings:


    [mysqld]
    set-variable = max_connections=100


    log-slow-queries
    safe-show-database
    max_allowed_packet=32M
    query_cache_size=128M

    query_cache_limit=2M

    tmp_table_size=64M

    max_heap_table_size=64M

    thread_cache_size=4

    table_cache=5124



    #### Per connection configuration ####

    sort_buffer_size=4M

    join_buffer_size=4M

    thread_stack=192K







    mysqltuner current report


    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 21h 21m 53s (455K q [5.925 qps], 27K conn, TX: 611M, RX: 52M)
    [--] Reads / Writes: 56% / 44%
    [--] Total buffers: 405.0M global + 8.6M per thread (100 max threads)
    [OK] Maximum possible memory usage: 1.2G (61% of installed RAM)
    [OK] Slow queries: 0% (0/455K)
    [OK] Highest usage of available connections: 11% (11/100)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/2.5M
    [OK] Key buffer hit rate: 98.5% (105K cached / 1K reads)
    [OK] Query cache efficiency: 57.6% (141K cached / 245K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts)
    [!!] Temporary tables created on disk: 28% (8K on disk / 28K total)
    [OK] Thread cache hit rate: 99% (80 created / 27K connections)
    [OK] Table cache hit rate: 99% (3K open / 3K opened)
    [OK] Open file limit used: 2% (286/10K)
    [OK] Table locks acquired immediately: 99% (188K immediate / 188K locks)
    [OK] InnoDB data size / buffer pool: 70.5M/200.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
    Variables to adjust:
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)


    Thanks in advance.
     
  2. 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
    We would need to know what MySQL version you are using, since /etc/my.cnf variables are version dependent where MySQL 5.1 has different settings for some values over MySQL 5.0
     
  3. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    MySQL 5.1

    * Event scheduler
    * Partitioning
    * Plugin API
    * Row-based replication
    * Server log tables



    * December 8, 2008



    * December 31, 2013
     
  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
    First of all, you'd want to revise these:

    Code:
    [mysqld]
    max_connections=100
    slow_query_log
    max_allowed_packet=32M
    query_cache_size=128M
    query_cache_limit=2M
    tmp_table_size=64M
    max_heap_table_size=64M
    thread_cache_size=4
    table_open_cache=5124
    
    #### Per connection configuration ####
    sort_buffer_size=4M
    join_buffer_size=4M
    thread_stack=192K
    I changed the following:
    set-variable = is deprecated and shouldn't be used
    log-slow-queries doesn't work under MySQL 5.1, it is slow_query_log instead for that variable
    safe-show-databases is already the default
    table_cache was renamed to table_open_cache in MySQL 5.1

    After fixing the incorrect values in /etc/my.cnf, then restart MySQL and wait 24 hours this time. Last time, the report was run with less than 24 hours based on this line in it:

     
  5. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Many Thanks Tristan, you are a star.

    Sorry for bugging again, i copied partial my.cnf, here is the full one having innodb details too. Could you please double check and comment again.


    [mysqld]
    set-variable = max_connections=100


    log-slow-queries
    safe-show-database
    max_allowed_packet=32M
    query_cache_size=128M

    query_cache_limit=2M

    tmp_table_size=64M

    max_heap_table_size=64M

    thread_cache_size=4

    table_cache=5124



    #### Per connection configuration ####

    sort_buffer_size=4M

    join_buffer_size=4M

    thread_stack=192K


    #######


    ##### INNODB Specific Options ######

    default_table_type=InnoDB

    innodb_buffer_pool_size=200M

    innodb_flush_log_at_trx_commit=0

    innodb_log_buffer_size=4M

    innodb_flush_method=O_DSYNC
     
  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
    The InnoDB ones would not be changed. Add those to the end of the file I already noted to be used, so basically add this section:

    Code:
    ##### INNODB Specific Options ######
    default_table_type=InnoDB
    innodb_buffer_pool_size=200M
    innodb_flush_log_at_trx_commit=0
    innodb_log_buffer_size=4M
    innodb_flush_method=O_DSYNC 
     
  7. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Great Tristan, many thanks for all this valued help. Really appreciate it.

    Will return in a days time and see what i get.

    Btw these Mysql max connection is counted per second or per Milli second.
     
  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
  9. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Thanks for the explanation over max connection, its more logical that it has to be anytime per max conn every second or MS it will only serve if its allowed. Does that also mean, that faster the sql behave more connections it can server and vice- versa???

    Here is the new config running, will see what recommendation i get after 24 hrs. Thanks.


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

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 43s (404 q [9.395 qps], 31 conn, TX: 286K, RX: 42K)
    [--] Reads / Writes: 48% / 52%
    [--] Total buffers: 405.0M global + 8.6M per thread (100 max threads)
    [OK] Maximum possible memory usage: 1.2G (61% of installed RAM)
    [OK] Slow queries: 0% (0/404)
    [OK] Highest usage of available connections: 3% (3/100)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/2.5M
    [!!] Key buffer hit rate: 90.5% (74 cached / 7 reads)
    [OK] Query cache efficiency: 49.5% (105 cached / 212 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10 sorts)
    [OK] Temporary tables created on disk: 20% (2 on disk / 10 total)
    [OK] Thread cache hit rate: 90% (3 created / 31 connections)
    [OK] Table cache hit rate: 90% (63 open / 70 opened)
    [OK] Open file limit used: 0% (29/10K)
    [OK] Table locks acquired immediately: 100% (243 immediate / 243 locks)
    [OK] InnoDB data size / buffer pool: 70.5M/200.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
     
  10. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi Tristan, here is the new recommendation by mysqltuner script. please comment



    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56-log
    [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: 6M (Tables: 94)
    [--] Data in InnoDB tables: 70M (Tables: 1581)
    [!!] Total fragmented tables: 1602

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

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 0h 10m 52s (458K q [5.263 qps], 26K conn, TX: 649M, RX: 50M)
    [--] Reads / Writes: 62% / 38%
    [--] Total buffers: 405.0M global + 8.6M per thread (100 max threads)
    [OK] Maximum possible memory usage: 1.2G (61% of installed RAM)
    [OK] Slow queries: 0% (7/458K)
    [OK] Highest usage of available connections: 10% (10/100)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/2.5M
    [OK] Key buffer hit rate: 98.8% (143K cached / 1K reads)
    [OK] Query cache efficiency: 56.0% (134K cached / 239K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts)
    [!!] Temporary tables created on disk: 33% (15K on disk / 45K total)
    [OK] Thread cache hit rate: 99% (36 created / 26K connections)
    [OK] Table cache hit rate: 99% (3K open / 3K opened)
    [OK] Open file limit used: 2% (286/10K)
    [OK] Table locks acquired immediately: 99% (172K immediate / 172K locks)
    [OK] InnoDB data size / buffer pool: 70.3M/200.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
     
  11. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Figured I'd wait a day to see if there was a response before I jumped in... no response, so here we go :)

    You probably won't notice much difference from optimization since your server has a very light load. However, MySQL is configured to use far too much RAM -- up to 61% of your total RAM. Please make the following changes:
    Code:
    sort_buffer_size=256K
    join_buffer_size=256K
    
    Even if mysqltuner.pl says you should increase those two variable further, in most cases you should not.

    You should also change:
    Code:
    thread_cache_size=12
    table_open_cache=4096
    tmp_table_size=96M
    max_heap_table_size=96M 
    
    Please delete thread_stack from your my.cnf since you are just setting it to the default value.

    Since you're primarily using InnoDB, you should look into using the InnoDB plugin. It is superior to MySQL's built-in InnoDB. Please run this command via SSH and confirm that the file is found:
    Code:
    ls -l /usr/lib/mysql/plugin/ha_innodb_plugin.so
    If the file is found, you may add the following to the top of your my.cnf:
    Code:
    plugin_dir=/usr/lib/mysql/plugin
    ####  InnoDB-Plugin SETTINGS NEED REMOVED BEFORE MySQL 5.5 UPGRADE!!!! ###
    
    ignore_builtin_innodb
    plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
    
    innodb_strict_mode      = 1
    ####  END InnoDB-Plugin #####
    Please note that the line that begins "plugin-load" is VERY long, and must be all on one line in your my.cnf. Make sure you get it all. Once you've enabled the plugin, you can also remove the setting for innodb_log_buffer_size, as your setting is smaller than the default for InnoDB-plugin.

    Finally, you have two settings that I question... Do you know why you set these two variables this way?:
    Code:
    innodb_flush_log_at_trx_commit=0
    innodb_flush_method=O_DSYNC
    Setting innodb_flush_log_at_trx_commit=2 and innodb_flush_method=O_DIRECT are more reliable options than your settings that still have performance benefits over the default settings. Since your load is very light, it is very doubtful you'll notice any difference in performance.

    Let me know if you have any questions.
     
  12. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Excellent and very detailed response Alphawaolf. Thanks a ton for explaining and writing for a layman knowledge level.

    sort_buffer_size=256K
    join_buffer_size=256K
    Supposed to be 1 meg by default, right? So i bumped to 2 and then 4 for fun only, as i am still preparing my server and want to try all settings before storm arise, hopefully next month. Usually for 20 PHPBB forum 1 mb sort would be enough, but i am just curious to know that why you recommend to 256k??? I also intend to run drupal (after few months) with lot of views and panel that might chew more ram, that why i increased the ram from 1 gb to 2 gb (May be i was wrong there, as its not needed yet.....lol)

    thread_cache_size=12
    table_open_cache=4096
    What does these two stuff do????

    ls -l /usr/lib/mysql/plugin/ha_innodb_plugin.so
    Is this update is safe to do on live server???

    innodb_flush_log_at_trx_commit=0
    This i learned in lullabot video saying delay of 1 sec in writing trx log boost performance, and its ok to do so if i am not running any banking or ecomm stuff.

    innodb_flush_method=O_DSYNC
    This i dont rem.
     
  13. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi Gauravk,

    The default for sort_buffer_size is 2M, and the default for join_buffer_size I believe is 128K. The reason I picked 256K for both is that MySQL uses a much slower method (about 37x slower) of memory allocation for buffers larger than 256K. Those two buffers are created quite frequently since they are per-connection buffers. Also, the join buffer can be allocated multiple times per connection (once per join per query), and unlike most buffers it is actually the minimum size rather than the maximum. For those reasons, it is better to set those buffers to no more than 256K unless you've got strong evidence that using larger buffers will actually improve performance with your load. Here is more good info on the sort_buffer_size: More on understanding sort_buffer_size « MySQL Expert | MySQL Performance | MySQL Consulting

    Having plenty of RAM is always a good thing. If you have spare RAM, Linux will cache many of the files it reads from the disk, including web pages, images, MySQL tables, etc. The more it caches, the less often it has to read from the disk, and the faster your server will seem. Reading from memory is of course many many times faster than reading from the disk.

    Creating threads to handle connections takes time. The thread_cache_size variable tells MySQL how many threads to keep in reserve so that new threads don't have to be created to handle new connections. Your server used a maximum of 11 concurrent connections in the data you provided, so I picked 12 for thread_cache_size. Basically, the first time a thread is created, it will be cached and reused for subsequent connections. You can see the effects of thread_cache_size in the mysqltuner.pl line that reads "Thread cache hit rate: 99% (36 created / 26K connections)".

    The table_open_cache tells MySQL how many tables to keep open... kinda. If a table is opened by multiple people, each instance of the open table counts as an "open table". Ideally the table_open_cache is large enough that when a query needs access to a table, MySQL will already have it open, and not have to close another table first. However, if the table_open_cache is too large, it actually slows down the table open/close process. Your mysqltuner output said you had 3k tables currently open, and it had only ever opened 3k tables (a good ratio). I reduced the table_open_cache to 4096 to get it closer to the actual number of tables you were using.

    The "ls -l /usr/lib/mysql/plugin/ha_innodb_plugin.so" command is safe to run. All the ls command does is list the contents of a directory. I only want to know if a specific file exists in the directory, so that's what everything after "ls" is. If it doesn't find the file, it will say "/bin/ls: /usr/lib/mysql/plugin/ha_innodb_plugin.so: No such file or directory". In that case, you cannot use the InnoDB-plugin until we find its location.

    I would set innodb_flush_log_at_trx_commit=2. The difference in performance between 0 and 2 is negligible, but when it is set to 0 then any crash of MySQL will lose up to 1 second of data. When set to 2, the entire operating system would have to crash for you to lose any data. Both 0 and 2 of course perform much better than the default of setting of 1.

    innodb_flush_method=O_DIRECT allows InnoDB to bypass the operating system cache when reading/writing data to/from the disk. InnoDB does a great job of optimizing I/O, so as long as you have the write-cache enabled on your RAID card, this is a nice fast option. The extra benefit is that it prevents the OS from buffering data that InnoDB is already buffering. That double-buffering wastes memory which could be used to buffer more of your static content like images.

    I hope that explains everything. When you get a chance, please run this command and let me know if the file is found or not:
    Code:
    ls -l /usr/lib/mysql/plugin/ha_innodb_plugin.so
     
  14. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Many thanks for your patience Alphawolf. Really thankful for your kind support and willingness to share your knowledge.


    I have run the ls line this is what it returned:

    ls -l /usr/lib/mysql/plugin/ha_innodb_plugin.so
    lrwxrwxrwx 1 root root 25 Dec 7 13:04 /usr/lib/mysql/plugin/ha_innodb_plugin.so -> ha_innodb_plugin.so.0.0.0*



    I also made the my.cnf changes below and they are live now:


    [mysqld]
    max_connections=100
    slow_query_log
    max_allowed_packet=32M
    query_cache_size=128M
    query_cache_limit=2M
    tmp_table_size=96M
    max_heap_table_size=96M
    thread_cache_size=12
    table_open_cache=4096

    #### Per connection configuration ####
    sort_buffer_size=256k
    join_buffer_size=256k
    thread_stack=192K


    ##### INNODB Specific Options ######

    default_table_type=InnoDB
    innodb_buffer_pool_size=200M
    innodb_flush_log_at_trx_commit=2
    innodb_log_buffer_size=4M
    innodb_flush_method=O_DIRECT




    Current mysqltuner.pl report, will check again in 24hrs for better recommendations.


    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56-log
    [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: 5M (Tables: 94)
    [--] Data in InnoDB tables: 75M (Tables: 1622)
    [!!] Total fragmented tables: 1643

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

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 7m 52s (1K q [3.822 qps], 137 conn, TX: 2M, RX: 205K)
    [--] Reads / Writes: 59% / 41%
    [--] Total buffers: 437.0M global + 1.1M per thread (100 max threads)
    [OK] Maximum possible memory usage: 543.2M (26% of installed RAM)
    [OK] Slow queries: 0% (0/1K)
    [OK] Highest usage of available connections: 6% (6/100)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/2.5M
    [OK] Key buffer hit rate: 96.9% (5K cached / 165 reads)
    [OK] Query cache efficiency: 39.3% (382 cached / 972 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 77 sorts)
    [OK] Temporary tables created on disk: 20% (15 on disk / 73 total)
    [OK] Thread cache hit rate: 95% (6 created / 137 connections)
    [OK] Table cache hit rate: 99% (1K open / 1K opened)
    [OK] Open file limit used: 2% (235/8K)
    [OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
    [OK] InnoDB data size / buffer pool: 75.5M/200.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
     
  15. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Before this script change my sites actual load time per my chrono was faster.

    Old timing:
    First load: less than 6 sec
    Consecutive load: 3 - 4 sec

    New timing
    First load: less than 10 sec
    Consecutive load: 6 - 8 sec

    May be its too early to comment/judge so giving bit of time to see, if after few hrs it improvise by building some cached data and load pages faster.
     
  16. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi Gauravk,

    In some situations setting innodb_flush_method=O_DIRECT can slow things down -- usually when your files reside on a SAN or when write-caching is disabled. If you can wait the full 24 hours to see if things improve, please do, as I would like to see what mysqltuner says. If not, go ahead and change that setting back and see what happens.

    How are you timing your pages? For the accurate results I generally use the "Net" tab of the Firebug add-on to Firefox. I believe the "Page Speed" and "YSlow" add-ons also have this function.

    Your server has the InnoDB-plugin, so it is safe to add this to your my.cnf:

    Code:
    plugin_dir=/usr/lib/mysql/plugin
    
    ####  InnoDB-Plugin SETTINGS NEED REMOVED BEFORE MySQL 5.5 UPGRADE!!!! ###
    
    ignore_builtin_innodb
    plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
    
    innodb_strict_mode      = 1
    ####  END InnoDB-Plugin #####
    Remember that the "plugin-load" line is very long, and MUST be all on one line. It is safe to switch to the InnoDB-plugin on a production server, as the plugin only changes how the data is processed and doesn't change the actual data. There are features of the InnoDB-plugin that could be enabled that would change the data, but none of them are enabled in the settings I provided.

    You can also remove the thread_stack and innodb_log_buffer_size settings. Your thread_stack is the same as the default, so there's not need for it to be there, and your innodb_log_buffer_size is smaller than the default when the InnoDB-plugin is active.
     
  17. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    I havent changed anything so far, i was waiting for your response. 24hr old mysql recommendations are here:
    Code:
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56-log
    [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: 6M (Tables: 94)
    [--] Data in InnoDB tables: 80M (Tables: 1622)
    [!!] Total fragmented tables: 1643
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 0h 2m 49s (591K q [6.829 qps], 36K conn, TX: 687M, RX: 70M)
    [--] Reads / Writes: 44% / 56%
    [--] Total buffers: 437.0M global + 1.1M per thread (100 max threads)
    [OK] Maximum possible memory usage: 543.2M (26% of installed RAM)
    [OK] Slow queries: 0% (0/591K)
    [OK] Highest usage of available connections: 10% (10/100)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/2.5M
    [OK] Key buffer hit rate: 99.3% (219K cached / 1K reads)
    [OK] Query cache efficiency: 62.9% (188K cached / 299K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
    [!!] Temporary tables created on disk: 29% (8K on disk / 29K total)
    [OK] Thread cache hit rate: 99% (10 created / 36K connections)
    [OK] Table cache hit rate: 99% (3K open / 3K opened)
    [OK] Open file limit used: 3% (288/8K)
    [OK] Table locks acquired immediately: 99% (256K immediate / 256K locks)
    [OK] InnoDB data size / buffer pool: 80.8M/200.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        tmp_table_size (> 96M)
        max_heap_table_size (> 96M)
    
     
  18. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    changed back to innodb_flush_method=O_DSYNC

    Also added that innodb plugin.

    Felt very little improvement in time, may be im reacting psychologically. Will check again and verify which flush method is better for my environment. Awaiting your comment on tunning.

    Thanks a lot.
     
    #18 Gauravk, Feb 6, 2012
    Last edited: Feb 6, 2012
  19. alphawolf50

    alphawolf50 Well-Known Member

    Joined:
    Apr 28, 2011
    Messages:
    186
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hi Gauravk,

    The changes we've made that mysqltuner reports on all show positive improvement. The only two it doesn't report on is innodb_flush_method and innodb_flush_log_at_trx_commit, so those will be the two to test.

    It looks like your server has both been more active and has had more writes in the last 24 hours. If tweaking the two settings above don't help, this fact could explain any slowdown you're seeing. Your mysqltuner from Feb-03 shows:
    The most recent shows:
    The important bits are highlighted, but for summary:
    • There are 10K more connections than previously
    • MySQL is receiving 20M more data than previously.
    • The % of writes has increased from 38% to 56%
    • The number of queries has increased from 458K to 591K.

    To be fair, your server still has a very light load so one wouldn't expect the modest increase in traffic to affect performance much. However, your read/write ratio has almost reversed itself (From 62%/38% to 44%/56%) which can have a greater effect. Writing to the database is always slower, and writes cause locks on rows and tables. If your select query needs to read a row that is being written, it has to wait for the write to complete before it can do so. So more writes = slower reads. And there's more! :)

    The query cache is single-threaded. While InnoDB does pretty well at concurrent reads/writes, the query cache does not. Whenever even one single value changes in a table, the query cache has to purge every query it has cached that references that table. While this is taking place, SELECT queries cannot retrieve results from the query cache, nor can they place new results in the cache. As you can imagine, the greater your ratio of writes to reads, the worse this situation becomes. So... what to do?

    If your read/write ratio stays as it is, you should look at lowering your query_cache_size, or even turning off the query cache completely. I realize that's a bit counter-intuitive. The larger your query cache, the longer it takes for MySQL to invalidate results, and the longer every other query has to wait before they can run. Basically, when you have a high ratio of writes to reads, the query cache turns MySQL into a slow single-threaded application.

    Alright, I realize I've given a lot of theory and not a lot of tuning advice, but you seem happy to learn so I'm happy to give what knowledge I have. For now, just try adjusting innodb_flush_method and innodb_flush_log_at_trx_commit to see if you get any improvement. If that doesn't help, try decreasing query_cache_size in 16M increments or even turning it off completely. Eventually you'll start seeing "Query cache prunes per day" increasing in your mysqltuner -- stop decreasing the query cache at that point and test out performance for a while. Then compare that to performance with the query cache turned off.

    You can also increase tmp_table_size and max_heap_table_size each to 128M to help lower the number of temp tables created on disk. Currently, 29% of your temp tables are created on disk. If increasing these values to 128M doesn't lower this percentage, then there will be no reason to increase these values further.

    Hope that helps! :)
     
  20. Gauravk

    Gauravk Well-Known Member

    Joined:
    Jan 23, 2012
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Like always, excellent insight on whats going on as if this is your own server. Really Appreciate it.

    Regards to read and write both days (shown above) seems to be one off those days. I was referring to my old report that has activity of few days and ballpark figure in long run would stay at level of Read (60%) and Writes (40%).

    I already turned innodb flush to sync and i feel it makes a difference.

    Do i reduce query_cache_size?
    Do i increase tmp_table_size and max_heap_table_size = 128M?

    Last 4hr codes are here,
    Code:
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4h 26m 54s (104K q [6.520 qps], 7K conn, TX: 133M, RX: 11M)
    [--] Reads / Writes: 52% / 48%
    [--] Total buffers: 444.0M global + 1.1M per thread (100 max threads)
    [OK] Maximum possible memory usage: 550.2M (26% of installed RAM)
    [OK] Slow queries: 0% (0/104K)
    [OK] Highest usage of available connections: 8% (8/100)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/2.5M
    [OK] Key buffer hit rate: 99.4% (73K cached / 423 reads)
    [OK] Query cache efficiency: 57.1% (32K cached / 57K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
    [OK] Temporary tables created on disk: 14% (425 on disk / 2K total)
    [OK] Thread cache hit rate: 99% (8 created / 7K connections)
    [OK] Table cache hit rate: 99% (1K open / 1K opened)
    [OK] Open file limit used: 2% (240/8K)
    [OK] Table locks acquired immediately: 99% (49K immediate / 49K locks)
    [OK] InnoDB data size / buffer pool: 80.8M/200.0M
    
    Thanks for being there and helping so much.
     
Loading...

Share This Page