Topics:

Check Your Database Status

  1. Log into the Account Control Center (ACC)
  2. Click Databases in the left sidebar
  3. Click Manage Your Databases
  4. Under Database Name, click on the name of the database you want to check
  5. Click the phpMyAdmin button
  6. Enter your database password and click Login to phpMyAdmin
  7. Under the phpMyAdmin logo in the upper left hand corner, click the “Home” icon
  8. Click Status in the top navigation bar
  9. Click Processes

This will show you the processes and the time it takes them to execute. You will be able to narrow down processes that need to be optimized here.

Why Is MySQL Slow?

Possible Reason #1: You Don’t Have an Index

What is it?

An index is a type of database table that compiles information in an easily searchable way. Since the searching is easier, the queries will complete faster, thus speeding up your database.

Is it happening to me?

Check your database and see if you have an index table. If you don’t have one, this may be the reason you database is sluggish.

How to fix

To fix this, you simply have to add an index to your MySQL code. Check out MySQL’s article on the CREATE INDEX Syntax for more information on index creation.

Possible Reason #2: Complex Queries

What is it?

If a query is too complicated and the code is not optimized, it will use more CPU. If the queries are causing the database to hit the upper limits of its allotted CPU, the database will become slow.

Is it happening to me?

Check your database status. If there are queries with long run times, this may be the problem. Look for elements like “table cache” and “query cache,” since these can cause the the queries’ CPU usage to escalate.

How to fix

Inspect your code for optimizations. If you can find a way to rewrite the code for more efficient CPU usage, the database will speed up. If this isn’t possible, you may want to consider upgrading to a QS dedicated server for more CPU.

Possible Reason #3: Persistent Connections

What is it?

When a database is queried, a link is established. When persistent connections is on, links remain after the script has finished execution.

When a new link needs to be established, PHP will check for a persistent connection that matches the new link. If one does, it will use the preexisting link. If it can’t find an identical link, the script will create a new one.

This may seem efficient, but it can actually severely slow down your database. Persistent connections often create multiple links per person and save them in case they ever try to access the database again. When it does this for every visitor, it can begin to weigh heavily on database performance.

Is it happening to me?

If you know you have persistent connections set up, this may be slowing down your database.

How to fix

Disable persistent connections, unless you know your code is dependent on them.

If this is the problem, your database will speed up after persistent connections has been disabled.

Possible Reason #4: High Usage

What is it?

If your database is being used in high volumes, this can slow the database down. When there are too many queries to process at once, the CPU will bottleneck, resulting in a slow database.

Is it happening to me?

If you are aware that traffic to your website has increased, this may have caused your database slow-down.

How to fix

One way to speed up the database is to make the code more efficient. Processing power can only stretch so far. If the number of incoming links is pushing your system limits, your processing power power may be running itself ragged trying to keep up.

So if you can find a way to make your code more efficient, the process of funneling all the visitors through may be easier on your server’s resources.

If this is not an option, you may want to consider upgrading to a QS dedicated server for more processing power.