1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Please help me improve MySQL settings

Discussion in 'Server Configuration and Hosting' started by Amin Sabet, Mar 4, 2015.

  1. Amin Sabet

    Amin Sabet Well-Known Member

    I know very little about MySQL and would greatly appreciate some help adjusting the settings in my.cnf.

    Everything's on one server. I don't have a separate server for MySQL. The server is "bare metal", not virtualized, and has the following specs:

    240 GB on-board SSD
    2TB "Nearline" SAS
    Intel Xeon E5-2640 v2 Processor
    RAM: 32 GB 1866 MHz DDR3

    Current my.cnf:

    Code:
    [mysqld]
    ft_min_word_len = 1
    ft_stopword_file = "/dev/null"
    tmpdir = "/tmp"
    skip-networking
    local-infile = 0
    back_log = 100
    symbolic-links=0
    table_open_cache=8192
    thread_cache_size=50
    max_connections=200
    innodb_buffer_pool_size=8192M
    query_cache_type=1
    query_cache_size=128M
    query_cache_limit=8M
    join_buffer_size=1M
    table_definition_cache=6384
    tmp_table_size=512M
    max_heap_table_size=512M
    innodb_thread_concurrency=0
    key_buffer_size=1024M
    max_allowed_packet=32M
    sort_buffer_size=2M
    read_buffer_size=2M
    read_rnd_buffer_size=8M
    myisam_sort_buffer_size=64M
    
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
    Output from Tuning-primer:

    Code:
            -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    
    MySQL Version 5.5.42-cll x86_64
    
    Uptime = 1 days 9 hrs 18 min 3 sec
    Avg. qps = 55
    Total Questions = 6683272
    Threads Connected = 2
    
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use 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 0 out of 6683293 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 = 50
    Current threads_cached = 22
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 200
    Current threads_connected = 2
    Historic max_used_connections = 24
    The number of used connections is 12% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    Current InnoDB index space = 673 M
    Current InnoDB data space = 2.37 G
    Current InnoDB buffer pool free = 75 %
    Current innodb_buffer_pool_size = 8.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 : 9.45 G
    Configured Max Per-thread Buffers : 2.58 G
    Configured Max Global Buffers : 9.14 G
    Configured Max Memory Limit : 11.72 G
    Physical Memory : 31.32 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 1.16 G
    Current key_buffer_size = 1.00 G
    Key cache miss rate is 1 : 5021
    Key buffer free ratio = 69 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 128 M
    Current query_cache_used = 95 M
    Current query_cache_limit = 8 M
    Current Query cache Memory fill ratio = 74.68 %
    Current query_cache_min_res_unit = 4 K
    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 = 8 M
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 1.00 M
    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 = 16594 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 = 8192 tables
    Current table_definition_cache = 6384 tables
    You have a total of 2015 tables
    You have 2710 open tables.
    The table_cache value seems to be fine
    
    TEMP TABLES
    Current max_heap_table_size = 512 M
    Current tmp_table_size = 512 M
    Of 100697 temp tables, 20% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 2 M
    Current table scan ratio = 96 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 6503
    Your table locking seems to be fine
    

    Output from mysqlmymonlite is attached (too long to post)
     

    Attached Files:

  2. hellreturn

    hellreturn Active Member

    Try
    key_buffer_size=1500M

    If you have enough RAM. You are almost reaching your buffer size limit.

    Set both to 256M and if you still have 20% tmp tables created on disk then you can save memory / process.
     
  3. Amin Sabet

    Amin Sabet Well-Known Member

  4. hellreturn

    hellreturn Active Member

  5. Amin Sabet

    Amin Sabet Well-Known Member

    @hellreturn - thanks, I just ran that and got the output below. I am not sure how to follow the recommendations though.

    Code:
    >>  MySQLTuner 1.4.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
    [OK] Currently running supported MySQL version 5.5.42-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 1G (Tables: 672)
    [--] Data in InnoDB tables: 2G (Tables: 1268)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 35M (Tables: 34)
    [!!] Total fragmented tables: 1173
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 12h 58m 21s (7M q [56.003 qps], 697K conn, TX: 123B, RX: 2B)
    [--] Reads / Writes: 62% / 38%
    [--] Total buffers: 9.6G global + 13.2M per thread (200 max threads)
    [OK] Maximum possible memory usage: 12.2G (39% of installed RAM)
    [OK] Slow queries: 0% (0/7M)
    [OK] Highest usage of available connections: 12% (24/200)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/1.2G
    [OK] Key buffer hit rate: 100.0% (710M cached / 133K reads)
    [OK] Query cache efficiency: 66.7% (2M cached / 4M selects)
    [!!] Query cache prunes per day: 117950
    [OK] Sorts requiring temporary tables: 0% (881 temp sorts / 282K sorts)
    [!!] Temporary tables created on disk: 26% (29K on disk / 110K total)
    [OK] Thread cache hit rate: 99% (24 created / 697K connections)
    [OK] Table cache hit rate: 65% (2K open / 4K opened)
    [OK] Open file limit used: 8% (1K/16K)
    [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
    [OK] InnoDB buffer pool / data size: 8.0G/2.4G
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 128M)
    
    "Temporary table size is already large - reduce result set size"

    How?

    "Reduce your SELECT DISTINCT queries without LIMIT clauses"

    How?

    "Variables to adjust: query_cache_size (> 128M)"

    Change to 256M?
     
  6. HWS

    HWS Well-Known Member

    I would convert all myisam tables to innodb and optimize the server for innodb only.

    You can't do much about the temporary tables, thats mostly a query issue (how the queries are written). XenForo is not perfect for that. But it also does not harm that much.

    Your query cache is small but also not very efficient. Try to raise it to 512 M or even a gig and look at Query cache efficiency again in a few days.

    Also your query_cache_limit is much to small for caching frequent XenForo results.
     
  7. Amin Sabet

    Amin Sabet Well-Known Member

    So, based on the recommendations so far, I should make the changes in red below? I altered a couple suggestions to make the changes smaller in magnitude.

    Code:
    [mysqld]
    ft_min_word_len = 1
    ft_stopword_file = "/dev/null"
    tmpdir = "/tmp"
    skip-networking
    local-infile = 0
    back_log = 100
    symbolic-links=0
    table_open_cache=8192
    thread_cache_size=50
    max_connections=200
    innodb_buffer_pool_size=8192M
    query_cache_type=1
    query_cache_size=256M
    query_cache_limit=16M
    join_buffer_size=1M
    table_definition_cache=6384
    tmp_table_size=256M
    max_heap_table_size=256M
    innodb_thread_concurrency=0
    key_buffer_size=1536M
    max_allowed_packet=32M
    sort_buffer_size=2M
    read_buffer_size=2M
    read_rnd_buffer_size=8M
    myisam_sort_buffer_size=64M
    
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
     
  8. HWS

    HWS Well-Known Member

    I doubt this will make any noticable difference. ;)
     
  9. Amin Sabet

    Amin Sabet Well-Known Member

    Is there something I can do with my.cnf settings that will make a noticeable difference?
     
    Last edited: Mar 4, 2015
  10. HWS

    HWS Well-Known Member

    The biggest problem is your myisam support. This is why it won't be possible to create a perfectly configured MySQL server. If you only run XenForo at your database server (with Enhanced Search), you can convert all tables to innodb without a problem. Without Enhanced Serach you have to be sure you run MySQL 5.6.4 or higher.

    If I were you and had to support both table types, I would just optimize the query cache (up to at least 1G and the limit depends on your board size, just test few values) and leave the rest as it is. I would prop. also raise innodb_buffer_pool_size to 16G (you have 32G RAM, if you ran innodb only, I would raise it to 24G and minimize all myisam memory settings).
     
  11. Deebs

    Deebs Well-Known Member

    Disagree with this. My experience with the query cache with tables where you get lots of updates, or on servers with lots of cores, is that it hinders performance. For any table that receives an update everything in the query cache for that table is flushed. It also does not scale with a single mutex across multiple cores. Finally, the overhead of managing a large query cache (in the hundreds of MB) negates any possible performance improvement.

    TL;DR

    The query cache does not improve performance by default, it can incur a performance penalty depending on your MySQL workload. Perform all tests with and without the query cache.

    http://dev.mysql.com/doc/refman/5.6/en/query-cache.html
    http://www.percona.com/blog/2015/01...works-and-workload-impacts-both-good-and-bad/
     
    hellreturn and HWS like this.
  12. HWS

    HWS Well-Known Member

    Correct. I also would not necessarily activate a query cache in an optimized MySQL server. Depends on the data you store and query. However it may make sense with XF where most work is read.

    To optimize a server you have to try and check many settings, before you may be satisfied for some time. ;)
     
    Last edited: Mar 4, 2015
  13. hellreturn

    hellreturn Active Member

    I keep query cache disabled on my servers since 50-80% of query results are different most of the time. Query cache can be helpful if you run wordpress or sites which would keep displaying same results to visitors.

    In forums data changes and hence I keep query cache disabled. No look up penalty.
     
  14. Amin Sabet

    Amin Sabet Well-Known Member

    Update:

    I deleted my non-Xenforo related databases (not needed anymore as all my active sites are now Xenforo).

    @MattW has installed Enhanced Search on all my sites and is converting my databases to MariaDB

    Everything about the move to XenForo has been one good thing after another!

    Thanks for the help!
     
    orange7 and MattW like this.

Share This Page