database overload - slow queries

[Yellowspurs]

My Gallery was shut down by my host ixwebhosting.com for one day yesterday. They said I have to optimize the queries of the database or else find a dedicated hosting service. Sound very cold.

I would like to hear from the experts here about this problem. My Gallery has on average less than 40 people viewing photos at any moment. I'm wondering whether it's their slow server and outdated hardware that is the problem.

You may take view my gallery at www.ganoi.com/photopost

I'm running version 5.31 of Photopost Pro. Our stat are:

Users: 2,445
Photos: 20,043
Comments: 85,518
Views: 6,126,171
Disk Space: 1,894.0mb


Below are the message from ixwebhosting.com.

It has come to our attention that your database abc_truongga caused an overload on our mySQL database. It has been chmoded to 000. It has been suggested that you need to either optimize the queries done by that database, or seek dedicated hosting for this content. See details below:

Total quantity of slow sql queries per day: 632
Total execution time of slow queries: 2.14 hours
Quantity of examined/sent rows per day: 4794740 / 364077

Most common slow queries:
1. Time: 11% (912 sec)
Amount: 12% (81 queries)
Avg. Query Exec/Lock Time: 11 / 0 sec.
Used databases: [abc_truongga]
Query example: use abc_truongga; SELECT id,user,userid,cat,title,bigimage,views FROM photos WHERE bigimage!='' AND approved='1' ORDER BY date DESC LIMIT 6;

2. Time: 11% (919 sec)
Amount: 11% (75 queries)
Avg. Query Exec/Lock Time: 12 / 0 sec.
Used databases: [abc_truongga]
Query example: SELECT
id,user,userid,cat,storecat,storeid,date,title,description,keywor
ds,bigimage,width,height,filesize,views,medwidth,medheight,medsiz
e,approved,rating,extra1,extra2,extra3,extra4,extra5,extra6,dispo
rder,ipaddress,allowprint FROM photos WHERE id=16419;


3. Time: 8% (664 sec)
Amount: 6% (39 queries)
Avg. Query Exec/Lock Time: 17 / 1 sec.
Used databases: [abc_truongga]
Query example: SELECT id,bigimage,cat,userid,approved,storecat,height,width FROM photos WHERE cat=501 AND approved=1 ORDER BY disporder,lastpost DESC;

4. Time: 2% (212 sec)
Amount: 3% (23 queries)
Avg. Query Exec/Lock Time: 9 / 0 sec.
Used databases: [abc_truongga]
Query example: use abc_truongga; SELECT id,user,userid,cat,storecat,date,title,description, keywords,bigimage,width,height,filesize,views, medwidth,medheight,medsize,approved,disporder FROM photos WHERE id=30284;

5. Time: 3% (286 sec)
Amount: 3% (22 queries)
Avg. Query Exec/Lock Time: 13 / 1 sec.
Used databases: [abc_truongga]
Query example: use abc_truongga; SELECT c.id, c.description, c.catorder, c.photos, c.posts, c.catdepth, p.id, p.bigimage, p.cat, p.approved, p.user, p.userid, p.date, p.width, p.height, p2.bigimage, p2.cat, p2.storecat, p2.userid, p2.width, p2.height, com.username, com.id, com.date, com.photo FROM categories c LEFT JOIN photos p ON c.lastphoto=p.id LEFT JOIN photos p2 ON c.thumbnail=p2.id LEFT JOIN comments com ON com.id=c.lastpost ORDER BY c.catorder ASC;

[Chuck S]

You can try 5.5 but I think your server has some part in any slow queries

I did those same queries on a site that has roughly the same size database photos comments and I get these times

1. Showing rows 0 - 5 (6 total, Query took 0.0089 sec)

2. Showing rows 0 - 0 (1 total, Query took 0.0012 sec)

3. Showing rows 0 - 29 (337 total, Query took 0.0038 sec)

4. Showing rows 0 - 0 (1 total, Query took 0.0008 sec)

5. Showing rows 0 - 20 (21 total, Query took 0.0016 sec)

Now take note query timing varies and we will depend on other factors like how many cats you have photos comments etc etc

First thing I would suggest you do is REPAIR and OPTIMIZE your database

[Yellowspurs]

Thank you for your input Chuck. I will try to upgrade to 5.5 soon but please point me to where I can find instruction on how to REPAIR and OPTIMIZE the database.

Thank.