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 connections aborted

Discussion in 'Workarounds and Optimization' started by shorif2000, Jun 24, 2011.

  1. shorif2000

    shorif2000 Registered

    Jun 24, 2011
    Likes Received:
    Trophy Points:
    Hi, I am trying to figure out how to solve this problem. When I run mysqltuner I see aborted connections.

    I am using master slave replication and this is one of the slaves. I am also using mysql-proxy with rw-splitting on a loadbalancer, this is one of the backend db servers.

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.49-3-log
    [OK] Operating on 64-bit architecture
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 2G (Tables: 68)
    [--] Data in InnoDB tables: 4G (Tables: 50)
    [!!] Total fragmented tables: 36
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 10h 38m 55s (12M q [322.272 qps], 430 conn, TX: 38B, RX: 3B)
    [--] Reads / Writes: 85% / 15%
    [--] Total buffers: 7.4G global + 16.4M per thread (300 max threads)
    [OK] Maximum possible memory usage: 12.2G (77% of installed RAM)
    [OK] Slow queries: 0% (99/12M)
    [OK] Highest usage of available connections: 4% (12/300)
    [OK] Key buffer size / total MyISAM indexes: 1.5G/1.4G
    [OK] Key buffer hit rate: 99.9% (667M cached / 586K reads)
    [OK] Query cache efficiency: 77.3% (9M cached / 12M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (15 temp sorts / 151K sorts)
    [!!] Temporary tables created on disk: 43% (76K on disk / 176K total)
    [OK] Thread cache hit rate: 96% (14 created / 430 connections)
    [!!] Table cache hit rate: 17% (206 open / 1K opened)
    [OK] Open file limit used: 2% (236/8K)
    [OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
    [!!] Connections aborted: 9%
    [OK] InnoDB data size / buffer pool: 4.4G/5.0G
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        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
        Increase table_cache gradually to avoid file descriptor limits
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        table_cache (> 4096)

    This is mysql conf file

    # The MySQL database server configuration file.
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    # For explanations see
    # This will be passed to all mysql clients
    # It has been reported that passwords should be enclosed with ticks/quotes
    # escpecially if they contain "#" chars...
    # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
    port		= 3306
    socket		= /var/run/mysqld/mysqld.sock
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    # This was formally known as [safe_mysqld]. Both versions are currently parsed.
    socket		= /var/run/mysqld/mysqld.sock
    nice		= 0
    # * Basic Settings
    user		= mysql
    pid-file	= /var/run/mysqld/
    socket		= /var/run/mysqld/mysqld.sock
    port		= 3306
    basedir		= /usr
    datadir		= /var/lib/mysql
    tmpdir		= /tmp
    language	= /usr/share/mysql/english
    server-id 	= 1921680231
    log-bin		= mysql-bin
    sync_binlog	= 1
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    #bind-address		=
    # * Fine Tuning
    max_connections			= 300
    table_cache			= 4096
    sort_buffer			= 6M
    read_buffer			= 4M
    join_buffer_size		= 6M
    thread_concurrency		= 16
    key_buffer_size			= 1536M
    tmp_table_size          	= 384M  # Default 32MB
    max_heap_table_size     	= 384M # Default 16MB
    query_cache_limit		= 512M
    query_cache_size        	= 512M
    innodb_buffer_pool_size 	= 5G # Default 8MB
    innodb_log_buffer_size		= 2M
    innodb_thread_concurrency	= 16
    #innodb_log_file_size		= 128M
    max_allowed_packet		= 16M
    thread_stack			= 192K
    thread_cache_size       	= 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    #max_connections        = 100
    #table_cache            = 64
    #thread_concurrency     = 10
    # * Query Cache Configuration
    # * Logging and Replication
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file        = /var/log/mysql/mysql.log
    #general_log             = 1
    # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
    # Here you can see queries with especially long duration
    #log_slow_queries	= /var/log/mysql/mysql-slow.log
    #long_query_time = 2
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id		= 1921680231
    #log_bin		= /var/log/mysql/mysql-bin.log
    #expire_logs_days	= 2
    #max_binlog_size         = 100M
    #binlog_do_db		= shopto
    #binlog_ignore_db	= include_database_name
    # * InnoDB
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    # * Security Features
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem
    max_allowed_packet	= 16M
    #no-auto-rehash	# faster start of mysql but no tab completition
    key_buffer		= 16M
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    !includedir /etc/mysql/conf.d/

    Can anyone help please. This is on a live site
    #1 shorif2000, Jun 24, 2011
    Last edited: Jun 24, 2011
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Oct 2, 2010
    Likes Received:
    Trophy Points:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    You are using MySQL 5.1 and have old or deprecated settings in your /etc/my.cnf file. First of all, I've removed those settings or changed the variable names to those applicable for MySQL 5.1. I've also removed most of the comments to make it a lot easier to read what is there:

    A few examples:

    thread_concurrency doesn't exist on anything other than Solaris, so I removed it
    skip-external-locking is the default, so I removed it
    I renamed settings such as read_buffer to read_buffer_size to have the right names for those settings.
    I renamed table_cache to table_open_cache which is the current name on MySQL 5.1 (MySQL 5.0 did call that variable table_cache)
    I removed locations to sockets and directories, since those are already defaults. If something is the default, it does not need to be set again in /etc/my.cnf file for legibility's sake.

    I would suggest trying to revise to these settings first, restart MySQL, then go from there on following mysqltuner's suggestions. As for closing connections, I wasn't able to find a lot of information on that online on what might cause the message, but it must be due to this portion of the return:

    [!!] Connections aborted: 9%

    Since you don't appear to have any timeouts in your /etc/my.cnf file, those likely need added.

Share This Page