Hi,
I have a hexacore dedicated server, with 64GB RAM, running CentOS 6.4.
Vendor: GenuineIntel
Name: Intel(R) Core(TM) i7-3930K CPU @ 3.20GHz
Speed: 1200.000 MHz
Cache: 12288 KB
The problem I am dealing with is that mysql consumes up to 600% of my cpu (server load up to 70!) every five minutes when the record are taking place.This situation last about 1-2 minutes before the server load comes back to normal at 4-5.
I need some help with the configuration of my.cnf file.
Here is the output of mysqltuner:
Thanks in advance
I have a hexacore dedicated server, with 64GB RAM, running CentOS 6.4.
Vendor: GenuineIntel
Name: Intel(R) Core(TM) i7-3930K CPU @ 3.20GHz
Speed: 1200.000 MHz
Cache: 12288 KB
The problem I am dealing with is that mysql consumes up to 600% of my cpu (server load up to 70!) every five minutes when the record are taking place.This situation last about 1-2 minutes before the server load comes back to normal at 4-5.
I need some help with the configuration of my.cnf file.
Code:
[mysqld]
max_connections=250
max_user_connections=150
query_cache_type=1
query_cache_size=256M
query_cache_limit=70M
tmp_table_size=8M
max_heap_table_size=8M
thread_cache_size=64
table_open_cache=1024
wait_timeout=300
interactive_timeout=300
innodb_file_per_table=1
innodb_buffer_pool_size=4G
innodb_log_file_size=512M
default-storage-engine=MyISAM
local-infile=0
max_allowed_packet=64M
log-slow-queries=/var/lib/mysql/slow.log
open_files_limit=2846
table_cache=4096
join_buffer_size=4M
key_buffer_size=2M
Here is the output of mysqltuner:
Code:
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 348M (Tables: 319)
[--] Data in InnoDB tables: 615M (Tables: 389)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 8)
[!!] Total fragmented tables: 35
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 19h 41m 29s (185M q [562.959 qps], 2M conn, TX: 1909B, RX: 62B)
[--] Reads / Writes: 53% / 47%
[--] Total buffers: 4.3G global + 6.6M per thread (250 max threads)
[OK] Maximum possible memory usage: 5.9G (9% of installed RAM)
[OK] Slow queries: 0% (40K/185M)
[OK] Highest usage of available connections: 61% (154/250)
[!!] Key buffer size / total MyISAM indexes: 2.0M/530.2M
[!!] Key buffer hit rate: 92.5% (39M cached / 2M reads)
[OK] Query cache efficiency: 90.1% (139M cached / 155M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (13 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 6374
[OK] Temporary tables created on disk: 24% (11K on disk / 45K total)
[OK] Thread cache hit rate: 97% (56K created / 2M connections)
[OK] Table cache hit rate: 99% (2K open / 2K opened)
[OK] Open file limit used: 11% (943/8K)
[OK] Table locks acquired immediately: 99% (30M immediate / 30M locks)
[OK] InnoDB data size / buffer pool: 615.2M/4.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Variables to adjust:
key_buffer_size (> 530.2M)
join_buffer_size (> 4.0M, or always use indexes with joins)