need assistance with my.cnf - 1500% CPU usage
- by Alan Long
I'm running into a few issues with our new database server. It is a HP G8 with 2 INTEL XEON E5-2650 processors and 32GB of ram. This server is dedicated as a MySQL server (5.1.69) for our intranet portal. I have been having issues with this server staying alive - I notice high CPU usage during certain times of day (8% ~ 1500%+) and see very low memory usage (7 ~ 15%) based on using the 'top' command. When the CPU usage passes 1000%, that is when the app usually dies. I'm trying to see what I'm doing wrong with the config file, hopefully one of the experts can chime in and let me know what they think.
See below for my.cnf file:
[mysqld]
default-storage-engine=InnoDB
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#user=mysql
large-pages
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=275
tmp_table_size=1G
key_buffer_size=384M
key_buffer=384M
thread_cache_size=1024
long_query_time=5
low_priority_updates=1
max_heap_table_size=1G
myisam_sort_buffer_size=8M
concurrent_insert=2
table_cache=1024
sort_buffer_size=8M
read_buffer_size=5M
read_rnd_buffer_size=6M
join_buffer_size=16M
table_definition_cache=6k
open_files_limit=8k
slow_query_log
#skip-name-resolve
# Innodb Settings
innodb_buffer_pool_size=18G
innodb_thread_concurrency=0
innodb_log_file_size=1G
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=50
innodb_file_per_table
#innodb_buffer_pool_instances=4
#eliminating double buffering
innodb_flush_method = O_DIRECT
flush_time=86400
innodb_additional_mem_pool_size=40M
#innodb_io_capacity = 5000
#innodb_read_io_threads = 64
#innodb_write_io_threads = 64
# increase until threads_created doesnt grow anymore
thread_cache=1024
query_cache_type=1
query_cache_limit=4M
query_cache_size=256M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 0
wait_timeout = 1800
connect_timeout = 10
interactive_timeout = 60
[mysqldump]
max_allowed_packet=32M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-slow-queries=/var/log/mysql/slow-queries.log
long_query_time = 1
log-queries-not-using-indexes
we connect to one database with 75 tables, the largest table has 1,150,000 entries and the second largest has 128,036 entries. I have also verified that our PHP queries are optimized as best as possible.
Reference - MySQLtuner:
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> 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.69-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 420M (Tables: 75)
[!!] Total fragmented tables: 75
-------- Security Recommendations -------------------------------------------
[!!] User '[email protected]' has no password set.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 14m 50s (8M q [1K qps], 705 conn, TX: 6B, RX: 892M)
[--] Reads / Writes: 68% / 32%
[--] Total buffers: 19.7G global + 35.2M per thread (275 max threads)
[!!] Maximum possible memory usage: 29.1G (93% of installed RAM)
[OK] Slow queries: 0% (472/8M)
[OK] Highest usage of available connections: 66% (183/275)
[OK] Key buffer size / total MyISAM indexes: 384.0M/91.0K
[OK] Key buffer hit rate: 100.0% (173 cached / 0 reads)
[OK] Query cache efficiency: 96.2% (7M cached / 7M selects)
[!!] Query cache prunes per day: 553614
[OK] Sorts requiring temporary tables: 0% (3 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 49% (3K on disk / 7K total)
[OK] Thread cache hit rate: 74% (183 created / 705 connections)
[OK] Table cache hit rate: 97% (231 open / 238 opened)
[OK] Open file limit used: 0% (17/8K)
[OK] Table locks acquired immediately: 100% (432K immediate / 432K locks)
[OK] InnoDB data size / buffer pool: 420.9M/18.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Increasing the query_cache size over 128M may reduce performance
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 256M) [see warning above]
Thanks in advanced for your help!
