Moodle in English: overload warning, without commensurate traffic

overload warning, without commensurate traffic

by Benz Kohlee - Friday, April 28, 2006, 12:42 AM

Hello All,

My hearty thanks to Martin Dougiamas, and the whole moodle community, for making the effort, that makes moodle possible!

I have been using moodle for over a year and half. And through this wonderful journey, have had 3 hosts. Finally settled on two, and apart from site inaccessibility here and there, everything seemed smooth. Until today when I was not able to access my moodle site. It was constantly giving this error:

Service Temporarily Unavailable

The server is temporarily unable to service your request due to
maintenance downtime or capacity problems. Please try again later.

So, I went to my host's website to find out if there was some news on the server status. Upon going there, I found that the server status was fine. Consequently, I proceeded to open a ticket.
To my dismay I found an "Abuse" ticket already open. And then I saw an email from the host:

We've noticed that your account is consuming too much of the server resources and which resulted in a server overload. We were forced to limit the account as to prevent further damage to the server.

At first I was really confused, as the traffic has been nothing to scream about. And then I was excited, that my site was getting so much traffic. Being cautious, I wanted to confirm this and checked my stats. All the stats I checked, told me that the traffic is nominal. Now I was confused. For example, my bandwidth for the whole month so far is under 1 Gig, and they allow 500Gigs, and the hits are also inconsiderable, basically what I have been getting lately, around 100 to 300 hits per day.
So I opened the ticket, informing them of the discrepancy between what they were saying and what my moodle and traffic logs were saying, and they replied:

Your account is overloading with too heavy mysql queries, it is not up to traffic or disk space usage. Here is a part from the log we have :

Then I read the log that they sent me, and all but one of the entries in the log are from two weeks ago. Now, I am not a mysql expert, but I am also not a computer dummy. So far I cannot figure out exactly what the log is saying, but from what little I know, I don't see the problem. Here is an excerpt from the host's log that they emailed to me, I have edited out the key, and put "key_deleted" instead:

| 277000 | knowcuts_mdle2 | localhost | knowcuts_mdle2 | Query | 44 | Sending data | SELECT * FROM mdl_cache_text WHERE md5key = 'key_deleted' AND timemodified > '1 |

report-2006-04-16-02-18: | 1426091 | knowcuts_mdle2 | localhost | knowcuts_mdle2 | Query | 4 | Sending data | SELECT * FROM mdl_cache_text WHERE md5key = 'key_deleted' AND timemodified > '1 |
report-2006-04-16-02-18: | 1426145 | knowcuts_mdle2 | localhost | knowcuts_mdle2 | Query | 4 | Sending data | SELECT * FROM mdl_cache_text WHERE md5key = 'key_deleted' AND timemodified > '1 |
report-2006-04-16-02-18: | 1426188 | knowcuts_mdle2 | localhost | knowcuts_mdle2 | Query | 4 | Sending data | SELECT * FROM mdl_cache_text WHERE md5key = 'key_deleted' AND timemodified > '1 |
.
.
.

Followed by the following note from the host tech support:

As you can see all the queries take too long for execution, please note that the normal time for execution of mysql query should be less than a second. You need urgent database optimization and indexing. We strongly recommend that you use the attached server log, depicting the overloads caused by your website, and consult a professional developer to investigate if your website can be optimized and perform this optimization if possible.

Can anyone please advise how I can resolve this issue? Can I optimize and index the mysql databae myself. I am kind of confused, as after that suggestion, my host is also advising me to upgrade to their VPS account. Is this the solution?

In order your full website functionality be restored without any actions required on your side you have another option - transfering to a VPS (Virtual Private Server). Once accounts on a shared hosting exceed the allowed resources they need to migrate to a VPS solution. The VPS option is much better than shared hosting because all the server resources are dedicated to you only, for example, on shared hosting all server resources are shared among hundreds of users (accounts).

The traffic on the complaining host is nominal, and I have another host where I keep my video content, and their traffic is nominal too, but much higher than the complaining host.

If you need any more info, please ask.

And thank you all for reading, and answering.

Benz

Re: overload warning, without commensurate traffic

by Benz Kohlee - Friday, April 28, 2006, 6:38 AM

Hi All,

After doing some research, I found out that the problem may have something to do with the mdl_cache_text table being corrupt and/or too full. So I proceeded to the mysql interface, and first backed up my data, then repaired the table, and then emptied the table.
Almost instantly there was a dramatic improvement. Where before I was getting the "Service Temporarily Unavailable" message every so often, now after about 4 hours of accessing the site , there are no issues any more so far. And dare I say, it seems faster as well, but that could be a psychological feeling.

Hope this helps someone.

Thanks

Benz

Average of ratings: -

Re: overload warning, without commensurate traffic

by Samuli Karevaara - Friday, April 28, 2006, 2:11 PM

It could be that the cron job is not being ran on your site, so the cache table is never cleaned up.

Average of ratings: -

Re: overload warning, without commensurate traffic

by Benz Kohlee - Thursday, May 4, 2006, 12:13 AM

Thanks for your participation. Unfortunately, the host is still giving me a "Server Temporarily Unavailable" (STU) message intermittently.
After I read your suggestion and researched the moodle forums and consulted several of my colleages, I have done the following:

Ran the cron manually every day.
Repaired the database in phpadmin
optimized each table in the database
emptied the cache table several times

I enabled the debug option in the variables settings. However, I am not sure what the following means exactly. It appears at the bottom of the page. Can anyone please clarify what each variable represents?

0.187576 secs
Included 51 files
ticks: 19 user: 16 sys: 1 cuser: 0 csys: 0
Load average: 4.60

It seems that whenever the load average is high is when the STU appears. Can anyone clarify.

Thanks

Benz

Average of ratings: -

Re: overload warning, without commensurate traffic

by Benz Kohlee - Friday, May 12, 2006, 6:35 AM

Here is an update:
While performing a lot of reading, and consulting, and optimizing, flushing the db, planning and almost upgrading to 1.5.3, and 1.6, planning, downloading and almost switching to postgresql, downloading and learning about LAMP, XAMPP, WIMP etc (also got another host in the mean time), and basically learning a lot about stuff that I really never hoped to care about, I also started to observe the following:
Everytime I would get the Service Temporarily Unavailable, I noticed the following performance lags as well:

the hosts ftp service would be slow, around .5kbps to 1.5kbps
the hosts cpanel would be crawling
an empty, fresh moodle install would also give the same message
, and recently I tried a simple html file (a friend suggested I try) with a few lines of text only, would also give the Server Temporarily Unavailable message

Now it doesn't take someone with too keen of a mind to figure out what is going on.
I have informed the host, and they keep suggesting that I upgrade to a VPS account. Is this acceptable service?
My initial goal was to create a healthy relationship with the host so that as the site grew I would stick with them, getting more capable services. Therefore, I have been very polite and professional with them, and have informed them on a timely basis without making it personal. They seem to be ignoring my emotional intelligence, probably taking it for ignorance or stupidity, repeatedly stating that I should upgrade to VPS.
I feel cheated, because they immediately opened an abuse ticket. I feel drained because I have done everything in my power (sleepless nights, lots of coffee, and even disabling my marketing campaign to reduce traffic to negligible, as more than 50% of the people coming to the site were getting the unavailable error according to awstats) to make sure that my moodle install was not at fault and now with the evidence that even a simple html file gives the same message, I feel even more betrayed.
Rest assured, I am not going to be renewing my contract with them, but in the mean time, I am stuck with a host that refuses to admit that their servers are so overloaded, that they can't even serve an html file with a 8 lines of text.

What would you do?

drained, tired, and weary of 99.9% claims of uptime,

Benz

Average of ratings: -

Hardware and performance

overload warning, without commensurate traffic