High Server Load -- Would Appreciate Any Help Please

GasMan320

• September 30, 2013 23:54

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: >> MySQLTuner 1.2.0_1 - Major Hayden >> 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)
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: [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=12256
Also, I ran MySQL Tuning Primer and the results are as follows: -- 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!

• 

  cPanelMichael

  • October 01, 2013 12:42

  Hello :) The following thread may be useful if you are not positive that MySQL is causing the high load average: Troubleshooting high server loads on Linux servers Thank you.

  0
• 

  thinkbot

  • October 03, 2013 13:48

  uncomment this #key_buffer_size = 1G to key_buffer_size = 1200M and add under it max_connections = 100 query_cache_type = 1 query_cache_size = 50M query_cache_limit = 1M thread_cache_size = 30 table_cache = 2048 join_buffer_size=1M read_rnd_buffer_size=1M then restart

  0

Please sign in to leave a comment.