mySQL down & out

[Anvilman]

Well it seems that after nearly a year of running my site off 1 mySQL database, all the messups last week crippled my site. It was up/down all week, and was down all day yesterday due to a corrupted table (has NEVER happened before yesterday).

I got a programmer-friend to repair the table "user", but when the site came back the site ran like mud and the forums took 5minutes/page to load and didnt really work. Everything took forever to respond.

Today I got this message:
From: "PowWeb Admin" <admin@powweb.com>
Subject: Package Service mySQL (159586) Disabled

Using long queries causing server to stop responding and needing to be restarted.


# Time: 030707 0:13:15
# User@Host: omp[omp] @ h24-85-201-109.vn.shawcable.net [24.85.201.109]
# Query_time: 59 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
use omp;
REPAIR TABLE attachment;
# Time: 030707 0:13:19
# User@Host: omp[omp] @ localhost []
# Query_time: 153 Lock_time: 0 Rows_sent: 54 Rows_examined: 108
SELECT template,title
FROM template
WHERE (title IN ('forumhome_birthdaybit','error_nopermission','for umhome_pmloggedin','forumhome_welcometext','forumh ome_logoutcode','forumhome_newposts','forumhome_to dayposts','forumhome_logincode','forumhome_loggedi nuser','forumhome_loggedinusers','forumhome_lastpo stby','forumhome_moderator','forumhome_forumbit_le vel1_nopost','forumhome_forumbit_level1_post','for umhome_forumbit_level2_nopost','forumhome_forumbit _level2_post','forumhome','forumhome_unregmessage' ,'gobutton','timezone','username_loggedout','usern ame_loggedin','phpinclude','headinclude','header', 'footer','forumjumpbit','forumjump','nav_linkoff', 'nav_linkon','navbar','nav_joiner','pagenav','page nav_curpage','pagenav_firstlink','pagenav_lastlink ','pagenav_nextlink','pagenav_pagelink','pagenav_p revlink')
AND (templatesetid=-1 OR templatesetid='1'))
OR (title = 'maxloggedin')
OR (title = 'birthdays')
ORDER BY templatesetid;
# Time: 030707 0:14:21
# User@Host: adminuser[adminuser] @ localhost [127.0.0.1]
# Query_time: 158 Lock_time: 0 Rows_sent: 10 Rows_examined: 609
use nagios;
SELECT COUNT(userprofiles.country_id) AS total, country_name FROM userprofiles LEFT JOIN countries ON userprofiles.country_id=countries.country_id GROUP BY userprofiles.country_id ORDER BY total DESC LIMIT 0,10;
# Time: 030707 0:14:43
# User@Host: adminuser[adminuser] @ localhost [127.0.0.1]
# Query_time: 250 Lock_time: 0 Rows_sent: 245 Rows_examined: 1016
SELECT * FROM userprofiles LEFT JOIN orgtypes ON userprofiles.orgtype_id=orgtypes.orgtype_id LEFT JOIN countries ON userprofiles.country_id=countries.country_id LEFT JOIN ostypes ON userprofiles.ostype_id=ostypes.ostype_id WHERE has_been_confirmed='1' AND has_been_approved='1' AND has_been_reviewed='1' AND show_profile='1' AND publish_orgname='1';
# Time: 030707 0:15:03
# User@Host: adminuser[adminuser] @ localhost [127.0.0.1]
# Query_time: 258 Lock_time: 0 Rows_sent: 245 Rows_examined: 1016
SELECT * FROM userprofiles LEFT JOIN orgtypes ON userprofiles.orgtype_id=orgtypes.orgtype_id LEFT JOIN countries ON userprofiles.country_id=countries.country_id LEFT JOIN ostypes ON userprofiles.ostype_id=ostypes.ostype_id WHERE has_been_confirmed='1' AND has_been_approved='1' AND has_been_reviewed='1' AND show_profile='1' AND publish_orgname='1';
# Time: 030707 0:15:22
# User@Host: omp[omp] @ localhost []
# Query_time: 167 Lock_time: 0 Rows_sent: 54 Rows_examined: 108
use omp;
SELECT template,title
FROM template
WHERE (title IN ('forumhome_birthdaybit','error_nopermission','for umhome_pmloggedin','forumhome_welcometext','forumh ome_logoutcode','forumhome_newposts','forumhome_to dayposts','forumhome_logincode','forumhome_loggedi nuser','forumhome_loggedinusers','forumhome_lastpo stby','forumhome_moderator','forumhome_forumbit_le vel1_nopost','forumhome_forumbit_level1_post','for umhome_forumbit_level2_nopost','forumhome_forumbit _level2_post','forumhome','forumhome_unregmessage' ,'gobutton','timezone','username_loggedout','usern ame_loggedin','phpinclude','headinclude','header', 'footer','forumjumpbit','forumjump','nav_linkoff', 'nav_linkon','navbar','nav_joiner','pagenav','page nav_curpage','pagenav_firstlink','pagenav_lastlink ','pagenav_nextlink','pagenav_pagelink','pagenav_p revlink')
AND (templatesetid=-1 OR templatesetid='1'))
OR (title = 'maxloggedin')
OR (title = 'birthdays')
ORDER BY templatesetid;
# Time: 030707 0:15:45
# User@Host: adminuser[adminuser] @ localhost [127.0.0.1]
# Query_time: 84 Lock_time: 0 Rows_sent: 10 Rows_examined: 584
use nagios;
SELECT COUNT(userprofiles.orgtype_id) AS total, orgtype_name FROM userprofiles LEFT JOIN orgtypes ON userprofiles.orgtype_id=orgtypes.orgtype_id GROUP BY userprofiles.orgtype_id ORDER BY total DESC LIMIT 0,10;


So who's fault is all this? It seems like this is all a result of the constnat mySQL problems of last week, but now its being blamed on me. My site is NOT high-traffic, and my bandwidth is always well under the limits.

support@powweb.com hasn't been too helpful, unfortunately.

[B&T]

It would seem that powweb is finally trying to go after the source of all the MySQL instability.

I tried having my main pages driven off MySQL but MySQL has been too unreliable at powweb so I took another approach.

One problem is bad code that sucks machine resources when it runs. They seem to think your SQL is an example of bad code. In looking at the SQL, I would have to agree with them. I have never seen such long queries.

As for who's fault it is - it is your code.

I hope they keep digging so MySQL can get some stability around here :) I applaud their effrots to find the problems.

[Anvilman]

Is there query really long in the vBulletin portion or in my regular site? Because vB is a very reputable forum software that I've used for over a year, and the other code is professional & runs another major website, www.antsmarching.org

[xXtreme]

I also applaud any efforts to stabilize MySQL and I do believe writing lean scripts/queries is one way for the users to help. I try, stress on "try", to do this even out of pride in the work I put out.

Anvilman, the fact that the queries work on other servers doesn't make it efficient. Albeit having no idea what you're trying to achieve, I do however suggest you closely re-examine it.

[B&T]

Sorry, I can't tell you where the query comes from. But you can tell yourself. Look at the code.

[Anvilman]

well this is great

no response from powweb, even to emails. thanks guys.

argh, my site's still dead. its been running off this code for a year, i dont think it suddenly went AWOL on itself.

[windguard]

what the hell?

They sent me the same email, but now out of the 3 sites running off MySQL databased 2 are down, and the one which they emailed me about is still working...

None of my sites are high traffic as they are dedicated to 3 small gaming organizations...make it 2 now, I lost the Star Wars Galaxies org since they needed the site to start, and they had no access to it...I planned for a few months for it, and now they've joined another org since for 2 weeks they had no website.

Thanks Powweb!

I used OPS to contact support to restore from backup, no reply yet...

Their prices are great, but without support...someone people may consider paying more elsewhere so they dont have to worry about support when they need it...

[Anvilman]

.