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):
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.
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.