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

MySQL Tuning Suggestions

Discussion in 'Server Configuration and Hosting' started by MattW, Mar 1, 2012.

  1. MattW

    MattW Well-Known Member

    Just wondering if someone would be able to take a look at my current MySQL configuration, and possibly suggest any improvements? I've been managing my own servers for the last 5 years, and have never really managed to get a properly tweaked my.cnf.

    Anyway, config:
    Code:
    php -v
    PHP 5.3.10 (cli) (built: Feb 25 2012 09:05:55)
    Copyright (c) 1997-2012 The PHP Group
    Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies
        with eAccelerator v0.9.6.1, Copyright (c) 2004-2010 eAccelerator, by eAccelerator
        with the ionCube PHP Loader v4.0.12, Copyright (c) 2002-2011, by ionCube Ltd.
        with Suhosin v0.9.33, Copyright (c) 2007-2012, by SektionEins GmbH
    Code:
    mysql -v
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 42594
    Server version: 5.5.21-cll MySQL Community Server (GPL)
    
    current my.cnf (this was auto adjusted by cpanel when I upgraded to 11.32 and MySQL 5.5)
    Code:
    [myisamchk]
    read_buffer=2M
    key_buffer=256M
    sort_buffer_size=256M
    write_buffer=2M
    [mysqld]
    log-bin=mysql-bin
    myisam_sort_buffer_size=64M
    read_rnd_buffer_size=8M
    expire_logs_days=7
    query_cache_size=128M
    thread_cache_size=8
    max_allowed_packet=1M
    skip-federated
    table_definition_cache=2048
    local-infile=0
    table_cache=2048
    max_connections=60
    read_buffer_size=2M
    slow_query_log=1
    binlog-do-db=british_mods
    binlog-do-db=z22se_forum
    binlog-do-db=z22se_newshop
    binlog-do-db=pure1_shop
    server-id=1
    slow_query_log_file="/var/log/slow_queries.log"
    thread_concurrency=16
    sort_buffer_size=2M
    port=3306
    join_buffer_size=3M
    key_buffer=384M
    query_cache_limit=4M
    socket="/var/lib/mysql/mysql.sock"
    skip-external-locking
    query-cache-type=1
    long_query_time=5
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    [client]
    port=3306
    socket="/var/lib/mysql/mysql.sock"
    [mysqldump]
    max_allowed_packet=16M
    quick
    [mysql]
    no-auto-rehash
    [isamchk]
    read_buffer=2M
    key_buffer=256M
    sort_buffer_size=256M
    write_buffer=2M
    [mysqlhotcopy]
    interactive-timeout
    mysqltuner output
    Code:
    >>  MySQLTuner 1.0.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.21-cll
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
     
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 393M (Tables: 674)
    [--] Data in InnoDB tables: 224M (Tables: 278)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 3M (Tables: 6)
    [!!] Total fragmented tables: 103
     
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 23h 40m 25s (947K q [11.116 qps], 42K conn, TX: 2B, RX: 258M)
    [--] Reads / Writes: 47% / 53%
    [--] Total buffers: 672.0M global + 15.2M per thread (60 max threads)
    [OK] Maximum possible memory usage: 1.5G (51% of installed RAM)
    [OK] Slow queries: 0% (7/947K)
    [OK] Highest usage of available connections: 15% (9/60)
    [OK] Key buffer size / total MyISAM indexes: 384.0M/670.4M
    [OK] Key buffer hit rate: 99.5% (18M cached / 91K reads)
    [OK] Query cache efficiency: 60.0% (331K cached / 553K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (6 temp sorts / 29K sorts)
    [!!] Joins performed without indexes: 1666
    [OK] Temporary tables created on disk: 11% (7K on disk / 63K total)
    [OK] Thread cache hit rate: 99% (9 created / 42K connections)
    [!!] Table cache hit rate: 0% (1K open / 261K opened)
    [OK] Open file limit used: 39% (1K/4K)
    [OK] Table locks acquired immediately: 99% (692K immediate / 692K locks)
    [!!] InnoDB data size / buffer pool: 224.5M/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 (> 3.0M, or always use indexes with joins)
        table_cache (> 2048)
        innodb_buffer_pool_size (>= 224M)

    I'm running on a VPS with 3GB dedicated RAM, 2GB swap, Centos 5.7 32bit, WHM/CPANEL 11.32, NGINXadmin, apache 2.2.22 and memcached.

    MySQL on my VPS has previously not use INNODB, as all the phpBB tables were MyISAM.

    Thanks,
    Matt
     
  2. MattW

    MattW Well-Known Member

    Also, current load etc

    Code:
    free -m
                total      used      free    shared    buffers    cached
    Mem:          3072      3024        47          0        232      2026
    -/+ buffers/cache:        765      2306
    Swap:        2047          0      2047
    
    Code:
    uptime
    17:27:51 up 1 day, 4 min,  1 user,  load average: 0.24, 0.20, 0.18
    
    The VPS is still running another phpBB3 installed, 3 OpenCart shops, and a WP Blog.

    Oh, and I've added this into my config.php file for caching:

    PHP:
    $config['cache']['enabled'] = true;
    $config['cache']['frontend'] = 'Core';
    $config['cache']['frontendOptions']['cache_id_prefix'] = 'xf_';
    $config['cache']['backend'] = 'Memcached';
    $config['cache']['backendOptions'] = array(
            
    'compression' => false,
            
    'servers' => array(
                    array(
                            
    // your memcached server IP /address
                            
    'host' => 'localhost',
     
                            
    // memcached port
                            
    'port' => 11211,
                    )
            )
    );
     
  3. MattW

    MattW Well-Known Member

    I've made a few changes to the my.cnf file, and now this is what I'm getting from mysqltuner

    Code:
    root@vps [~/scripts]# ./mysqltuner.pl
     
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
     
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.21-cll
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
     
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 347M (Tables: 575)
    [--] Data in InnoDB tables: 232M (Tables: 285)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 5M (Tables: 6)
    [!!] Total fragmented tables: 93
     
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
     
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 10d 13h 18m 22s (9M q [10.785 qps], 568K conn, TX: 24B, RX: 1B)
    [--] Reads / Writes: 62% / 38%
    [--] Total buffers: 1.7G global + 15.2M per thread (60 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.6G (85% of installed RAM)
    [OK] Slow queries: 0% (64/9M)
    [OK] Highest usage of available connections: 20% (12/60)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/512.5M
    [OK] Key buffer hit rate: 100.0% (536M cached / 155K reads)
    [OK] Query cache efficiency: 66.6% (4M cached / 6M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (147 temp sorts / 370K sorts)
    [!!] Joins performed without indexes: 17372
    [OK] Temporary tables created on disk: 11% (81K on disk / 689K total)
    [OK] Thread cache hit rate: 99% (13 created / 568K connections)
    [!!] Table cache hit rate: 0% (1K open / 2M opened)
    [OK] Open file limit used: 38% (1K/4K)
    [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
    [OK] InnoDB data size / buffer pool: 232.1M/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:
        join_buffer_size (> 3.0M, or always use indexes with joins)
        table_cache (> 2048)
    
    I'm still after some suggestions if anyone is able to offer them.

    Code:
    [myisamchk]
    read_buffer=2M
    key_buffer=256M
    sort_buffer_size=256M
    write_buffer=2M
    [mysqld]
    innodb_additional_mem_pool_size=16M
    innodb_buffer_pool_size=1G
    log-bin=mysql-bin
    myisam_sort_buffer_size=64M
    read_rnd_buffer_size=8M
    expire_logs_days=7
    query_cache_size=128M
    thread_cache_size=8
    max_allowed_packet=1M
    skip-federated
    table_definition_cache=2048
    local-infile=0
    table_cache=2048
    max_connections=60
    read_buffer_size=2M
    slow_query_log=1
    binlog-do-db=british_mods
    binlog-do-db=z22se_xenforo
    binlog-do-db=z22se_newshop
    binlog-do-db=pure1_shop
    server-id=1
    slow_query_log_file="/var/log/slow_queries.log"
    thread_concurrency=16
    sort_buffer_size=2M
    port=3306
    join_buffer_size=3M
    key_buffer=512M
    query_cache_limit=4M
    socket="/var/lib/mysql/mysql.sock"
    skip-external-locking
    query-cache-type=1
    long_query_time=5
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    [client]
    port=3306
    socket="/var/lib/mysql/mysql.sock"
    [mysqldump]
    max_allowed_packet=16M
    quick
    [mysql]
    no-auto-rehash
    [isamchk]
    read_buffer=2M
    key_buffer=256M
    sort_buffer_size=256M
    write_buffer=2M
    [mysqlhotcopy]
    interactive-timeout
    
     
  4. Solidus

    Solidus Well-Known Member

    Code:
    [!!] Joins performed without indexes: 17372
    That's extremely high.
    Make the following changes,

    join_buffer_size = 80M
    innodb_buffer_pool_size = 20M
    myisam_sort_buffer_size = 32M
    #read_rnd_buffer_size=8M (comment it out)
    thread_cache_size = 12
    max_allowed_packet = 15M
    max_connections = 100
    read_buffer_size = 3M
    sort_buffer_size = 3M
    key_buffer_size = 350M (currently yours says key_buffer=)
    query_cache_limit = 10M
    #default-storage-engine=MyISAM (comment it out)
    #innodb_file_per_table=1 (comment it out)
    Also add the following somewhere under [mysqld]
    query_cache_size = 80M
    tmp_table_size = 100M
    max_heap_table_size = 100M
    Restart MySQL and see how that works for you.
     
  5. MattW

    MattW Well-Known Member

    That has always been high, and I think it's down my phpBB3 (I'm hosting another site on the same server which is still running phpBB3).

    Will make those other changes and let you know
     
  6. craigiri

    craigiri Well-Known Member

    Why fiddle if your server load is that low?
     
  7. Solidus

    Solidus Well-Known Member

    Fiddling is fun. Perhaps the OP is like me and wants maximum performance and hates to see errors and warnings.
     
  8. MattW

    MattW Well-Known Member

    This!

    I always like to try and get the best performance

    Have made the changes and cleaned up the config file

    Code:
    root@vps [/etc]# cat my.cnf
    [client]
    port=3306
    socket="/var/lib/mysql/mysql.sock"
    
    [mysqld]
    innodb_additional_mem_pool_size=16M
    innodb_buffer_pool_size=1G
    innodb_file_per_table=1
    innodb_log_buffer_size=4M
    innodb_flush_log_at_trx_commit=2
    log-bin=mysql-bin
    myisam_sort_buffer_size=32M
    expire_logs_days=7
    query_cache_size=128M
    thread_cache_size=12
    max_allowed_packet=15M
    skip-federated
    table_definition_cache=2048
    local-infile=0
    table_cache=2048
    max_connections=60
    read_buffer_size=3M
    sort_buffer_size=3M
    slow_query_log=1
    slow_query_log_file="/var/log/slow_queries.log"
    thread_concurrency=16
    port=3306
    join_buffer_size=80M
    key_buffer_size=350M
    query_cache_limit=10M
    socket="/var/lib/mysql/mysql.sock"
    skip-external-locking
    query-cache-type=1
    long_query_time=5
    query_cache_size = 80M
    tmp_table_size = 100M
    max_heap_table_size = 100M
    
    [myisamchk]
    read_buffer=2M
    key_buffer=256M
    sort_buffer_size=256M
    write_buffer=2M
    
    [mysqldump]
    max_allowed_packet=16M
    quick
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    read_buffer=2M
    key_buffer=256M
    sort_buffer_size=256M
    write_buffer=2M
    
    [mysqlhotcopy]
    interactive-timeout
    
     
  9. MattW

    MattW Well-Known Member

    I've also spent a couple of hours the other day swapping from suPHP to FCGI and setting Xcache up.
     
  10. MattW

    MattW Well-Known Member

    Just to update this thread, I removed a couple of things from the suggested config settings, as maximum RAM usage went pretty high.
    Code:
    [client]
    port=3306
    socket="/var/lib/mysql/mysql.sock"
     
    [mysqld]
    innodb_additional_mem_pool_size=16M
    innodb_buffer_pool_size=1G
    innodb_file_per_table=1
    innodb_log_buffer_size=4M
    innodb_flush_log_at_trx_commit=2
    log-bin=mysql-bin
    myisam_sort_buffer_size=64M
    expire_logs_days=7
    query_cache_size=128M
    thread_cache_size=12
    max_allowed_packet=15M
    skip-federated
    table_definition_cache=2048
    local-infile=0
    table_cache=2048
    max_connections=60
    read_buffer_size=2M
    slow_query_log=1
    slow_query_log_file="/var/log/slow_queries.log"
    thread_concurrency=16
    sort_buffer_size=2M
    port=3306
    join_buffer_size=3M
    key_buffer_size=600M
    query_cache_limit=4M
    socket="/var/lib/mysql/mysql.sock"
    skip-external-locking
    query-cache-type=1
    long_query_time=5
    default-storage-engine=InnoDB
     
    [myisamchk]
    read_buffer=2M
    key_buffer=256M
    sort_buffer_size=256M
    write_buffer=2M
     
    [mysqldump]
    max_allowed_packet=16M
    quick
     
    [mysql]
    no-auto-rehash
     
    [isamchk]
    read_buffer=2M
    key_buffer=256M
    sort_buffer_size=256M
    write_buffer=2M
     
    [mysqlhotcopy]
    interactive-timeout
    
    I've also fully replaced MySQL with Percona Server, and there is an instant speed increase using the existing my.cnf with Percona
     
  11. CyclingTribe

    CyclingTribe Well-Known Member

    Good luck - I've spent ages fiddling with mine, and in the end just settled for the best of a bunch of test configs I'd used. :D

    I did enjoy the experience though and learned quite a bit from it, so it wasn't time wasted by any means.

    Good luck. (y)
     
    MattW likes this.

Share This Page