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

MySQL Tuning.

Discussion in 'Server Configuration and Hosting' started by Slavik, Jul 9, 2012.

  1. Slavik

    Slavik XenForo Moderator Staff Member

    Looking to optimise my server a little as i'm going to be moving a Megento store (or 3) onto it soon, already hosting several XenForo sites and several wordpress sites with no issues.

    Its Quad core with 12gb Ram.

    Current my.cnf

    Code:
    [mysqld]
    bind-address=127.0.0.1
    local-infile=0
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
     
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
     
    query_cache_type=1
    query_cache_size=64M
    table_open_cache=16384
    table_definition_cache=6384
    tmp_table_size=64M
    join_buffer_size=512k
    

    Mysql tuner

    Code:
     >>  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.1.61
    [OK] Operating on 64-bit architecture
     
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 614M (Tables: 1416)
    [--] Data in InnoDB tables: 1G (Tables: 2446)
    [--] Data in MEMORY tables: 1M (Tables: 42)
    [!!] Total fragmented tables: 2612
     
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
     
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 11d 7h 4m 57s (18M q [18.815 qps], 721K conn, TX: 160B, RX: 4B)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 449.2M (3% of installed RAM)
    [OK] Slow queries: 0% (171/18M)
    [OK] Highest usage of available connections: 29% (44/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/430.4M
    [OK] Key buffer hit rate: 95.9% (181M cached / 7M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (10 temp sorts / 1M sorts)
    [!!] Joins performed without indexes: 13899
    [!!] Temporary tables created on disk: 33% (239K on disk / 723K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 1M opened)
    [OK] Open file limit used: 0% (74/65K)
    [OK] Table locks acquired immediately: 99% (21M immediate / 21M locks)
    [!!] InnoDB data size / buffer pool: 1.6G/8.0M
     
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        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
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 64)
        innodb_buffer_pool_size (>= 1G)
    Mysql primer

    Code:
            -- MYSQL PERFORMANCE TUNING PRIMER --
                - By: Matthew Montgomery -
     
    MySQL Version 5.1.61 x86_64
     
    Uptime = 11 days 7 hrs 3 min 52 sec
    Avg. qps = 18
    Total Questions = 18359638
    Threads Connected = 7
     
    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.1/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 171 out of 18359659 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.1/en/point-in-time-recovery.html
     
    WORKER THREADS
    Current thread_cache_size = 0
    Current threads_cached = 0
    Current threads_per_sec = 3
    Historic threads_per_sec = 0
    Threads created per/sec are overrunning threads cached
    You should raise thread_cache_size
     
    MAX CONNECTIONS
    Current max_connections = 151
    Current threads_connected = 7
    Historic max_used_connections = 44
    The number of used connections is 29% of the configured maximum.
    Your max_connections variable seems to be fine.
     
    INNODB STATUS
    Current InnoDB index space = 343 M
    Current InnoDB data space = 1.59 G
    Current InnoDB buffer pool free = 0 %
    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 : 138 M
    Configured Max Per-thread Buffers : 415 M
    Configured Max Global Buffers : 17 M
    Configured Max Memory Limit : 433 M
    Physical Memory : 11.61 G
    Max memory limit seem to be within acceptable norms
     
    KEY BUFFER
    Current MyISAM index space = 430 M
    Current key_buffer_size = 7 M
    Key cache miss rate is 1 : 24
    Key buffer free ratio = 81 %
    Your key_buffer_size seems to be fine
     
    QUERY CACHE
    Query cache is supported but not enabled
    Perhaps you should set the query_cache_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 = 132.00 K
    You have had 13596 queries where a join could not use an index properly
    You have had 303 joins without keys that check for key usage after each row
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.
     
    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.
     
    OPEN FILES LIMIT
    Current open_files_limit = 65535 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 = 64 tables
    Current table_definition_cache = 256 tables
    You have a total of 3927 tables
    You have 64 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 484516 temp tables, 33% 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 = 20 : 1
    read_buffer_size seems to be fine
     
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 69809
    Your table locking seems to be fine

    Proposed new my.cnf

    Code:
    [mysqld]
    bind-address=127.0.0.1
    local-infile=0
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    table_open_cache=8192
    thread_cache_size=4
    max_connections=200
    innodb_buffer_pool_size=4096M
    query_cache_type=1
    query_cache_size=256M
    query_cache_limit=8M
    join_buffer_size=1M
    table_definition_cache=6384
    tmp_table_size=512M
    max_heap_table_size=512M
    innodb_thread_concurrency=10
    key_buffer_size=512M
    max_allowed_packet=32M
    sort_buffer_size=64M
    read_buffer_size=64M
    read_rnd_buffer_size=64M
    myisam_sort_buffer_size=64M
     
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    

    Anyone spot anything glaringly obvious i've missed or messed up in the proposed new my.cnf before I commit?

    Cheers

    //Slav
     
  2. robdog

    robdog Well-Known Member

    Damn that is a beefy server, what are your current response times right now?
     
  3. Slavik

    Slavik XenForo Moderator Staff Member

    PDQ :)
     
  4. CyclingTribe

    CyclingTribe Well-Known Member

    Caveat: These suggestions are based on my own experience of tuning my own server over the past 18 months or so and may/may not be useful in your situation. (y)

    PHP:
    sort_buffer_size=64M
    read_buffer_size
    =64M
    read_rnd_buffer_size
    =64M

    I think these are way too large and since they are per-thread you'll be adding a large memory overhead (bigger isn't always better ;) ). I'd set them all to 2M and see how you go.

    PHP:
    key_buffer_size=512M
    I'd change this to 1024M (you have plenty of RAM in the machine).

    PHP:
    thread_cache_size=4
    I'd increase this to 50.

    PHP:
    query_cache_size=256M
    I'd reduce this to 64M maybe even 32M and use the additional memory elsewhere. (IME 32M works just fine.)

    PHP:
    innodb_thread_concurrency=10
    I'd set this to 0 (zero).

    PHP:
    innodb_flush_log_at_trx_commit 2
    innodb_flush_method 
    O_DIRECT
    I'd add these.

    NB: You might also want to consider adding an InnoDB log file [ innodb_log_file_size = 512M ] which buffers table writes - but it does add a delay to MySQL restarts and crash recovery (as the log transactions are checked against the tables), so it may be something you want to research yourself first.

    Cheers,
    Shaun :D
     
    Slavik and MattW like this.
  5. Slavik

    Slavik XenForo Moderator Staff Member

    Nice suggestions Shaun :)
     
  6. Deebs

    Deebs Well-Known Member

    Slavik,

    Personally disable the MySQL query cache, it is just crap and is designed for systems with 1 or 2 cpus at the most - old design. Everything is protected by a single mutex and almost everyone who strives for performance disables it and lets the query run as it is quicker as most of the time the cache is being invalidated due to row updates etc (remember, as soon as a row is updated/inserted/deleted then the query cache has to invalidate anything in cache). It is awful. Unfortunately if you are on stock MySQL 5.1 even disabling it leaves the mutex in place, you either need to migrate to Percona (great software, I use this) or stock MySQL 5.5.
     
    Slavik and CyclingTribe like this.
  7. Slavik

    Slavik XenForo Moderator Staff Member

    Thanks for the insight deebs.

    Unfortunately upgrading to 5.5 is tricky with plesk, officially it supports it, but as of yet everytime i've done so on my testbed it ends in disaster and have to roll back.

    Same with Percona, Plesk does not support anything outside of mysql, even though they are "drop in replacements", plesk specifically looks to the mysql package for any updates.
     
  8. Deebs

    Deebs Well-Known Member

    Here was me thinking you had a totally unmanaged server :p
     
    Jarod likes this.
  9. Slavik

    Slavik XenForo Moderator Staff Member

    I wish, unfortunately as of yet, ive not found another peice of software as good as plesk at handling 200+ clients :(
     
  10. Slavik

    Slavik XenForo Moderator Staff Member

    Just to point out, this article.

    It seems Magento at least seems to suck up and use the query cache to good effect?

    http://www.crucialwebhost.com/blog/improving-magento-speed-and-performance-with-mysql-query-cache/

    Just googling 'MySQL query cache magento' seems to suggest at least that it seems to do more good than harm?
     
  11. Slavik

    Slavik XenForo Moderator Staff Member

    Code:
    [mysqld]
    bind-address=127.0.0.1
    local-infile=0
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    table_open_cache=8192
    thread_cache_size=50
    max_connections=200
    innodb_buffer_pool_size=4096M
    query_cache_type=1
    query_cache_size=64M
    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
     
    
     
    CyclingTribe likes this.
  12. whyweprotest

    whyweprotest Well-Known Member

  13. Slavik

    Slavik XenForo Moderator Staff Member

    Insy and whyweprotest like this.
  14. melbo

    melbo Well-Known Member

    How did you calculate these?
     
  15. craigiri

    craigiri Well-Known Member

    Not an answer for yours, Melbo, but something on the table_open directive -
    http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/

    I have mine set a bunch lower and it's not affecting performance. According to that blog entry it may even hurt to have too many!

    I think that is mostly an ISAM tuning setting anyway - and it defaults to as low as 64. I am using an older version of mysql so it is table_open instead of table_open_cache

    Mine is set at 700. It does fill up, but all that means is that the machine goes to disk for them (I think). I may set it up to 900 or so, but no need for me to go higher. My reports of mysql in phpadmin and in mysql tuner are pretty good now and I don't want to fuss with it too much!
     
  16. melbo

    melbo Well-Known Member

    When I set them as high as Slaviks, mysqltuner started to ask me to make them larger. I set them both back down to 2000 and no more complaints.

    AMD Quad w/ 16GB RAM

    Here's my output after 27 hours:

    Code:
    [root@####### ~]# mysqltuner
     
    >>  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.5.27
    [OK] Operating on 64-bit architecture
     
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 232M (Tables: 174)
    [--] Data in InnoDB tables: 451M (Tables: 333)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 3M (Tables: 8)
    [!!] Total fragmented tables: 38
     
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
     
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 3h 33m 54s (503K q [5.075 qps], 52K conn, TX: 3B, RX: 132M)
    [--] Reads / Writes: 55% / 45%
    [--] Total buffers: 3.1G global + 7.2M per thread (50 max threads)
    [OK] Maximum possible memory usage: 3.4G (21% of installed RAM)
    [OK] Slow queries: 0% (0/503K)
    [OK] Highest usage of available connections: 40% (20/50)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/147.7M
    [OK] Key buffer hit rate: 99.7% (5M cached / 13K reads)
    [OK] Query cache efficiency: 65.3% (167K cached / 257K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 1% (420 temp sorts / 21K sorts)
    [!!] Joins performed without indexes: 15134
    [!!] Temporary tables created on disk: 48% (20K on disk / 42K total)
    [OK] Thread cache hit rate: 99% (20 created / 52K connections)
    [OK] Table cache hit rate: 74% (1K open / 1K opened)
    [OK] Open file limit used: 14% (592/4K)
    [OK] Table locks acquired immediately: 99% (309K immediate / 310K locks)
    [OK] InnoDB data size / buffer pool: 452.0M/2.0G
     
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        join_buffer_size (> 1.0M, or always use indexes with joins)
    Here's my.cnf - Lot's of commented out settings as this conf has been with me for 8 years.
    Code:
    [mysqld]
    # open-files-limit=16384
    max_allowed_packet=32M
    # max_delayed_threads=0
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    symbolic-links=0
    # back_log=50
    max_connections=50
    key_buffer_size=512M
    myisam_sort_buffer_size=64M
    #myisam_max_sort_file_size=2048M
    join_buffer_size=1M
    read_buffer_size=2M
    read_rnd_buffer_size=2M
    sort_buffer_size=2M
    table_definition_cache=2000
    table_open_cache=2000
    #table_cache=20000
    thread_cache_size=384
    # wait_timeout=60
    # interactive_timeout=30
    # connect_timeout=10
    tmp_table_size=512M
    max_heap_table_size=512M
    # max_seeks_for_key=1000
    # group_concat_max_len=1024
    # max_length_for_sort_data=1024
    # net_buffer_length=16384
    # max_connect_errors=100000
    # concurrent_insert=2
    # bulk_insert_buffer_size=8M
    query_cache_type=1
    query_cache_size=64M
    query_cache_limit=8M
    # query_prealloc_size=262144
    # query_alloc_block_size=65536
    # range_alloc_block_size=4096
    # transaction_alloc_block_size=8192
    # transaction_prealloc_size=4096
    default-storage-engine=InnoDB
    # max_write_lock_count=4
    innodb_buffer_pool_size=2048M
    innodb_additional_mem_pool_size=20M
     
    # Trial http://blog.secaserver.com/2011/08/mysql-recommended-my-cnf-settings-innodb-engine/
    # innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
    innodb_change_buffering=all
    innodb_file_per_table
    # innodb_log_file_size=256M
    # innodb_log_buffer_size=16M
    innodb_read_io_threads=16
    innodb_write_io_threads=16
    # End Trial
     
    innodb_thread_concurrency=0
    innodb_flush_log_at_trx_commit=2
    innodb_flush_method=O_DIRECT
     
    [mysqld_safe]
    # max_delayed_threads=0
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    # nice = -5
    # open-files-limit=16384
     
    [mysqldump]
    quick
    max_allowed_packet=2150MB
     
    # [myisamchk]
    # key_buffer_size=256M
    # sort_buffer_size=64M
    # read_buffer_size=16M
    # write_buffer_size=16M
     
    # [mysqlhotcopy]
    # interactive-timeout
     
  17. craigiri

    craigiri Well-Known Member

    If there is one thing I have learned over the years...it's to leave it alone if things work fairly well. As it stands, mine is working very well so I am going to hod myself back from fiddling!
    :)
     
  18. Volion

    Volion Active Member

    I have basically the same server setup, ergo cpu/ram. How is that revised .cnf working out for you Slavik?
     
  19. giorgino

    giorgino Well-Known Member

    Hi Debbs how to disable query cache correctly in MySql 5.5?
     
  20. Deebs

    Deebs Well-Known Member

    Add the following to my.cnf

    Code:
    query_cache_size                = 0 
    query_cache_type                = 0    
    Then from a mysql session type the following to turn it off dynamically
    Code:
    SET GLOBAL query_cache_size=0;
    SET GLOBAL query_cache_type=off;
    
     
    D.O.A., MattW, RoldanLT and 2 others like this.

Share This Page