UBB.threads PHP Forum Software Community Forums General Off Topic MySQL slow

My site is running erratically in the past week. Users are complaining. Sometimes it is ok, other times it takes a huge amount of time. Until a week ago, things were pretty normal.

Here are things I am seeing.

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.

Searching this forum, last place I see much is from 2008: Generated in 16.865 seconds...

I think my hosting site has overloaded their MySQL server. Am I correct?

I've complained to DreamHost tech support, but got a response that my processes were hitting the site's memory limits. It is pretty odd, because I typically only have a dozen or so users at any one time, and all was running smoothly up until a week ago.

Is there something the tech support/DBA people should check so I can try to convince them?

Are you on a shared host or a VPS? If you're on a VPS you could run a tool like MySQLTuner which will give recommendations based on your MySQL runtime information; if you're on a shared host then yeah, they all kind of try to squeeze every ounce of resources out of shared hosting machines.

Oh, forgot to mention that part: Shared server.

Would Zlib compression or optimizing the tables have any significant effect?

Why would the system go bad in just the past few days?

Well, shared servers you're sharing resources, so other (or new) sites could be taking up more resources on the machine, or your site could be experiencing increase in traffic as well.

With a VPS or Dedicated Server you'd have resources allocated for just your site (plus with a VPS you'd usually have burstable ram).

I just got 8 seconds.

You post table or traffic aren't large enough for things to be that slow.

You can go into you phpmyadmin panel and see what the overhead is for your tables and optimize. But it would have to be pretty large. I can't really think of any good explanations why it would be that slow. We were on dreamhost shared with a board much larger and way more traffic w/o problems.

On dreamhost shared your web is on one shared server and mysql is on another. I know there is a disconnect between the two groups there that handle support for each, so you might try to pin them down and ask specifically what it is because you can't find anything on your end.

I'd ask them if they can give you a slow queries output if they say you are hitting the memory limit. Or are you hitting the limit on the webserver?

I just got "Generated in 0.037 seconds in which 0.008 seconds were spent on a total of 9 queries. Zlib compression disabled."

The problem has been pretty erratic. It was really slow half an hour ago (up to 90 seconds), and then 15 minutes ago it took off (~1 second responses).

At least they're working on it -- The log shows the system was shut down several times overnight.

I am wondering if the db was out of disk space to do any sorts.

It is being consistently quick as I write this (knock on wood), so they may have figured out the problem. It would be nice if the techies would share the solution.

Fixed! I got this message from the tech support:

That's kind of a par for the course response from Dreamhost. Blame you first since you are the one complaining.

Yes. 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.

They replied to my first two complaints:
> I looked into the cause of your issue and it seems your scripts have been getting automatically killed by our Process Watcher script due to your sites going over Memory limits on the shared server

Then:
> The database server itself is stable and has been up for quite a while:

harp:~# uptime
14:40:53 up 182 days, 3:42, 1 user, load average: 8.04, 8.11, 17.47

I've checked the available logs for the daemon and there are quite a few
triggers so you definitely need to optimize your sites or consider moving
to a VPS.

I've checked today's logs and you have quite a bit of traffic today:

fir:/home/whitneyz/logs# for k in `ls -S */http/access.log`; do wc -l $k
| sort -r -n; done
61440 whitneyzone.com/http/access.log

You may not be using much software, but whatever you're running under your user account is indeed hitting the shared server limits. So you will need to optimize any scripts you're running to bring your usage under control.

The Process Watcher daemon works on a per user basis. If a user were to hit the shared server limits, their scripts/processes are killed and only their user is affect - no one else. So it's not an overloaded database server at all, but the processes/scripts you're running.

Following that I wrote:
> This problem is becoming unmanageable! There is SOMETHING outside of my
> website that is causing MySQL to run slower than molasses.

I got this back:
> I apologize for the trouble with your database service. We did identify a higher than normal load and had our admins get that resolved. It's looking much better now and I am seeing much faster connection times to the databases on that server. I do expect everything to continue running smoothly but please let me know if you see any further problems with this or need anything else.


I later responded:
The problem is STILL there!... Your MySQL database processor is broken!

And finally got the satisfactory result:
> I have identified a database causing issues with the SQL server harp and
after rectifying the problem the load has now dropped back down.

Is this not amazing that every host blames your software for the problem?

Well, because of THIS forum and the support in numbers we have here, I was able to point out that the very same software is running on hundreds of sites all over the world without the trouble I was having. It gave me the foundation to get their attention so they would fix the actual problem.

Amen to that! How many times I've heard this tale.

Well, at some point a site will hit limits and will need to be moved to a VPS or a Dedicated Server; it's just not fair to small sites that don't use resources to have a couple of busy sites kill resources ...

When I was working for my old webhost I had to do a lot of identification and contacting owners with VPS/Dedicated options because they where hitting limits and lagging servers.

I used that same reasoning with 3 Host Providers. All 3 insisted that my site (UBBThreads) was "hogging" to many resouces and memory. All 3 threatened me with expulsion if "I" did not correct the alleged usage problem or move to a VPS.

I did get one "tech" to check what was atually using so much memory and he said it was "1"... that is uno, one single 15KB .gif file that was the culprit. My response was, "You have got to be kidding, right? That's impossible!" For fun, I removed the one .gif file and the problem continued. Eventually, I cancelled my account I had with all 3 of these companies, respectively and ended up going with a VPS. That went fine for awhile albeit much more expensive. The company grew quickly and the performance and service declined unfortunately.

So, I moved once again to a shared server on Express Hosting and things have been 100% plus. Speeds are noticeably better and I have more features than any of the other 5 I've had over the years. The owner, Tom Walsh has been stellar in the service department, moving my entire website and board and setting everything up.

My VPS is only $18 per month; not much more than I was paying for shared hosting...

who are you with and are you satifified? what kind of traffic/bandwidth?

I've been with Interserver for quite a while; I'm on a 3 slice vps in their NJ center (their CA center is just a colo).

I'm running CentOS with OpenVZ (with a swap, a new option with the latest openvz), no control panel (I installed Usermin/Virtualmin/Webmin for free myself), on a monthly billing period (amounts to a 1 core cpu with 1128mb ram, 75gb storage, 9mbps/2700gb bw; but you can scale this up as far as you want to pay).