1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Mysql so slow, my.cnf and mysqltuner log in thread

Discussion in 'Databases' started by thesurface, Nov 8, 2015.

  1. #1
    Can u pls help me out? mysql is so slow i have Xeon 2xE5530 | 8c / 16t | 2.4 GHz+ | 24 GB Ram

    my.cnf

    # This file has been automatically moved from your previous
    # /etc/mysql/my.cnf, with just this comment added at the top, to maintain MySQL
    # operation using your previously customised configuration.
    
    # To switch to the new packaging configuration for automated management of
    # /etc/mysql/my.cnf across multiple variants:
    #
    # 1. Move your customisations from this file to /etc/mysql/conf.d/ and
    #    to /etc/mysql/<variant>.conf.d/ as appropriate.
    # 2. Run "update-alternatives --remove my.cnf /etc/mysql/my.cnf.migrated"
    # 3. Remove the file /etc/mysql/my.cnf.migrated
    
    #
    # 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
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    # 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.
    [client]
    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.
    [mysqld_safe]
    socket          = /var/run/mysqld/mysqld.sock
    nice            = 0
    
    [mysqld]
    #
    # * Basic Settings
    #
    #table_open_cache = 2000
    #table_definition_cache = 1000
    #wait_timeout = 30
    #connect_timeout = 1
    default-storage-engine=MyISAM
    default-tmp-storage-engine=MyISAM
    innodb=OFF
    user            = mysql
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    port            = 3306
    basedir         = /usr
    datadir         = /home/mysql
    tmpdir          = /tmp
    lc-messages-dir = /usr/share/mysql
    skip-external-locking
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address            = 127.0.0.1
    #
    # * Fine Tuning
    #
    #read_rnd_buffer_size = 10M
    join_buffer_size = 20G
    key_buffer_size = 20G
    sort_buffer_size = 5M
    #key_buffer             = 100M
    max_allowed_packet      = 5M
    thread_stack            = 192K
    thread_cache_size       = 5000
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    max_connections        = 200
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    #slow_query_log=1
    #slow_query_log_file=mysql-slow.log
    #long_query_time=0.1
    #log-queries-not-using-indexes
    
    #tmp_table_size= 100M
    #max_heap_table_size= 32M
    query_cache_limit       = 50M
    query_cache_size        = 50M
    query_cache_type=1
    #
    # * 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 log - should be very few entries.
    #
    log_error = /var/log/mysql/error.log
    #
    # Here you can see queries with especially long duration
    #log_slow_queries       = /var/log/mysql/mysql-slow.log
    #long_query_time = 2
    #log-queries-not-using-indexes
    #
    # 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              = 1
    #log_bin                        = /var/log/mysql/mysql-bin.log
    expire_logs_days        = 10
    max_binlog_size         = 100M
    #binlog_do_db           = include_database_name
    #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
    
    
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet      = 16M
    
    [mysql]
    #no-auto-rehash # faster start of mysql but no tab completition
    
    [isamchk]
    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/
    
    Code (markup):


    perl mysqltuner.pl

    [OK] Logged in using credentials from debian maintenance account.
    >>  MySQLTuner 1.6.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
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.6.27-0ubuntu0.15.04.1
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 17G (Tables: 227)
    [!!] Total fragmented tables: 26
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There is no anonymous account in all database users
    [OK] All database users have passwords assigned
    [!!] User 'root@127.0.0.1' has user name as password.
    [!!] User 'root@::1' has user name as password.
    [!!] User 'root@localhost' has user name as password.
    [!!] There is not basic password file list !
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 21m 20s (865K q [676.490 qps], 5K conn, TX: 229M, RX: 127M)
    [--] Reads / Writes: 16% / 84%
    [--] Binary logging is disabled
    [--] Total buffers: 20.1G global + 20.0G per thread (200 max threads)
    [!!] Maximum reached memory usage: 1120.4G (4759.29% of installed RAM)
    [!!] Maximum possible memory usage: 4021.2G (17081.79% of installed RAM)
    [OK] Slow queries: 0% (46/865K)
    [OK] Highest usage of available connections: 27% (55/200)
    [OK] Aborted connections: 0.02%  (1/5408)
    [OK] Query cache efficiency: 63.8% (185K cached / 291K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 10% (534 temp sorts / 4K sorts)
    [!!] Joins performed without indexes: 53
    [OK] Temporary tables created on disk: 4% (42 on disk / 897 total)
    [OK] Thread cache hit rate: 98% (55 created / 5K connections)
    [OK] Table cache hit rate: 97% (329 open / 336 opened)
    [OK] Open file limit used: 33% (341/1K)
    [OK] Table locks acquired immediately: 96% (636K immediate / 657K locks)
    
    -------- MyISAM Metrics -----------------------------------------------------
    [!!] Key buffer used: 19.7% (4B used / 21B cache)
    [OK] Key buffer size / total MyISAM indexes: 20.0G/3.7G
    [OK] Read Key buffer hit rate: 99.9% (132M cached / 92K reads)
    [!!] Write Key buffer hit rate: 81.6% (13M cached / 2M writes)
    
    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is disabled.
    [!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine
    
    -------- AriaDB Metrics -----------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        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 ***
        join_buffer_size (> 20.0G, or always use indexes with joins)
    Code (markup):

     
    thesurface, Nov 8, 2015 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    If you're not using InnoDB or another equivalent, there's not going to be much you can do as far as tuning goes. key_buffer_size is about the only configuration that makes any difference with myisam.

    You should first off enable slow_query_log, to see if it's an issue with the queries you're running.

    Apart from that, what type of usage is the database for? Basically is this read heavy, write heavy, or mixed usage? How much traffic? What application is using the database? Hard to make any specific suggestions without knowing what the database is being used for.

    With this much available RAM, I'd probably suggest switching to InnoDB or MariaDB right away. This configuration wizard a good tool to get a baseline my.cnf: https://tools.percona.com/

    Quick comments:

    [!!] Joins performed without indexes: 53
    On a large database this could be a huge issue.

    key_buffer_size is way too large. You're probably hitting swap because the server's allocating most of the memory. Drop this to 25 - 30% of total RAM.

    Drop, join_buffer_size way down immediately. join_buffer_size is allocated per Join not per thread even so having this extremely high is basically going to destroy a server. Drop this to something more like 2M.
     
    Last edited: Nov 16, 2015
    jestep, Nov 16, 2015 IP
  3. topcoder

    topcoder Well-Known Member

    Messages:
    124
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    138
    #3
    I don't think it's the configs, I think it's your database design, amount of data, and queries. None of what you posted.
     
    topcoder, Nov 19, 2015 IP