Host asking me to optimize my database tables

[JohnnyDoomo]

I have recently been having CPU load problems with my server that has a site on it, powered by a script I bought.

 

I am not a programmer, so I am unable to dive into the code and fix the problem, nor do I understand what the issue is.

 

After getting my site back to a normal load, the host posted this:

 

Please check the following mysql process status. Please optimize your database tables.

 

| 1628 | clipup_clipper | localhost | clipup_newclip          | Query  | 0    | Writing to net | SELECT id,bigimage,cat,userid,approved,storecat,height,width FROM pp_photos WHERE cat=503 AND approv |

| 1629 | clipup_clipper | localhost | clipup_newclip          | Query  | 0    | Sending data  | SELECT userid,date,photo FROM pp_ipcache WHERE ipaddr='157.55.17.193' AND type='view' AND photo=4333 |

| 1630 | clipup_clipper | localhost | clipup_newclip          | Sleep  | 0    |                |

| 1757 | clipup_clipper | localhost | clipup_newclip | Query  | 1    | Sending data | SELECT userid,date,photo FROM pp_ipcache WHERE ipaddr='65.52.108.145' AND type='view' AND photo=3432 |

| 1758 | clipup_clipper | localhost | clipup_newclip | Sleep  | 0    |              |

| 1844 | clipup_clipper | localhost | clipup_newclip | Query  | 0    | Copying to tmp table | SELECT * FROM pp_photos WHERE approved=1 AND cat IN (501,511,502,503,562,504,506,505,508,2,510,509)  |

 

I don't know if this is enough for others to see my problem, or to help at all.

 

Am I going to have to have a programmer go through my entire site's script and make it more optimized?

 

Any info others can provide me with, would be most beneficial as I am at a loss as to what I need to do. One strange thing is, I've had high loads in the past, but it's always been due to lots of traffic. I've never had a problem like this under regular traffic.

 

Can I get away without fixing the problem?

 

Thanks for any pointers you can provide me with!

[requinix]

And that there is the downside to getting these kinds of script on the Internet: iffy support.

 

You should get a programmer to go through everything. That's a fact but perhaps not a necessity. Do you know anything about PHP or MySQL? Is there a phpMyAdmin you can use to access the database (or something similar)?

[JohnnyDoomo]

Yes, the script is installed on a dedicated server that I have access to phpmyadmin. It's just that I lack any knowledge to really understand anything in the tables.

[requinix]

There might be a couple easy things you can do.

 

Find the place that phpMyAdmin lets you run queries and enter

EXPLAIN SELECT * FROM pp_photos WHERE approved=1 AND cat IN (501,511,502,503,562,504,506,505,508,2,510,509)

What do you get?

[JohnnyDoomo]

There might be a couple easy things you can do.

 

Find the place that phpMyAdmin lets you run queries and enter

EXPLAIN SELECT * FROM pp_photos WHERE approved=1 AND cat IN (501,511,502,503,562,504,506,505,508,2,510,509)

What do you get?

 

This is what I got when I ran that: http://www.dumpt.com/img/viewer.php?file=zphc1qi75qx2ztuy82s7.gif

 

Does that give you anything? I can see that it looks like it's pointing out that I have a lot of entries in my database, but that's all I can tell from it.

 

Anything else I should be concerned about here?

[gizmola]

Yes, that is showing you that the query in question, scans the entire table, reading 44310 rows, even though you provide a list of categories, and there is an index on cat that *could* be used.  This could be a low cardinality issue, or the choice of the optimizer.  Although you aren't in a position to do much else, you can and should run analyze on your tables, on the off chance that the statistics being used by the query optimizer have gotten stale, and the db is making bad decisions to not use certain indexes that could help.

 

Doing database analysis and query optimization is something that requires a technical understanding of the application code, as well as how to read and understand explain plans.

 

With that said, a database that is not overly large will run well, albeit in a non-optimized fashion, if the server has been resourced and configured adequately.  In the scheme of things, tablescanning 44k rows, while to be avoided if possible, is not something that should be that big of an issue.  You are running apparently on a shared host where the database resources being allocated might not be sufficient to run your system. 

 

Again however, you need a pro to help you go through your system, looking at some of the things being discussed (slow query log, report from the hosting company, explain on the individual queries, analysis of the database schema, indexes and queries being executed) if you are interested in having a performant system, that uses resources competently.    Unfortunately there is no magic button (other than analyze) you can push.

 

 

[JohnnyDoomo]

Yes, that is showing you that the query in question, scans the entire table, reading 44310 rows, even though you provide a list of categories, and there is an index on cat that *could* be used.  This could be a low cardinality issue, or the choice of the optimizer.  Although you aren't in a position to do much else, you can and should run analyze on your tables, on the off chance that the statistics being used by the query optimizer have gotten stale, and the db is making bad decisions to not use certain indexes that could help.

 

Doing database analysis and query optimization is something that requires a technical understanding of the application code, as well as how to read and understand explain plans.

 

With that said, a database that is not overly large will run well, albeit in a non-optimized fashion, if the server has been resourced and configured adequately.  In the scheme of things, tablescanning 44k rows, while to be avoided if possible, is not something that should be that big of an issue.  You are running apparently on a shared host where the database resources being allocated might not be sufficient to run your system. 

 

Again however, you need a pro to help you go through your system, looking at some of the things being discussed (slow query log, report from the hosting company, explain on the individual queries, analysis of the database schema, indexes and queries being executed) if you are interested in having a performant system, that uses resources competently.    Unfortunately there is no magic button (other than analyze) you can push.

 

 

 

Thank you for your reply.

 

I don't know that this makes any difference to your comments, but I am on a dedicated server, I just haven't upgraded it... ever. (2.33 Dual core, 2GB memory).

 

I'm assuming having a programmer optimize my code, would be better than upgrading the server, if I can only do one or the other? (I do have plans to upgrade the server, but at current, I can only afford one way or the other.) If I did upgrade the server though, it would be able to be upgraded to a i7-3820 CPU @ 3.60GHz (quadcore) with 16GB of ram.

[JohnnyDoomo]

When I talked more with my host, they said that this is the query that needs to be optimized:

 

| 229 | clipup_clipper | localhost | clipup_newclip | Query  | 2    | Sending data | SELECT userid,date,photo FROM pp_ipcache WHERE ipaddr='165.152.14.81' AND type='view' AND photo=45042 |

 

I don't know if that's enough data, but if someone was able to tell me what that query is specifically doing, my script has an admin control panel that I might be able to turn off the feature that is causing the issue.

 

If it is not, does this data look like it's enough to have a programmer optimize my mysql database without the php files, or from that query, would the programmer need the php files as well? (or is it not enough data to tell that either?)

 

I would like to not give a programmer a complete backend if at all possible, as I try to get my problem fixed.

 

Thanks for any help!

[awjudd]

Is the photo column a BLOB?  Is your table properly indexed?  Run an EXPLAIN on the query to get more information on the actual query.

 

~awjudd

[The Little Guy]

Creating an index on that query should greatly speed things up.

 

create index ip_type_photo on pp_ipcache (ipaddr, type, photo);
-- or maybe even just this:
create index photo on pp_ipcache (photo);

 

I don't know how many records that table has, and the more there is the longer it will take to create an index, and phpMyAdmin would then be the last place you would wan't to create the index. A Command prompt or shell would be best.

[gizmola]

The query from the ISP is the "problem query" until the next "problem query". 

 

In terms of resources on your server, I have to disagree that you have a cpu problem.  More likely what you have is an IO/Memory issue. 

 

If I understand correctly, you have a traditional LAMP stack server (Linux, Apache, MySQL, PHP).  So out of the entire available memory of the server it is being spread like so:

 

-Some allocation to the OS itself

-Some allocation to daemons for the Email server, imap server etc.

-Some allocation to MySQL

-Variable per process allocation to Apache (the more traffic you have the more this available memory is used).  Each apache child in a typical php installation will also need as much memory as the php script needs and this can often be 10's of megabytes per connection.

 

Furthermore if you have a control panel like CPanel, then there is allocation to the apache that supports cpanel.

 

If it was my server, I would run "explain extended SELECT userid,date,photo FROM pp_ipcache WHERE ipaddr='165.152.14.81' AND type='view' AND photo=45042"

 

You can report back to us on that, along with "show create table pp_ipcache" and we will have enough information to tell you whether or not this can be fixed with an index as The Little Guy assumes. 

 

With that said, memory is king in most servers, and having a server with 16G is going to give you a lot more headroom before you hit the roof of what sort of load your server can support.  Unfortunately along with the memory you also need someone who understands how to configure mysql to take advantage of that memory.