We have customers from time to time be real resource hogs. We’ve found two common types of users using all the resources
- New scripts / features installed
The common issue here is users simply not researching the scripts before installing them. A good first step is to check out the scripts forums for users and see what the general consensus is in terms of resource usage. If things are good then install it on your hosting account. If it’s not great, and other users are reporting that they are getting booted from there hosts it may not be a great script in terms of CPU usage. Look for ways in improving the resource usage if possible.The next step is to make sure the script is still being developed! There is nothing worse then installing a script from 2005 then having it exploited over and over again. Or it simply being so out of date common practices to improve performance are non existent.
- Testing of scripts
It is never a good idea to be testing scripts in a live environment especially in a shared hosting environment. If you’re creating infinite loops, bad queries technicians are going to pick up on this. If it’s very serious you could very well see your hosting disabled. It is always best to test them off site on your computer first to make sure they’re working properly! It’s also easier to develop when it’s on your computer as well, there also will be no need to be uploading files constantly. I would recommend using XAMPP to doing your coding and testing. It is a simple interface that will install Apache, PHP4, PHP5 and MySQL5. Along with that for it’s PHP setup it installs all the common PHP modules so no worries having to modify configuration files. Once you’ve done testing on your computer, upload your files and the chances of the script causing issues are way lower as you’ve already ran it through your computer. So bad queries would probably bog your machine down and bugs causing infinite loops would probably be gone as well.
We sometimes have new customers come from other hosts where they had their account suspended due to resource usage. We love the fact they’ve chosen Hawk Host as their new host, but at the same time if it’s due to bad coding and not a bad host the problem will continue. So if you’re one of the few where it was a bad script, and we’re contacting you about it then it may be time to evaluate your script. We’ve always been willing to discuss possible problems that are causing your high usage. A lot of our staff are great with PHP and can pick up a lot of the common problems pretty fast which other hosts just say a.php is using 100% cpu fix it! Not to say we’ll go back and fix everything for you, however if it’s something we can find in 5 minutes we’ll gladly give suggestions.
So now you’re wondering well Tony what are the common CPU usage things that happen with regards to PHP / MySQL scripts?
- Lack of understanding of MySQL Joins
Well a very common problem is for a user to not make use of joins of table while querying the database. So for example if you wanted to get a listing of messages as well as the users information. You’d loop the messages table but instead of grabbing the user info with a join you do another query for each message to grab the user information. So for one page of say 100 messages that is 100 queries just to grab every single user!So to summarize this one probably be not using mysql joins resulting in large number of MySQL queries
- Unnecessary use of Loops Another problem we see quite a bit is the use of multiple loops when it is not needed. Always be creative when you are coding! Using 3 or 4 loops when a bit of creativity could result in 1 loop is a big issue. You end up going through a loop 100,000 times opposed to just 100 times which would be possible with a bit of PHP knowledge and creativity.
- Not Using Indexes On Fields In MySQL!
This has to be the biggest problem for us with regards to MySQL CPU usage. When you join multiple tables together on a common field it is always best to have an index on the foreign table. So for example if you are wishing to list all the messages made by a certain user. The messages table has a user_id field to identify the user. Make sure this field is an indexed field on the messages table! Otherwise what happens each time you do this query you use a lot of CPU time and put strain the hard drives due to MySQL having to scan the entire table to find messages with that user. Opposed to an index that is much much faster and doesn’t run into as many issues as a table grows.So some recent success with this? Well we had a customer with a lot of MySQL usage and we asked them about it and they had no idea. So we decided to look at the queries they were running (with their permission of course). We found a join of two tables without an index. We turned a query that took 5 seconds down to 0.0005 seconds thanks to adding an index! Yep now the user is humming along and we don’t even notice their mysql queries anymore.
Well I hope this has been informative for everyone on shared hosting resource usage. Hopefully it helps some of you stay in a shared environment longer opposed to moving to another host or purchasing a dedicated server which may not be a good solution at all. It could be as simple as 5 minutes of work to fix up your CPU usage problems. Or in some cases as simple as doing your development on your computer before bringing it online to the shared hosting server.