MySQL High Server Load

Portal Home > Knowledgebase > Articles Database > MySQL High Server Load

Posted by uninet, 12-18-2008, 02:52 AM
MySQL High Server Load MySQL the last few days seems to be constantly the most demanding process in top, which it never was before. As far as I can tell, nothing has substantially changed with regards to traffic to MySQL driven sites on the server. Is there anything that might be wrong with the databases, etc., that might throw MySQL into a tizzy? Thanks! __________________Universal Networks Web Design, Online Publishing and ServerForest Web Hosting
Posted by The Universes, 12-18-2008, 03:25 AM
Run mysqltuner and tuning primer and lets have a look. (that will rule out some obvious problems)http://wiki.mysqltuner.com/MySQLTune...ing_MySQLTunerhttp://www.day32.com/MySQL/tuning-primer.sh __________________The Universes - Server/VPS Management and PHP/MySQL Application Development
Posted by uninet, 12-18-2008, 01:04 PM
Thanks for your help! Some of the stuff jumps out at me as things I probably should adjust, others I'm not sure how serious they are. How's this look to you? Quote: Originally Posted by mysqltuner >> MySQLTuner 1.0.0 - 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.0.67-community [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 4G (Tables: 2195) [--] Data in InnoDB tables: 1M (Tables: 66) [--] Data in MEMORY tables: 0B (Tables: 1) [!!] Total fragmented tables: 66 -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 12h 43m 23s (6M q [48.847 qps], 53K conn, TX: 14B, RX: 1B) [--] Reads / Writes: 47% / 53% [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads) [OK] Maximum possible memory usage: 1.4G (35% of installed RAM) [OK] Slow queries: 0% (2K/6M) [OK] Highest usage of available connections: 37% (187/500) [OK] Key buffer size / total MyISAM indexes: 8.0M/135.9M [OK] Key buffer hit rate: 99.2% (3B cached / 24M reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (862 temp sorts / 694K sorts) [!!] Joins performed without indexes: 156121 [OK] Temporary tables created on disk: 11% (37K on disk / 314K total) [!!] Thread cache is disabled [!!] Table cache hit rate: 0% (64 open / 267K opened) [OK] Open file limit used: 3% (124/4K) [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks) [OK] InnoDB data size / buffer pool: 1.7M/8.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 (> 64) Quote: Originally Posted by tuning primer mysqld is alive -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.0.67-community x86_64 Uptime = 1 days 12 hrs 46 min 30 sec Avg. qps = 48 Total Questions = 6461609 Threads Connected = 7 Warning: Server has not been running for at least 48hrs. It may not be safe to use these recommendations To find out more information on how each of these runtime variables effects performance visit:http://dev.mysql.com/doc/refman/5.0/...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 sec. You have 2025 out of 6461640 that take longer than 10 sec. to complete Your long_query_time may be too high, I typically set this under 5 sec. 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.0/...-recovery.html WORKER THREADS Current thread_cache_size = 0 Current threads_cached = 0 Current threads_per_sec = 1 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 500 Current threads_connected = 7 Historic max_used_connections = 187 The number of used connections is 37% of the configured maximum. Your max_connections variable seems to be fine. MEMORY USAGE Max Memory Ever Allocated : 532 M Configured Max Per-thread Buffers : 1 G Configured Max Global Buffers : 17 M Configured Max Memory Limit : 1 G Physical Memory : 3.86 G Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 134 M Current key_buffer_size = 7 M Key cache miss rate is 1 : 129 Key buffer fill ratio = 100.00 % You could increase key_buffer_size It is safe to raise this up to 1/4 of total system memory; assuming this is a dedicated database server. 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 133498 queries where a join could not use an index properly You have had 23712 joins without keys that check for key usage after each row 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 = 4096 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_cache value = 64 tables You have a total of 2279 tables You have 64 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 TEMP TABLES Current max_heap_table_size = 16 M Current tmp_table_size = 32 M Of 279197 temp tables, 13% were created on disk Effective in-memory tmp_table_size is limited to max_heap_table_size. Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 128 K Current table scan ratio = 1037 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 1065 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=2'. Thanks again! I've never waded into optimizing MySQL -- probably should have, but other issues always seemed to take priority! __________________Universal Networks Web Design, Online Publishing and ServerForest Web Hosting
Posted by uninet, 12-22-2008, 01:13 AM
I tried a lot of those tweaks, but the problem seems to be getting worse. Is there a way I can limit the per-user resource usage of MySQL? It seems to me that one of two users is causing the crazily high usage of MySQL and it'd be great if I could keep those accounts from hurting the rest (giving an average server load of 20 on a quad core system that normally is around a load of 1-2). Thanks! __________________Universal Networks Web Design, Online Publishing and ServerForest Web Hosting
Posted by gate2vn, 12-22-2008, 02:23 AM
which version of mySQL are you using? I find out that the 5.1 version is better than 5.0 __________________3inetworks JSCUS west coast managed hosting solutionsclustered hosting
Posted by Steven, 12-22-2008, 02:38 AM
Quote: Originally Posted by uninet I tried a lot of those tweaks, but the problem seems to be getting worse. Is there a way I can limit the per-user resource usage of MySQL? It seems to me that one of two users is causing the crazily high usage of MySQL and it'd be great if I could keep those accounts from hurting the rest (giving an average server load of 20 on a quad core system that normally is around a load of 1-2). Thanks! There is no real way to limit in the way you want. Generally I recommend utilizing a compiled vanilla linux kernel with minimal drivers installed. And then tweaking mysql based on what the server wants. Those scripts are good, but they are not the best. mysqladmin processlist will show you big time users usually __________________Steven CiaburriSystem Administration ExtraordinaireCompetent Linux Server Management from Rack911
Posted by The Universes, 12-22-2008, 03:05 AM
So you have increased your table cache, thread cache? Your only doing 50 queries per second, which is nothing. Enable slow queries log. Are your swapping by any chance? __________________The Universes - Server/VPS Management and PHP/MySQL Application Development
Posted by Steven, 12-22-2008, 04:13 AM
Quote: Originally Posted by The Universes So you have increased your table cache, thread cache? Your only doing 50 queries per second, which is nothing. Enable slow queries log. Are your swapping by any chance? I wouldn't say 50 queries a second is nothing. It depends on the type of query. In the world of system administration there is not a one size glove. What is nothing on one situation is something on another. He should tune several other variables also. key_buffer, join_buffer. I personally would revise the entire config. I would look into the kernel as I mentioned before. Just by some kernel tweaks I have seen load get dropped by more then half without anything else done to the server (keep in mind its not always this way, but its pretty common). __________________Steven CiaburriSystem Administration ExtraordinaireCompetent Linux Server Management from Rack911 Last edited by Steven : 12-22-2008 at 03:18 AM.
Posted by uninet, 12-22-2008, 10:39 PM
Thanks, everyone. Here's what I have configured in my my.cnf right now: Quote: [mysqld] set-variable = max_connections=500 safe-show-database query_cache_size=16M join_buffer_size=512K table_cache=96 key_buffer_size=32M thread_cache_size=4 All but the first two I added after running those two programs. I tried to add at least what those scripts asked for. The first two were added by cPanel/WHM during setup. Regarding the Linux kernel, right now I'm running a custom RHEL kernel compiled by SoftLayer to work with their hardware configuration; I think they recommend against self-compiling kernels since it can disrupt some features (I believe with the service monitoring/reboot function), but I can ask them to recompile it with given settings if it need something. Swap seems pretty good, I think: Quote: Mem: 4050776k total, 4027412k used, 23364k free, 12672k buffers Swap: 2096440k total, 152k used, 2096288k free, 3272204k cached I'm running MySQL 5.0.67 from RHEL. How significant do you think the upgrade to 5.1 would be performance wise? I rather like staying within the distribution, since RedHat keeps offering security updates for so long, but I'm not totally adverse to compiling something new. It will take me back to the old days when most of what I wanted to run on Linux had to be compiled Oh, to give you an idea on the server, it is a Xeon 3220 (Quad Core) with, as you can see above, 4 GB of ram. MySQL seemed happy enough until recently. Right now something is odd, because it was running better for me back on my old Celeron 2.4 GHz system I retired this summer. Thanks again for the advice! __________________Universal Networks Web Design, Online Publishing and ServerForest Web Hosting

Add to Favourites Print this Article