Previous Thread
Next Thread
Print Thread
Hop To
MySQL slow #250387 09/25/2012 5:09 PM
Joined: May 2006
Posts: 238
Steve C Offline OP
enthusiast
OP Offline
enthusiast
Joined: May 2006
Posts: 238
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?


Re: MySQL slow [Re: Steve C] #250388 09/25/2012 5:33 PM
Joined: Jun 2006
Posts: 15,852
Gizmo Offline
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 15,852
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.


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: MySQL slow [Re: Steve C] #250389 09/25/2012 5:44 PM
Joined: May 2006
Posts: 238
Steve C Offline OP
enthusiast
OP Offline
enthusiast
Joined: May 2006
Posts: 238
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?

Re: MySQL slow [Re: Steve C] #250391 09/25/2012 6:55 PM
Joined: Jun 2006
Posts: 15,852
Gizmo Offline
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 15,852
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 am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: MySQL slow [Re: Steve C] #250392 09/25/2012 7:17 PM
Joined: Jun 2004
Posts: 207
cascadeclimbers Offline
enthusiast
Offline
enthusiast
Joined: Jun 2004
Posts: 207
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?


Won't you take me to Funkytown?
Re: MySQL slow [Re: Steve C] #250396 09/26/2012 6:39 AM
Joined: Feb 2007
Posts: 1,276
JAISP Offline
Veteran
Offline
Veteran
Joined: Feb 2007
Posts: 1,276
I just got "Generated in 0.037 seconds in which 0.008 seconds were spent on a total of 9 queries. Zlib compression disabled."

Re: MySQL slow [Re: Steve C] #250397 09/26/2012 10:56 AM
Joined: May 2006
Posts: 238
Steve C Offline OP
enthusiast
OP Offline
enthusiast
Joined: May 2006
Posts: 238
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.

Re: MySQL slow [Re: Steve C] #250399 09/26/2012 2:32 PM
Joined: May 2006
Posts: 238
Steve C Offline OP
enthusiast
OP Offline
enthusiast
Joined: May 2006
Posts: 238
Fixed! I got this message from the tech support:

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

Your sites should be operating normally again.

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

The admins have disabled and contacted that user.

Re: MySQL slow [Re: Steve C] #250411 09/27/2012 3:21 PM
Joined: Jun 2004
Posts: 207
cascadeclimbers Offline
enthusiast
Offline
enthusiast
Joined: Jun 2004
Posts: 207
That's kind of a par for the course response from Dreamhost. Blame you first since you are the one complaining.


Won't you take me to Funkytown?
Re: MySQL slow [Re: cascadeclimbers] #250413 09/27/2012 3:50 PM
Joined: May 2006
Posts: 238
Steve C Offline OP
enthusiast
OP Offline
enthusiast
Joined: May 2006
Posts: 238
Originally Posted by cascadeclimbers
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.




Re: MySQL slow [Re: Steve C] #250414 09/27/2012 3:53 PM
Joined: Dec 2003
Posts: 5,998
Ruben Offline
Offline
Joined: Dec 2003
Posts: 5,998
Is this not amazing that every host blames your software for the problem?


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Re: MySQL slow [Re: Steve C] #250415 09/27/2012 3:58 PM
Joined: May 2006
Posts: 238
Steve C Offline OP
enthusiast
OP Offline
enthusiast
Joined: May 2006
Posts: 238
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.

Re: MySQL slow [Re: Ruben] #250416 09/27/2012 4:06 PM
Joined: Jan 2004
Posts: 2,468
driv Offline
Offline
Joined: Jan 2004
Posts: 2,468
Originally Posted by Ruben
Is this not amazing that every host blames your software for the problem?

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


Using version :: 7.7.2
Re: MySQL slow [Re: Ruben] #250420 09/27/2012 8:11 PM
Joined: Jun 2006
Posts: 15,852
Gizmo Offline
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 15,852
Originally Posted by Ruben
Is this not amazing that every host blames your software for the problem?
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 tongue...

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 am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: MySQL slow [Re: Steve C] #250432 09/28/2012 5:11 PM
Joined: Dec 2003
Posts: 228
Pilgrim Offline
enthusiast
Offline
enthusiast
Joined: Dec 2003
Posts: 228
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.

[Linked Image]


Artificial Intelligence is no match for natural stupidity!
Re: MySQL slow [Re: Steve C] #250433 09/28/2012 6:19 PM
Joined: Jun 2006
Posts: 15,852
Gizmo Offline
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 15,852
My VPS is only $18 per month; not much more than I was paying for shared hosting...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: MySQL slow [Re: Steve C] #250461 09/30/2012 3:12 PM
Joined: Jun 2006
Posts: 869
Stan Offline
old hand
Offline
old hand
Joined: Jun 2006
Posts: 869
who are you with and are you satifified? what kind of traffic/bandwidth?


http://clubadventist.com/forums

No longer following the carrot
Re: MySQL slow [Re: Steve C] #250467 09/30/2012 4:05 PM
Joined: Jun 2006
Posts: 15,852
Gizmo Offline
UBB.threads Developer
Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 15,852
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).


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!

Forum Search
ShoutChat Box
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
Mobile app?
by Baldeagle - 12/06/2019 9:32 PM
How do you change Text Line spacing?
by jorb - 11/23/2019 12:14 AM
What happened to FAQ or Forum Help
by Ruben - 11/20/2019 11:58 AM
Search feature encountering an Error message
by jorb - 11/20/2019 12:06 AM
UBB Dev
by JAISP - 11/03/2019 11:01 AM
Who's Online Now
0 registered members (), 86 guests, and 378 spiders.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Amusing Terain Scenics
Amusing Terain Scenics
by isaac, August 19
Sky places
Sky places
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Snapshot build 20191023)