Portal Home > Knowledgebase > Articles Database > mysql is killing my vps...
mysql is killing my vps...
| Posted by RS Shamil, 07-14-2008, 01:20 PM |
| Since yesterday, the server load averges on my VPS have been very high - escalating to 6.5 in cases.
The process causing this is:
PidOwnerPriorityCpu %Mem %Command 7370 mysql -10
76.7
3.0 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/opal.ngwebservers.net.pid --skip-external-locking
My VPS is hosted by Virpus Networks, and has spec:
512RAM (1024 burstable), 10GB hard drive space.
8 of these processors on node:
Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Xeon(TM) CPU 2.80GHz
Processor #1 speed: 174.594 MHz
Processor #1 cache size: 2048 KB
No hardware or software changes were implemented on the VPS as far as I am aware.
The MySQL process causing this is:
28993movies_mybblocalhostmovies_mybbQuery36Copying to tmp tableSELECT t.tid, t.dateline, p.edittime, t.subject, f.allowhtml, f.allowmycode, f.allowsmilies, f.allow
This has been going on since I first saw the loads go high...
|
| Posted by unity100, 07-14-2008, 01:35 PM |
| that sql query seems to be complex. check out whether it has any JOIN parts on the rest of the query, and adjust join buffer in my.cnf . also adapt the my.cnf file for 1 gb ram for your purposes. you can google for "my.cnf for 1 gb". you also should have my-large.cnf my-huge.cnf in your mysql lib folder.
|
| Posted by unity100, 07-14-2008, 01:47 PM |
| also mysql is trying to copy it to temporary table.
|
| Posted by RS Shamil, 07-14-2008, 01:53 PM |
| Ty, I'm migrating the mysql databases off and onto a new server to help clear things up.
|
| Posted by SysAdminMan, 07-14-2008, 04:05 PM |
| Hi,
I'm not sure if this directly relates to a VPS (my guess is that it does if it's a VZ VPS - not sure about Xen) but it's says here that the 'perfect' values for load averages is [number of cpus x 1.0] - so in your case 8.
So that implies, with a value of 6, the cpu's are still a little under worked.
Matt
|
| Posted by user24787, 07-15-2008, 07:06 AM |
| We have quite alot of "PTC" Site customers who use ALOT of MySQL Resources, When they hit the level of using alot of resource and there single VPS can't manage anymore instead of an upgrade we usually provide them with a 2nd VPS from which they can run MySQL alone, It's a big application and does cause alot of outage on a small high resource VPS.
|
| Posted by tsj5j, 07-15-2008, 01:51 PM |
| I used to stand by that, but I've come to realize that load averages are getting irrelevant.
A high load average may not mean your CPU is overworked, it can mean your swapping heavily, etc.
There are many reasons for a high load average.
Perhaps you can try to identify the bottleneck.
Also, ensure your MySQL settings are tweaked for your configuration.
Lastly, query optimization and caching can help reduce resource usage and save prices in the long run.
|
| Posted by SysAdminMan, 07-15-2008, 03:54 PM |
| I totally agree. My post was meant to convey that using "load average" in isolation as a measure of how 'busy' the server was is probably not a good idea as it only measures a very specific factor - cpu queue length.
Even though, in that case, 6.5 means the cpu's weren't working at full capacity it takes no account of disk, memory, network usage.
|
| Posted by RS Shamil, 07-15-2008, 06:11 PM |
| Ok - we have the problem: mysql copying table to tmp table ....
|
Add to Favourites 
Print this Article
Also Read
