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