MySQL slow


#1

Is anyone else using MySQL on their site heavily?

My site, over the past week, has been erratically slow – sometimes we get satisfactory response times, and others, a page display takes over a minute!

I am running a plain-vanilla “UBB.Threads” forum, which runs on a MySQL database. It has ~70 database tables, and every hit on the forum by a user causes MySQL to run about a dozen queries to serve up the page. It is a forum similar to this one, but this one runs “MyBB” software. (We are a very small non-profit, so to keep expenses down, we are trying to run on a DreamHost shared server.)

The software displays al line at the bottom showing how long the it took to create the page, including time spent by MySQL running the necessary queries. A typical time will show the queries took a total time of 0.1 or 0.2 seconds. But lately, we will often get queries taking 10 to 100 seconds! Then a minute later, I’ll try, and the same set of queries will take under 1 second. To me, this points to a database server that is overloaded. My queries taking a long time periodically points to the server working too hard processing too many queries at the same time.

My site has had a steady number of users for months. In fact, due to its seasonal nature, visitation is going down. There is no reason for the system to start running so much slower over the past week or so, except for a change in the shared environment.

I have complained to DH Tech Support people, only to get responses like my site is running out of memory and such. If that were the case, EVERY query would be taking a long time. They don’t seem to acknowledge that an overloaded database processor is what would cause the erratic times. Since I can’t seem to get a reasonable response from them, I thought I’d post here to see if anyone else is having the same trouble.

Here are examples of slow runs:
Generated in 17.610 seconds in which 17.541 seconds were spent on a total of 9 queries.
Generated in 3.649 seconds in which 3.451 seconds were spent on a total of 9 queries.
Generated in 11.321 seconds in which 10.836 seconds were spent on a total of 15 queries.
Generated in 45.916 seconds in which 45.802 seconds were spent on a total of 13 queries.

Here are better ones:
Generated in 1.707 seconds in which 1.463 seconds were spent on a total of 9 queries.
Generated in 1.830 seconds in which 1.783 seconds were spent on a total of 12 queries.
Generated in 0.596 seconds in which 0.531 seconds were spent on a total of 14 queries.
Generated in 0.087 seconds in which 0.024 seconds were spent on a total of 14 queries.
Generated in 1.380 seconds in which 1.327 seconds were spent on a total of 28 queries.

These about a minute apart:
Generated in 79.416 seconds in which 79.340 seconds were spent on a total of 37 queries.
Generated in 0.091 seconds in which 0.022 seconds were spent on a total of 14 queries.

And now, my regular users are starting to complain!


#2

Unfortunately, the problem continues.

First hits this morning:

  1. Generated in 32.817 seconds in which 32.325 seconds were spent on a total of 37 queries.
  2. Generated in 48.210 seconds in which 48.144 seconds were spent on a total of 14 queries.
  3. Generated in 93.433 seconds in which 46.632 seconds were spent on a total of 15 queries.

I can see from my logs that between 1:10 AM and 3:17 AM someone was fiddling with the MySQL database, but this morning, I get the above stalls.

I am wondering if it could be something like not enough disk space or memory to do a simple sort.


#3

I see that your support ticket was answered, and your site is operating normally again. Just FYI the last email sent from us explains the cause of the database issue you were having :slight_smile: Feel free to reply to that email directly if you have any further questions/concerns. Thanks!


#4

ES: Thanks much for taking care of the problem! The forum at whitneyzone.com is running smoothly again.

Here’s what tech support wrote:

[quote]I have identified a database causing issues with the SQL server and after rectifying the problem the load has now dropped back down.

The issue was caused by a different user (website) running some server resource intensive queries which was bogging down the server.

The admins have disabled and contacted that user.[/quote]

Being a database developer myself (but not within website MySQL servers), I know that the load by one (mis-)user can really ruin processing for everyone else.

In this situation, it was difficult getting the web-side techies convinced that it was the database side. They kept pointing the finger at my site.


Edit: checking in several hours later… The MySQL server is screamingly fast now!!! All my users are happy.
“Generated in 0.075 seconds in which 0.012 seconds were spent on a total of 14 queries.”