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.

mysqltuner.pl | Any suggestions ?

Discussion in 'Workarounds and Optimization' started by ModServ, Feb 13, 2011.

  1. ModServ

    ModServ Well-Known Member

    Joined:
    Oct 17, 2006
    Messages:
    332
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Egypt
    cPanel Access Level:
    Root Administrator
    Hello,

    I ran mysqltuner.pl every month, What's your suggestion/advice of that results ? excluding "max_connections" problem.

    HTML:
     >>  MySQLTuner 1.1.2 - 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.54-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 9G (Tables: 36864)
    [--] Data in InnoDB tables: 12M (Tables: 242)
    [--] Data in CSV tables: 0B (Tables: 2)
    [--] Data in MEMORY tables: 246M (Tables: 715)
    [!!] Total fragmented tables: 2389
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 9d 3h 12m 1s (129M q [164.164 qps], 3M conn, TX: 2559B, RX: 28B)
    [--] Reads / Writes: 73% / 27%
    [--] Total buffers: 2.2G global + 27.4M per thread (600 max threads)
    [!!] Maximum possible memory usage: 18.3G (237% of installed RAM)
    [OK] Slow queries: 0% (3K/129M)
    [!!] Highest connection usage: 100%  (601/600)
    [OK] Key buffer size / total MyISAM indexes: 2.0G/2.6G
    [OK] Key buffer hit rate: 99.9% (5B cached / 3M reads)
    [OK] Query cache efficiency: 64.6% (67M cached / 104M selects)
    [!!] Query cache prunes per day: 1027233
    [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 11M sorts)
    [!!] Joins performed without indexes: 362899
    [OK] Temporary tables created on disk: 18% (1M on disk / 6M total)
    [OK] Thread cache hit rate: 99% (601 created / 3M connections)
    [OK] Table cache hit rate: 40% (67K open / 167K opened)
    [OK] Open file limit used: 39% (102K/262K)
    [OK] Table locks acquired immediately: 99% (80M immediate / 80M locks)
    [OK] InnoDB data size / buffer pool: 13.0M/16.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Reduce or eliminate persistent connections to reduce connection usage
        Increasing the query_cache size over 128M may reduce performance
        Adjust your join queries to always utilize indexes
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        max_connections (> 600)
        wait_timeout (< 28800)
        interactive_timeout (< 28800)
        query_cache_size (> 192M) [see warning above]
        join_buffer_size (> 128.0K, or always use indexes with joins)
    /etc/my.cnf:

    HTML:
    # Example MySQL config file for very large systems.
    #
    # This is for a large system with memory of 1G-2G where the system runs mainly
    # MySQL.
    #
    # You can copy this file to
    # /etc/my.cnf to set global options,
    # mysql-data-dir/my.cnf to set server-specific options (in this
    # installation this directory is /var/lib/mysql) or
    # ~/.my.cnf to set user-specific options.
    #
    # In this file, you can use all long options that a program supports.
    # If you want to know which options a program supports, run the program
    # with the "--help" option.
    
    # The following options will be passed to all MySQL clients
    [client]
    #password       = your_password
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    
    # Here follows entries for some specific programs
    
    # The MySQL server
    [mysqld]
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    skip-locking
    max_connections=700
    max_user_connections=700
    key_buffer = 2048M
    max_allowed_packet = 192M
    table_cache =131072
    sort_buffer_size = 9M
    read_buffer_size = 9M
    read_rnd_buffer_size  = 9M
    myisam_sort_buffer_size = 192M
    thread_cache_size = 192
    query_cache_size = 192M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8
    log_slow_queries=/var/log/mysqld.slow.log
    long_query_time = 5
    log-long-format
    expire_logs_days = 1
    concurrent_insert = 2
    
    # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    # if all processes that need to connect to mysqld run on the same host.
    # All interaction with mysqld must be made via Unix sockets or named pipes.
    # Note that using this option without enabling named pipes on Windows
    # (via the "enable-named-pipe" option) will render mysqld useless!
    #
    #skip-networking
    
    # Replication Master Server (default)
    # binary logging is required for replication
    log-bin=mysql-bin
    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id       = 1
    
    # Replication Slave (comment out master section to use this)
    #
    # To configure this host as a replication slave, you can choose between
    # two methods :
    #
    # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
    #    the syntax is:
    #
    #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
    #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
    #
    #    where you replace <host>, <user>, <password> by quoted strings and
    #    <port> by the master's port number (3306 by default).
    #
    #    Example:
    #
    #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
    #    MASTER_USER='joe', MASTER_PASSWORD='secret';
    #
    # OR
    #
    # 2) Set the variables below. However, in case you choose this method, then
    #    start replication for the first time (even unsuccessfully, for example
    #    if you mistyped the password in master-password and the slave fails to
    #    connect), the slave will create a master.info file, and any later
    #    change in this file to the variables' values below will be ignored and
    #    overridden by the content of the master.info file, unless you shutdown
    #    the slave server, delete master.info and restart the slaver server.
    #    For that reason, you may want to leave the lines below untouched
    #    (commented) and instead use CHANGE MASTER TO (see above)
    #
    # required unique id between 2 and 2^32 - 1
    # (and different from the master)
    # defaults to 2 if master-host is set
    # but will not function as a slave if omitted
    #server-id       = 2
    #
    # The replication master for this slave - required
    #master-host     =   <hostname>
    #
    # The username the slave will use for authentication when connecting
    # to the master - required
    #master-user     =   <username>
    #
    # The password the slave will authenticate with when connecting to
    # the master - required
    #master-password =   <password>
    #
    # The port the master is listening on.
    # optional - defaults to 3306
    #master-port     =  <port>
    #
    # binary logging - not required for slaves, but recommended
    #log-bin=mysql-bin
    
    # Point the following paths to different dedicated disks
    #tmpdir         = /tmp/
    #log-update     = /path-to-dedicated-directory/hostname
    
    # Uncomment the following if you are using BDB tables
    #bdb_cache_size = 384M
    #bdb_max_lock = 100000
    
    # Uncomment the following if you are using InnoDB tables
    #innodb_data_home_dir = /var/lib/mysql/
    #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
    #innodb_log_group_home_dir = /var/lib/mysql/
    #innodb_log_arch_dir = /var/lib/mysql/
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    innodb_buffer_pool_size = 16M
    #innodb_additional_mem_pool_size = 20M
    # Set .._log_file_size to 25 % of buffer pool size
    #innodb_log_file_size = 100M
    #innodb_log_buffer_size = 8M
    #innodb_flush_log_at_trx_commit = 1
    #innodb_lock_wait_timeout = 50
    local-infile=0
    
    [mysqldump]
    quick
    max_allowed_packet = 192M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    
    [isamchk]
    key_buffer = 2048M
    sort_buffer_size = 9M
    read_buffer = 9M
    write_buffer = 9M
    
    [myisamchk]
    key_buffer = 2048M
    sort_buffer_size = 9M
    read_buffer = 9M
    write_buffer = 9M
    
    [mysqlhotcopy]
    interactive-timeout
     
  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
    My first suggestion is to take out all the lines in your /etc/my.cnf that aren't doing anything such as comments as well as those already configured like the port (3306 is the default, /var/lib/mysql/mysql.sock is the default, etc.) Anything that is the default, there is no need to have in the /etc/my.cnf file.

    Next, skip-locking is default and called skip-external-locking now. thread_concurrency doesn't exist on Linux systems (only does on Solaris).

    The quick type is already default for mysqldump, so I've taken it out. I'm uncertain if you should have log-bin setup or not, since it can create large log files, so that's up to you.

    You appear to be using MySQL 5.1.54 and the slow logging line you are using is for MySQL 5.0 so it won't work properly for MySQL 5.1 and should be changed to slow_query_log (this then saves to /var/lib/mysql/hostname-slow.log location without creating the file as it auto creates), or changed to slow_query_log_file=/var/log/mysqld.slow.log where you'd have to create the file at /var/log/mysqld.slow.log with 660 file permissions and mysql:mysql ownership. I've chosen to use the first one to replace the prior incorrect slow query logging line.

    I changed key_buffer to key_buffer_size and table_cache to table_open_cache, which are the correct names for these variables in MySQL 5.1.

    Honestly, I would like to yank a ton of other values out of your /etc/my.cnf until we could get it below using high memory, since you've used the default template MySQL provides and I did a study on the template versus the defaults, and it performs worse than the defaults even (this was in my MySQL Optimization presentation at the 2011 conference at MySQL Optimization | cPanel Video Site).

    Anyway, just to get the /etc/my.cnf to a clean state, it now looks like this without really changing many of the other values themselves:

    Code:
    [mysqld]
    max_connections=700
    max_user_connections=100
    key_buffer_size = 2048M
    max_allowed_packet = 192M
    table_open_cache =131072
    sort_buffer_size = 9M
    read_buffer_size = 9M
    read_rnd_buffer_size  = 9M
    myisam_sort_buffer_size = 192M
    thread_cache_size = 192
    query_cache_size = 128M
    slow_query_log
    long_query_time = 5
    log-long-format
    expire_logs_days = 1
    concurrent_insert = 2
    log-bin=mysql-bin
    server-id       = 1
    innodb_buffer_pool_size = 16M
    local-infile=0
    wait_timeout=14400
    interactive_timeout=14400
    
    [mysqldump]
    max_allowed_packet = 192M
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer = 2048M
    sort_buffer_size = 9M
    read_buffer = 9M
    write_buffer = 9M
    
    [myisamchk]
    key_buffer = 2048M
    sort_buffer_size = 9M
    read_buffer = 9M
    write_buffer = 9M
    
    [mysqlhotcopy]
    interactive-timeout
    As you can see, that's a lot easier to actually read that what it was before. It also now contains the right names and variables for what works with MySQL 5.1 versus what it had before.
     
  3. ModServ

    ModServ Well-Known Member

    Joined:
    Oct 17, 2006
    Messages:
    332
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Egypt
    cPanel Access Level:
    Root Administrator
    Hello,

    Yes, I'm using mysql-bin.

    Thank you so much for your suggestions and advices, I've done it all, The rest are the values, What do you think about it ?

    Is that values are good with Q9550 and 8GB of Ram, 26GB Swap, 500GB+250GB HDD ?

    Waiting you,

    Note: I've downloaded the interview and will review it after couple of hours.

    Many thanks.
     
  4. ModServ

    ModServ Well-Known Member

    Joined:
    Oct 17, 2006
    Messages:
    332
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Egypt
    cPanel Access Level:
    Root Administrator
    Tried that confguration but it seems it has an error in it :S

    Code:
    [/]# service mysql stop
    Shutting down MySQL.........                               [  OK  ]
    [/]# service mysql start
    Starting MySQL.Manager of pid-file quit without updating fi[FAILED]
     
  5. 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
    Your MySQL version is 5.1 right?

    Code:
    mysql --version
    Thanks.
     
  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
    Also, it appears to be failing on this line on my machine:

    Code:
    query_cache_size=128M
    Please try commenting out that line if you are indeed running MySQL 5.1. If you aren't running MySQL 5.1, then it will fail on table_open_cache as well, since it's table_cache on MySQL 5.0 and table_open_cache on MySQL 5.1.
     
  7. ModServ

    ModServ Well-Known Member

    Joined:
    Oct 17, 2006
    Messages:
    332
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Egypt
    cPanel Access Level:
    Root Administrator
    Hello,

    Version is 5.1

    mysql Ver 14.14 Distrib 5.1.54

    I will try it again after couple of hours.

    Thank you,
     
  8. ModServ

    ModServ Well-Known Member

    Joined:
    Oct 17, 2006
    Messages:
    332
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Egypt
    cPanel Access Level:
    Root Administrator
    Hello,

    Seems like the server I tried on is CRAP, I tried it on my own server and works like charm, No errors at all, I will monitor those changes for 48 hours and be back again with mysqltuner and result, Waiting your suggestions/advices about the values

    Thanks,
     
Loading...

Share This Page