HostGator Web Hosting Help

CPU Performance Issues

Summary

HostGator uses a CPU protection system on our shared servers.



What is CPU Protection?

HostGator employs a system that monitors server resources like the CPU and memory. It makes sure that every user on a shared server gets their fair share of the system resources. It also prevents other users from causing performance issues for other accounts. This system is in place on our shared servers so that each user has the least chance of being affected by issues cause by another user. If a user is found to be abusing the server and it is somehow affecting other customers we will identify the user and take corrective action. This sometimes means suspending the user. Dedicated and VPS servers do not have this protection system.

Performance issues and Suspensions are usually caused by:

  • Poorly coded PHP scripts such as multiple nested loops, or infinite loops.
  • Themes, Addons, or Plugins (if you just added a new component to your script, you may want to remove it to see if that new component caused your site to suddenly perform slowly)
  • Queries that return inordinately large dataset results (eg: 100,000 records) in the response object of a round trip to the server.
  • Queries that take too long to execute due to heavy aggregation commands like SUM, COUNT over very large datasets without proper indexing.
  • Queries that are doing complex unoptimized nested joins.
  • Queries that execute large batch commands like INSERTS, UPDATES, OR DELETES - these types of SQL calls are 10 times more expensive and resource intensive than the SELECT query command.
  • DDoS Attacks
  • Any process that uses too much memory, like a rogue cron job
  • Too many simultaneous connections like Downloads, FTP, IMAP, or PHP

How can you troubleshoot performance issues?

The MySQL Slow Queries log can help determine which queries or scripts are unoptimized. Login into HostGator's cPanel. Look for the section called "Files" and click the icon called "File Manager". Start in the "Home Directory", choose Show Hidden Files, and then click Go. Once in the File Manager, look on the right side for a folder called "tmp" and navigate into that folder. Once inside the tmp folder, find a folder called "mysql_slow_queries". Navigate inside that folder.

Look for the files with recent dates and right-click on one of those files and choose "Code Edit". This will show the log details. Look for the logs where the Query_time is over 2 seconds. Queries that take over 2 seconds to execute usually need to be re-factored.

These logs are generated to help troubleshoot database programming queries that are not optimized. Locate the query and database causing the problem, then optimize the query or queries by re-writing the query, creating an appropriate Database Index, repairing any damaged databases, etc. If you are not familiar with database programming, you may need to seek help from your web developer/programmer.

We strongly reccomend that you backup your databases before working on them.

What are some simple steps you can perform to try to fix your database issues?

  1. You can run a repair on all your databases. In the cPanel, click on the MySQL Databases icon and in the Repair section, choose a database to repair. You can safely run a Check and Repair routine on all of your databases.
  2. You can try to optimize all of your databases. In the cPanel, click the PHP MyAdmin icon. On the next screen, choose a database name from the left side column. When the tables appear on the main screen, scroll to the bottom and click the link "Select All". Next, use the drop-down menu, and on the bottom, choose the "Optimize" option. You can perform this optimize routine to all of the tables in all of your databases.

Performing these two simple steps will not harm your important data as these steps above are non-intrusive maintenance routines. As such, they may not fix the core issue as well. For instance, if you have severe data corruption, it can cause your queries to time out before they fully execute. Data corruption can be difficult to detect, and repair. Database corruption can occur if a database operation did not completely succeed. Partial data is a form of corruption which can break Referential Integrity, causing your web application to fail also. In short, the free Repair or Optimization routines that were just mentioned may not be sufficient to fix severe database corruption.

What are some Advanced Steps that you can perform on your databases?

  1. You can download your websites onto your own local machine and create a local web development environment to test your sites. You can then set Break Points in your scripts and watch your computer's CPU performance as you step through your stack and heap. Try to stress test your queries as well to see how they respond under a load.
  2. You can empty tables that are merely statistic tables. When viewing your tables, you can see how many entries each one has and how many KB or MB it is using. Sometimes tables get thousands or millions of entries that just log statistical type of data, like what searches people have done on your site or what people have logged in, etc. If you have very large statistic tables, it may help optimize your site if you remove the records or rows within those tables. Warning: you should always consult the vendor of your scripts, or your web developer, before you attempt this - deleting any data in your database can potentially break your web application.
  3. You can refactor your scripts so that heavy duty aggregation or crunching is not all being done server-side. For example, you can rewrite your scripts so that they use Ajax calls, where your scripts are doing some of the CPU crunching in your Javascript. This way, you are leveraging your client's CPU time instead of just your server's.
  4. You can ask our team to restore a courtesy system backup if we have one on file for you. The system backup is a snapshot of exactly how your site performed on a certain date. Doing a restore will not guarantee that your sites will be fixed, as your core issue may have existed at the time our System Backup Tool created your backup. Rolling back to a system backup will restore all of your sites, and not just a portion. Feel free to contact us to see if we have a System Backup available.
  5. You can also ask us to do a full Reset of your account. This will delete everything on your account like it was on day one when you first signed up with us. Your account would be empty, containing no files, databases, email accounts etc. This would ensure a fresh start so that you can re-install all of your scripts with clean installations and clean databases. If you think that this is an option for you, please create backups of your data first.


Was this resource helpful?

Did this resolve your issue?


Please add any other comments or suggestions about this content:





Recommended Help Content

How to repair and optimize your database using phpMyAdmin:
Knowledgebase Article 442,999 views tags: database mysql optimizing repairing slow speed

Optimizing MySQL: Queries and Indexes Article 3 of 4 Courtesy of: Ian Gilfillan Ordering by surname is a common requirement, so it would make sense to create an index on surname. But in this example o
Knowledgebase Article 222,287 views tags: cpu exceeded index indexes mysql optimize optimizing queries query

Optimizing MySQL: Queries and Indexes Article 4 of 4 Courtesy of:Ian Gilfillan Most systems need to be highly optimized for selects - take a news site which performs millions of queries per day, but w
Knowledgebase Article 216,823 views tags: cpu exceeded index indexes mysql optimize optimizing queries query

Related Help Content

Optimizing MySQL: Queries and Indexes Article 1 of 4 Courtesy of: Ian Gilfillan Badly defined or non-existent indexes are one of the primary reasons for poor performance, understanding and then fixing
Knowledgebase Article 221,875 views tags: indexes mysql optimize optimizing queries query

Optimizing MySQL: Queries and Indexes Article 2 of 4 Courtesy of: Ian Gilfillan Some knowledge of how indexes work allows you to use them more efficiently. Firstly, note that when you update a table w
Knowledgebase Article 188,812 views tags: indexes mysql optimize optimizing queries query

How do I make my MySQL database compatible with the version you provide?
Knowledgebase Article 246,463 views tags: compatible database mysql

This article will explain MySQL database and user creation and deletion, and explain how to assign a user to a database.
Knowledgebase Article 875,143 views tags: creation database mysql

How do I import a backup of my database (.sql file) using phpMyAdmin?
Knowledgebase Article 783,029 views tags: database mysql phpmyadmin

This article will explain how to change the password for a database in the event that the original password is lost.
Knowledgebase Article 264,994 views tags: database mysql password

How can I connect to our MySQL database from Dreamweaver?
Knowledgebase Article 272,410 views tags: connecting database dream mysql weaver

How to Restore your MySQL Database?
Knowledgebase Article 384,953 views tags: admin database mysql php phpmyadmin restore