Hi,
Thank you for reading. My server is having lots of issues with MySQL and although I am very good at following directions, I am not quite sure what needs to be done.
I went ahead and ran mysqltuner.pl and this was the output:
When I run "top" I see mysqld process jumping from using around 15 to 20% CPU usage all the way up to 250% CPU usage. I am on a dedicated box with the following specs:
4 quad core CPUs - Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
6gb memory
raid drives
httpd 2.2.24 (Unix)
mysql 5.0.96
Here is my /etc/my.cnf file contents:
Also, I ran MySQL Tuning Primer and the results are as follows:
Would love any assistance any of you could provide. Thank you very much!
Thank you for reading. My server is having lots of issues with MySQL and although I am very good at following directions, I am not quite sure what needs to be done.
I went ahead and ran mysqltuner.pl and this was the output:
Code:
>> MySQLTuner 1.2.0_1 - 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.5.30-30.2
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 1928)
[--] Data in InnoDB tables: 16M (Tables: 108)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 2M (Tables: 31)
[!!] Total fragmented tables: 155
-------- Performance Metrics -------------------------------------------------
[--] Up for: 7d 3h 0m 45s (68M q [110.800 qps], 3M conn, TX: 1160B, RX: 17B)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (9% of installed RAM)
[OK] Slow queries: 0% (9K/68M)
[OK] Highest usage of available connections: 60% (91/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.1G
[OK] Key buffer hit rate: 98.1% (10B cached / 196M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (165K temp sorts / 1B sorts)
[!!] Joins performed without indexes: 14052
[OK] Temporary tables created on disk: 19% (324K on disk / 1M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 401K opened)
[OK] Open file limit used: 5% (698/12K)
[OK] Table locks acquired immediately: 99% (84M immediate / 84M locks)
[OK] InnoDB data size / buffer pool: 16.3M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
thread_cache_size (start at 4)
table_cache (> 400)4 quad core CPUs - Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
6gb memory
raid drives
httpd 2.2.24 (Unix)
mysql 5.0.96
Here is my /etc/my.cnf file contents:
Code:
[mysql]
# CLIENT #
port = 3306
socket = /home/mysql/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default_storage_engine = InnoDB
socket = /home/mysql/mysql.sock
pid_file = /home/mysql/mysql.pid
# MyISAM #
#key_buffer_size = 1G
# SAFETY #
skip_name_resolve
innodb = FORCE
# DATA STORAGE #
datadir = /home/mysql/
# INNODB #
# LOGGING #
open_files_limit=12256Also, I ran MySQL Tuning Primer and the results are as follows:
Code:
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.5.30-30.2 x86_64
Uptime = 7 days 2 hrs 32 min 53 sec
Avg. qps = 110
Total Questions = 68011600
Threads Connected = 3
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 9446 out of 68011621 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 2
Historic threads_per_sec = 5
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size
MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 3
Historic max_used_connections = 91
The number of used connections is 60% of the configured maximum.
Your max_connections variable seems to be fine.
INNODB STATUS
Current InnoDB index space = 21 M
Current InnoDB data space = 16 M
Current InnoDB buffer pool free = 68 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 402 M
Configured Max Per-thread Buffers : 415 M
Configured Max Global Buffers : 152 M
Configured Max Memory Limit : 567 M
Physical Memory : 5.81 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 1.14 G
Current key_buffer_size = 8 M
Key cache miss rate is 1 : 53
Key buffer free ratio = 67 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 132.00 K
You have had 14024 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 12256 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_open_cache = 400 tables
Current table_definition_cache = 400 tables
You have a total of 2108 tables
You have 400 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 1346151 temp tables, 19% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 8364 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes.
TABLE LOCKING
Current Lock Wait ratio = 1 : 192
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=ALWAYS'.Would love any assistance any of you could provide. Thank you very much!
