Optitmize MySQL Configuration to reduce CPU usage
Hello,
I'm using a Kimsufi server with this configuration: Core i5 4 cores/4 threads and with 16Gb of RAM and HDD 1To. With Ubuntu 14.04.4 LTS (GNU/Linux 3.14.32-xxxx-grs-ipv6-64 x86_64).
After launching a htop command my CPU is used 100% and mysql processs used approx. > 200% of CPU and website is very slow.
This is my /etc/mysql/my.cnf config:
and MySQLTuner results:
Can anyone help me to optimize that. My concern is that if this case is not resolved; may be my server will be hanged. Note that i'm a very newbbie in database administration so, please explain with more details!!! Please! Regards
#
# 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
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/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
#
key_buffer = 6144M
key_buffer_size = 6144M
max_allowed_packet = 512M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 500
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1024M
query_cache_size = 10240M
#
# * 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!
#default-storage_engine=InnoDB
#innodb_buffer_pool_size = 256M
#innodb_log_file_size = 256M
#innodb_log_buffer_size = 4M
#innodb_flush_log_at_trx_commit = 2
#innodb_thread_concurrency = 8
#innodb_flush_method = O_DIRECT
#
# * 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
secure-file-priv = /var/tmp
[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/and MySQLTuner results:
>> MySQLTuner 1.6.18 - Major Hayden
>> 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
error: Found option without preceding group in config file: /etc/mysql/conf.d/local.cnf at line: 1
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.5.49-0ubuntu0.14.04.1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics ---------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My ISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 3G (Tables: 429)
[--] Data in MyISAM tables: 18M (Tables: 38)
[--] Data in MEMORY tables: 0B (Tables: 17)
[OK] Total fragmented tables: 0
-------- Security Recommendations ----------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations ------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics ---------------------------------------------------
[--] Up for: 21m 39s (1M q [1K qps], 258 conn, TX: 751M, RX: 604M)
[--] Reads / Writes: 57% / 43%
[--] Binary logging is disabled
[--] Physical Memory : 15.6G
[--] Max MySQL memory : 16.6G
[--] Other process memory: 117.9M
[--] Total buffers: 16.2G global + 2.7M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 16.2G (104.03% of installed RAM)
[!!] Maximum possible memory usage: 16.6G (106.29% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 11% (17/151)
[!!] Aborted connections: 6.20% (16/258)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (118 temp sorts / 56K sorts)
[!!] Joins performed without indexes: 94
[!!] Temporary tables created on disk: 33% (383 on disk / 1K total)
[OK] Thread cache hit rate: 93% (17 created / 258 connections)
[OK] Table cache hit rate: 79% (197 open / 247 opened)
[OK] Open file limit used: 4% (42/1K)
[OK] Table locks acquired immediately: 100% (137K immediate / 137K locks)
-------- Performance schema ----------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ----------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics --------------------------------------------------------
[!!] Key buffer used: 18.8% (1B used / 6B cache)
[OK] Key buffer size / total MyISAM indexes: 6.0G/11.3M
[OK] Read Key buffer hit rate: 100.0% (14M cached / 3K reads)
[OK] Write Key buffer hit rate: 100.0% (2M cached / 0 writes)
-------- AriaDB Metrics --------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics --------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/3.0G
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (778690869 hits/ 778696521 total)
[!!] InnoDB Write Log efficiency: 37.2% (10851 hits/ 29171 total)
[OK] InnoDB log waits: 0.00% (0 waits / 40022 writes)
-------- TokuDB Metrics --------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics --------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics ---------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations -------------------------------------------------------
General recommendations:
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate unclosed connections and network issues
Configure your accounts with ip or subnets only, then update your configurat ion with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_type (=0)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
innodb_buffer_pool_size (>= 3G) if possible.
Can anyone help me to optimize that. My concern is that if this case is not resolved; may be my server will be hanged. Note that i'm a very newbbie in database administration so, please explain with more details!!! Please! Regards
-
Sign up here: Percona Tools for MySQL - Sign in and use the wizard to set your my.cnf 0 -
Hi Kernow, Thank you for you assistance. I've solved my problem with this tools. Regards 0 -
Hello, I'm happy to see you were able to address the issue. Thank you for updating us with the outcome. 0
Please sign in to leave a comment.
Comments
3 comments