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.

current my.cnf configuration

Discussion in 'Workarounds and Optimization' started by mark_rogers, Aug 19, 2013.

  1. mark_rogers

    mark_rogers Member

    Joined:
    Aug 19, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello cPanel expert community,

    I have a medium sized VPS on which I run 2 Magento installations, a few static pages as well as several WordPress / Joomla installation - all personal websites and overall nothing extremely fancy or with extreme traffic.

    My VPS Specifications:

    • Memory:12288 MB
    • CPU cores: 4 × 2.4 Ghz
    • OS: CentOS
    • Extras: WHM / cPanel
    Generally the speed on my sites was always there and I could not complain but recently I noticed websites loading slower, to be more specific it's all websites that require a MySQL database.

    I asked someone for advice and I was told it's most likely related to the growth of my databases and / or a bad my.cnf configuration file.

    The current my.cnf looks as follows:

    Code:
        [mysqld]
        open_files_limit=64000
        local-infile=0
        query_cache_size=512M
        query_cache_limit=2M
        default-storage-engine=MyISAM
        innodb_file_per_table=1
        thread_cache_size=4
        join_buffer_size=4M
        key_buffer_size=128M
        table_cache=640
        log-slow-queries=/home/mysql-slow-queries.log
        long_query_time=1
    I was recommended to run mysqltuner, which I did with the following output:

    Code:
        -------- General Statistics --------------------------------------------------
        [--] Skipped version check for MySQLTuner script
        [OK] Currently running supported MySQL version 5.5.32-cll
        [OK] Operating on 64-bit architecture
    
        -------- Storage Engine Statistics -------------------------------------------
        [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
        [--] Data in MyISAM tables: 165M (Tables: 1034)
        [--] Data in InnoDB tables: 92M (Tables: 1101)
        [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
        [--] Data in MEMORY tables: 372K (Tables: 55)
        [!!] Total fragmented tables: 25
    
        -------- Security Recommendations  -------------------------------------------
        [OK] All database users have passwords assigned
    
        -------- Performance Metrics -------------------------------------------------
        [--] Up for: 4h 39m 42s (571K q [34.034 qps], 15K conn, TX: 786M, RX: 113M)
        [--] Reads / Writes: 54% / 46%
        [--] Total buffers: 800.0M global + 6.6M per thread (151 max threads)
        [OK] Maximum possible memory usage: 1.8G (14% of installed RAM)
        [OK] Slow queries: 0% (2/571K)
        [OK] Highest usage of available connections: 7% (12/151)
        [OK] Key buffer size / total MyISAM indexes: 128.0M/40.3M
        [OK] Key buffer hit rate: 96.5% (548K cached / 18K reads)
        [OK] Query cache efficiency: 93.2% (440K cached / 472K selects)
        [OK] Query cache prunes per day: 0
        [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7K sorts)
        [!!] Joins performed without indexes: 946
        [OK] Temporary tables created on disk: 11% (2K on disk / 22K total)
        [OK] Thread cache hit rate: 99% (63 created / 15K connections)
        [!!] Table cache hit rate: 0% (634 open / 268K opened)
        [OK] Open file limit used: 1% (980/64K)
        [OK] Table locks acquired immediately: 99% (121K immediate / 121K locks)
        [OK] InnoDB data size / buffer pool: 93.0M/128.0M
    
    
        -------- Recommendations -----------------------------------------------------
        General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    
        Variables to adjust:
            join_buffer_size (> 4.0M, or always use indexes with joins)
            table_cache (> 640)
    
    After supplying this output I was recommended to apply the following changes:

    Code:
            thread_cache_size=16
            query_cache_size=1024M
            join_buffer_size=8M
            table_cache=4096
    My questions:

    1. Are the recommendations I was given safe? I did not apply them yet, but from what I read throughout other my.cnf related posts they seem a little bit over the top.
    2. I reckon that my current my.cnf file is rather "slim" if I am to compare it with other my.cnf files I've stumbled upon the web. Is there anything in particular that you think I am missing out?

    I am definitely not an expert or system administrator but merely an amateur, therefore it would be greatly appreciated if someone can share a little insight on how I can improve / enhance my current configuration.

    Thank you very much.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    648
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. mark_rogers

    mark_rogers Member

    Joined:
    Aug 19, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thank you very much Mr. Michael,

    I will see to report back once I have passed the hour mark.
     
  4. mark_rogers

    mark_rogers Member

    Joined:
    Aug 19, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Dear cPanel community and Mr. Michael,

    I have made a little further research and now also run tuning primer in addition to mysqltuner. I had MySQL now running for 48hours as per suggested by tuning primer in order to post the results / outcome:

    MySQL Tuner:

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 162M (Tables: 935)
    [--] Data in InnoDB tables: 87M (Tables: 758)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 372K (Tables: 38)
    [!!] Total fragmented tables: 42
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 1h 21m 45s (10M q [59.984 qps], 103K conn, TX: 16B, RX: 2B)
    [--] Reads / Writes: 60% / 40%
    [--] Total buffers: 800.0M global + 6.6M per thread (151 max threads)
    [OK] Maximum possible memory usage: 1.8G (14% of installed RAM)
    [OK] Slow queries: 0% (8/10M)
    [OK] Highest usage of available connections: 7% (11/151)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/32.0M
    [!!] Key buffer hit rate: 93.7% (1M cached / 113K reads)
    [OK] Query cache efficiency: 96.4% (9M cached / 9M selects)
    [!!] Query cache prunes per day: 27316
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 79K sorts)
    [!!] Joins performed without indexes: 3164
    [OK] Temporary tables created on disk: 9% (25K on disk / 266K total)
    [OK] Thread cache hit rate: 99% (587 created / 103K connections)
    [!!] Table cache hit rate: 0% (640 open / 2M opened)
    [OK] Open file limit used: 1% (1K/64K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 87.9M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Increasing the query_cache size over 128M may reduce performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 512M) [see warning above]
        join_buffer_size (> 4.0M, or always use indexes with joins)
        table_cache (> 640)
    
    Tuning Primer:

    Code:
            -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    
    MySQL Version 5.5.32-cll x86_64
    
    Uptime = 2 days 1 hrs 22 min 34 sec
    Avg. qps = 59
    Total Questions = 10660664
    Threads Connected = 4
    
    Server has been running for over 48hrs.
    It should be safe to follow these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is NOT enabled.
    Current long_query_time = 10.000000 sec.
    You have 8 out of 10660685 that take longer than 10.000000 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 http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 4
    Current threads_cached = 2
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 151
    Current threads_connected = 4
    Historic max_used_connections = 11
    The number of used connections is 7% of the configured maximum.
    You are using less than 10% of your configured max_connections.
    Lowering max_connections could help to avoid an over-allocation of memory
    See "MEMORY USAGE" section to make sure you are not over-allocating
    
    INNODB STATUS
    Current InnoDB index space = 60 M
    Current InnoDB data space = 87 M
    Current InnoDB buffer pool free = 0 %
    Current innodb_buffer_pool_size = 128 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 : 856 M
    Configured Max Per-thread Buffers : 1000 M
    Configured Max Global Buffers : 784 M
    Configured Max Memory Limit : 1.74 G
    Physical Memory : 11.73 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 32 M
    Current key_buffer_size = 128 M
    Key cache miss rate is 1 : 15
    Key buffer free ratio = 81 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 512 M
    Current query_cache_used = 471 M
    Current query_cache_limit = 2 M
    Current Query cache Memory fill ratio = 92.16 %
    Current query_cache_min_res_unit = 4 K
    However, 56183 queries have been removed from the query cache due to lack of memory
    Perhaps you should raise query_cache_size
    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 = 256 K
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 4.00 M
    You have had 3164 queries where a join could not use an index properly
    join_buffer_size >= 4 M
    This is not advised
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    
    OPEN FILES LIMIT
    Current open_files_limit = 64000 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_open_cache = 640 tables
    Current table_definition_cache = 400 tables
    You have a total of 1772 tables
    You have 640 open tables.
    Current table_cache hit rate is 0%
    , while 100% of your table cache is in use
    You should probably increase your table_cache
    You should probably increase your table_definition_cache value.
    
    TEMP TABLES
    Current max_heap_table_size = 16 M
    Current tmp_table_size = 16 M
    Of 241510 temp tables, 9% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 128 K
    Current table scan ratio = 240 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 29299
    Your table locking seems to be fine
    
    My current my.cnf file:

    Code:
        [mysqld]
        open_files_limit=64000
        local-infile=0
        query_cache_size=512M
        query_cache_limit=2M
        default-storage-engine=MyISAM
        innodb_file_per_table=1
        thread_cache_size=4
        join_buffer_size=4M
        key_buffer_size=128M
        table_cache=640
    
    It would be really greatly appreciated if someone can give me any suggestions or help me to apply a proper tuning / configuration on the current my.cnf file as I personally think there are a lot of things wrong / missing in it.

    Thank you very much.
     
    #4 mark_rogers, Aug 21, 2013
    Last edited: Aug 21, 2013
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    It looks ok, but few things:

    - your slow log is set to log queries taking longer than 10 s
    for performance optimization you should track all of them or at least 0.1 and then optimize them

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes

    - you got a lot of opened tables
    [!!] Table cache hit rate: 0% (640 open / 2M opened)
    even though you got less than 1k tables in MyISAM
    [--] Data in MyISAM tables: 162M (Tables: 935)

    That's becouse of queries using temporary tables (in memory or on disk)

    You can increase table_cache a bit, it's now called table_open_cache
    so instead of
    table_cache=640
    place
    table_open_cache = 1000

    and review queries with EXPLAIN query, to check if they use temporary tables and if they can be optimized
    especially those creating temp tables on disk (those are very slow, and doesn't scale much)
    [OK] Temporary tables created on disk: 9% (25K on disk / 266K total)
    those would be probably one of the slowest in slow query log

    add there in the end
    max_heap_table_size = 50M
    tmp_table_size = 50M

    to increase the size of temporary tables (in memory)


    - InnoDB buffer is almost full, can be increased a lot since you got much RAM
    innodb_buffer_pool_size = 1G

    - query cache doesn't scale well when set high, it's better to lower it, mysqltuner will display "suggestions" about it anyway
    query_cache_size=100M
    query_cache_limit=1M



    you don't have much traffic, so going into details with each mysql variable doesn't make much sense,
    the most can be done by tracking and optimizing your current queries (especially the ones creating temp tables on disk, or the ones not utilizing proper indexes), and that is thru slow query log

    so after you place above settings in my.cnf, please restart mysql, and it will create mysql-slow.log

    so that you can run this

    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest

    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

    and in slow.txt you will have a review of slow queries that should be checked and optimized


    If you are using custom code, the chances are some indexes needs to be created or some code/queries rewritten
    If you use CMS like wordpress/joomla/drupal etc. it's database structure is probably already optimized, but there might be plugins (written by many people around the world, many of them might not be aware of optimizations, or speed/scallability concerns when coding) that got inefficient queries, so they are the ones to be checked
     
    #5 thinkbot, Aug 21, 2013
    Last edited: Aug 21, 2013
  6. mark_rogers

    mark_rogers Member

    Joined:
    Aug 19, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Dear thinkbot,

    thank you very much for the kind message and thorough details, that's very much appreciated.
    Before I actually apply the new settings in the my.cnf file I just wanted to confirm / double check with you if everything seems to be okay. Here as follows:

    Code:
        [mysqld]
        open_files_limit=64000
        local-infile=0
        #query_cache_size=512M
        #query_cache_limit=2M
        query_cache_size=100M
        query_cache_limit=1M
        default-storage-engine=MyISAM
        innodb_file_per_table=1
        innodb_buffer_pool_size = 1G
        thread_cache_size=4
        join_buffer_size=4M
        key_buffer_size=128M
        #table_cache=640
        table_open_cache = 1000
        max_heap_table_size = 50M
        tmp_table_size = 50M
        slow_query_log=1
        slow_query_log_file=mysql-slow.log
        long_query_time=0.1
        log-queries-not-using-indexes
    
    Please kindly let me know and thank you so much for your help.
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    yes correct, you can also increase
    thread_cache_size=20
    and add

    read_rnd_buffer_size=4M

    then restart
     
  8. mark_rogers

    mark_rogers Member

    Joined:
    Aug 19, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thank you thinkbot - I have seen to implement all suggestions you have so kindly pointed out and for the time being, everything seems to be safe and working. I will report back here after 48 hours have past with related mysqltuner and tuning primer results.

    Thanks again for your suggestions!
     
  9. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    btw. for Magento, Wordpress and Joomla you should also check caching settings (in admin areas of those scripts), which will make great difference
    one more thing, which php handler you use ? suPHP ? mod_php ?

    also it's good to install WHM Munin plugin, to monitor server resources (CPU, Load etc)
     
  10. mark_rogers

    mark_rogers Member

    Joined:
    Aug 19, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello thinkbot, thanks for your reply.

    Most of my installations have cache settings enabled, for Magento I make additional use of memcached. In relation to PHP I use DSO as PHP5 handler.

    Best regards,
    Mark
     
  11. mark_rogers

    mark_rogers Member

    Joined:
    Aug 19, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello Thinkbot and cPanel community,

    here are my current results using the newly configured my.cnf configuration:

    Code:
    
    [mysqld]
    open_files_limit=64000
    local-infile=0
    query_cache_size=128M
    query_cache_limit=1M
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    innodb_buffer_pool_size=1G
    thread_cache_size=20
    read_rnd_buffer_size=4M
    join_buffer_size=4M
    key_buffer_size=128M
    table_open_cache=1000
    max_heap_table_size=50M
    tmp_table_size=50M
    slow_query_log=1
    slow_query_log_file=/home/mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes
    
    MySQLTuner results:

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 163M (Tables: 936)
    [--] Data in InnoDB tables: 83M (Tables: 759)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 372K (Tables: 38)
    [!!] Total fragmented tables: 29
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 19h 21m 45s (7M q [48.223 qps], 67K conn, TX: 12B, RX: 1B)
    [--] Reads / Writes: 66% / 34%
    [--] Total buffers: 1.3G global + 10.4M per thread (151 max threads)
    [OK] Maximum possible memory usage: 2.8G (24% of installed RAM)
    [OK] Slow queries: 1% (88K/7M)
    [OK] Highest usage of available connections: 7% (12/151)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/31.0M
    [COLOR="#FF0000"][!!] Key buffer hit rate: 93.8% (1M cached / 116K reads)[/COLOR]
    [OK] Query cache efficiency: 95.6% (6M cached / 7M selects)
    [COLOR="#FF0000"][!!] Query cache prunes per day: 88560[/COLOR]
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 72K sorts)
    [COLOR="#FF0000"][!!] Joins performed without indexes: 3277[/COLOR]
    [OK] Temporary tables created on disk: 9% (22K on disk / 231K total)
    [OK] Thread cache hit rate: 99% (12 created / 67K connections)
    [COLOR="#FF0000"][!!] Table cache hit rate: 0% (1K open / 1M opened)[/COLOR]
    [OK] Open file limit used: 2% (1K/64K)
    [OK] Table locks acquired immediately: 99% (941K immediate / 942K locks)
    [OK] InnoDB data size / buffer pool: 83.3M/1.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 128M)
        join_buffer_size (> 4.0M, or always use indexes with joins)
        table_cache (> 1000)
    
    Tuning Primer results:

    Code:
    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 0.100000 sec.
    You have 88357 out of 7536421 that take longer than 0.100000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    [COLOR="#FF0000"]The binary update log is NOT enabled.
    You will not be able to do point in time recovery[/COLOR]
    See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 20
    Current threads_cached = 9
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 151
    Current threads_connected = 3
    Historic max_used_connections = 12
    [COLOR="#FF0000"]The number of used connections is 7% of the configured maximum.
    You are using less than 10% of your configured max_connections.
    Lowering max_connections could help to avoid an over-allocation of memory
    See "MEMORY USAGE" section to make sure you are not over-allocating[/COLOR]
    
    INNODB STATUS
    Current InnoDB index space = 60 M
    Current InnoDB data space = 83 M
    Current InnoDB buffer pool free = 62 %
    Current innodb_buffer_pool_size = 1.00 G
    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.38 G
    Configured Max Per-thread Buffers : 1.52 G
    Configured Max Global Buffers : 1.26 G
    Configured Max Memory Limit : 2.79 G
    Physical Memory : 11.73 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 30 M
    Current key_buffer_size = 128 M
    Key cache miss rate is 1 : 16
    Key buffer free ratio = 81 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 128 M
    Current query_cache_used = 100 M
    Current query_cache_limit = 1 M
    Current Query cache Memory fill ratio = 78.19 %
    Current query_cache_min_res_unit = 4 K
    [COLOR="#FF8C00"]MySQL won't cache query results that are larger than query_cache_limit in size[/COLOR]
    
    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current read_rnd_buffer_size = 4 M
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 4.00 M
    You have had 3261 queries where a join could not use an index properly
    [COLOR="#FF0000"]You have had 16 joins without keys that check for key usage after each row
    join_buffer_size >= 4 M
    This is not advised[/COLOR]
    You should enable "log-queries-not-using-indexes" [B]<- I have this in my my.cnf though ?![/B]
    Then look for non indexed joins in the slow query log.
    
    OPEN FILES LIMIT
    Current open_files_limit = 64000 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_open_cache = 1000 tables
    Current table_definition_cache = 400 tables
    You have a total of 1774 tables
    You have [COLOR="#FF0000"]1000 open tables.[/COLOR]
    Current table_cache hit rate is[COLOR="#FF0000"] 0%[/COLOR]
    , while [COLOR="#FF0000"]100% [/COLOR]of your table cache is in use
    [COLOR="#FF0000"]You should probably increase your table_cache
    You should probably increase your table_definition_cache value.[/COLOR]
    
    TEMP TABLES
    Current max_heap_table_size = 50 M
    Current tmp_table_size = 50 M
    Of 209631 temp tables, 9% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 128 K
    Current table scan ratio = 244 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = [COLOR="#FF0000"]1 : 18134[/COLOR]
    Your table locking seems to be fine
    
    I have seen to highlight the issues reported by the related software in red. It would be greatly appreciated if someone can help me with this situation.

    Thank you very much and best regards,
    Mark
     
    #11 mark_rogers, Aug 23, 2013
    Last edited: Aug 23, 2013
  12. Rhuan

    Rhuan Active Member

    Joined:
    Nov 10, 2010
    Messages:
    39
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Brazil
    cPanel Access Level:
    Root Administrator
    Hi bro, I will help you :)

    Code:
    [!!] Total fragmented tables: 29
    Run mysqlcheck -Ao to optimize all databases and tables...

    Code:
    [!!] Key buffer hit rate: 93.8% (1M cached / 116K reads)
    You have to increase key_buffer_size...

    Code:
    [!!] Query cache prunes per day: 88560
    This is a high number, increase query_cache_size

    Code:
    [!!] Joins performed without indexes: 3277
    Increase join_buffer_size

    Code:
    [!!] Table cache hit rate: 0% (1K open / 1M opened)
    Increase table_cache

    Another tips...

    Code:
    innodb_buffer_pool_size=1G
    You can change for 512M or 256M because you have only 83.3M of InnoDB data...

    Code:
    open_files_limit=64000
    Change for 40000...
     
  13. mark_rogers

    mark_rogers Member

    Joined:
    Aug 19, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thank you Rhuan for getting back at me in relation to this situation, very much appreciated.

    Which values would you recommend for the items you've pointed out?

    See my current config for those items:

    key_buffer_size=128M
    query_cache_size=128M
    query_cache_limit=1M
    join_buffer_size=4M
    table_open_cache=1000

    I am clearly not an expert with these settings and would not want to crash my server.

    Thank you very much for your help,
    Mark
     
  14. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    I totally disagree with Rhuan,
    you can't base everything on mysqltuner readings and suggestions without understanding mysql variables and how it works

    there will almost always be some "fragmented" tables

    if you checked
    [OK] Key buffer size / total MyISAM indexes: 128.0M/31.0M
    you would see that key_buffer_size is well enough

    nope
    [OK] Query cache efficiency: 95.6% (6M cached / 7M selects)
    Current Query cache Memory fill ratio = 78.19 %
    No need to further increase query cache, it's better to prune than to make it too big (since query cache doesn't scale well in mysql)
    The best option is to add SQL_NO_CACHE to queries that often change, or that read from tables that are updated often
    In Magento you will have tons of queries using query cache, so don't really worry about it, don't make it too big.


    He gets info about queries not using indexes, so why you suggest to futher increase join_buffer_size ?
    it's already big 4MB
    If you want to fix this warning check the queries that doesn't utilize indexes, anyways, in most installations you will have those

    NO NO NO
    [--] Data in MyISAM tables: 163M (Tables: 936)
    There is less than 1k MyISAM tables
    The rest that are being open are temporary tables

    increasing mysql tables cache too much will decrease performance, table cache doesn't scale well, you should update to mysql 5.6 where there are multiple buffers for table cache

    and 12GB of RAM, so no need
    Current InnoDB buffer pool free = 62 %
    and innodb buffer is used in almost 40% even though he got 83MB of data only

    to sum all, you shouldn't base on your opitions on mysqltuner or other tools results, couse they only take numbers from mysql variables and show previously defined ratios

    I would suggest updating to Percona MySQL 5.6

    the rest is quite good, of couse it could be better but you would need to go into details, and this can't be done accurately thru forums when you don't have much mysql optimization/benchmarking experience
     
  15. Rhuan

    Rhuan Active Member

    Joined:
    Nov 10, 2010
    Messages:
    39
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Brazil
    cPanel Access Level:
    Root Administrator
    Hi thinkbot, thank you for your corrections I will read more about this :)
     
  16. mark_rogers

    mark_rogers Member

    Joined:
    Aug 19, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thank you both for contributing to this post, very much appreciated.

    Thinkbot, do you think I should leave all current values in the my.cnf or is there anything that you think I can further adjust / tweak?

    Thank you,
    Mark
     
  17. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    well, you can increase the table cache more to 4000
    table_open_cache = 4000

    but dont go over values like 8000+


    other than that,

    innodb_log_file_size = 50M

    but before restarting the mysql, you need to remove old innodb log files by
    rm -rf /var/lib/mysql/ib_logfile*

    then
    service mysql restart
     
  18. mark_rogers

    mark_rogers Member

    Joined:
    Aug 19, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thank you thinkbot for your expert insight and advises throughout the entire post - I very much appreciated your help.

    As per your suggestion I have seen to increase the table_open_cache value from 1000 to 4000

    One last question, you've mentioned: "it's better to prune than to make it too big (since query cache doesn't scale well in mysql)" - is there any specific command to get this done?

    Thank you,
    Mark
     
    #18 mark_rogers, Aug 24, 2013
    Last edited: Aug 25, 2013
  19. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    query cache is pruned/cleared automatically :)
     
Loading...

Share This Page