Extremely Slow - VPS, Debian

Hi all. I’ve recently moved a site over to VPS (hosted with ixWebhosing). It has been a massive learning experience. It was all working well for a couple days, however since around 2 days ago the sites have been loading extremely slowly (see sweetmma.com and sweetboxing.com). Around the net I found a lot of people saying this happens when you use up all of your resources - or something like that - and an indication of this would be if ‘swap’ memory is being used. From what I can tell swap memory is not being used (I don’t know what swap memory is, but using the ‘top’ command I’ve never seen anything but zeros for the swap fields). So I contacted by host and they replied with this:

Please accept our apologies for this delay. Yes, we have found the issue reason. There are a lot of locked MySQL queries on your VPS like these ones:

| 3489507 | soumitra_wiki | localhost | soumitra_wikidb | Query | 938 | Locked | UPDATE /* 146.0.73.219 */ `wiki_page` S
ET page_counter = page_counter + 1 WHERE page_id = 1 |
| 3489901 | soumitra_wiki | localhost | soumitra_wikidb | Query | 877 | Locked | SELECT /* LinkBatch::doQuery 207.46.13.9
8 */ page_id, page_namespace, page_title FROM `wiki_page` WH |
| 3490114 | soumitra_wiki | localhost | soumitra_wikidb | Query | 837 | Locked | SELECT /* LinkBatch::doQuery 207.46.199.
46 */ page_id, page_namespace, page_title FROM `wiki_page` W |
| 3490169 | soumitra_wiki | localhost | soumitra_wikidb | Query | 828 | Locked | SELECT /* LinkBatch::doQuery 207.46.199.
46 */ page_id, page_namespace, page_title FROM `wiki_page` W |
| 3490180 | soumitra_wiki | localhost | soumitra_wikidb | Query | 825 | Locked | SELECT /* LinkBatch::doQuery 157.55.16.2
19 */ page_id, page_namespace, page_title FROM `wiki_page` W |
| 3490443 | soumitra_wiki | localhost | soumitra_wikidb | Query | 775 | Locked | SELECT /* LinkBatch::doQuery 65.52.109.2
6 */ page_id, page_namespace, page_title FROM `wiki_page` WH |
| 3490479 | soumitra_wiki | localhost | soumitra_wikidb | Query | 770 | Locked | SELECT /* LinkBatch::doQuery 207.46.199.
46 */ page_id, page_namespace, page_title FROM `wiki_page` W |
| 3490619 | soumitra_wiki | localhost | soumitra_wikidb | Query | 738 | Locked | SELECT /* LinkBatch::doQuery 207.46.199.
46 */ page_id, page_namespace, page_title FROM `wiki_page` W |
| 3491213 | soumitra_wiki | localhost | soumitra_wikidb | Query | 626 | Locked | SELECT /* LinkBatch::doQuery 157.55.16.2
19 */ page_id, page_namespace, page_title FROM `wiki_page` W |


Please recheck and fix your application code.

Now, I’ve check in mysql, we have no username soumitra_wiki and no database soumitra_wikidb. Further, when I run ‘show processlist;’ (and I have many times) I’ve never seen one of these locked queries, although, there is one process which is bothering me:

| 19003 | root | localhost | NULL | Sleep   | 3424 |       | NULL        

It has been sleeping for a long time (the wait_timeout variable is 28800 seconds though - should I change this?).

Anyway, I’m an absolute wreck here, this past month has been horrible for the sites - ix ‘disabled’ our site on shared hosting because it was using up too many resources (that was a week of down time). Then there were a couple hicups while moving to VPS (this is my first time working with VPS, and first time with a Linux OS also). And now this is happening.

Any help/guidance anyone could offer me would be greatly appreciated.

Thanks in advance,

Nick.

Further, I’m not sure the problem is the MySQL Server… sweetboxing.com/weAreMoving.php is a very simple page - all hard-coded, no database access, and it is loading just as slow as the other pages.

My advice would be to hound them a little more. It sounds like they didn’t give you a very satisfactory response, and considering they sent info about a database that you don’t even own, it sounds like they didn’t give your issues much attention. With a higher level service such as VPS, they ought to provide more personalized support. Get them on the phone and bug them until you’re happy!

I may have found the solution. I ran the bash script outlined here - http://aleembawany.com/2010/11/26/vps-configuration-for-apache-mysql-php/ - it changes some of the MySQL, Apache and PHP config settings and things appear to be running a bit better (still slower than it was running on shared hosting - annoying!). In a couple hours I’ll compare the new values with the old value - I’ll probably have some questions regarding that, so I’ll post them in this thread.

Nick.

I also just got this response from my host - let me know what you think (I’m running vBulletin on these sites):

Dear Customer,


We are glad to assist you again.

Please accept our apologies for misguiding you. Looks like provided output was taken mistakenly from another window (another VPS had similar problems with slow sites loading).

As we can see, your VPS is pretty loaded:

top - 01:39:14 up 1 day, 5:45, 1 user, load average: 13.20, 13.97, 9.96
Tasks: 45 total, 13 running, 32 sleeping, 0 stopped, 0 zombie
Cpu(s): 73.3%us, 13.7%sy, 0.0%ni, 13.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 1048576k total, 360220k used, 688356k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
19645 www-data 16 0 230m 21m 4240 R 9.6 2.1 0:01.89 apache2
19655 www-data 15 0 229m 20m 4232 S 7.3 2.0 0:01.68 apache2
19697 www-data 16 0 232m 23m 4012 R 6.0 2.2 0:00.18 apache2
19651 www-data 16 0 230m 21m 4288 S 4.6 2.1 0:01.25 apache2
1540 mysql 15 0 264m 73m 5916 S 3.6 7.2 11:58.89 mysqld
19564 www-data 16 0 229m 20m 4356 R 3.6 2.0 0:04.01 apache2
19659 www-data 16 0 232m 23m 4228 R 2.0 2.3 0:01.30 apache2
19699 www-data 21 0 228m 19m 3568 R 1.7 1.9 0:00.05 apache2
19700 www-data 23 0 228m 19m 3544 R 1.3 1.9 0:00.04 apache2
19701 www-data 20 0 223m 12m 2580 R 0.3 1.2 0:00.01 apache2

Unfortunately you have VPS-X2 plan and some custom Apache configuration so we will not perform further investigation. Also your VPS uses almost all guaranteed memory (384Mb on X2 plan) and we suggest you upgrade your VPS to VPS-X4 and order control panel installation. Thus you will get our basic support and resource limits for VPS will be higher.


Please do not hesitate to contact us if you have any difficulties. We are available 24/7 and glad to assist you anytime.

Thank You.

Sounds iffy response - however, as they said, your load there looks low - it is entirely possible the other VPS was causing your site to have no resources to work on and making it run slow.

I notice on their page it doesn’t say whether it’s a xen or openvz vps, xen is more resilient against overloading than openvz which is often heavily oversold. I’ve generally found that if it’s not specified, it’ll likely be openvz.
(the burstable ram confirms this pretty much)

load average: 13.20, 13.97, 9.96 when in conjunction with your memory/cpu would certainly indicate a server heavily overloaded.

In light of comparatively or better specified vps available elsewhere for the price you are paying, and what would seem to be some questionable support, you might want to consider other options.