Plesk: 8.3
Mysql: 5.0.45
Hi all,
Found this forum via Google and it seemed like a pretty good source of information!
I've recently started experiencing high load issues on a Plesk web-server (hosts approx. 100 websites) I've recently been given to manage, and from looking at htop results this looks to be due to some mysql processes running, as soon as I stop the mysql process load drops dramatically, as soon as it's started it's load jumps again. Obviously just leaving mysql stopped is not an option
heheI've had a play around with mysqltuner and mytop but don't seem to be able to pin-point what is causing the excess load and to be honest my googling thus far hasn't really pointed me in the right direction (besides theses forums!).
Anyone got any ideas on what could be causing this issue or how to track it down? The server had been running for months without issue up until recently.
htop (top processes, high-load is between averaging 12.00-17.00):
1 [|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 72.5%] Tasks: 360 total, 11 running
2 [||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 67.8%] Load average: 12.68 12.57 12.97
3 [||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 64.7%] Uptime: 23:51:57
4 [|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 70.4%]
5 [||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 62.5%]
6 [||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 66.0%]
7 [||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 64.5%]
8 [|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 63.4%]
Mem[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||2290/3954MB]
Swp[| 9/7632MB]
PID USER PRI NI VIRT RES SHR S CPU% MEM% TIME+ Command
26894 mysql 15 0 360M 64128 6020 S 100. 1.6 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 -
26896 mysql 15 0 360M 64128 6020 S 100. 1.6 0:00.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 -
26368 www-data 16 0 427M 136M 4464 S 29.5 3.4 0:00.45 /usr/sbin/apache2 -k start
9406 mysql 15 0 360M 64128 6020 S 27.5 1.6 5:04.86 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 -
24949 psaadm 15 0 163M 25128 15056 S 13.1 0.6 24:10.01 /opt/psa/admin/bin/httpsd
31662 psaadm 16 0 164M 22956 12628 S 12.5 0.6 4:54.44 /opt/psa/admin/bin/httpsd
3204 psaadm 16 0 163M 23760 13688 S 12.5 0.6 25:26.93 /opt/psa/admin/bin/httpsd
23515 psaadm 16 0 163M 22732 12696 S 12.5 0.6 5:25.13 /opt/psa/admin/bin/httpsd
MyTop:
MySQL on localhost (5.0.45-Debian_1ubuntu3.4-log) up 0+00:14:37 [11:50:03]
Queries: 1.9M qps: 2261 Slow: 0.0 Se/In/Up/De(%): 47/00/00/00
qps now: 2291 Slow qps: 0.0 Threads: 14 ( 1/ 4) 47/00/00/00
Key Efficiency: 97.4% Bps in/out: 0.2/ 15.8 Now in/out: 8.3/ 1.4k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
105633 admin localhost psa 0 Query show full processlist
106243 ****user localhost ***DB 0 Sleep
106252 ****user localhost ****DB 0 Sleep
106256 admin localhost psa 0 Sleep
106259 admin localhost psa 0 Sleep
106262 admin localhost psa 0 Sleep
106263 admin localhost psa 0 Sleep
106264 admin localhost psa 0 Sleep
106265 admin localhost psa 0 Sleep
106266 admin localhost psa 0 Sleep
106267 admin localhost psa 0 Sleep
106268 admin localhost psa 0 Sleep
106269 unauthent localhost 0 Connec Reading from net
106270 admin localhost psa 0 Sleep
93348 ***stem localhost v***_**x 105 Sleep
MySQLTuner Output:
>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>> 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.45-Debian_1ubuntu3.4-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 298M (Tables: 2610)
[--] Data in InnoDB tables: 26M (Tables: 345)
[!!] Total fragmented tables: 307
-------- Performance Metrics -------------------------------------------------
[--] Up for: 14m 0s (1M q [2K qps], 101K conn, TX: 551M, RX: 97M)
[--] Reads / Writes: 7% / 93%
[--] Total buffers: 58.0M global + 2.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 320.5M (8% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 44% (44/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/105.0M
[OK] Key buffer hit rate: 97.5% (187K cached / 4K reads)
[OK] Query cache efficiency: 99.2% (898K cached / 905K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
[!!] Joins performed without indexes: 153
[OK] Temporary tables created on disk: 0% (214 on disk / 295K total)
[OK] Thread cache hit rate: 95% (4K created / 101K connections)
[!!] Table cache hit rate: 0% (64 open / 13K opened)
[OK] Open file limit used: 10% (105/1K)
[OK] Table locks acquired immediately: 99% (110K immediate / 110K locks)
[!!] InnoDB data size / buffer pool: 26.1M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 64)
innodb_buffer_pool_size (>= 26M)