Joomla MySQL query performance affected by anything non-server related?

Been getting the occasional warning on one of our client sites about
excessive CPU usage and/or slow MySQL queries.

Looking at the logs, it looks like it's more the latter (slow queries).

Some of the queries involve some joins, etc. But nothing too crazy, and
there are the standard indexes on most of the relevant columns used in
join/where clauses. Everything Joomla 1.5 does when it does it's initial
setup.

I've done everything the web host recommends and the last thing they said to
check the PHP code and try to optimize that a bit. But it's standard
Joomla 1.5 stuff. They claim they don't have any other clients having
problems with Joomla 1.5.

In the end, I think this is a problem with their MySQL server or their server
in general, but they won't admit to that. So forgive me if this is more of
a MySQL issue and not so much PHP-DB related, but it's the PHP side that is
the last thing they're recommending I check that I technically haven't
because I think Joomla's probably fine.

As an example of a query that showed up on the log recently:

UPDATE jos_content SET hits = ( hits + 1 ) WHERE id='123'

The log says this took 7 seconds. There's an index on "id". There are only
400 or so records in the whole table. It's not like there are a million
records and no index.

Is there ANY rational reason this query would EVER take 7 seconds to execute?


Any info/thoughts/advice would be appreciated. I know my way around a
database or two and know a few things about optimization, but I need some
solid evidence to throw at this web host so they'll listen to me. They're
usually really awesome, but this case is causing me to lose my hair.

Thanks in advance!

-TG

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Joomla MySQL query performance affected by anythingnon-server related?

> UPDATE jos_content SET hits = ( hits + 1 ) WHERE id='123'
>
> The log says this took 7 seconds. There's an index on "id". There are only
> 400 or so records in the whole table. It's not like there are a million
> records and no index.
>
> Is there ANY rational reason this query would EVER take 7 seconds to execute?

It could be worth either doing an analyze
(http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html) or possibly
doing a dump/restore - in case there is lots of "bloat" either in the
indexes or tables. Normally you wouldn't see this with mysql (especially
myisam) but it's worth a shot.

What other queries are running at the same time? (use 'show processlist'
inside mysql to get a list), maybe you're getting lots of hits to the
same tables and myisam is locking everything, and it's time to convert
to innodb.


--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Joomla MySQL query performance affected by anything non-server related?

Thanks for the response, Chris. I have analyzed, optimized, checked, etc.
Everything looks relatively good/healthy on the server side of things. It
is MyISAM. That's one thing I don't really know very well, the benefits of
the different DB engines.

Not sure if I'm going to be able to see a process list when there are
problems because the problems are really intermittant.

When I do "show processlist" logged in as the same user, I don't see anything
else executing.

The client gets an average of 600 hits per day on their site. Nothing too
crazy. That's the other thing that's driving me nuts. They get traffic,
but it shouldn't be so much that it's causing issues ilke this.

Shouldn't have to make such radical changes for this site. All I can think
is that it's an issue with their server and/or other customers on the same
shared server.

-TG

----- Original Message -----
From: Chris
To: TG
Cc: php-db@lists.php.net
Date: Thu, 06 Nov 2008 08:45:16 +1100
Subject: Re: [PHP-DB] Joomla MySQL query performance affected by anything
non-server related?

>
> > UPDATE jos_content SET hits = ( hits + 1 ) WHERE id='123'
> >
> > The log says this took 7 seconds. There's an index on "id". There are
only
> > 400 or so records in the whole table. It's not like there are a million
> > records and no index.
> >
> > Is there ANY rational reason this query would EVER take 7 seconds to
execute?
>
> It could be worth either doing an analyze
> (http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html) or possibly
> doing a dump/restore - in case there is lots of "bloat" either in the
> indexes or tables. Normally you wouldn't see this with mysql (especially
> myisam) but it's worth a shot.
>
> What other queries are running at the same time? (use 'show processlist'
> inside mysql to get a list), maybe you're getting lots of hits to the
> same tables and myisam is locking everything, and it's time to convert
> to innodb.
>
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Joomla MySQL query performance affected by anythingnon-server related?

TG wrote:
> Thanks for the response, Chris. I have analyzed, optimized, checked, etc.
> Everything looks relatively good/healthy on the server side of things. It
> is MyISAM. That's one thing I don't really know very well, the benefits of
> the different DB engines.
>
> Not sure if I'm going to be able to see a process list when there are
> problems because the problems are really intermittant.
>
> When I do "show processlist" logged in as the same user, I don't see anything
> else executing.
>
> The client gets an average of 600 hits per day on their site. Nothing too
> crazy. That's the other thing that's driving me nuts. They get traffic,
> but it shouldn't be so much that it's causing issues ilke this.
>
> Shouldn't have to make such radical changes for this site. All I can think
> is that it's an issue with their server and/or other customers on the same
> shared server.

Too many customers on the same server imo. 600 hits a day is nothing and
if it takes 7 secs to update one column, that's just plain ridiculous.

Not sure how you "prove" that to the host though..

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Joomla MySQL query performance affected by anythingnon-server related?

TG wrote:
> Been getting the occasional warning on one of our client sites about
> excessive CPU usage and/or slow MySQL queries.
>
> Looking at the logs, it looks like it's more the latter (slow queries).
>
> Some of the queries involve some joins, etc. But nothing too crazy, and
> there are the standard indexes on most of the relevant columns used in
> join/where clauses. Everything Joomla 1.5 does when it does it's initial
> setup.
>
> I've done everything the web host recommends and the last thing they said to
> check the PHP code and try to optimize that a bit. But it's standard
> Joomla 1.5 stuff. They claim they don't have any other clients having
> problems with Joomla 1.5.
>
> In the end, I think this is a problem with their MySQL server or their server
> in general, but they won't admit to that. So forgive me if this is more of
> a MySQL issue and not so much PHP-DB related, but it's the PHP side that is
> the last thing they're recommending I check that I technically haven't
> because I think Joomla's probably fine.
>
sounds like hostmonster.com :-)
had the same issue few times.
tried to figure out and wasn't able. talked several times with support
and "suddenly" it was working ok again.

afan



> As an example of a query that showed up on the log recently:
>
> UPDATE jos_content SET hits = ( hits + 1 ) WHERE id='123'
>
> The log says this took 7 seconds. There's an index on "id". There are only
> 400 or so records in the whole table. It's not like there are a million
> records and no index.
>
> Is there ANY rational reason this query would EVER take 7 seconds to execute?
>
>
> Any info/thoughts/advice would be appreciated. I know my way around a
> database or two and know a few things about optimization, but I need some
> solid evidence to throw at this web host so they'll listen to me. They're
> usually really awesome, but this case is causing me to lose my hair.
>
> Thanks in advance!
>
> -TG
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Joomla MySQL query performance affected by anything non-server related?

----- Original Message -----
From: Afan Pasalic
To: TG
Cc: php-db@lists.php.net
Date: Wed, 05 Nov 2008 17:07:46 -0600
Subject: Re: [PHP-DB] Joomla MySQL query performance affected by anything
non-server related?

> TG wrote:
> > Been getting the occasional warning on one of our client sites about
> > excessive CPU usage and/or slow MySQL queries.
> >
> > Looking at the logs, it looks like it's more the latter (slow queries).
> >
> > Some of the queries involve some joins, etc. But nothing too crazy, and
> > there are the standard indexes on most of the relevant columns used in
> > join/where clauses. Everything Joomla 1.5 does when it does it's initial
> > setup.
> >
> > I've done everything the web host recommends and the last thing they said
to
> > check the PHP code and try to optimize that a bit. But it's standard
> > Joomla 1.5 stuff. They claim they don't have any other clients having
> > problems with Joomla 1.5.
> >
> > In the end, I think this is a problem with their MySQL server or their
server
> > in general, but they won't admit to that. So forgive me if this is more
of
> > a MySQL issue and not so much PHP-DB related, but it's the PHP side that
is
> > the last thing they're recommending I check that I technically haven't
> > because I think Joomla's probably fine.
> >
> sounds like hostmonster.com :-)
> had the same issue few times.
> tried to figure out and wasn't able. talked several times with support
> and "suddenly" it was working ok again.
>
> afan



Bingo! hah.. I was going to be nice and not mention their name, because
they've been outstanding in everything else. Tech support has always been
prompt and knowledgable.

I still like my Dreamhost.

Thanks for letting me know that other people have had this problem with them.
Maybe it'll be magically 'fixed' now for us as well.

The "too slow" logs for the last few days haven't had anything over the 7
second one I mentioned. The problem really came to light when there were
multiple 20+ second queries causing problems.

-TG

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Joomla MySQL query performance affected by anythingnon-server related?

TG wrote:
> ----- Original Message -----
> From: Afan Pasalic
> To: TG
> Cc: php-db@lists.php.net
> Date: Wed, 05 Nov 2008 17:07:46 -0600
> Subject: Re: [PHP-DB] Joomla MySQL query performance affected by anything
> non-server related?
>
>
>> TG wrote:
>>
>>> Been getting the occasional warning on one of our client sites about
>>> excessive CPU usage and/or slow MySQL queries.
>>>
>>> Looking at the logs, it looks like it's more the latter (slow queries).
>>>
>>> Some of the queries involve some joins, etc. But nothing too crazy, and
>>> there are the standard indexes on most of the relevant columns used in
>>> join/where clauses. Everything Joomla 1.5 does when it does it's initial
>>> setup.
>>>
>>> I've done everything the web host recommends and the last thing they said
>>>
> to
>
>>> check the PHP code and try to optimize that a bit. But it's standard
>>> Joomla 1.5 stuff. They claim they don't have any other clients having
>>> problems with Joomla 1.5.
>>>
>>> In the end, I think this is a problem with their MySQL server or their
>>>
> server
>
>>> in general, but they won't admit to that. So forgive me if this is more
>>>
> of
>
>>> a MySQL issue and not so much PHP-DB related, but it's the PHP side that
>>>
> is
>
>>> the last thing they're recommending I check that I technically haven't
>>> because I think Joomla's probably fine.
>>>
>>>
>> sounds like hostmonster.com :-)
>> had the same issue few times.
>> tried to figure out and wasn't able. talked several times with support
>> and "suddenly" it was working ok again.
>>
>> afan
>>
>
>
>
> Bingo! hah.. I was going to be nice and not mention their name, because
> they've been outstanding in everything else. Tech support has always been
> prompt and knowledgable.
>
> I still like my Dreamhost.
>
> Thanks for letting me know that other people have had this problem with them.
> Maybe it'll be magically 'fixed' now for us as well.
>
> The "too slow" logs for the last few days haven't had anything over the 7
> second one I mentioned. The problem really came to light when there were
> multiple 20+ second queries causing problems.
>
> -TG
>
agree. they drove me nuts several times with some crazy issues, but - it
usually happen with other hosting companies too.
as you said support is fast and good. I like them and I use them for a
while.

-afan



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Joomla MySQL query performance affected by anything non-server related?

On Wed, Nov 5, 2008 at 2:17 PM, Chris wrote:
> Too many customers on the same server imo. 600 hits a day is nothing and if
> it takes 7 secs to update one column, that's just plain ridiculous.

I agree with this consensus, and I agree that it's not clear how you
would "prove" this. Where I differ with my fellows here is that I
don't think proving it is an issue. I suspect they know the problem
and they are blowing smoke, so it's not a question of proving
anything. Simply call their bullshit in a professional and respectful
way.

But I'm not sure what the point of that is, really. You're using
shared hosting, so this is kinda how it goes. I doubt any shared host
will guarantee any sort of performance. You get a small share of an
unspecific set of hardware with an unspecified number of other
customers. Shared hosting is generally geared to hosting small sites
of static pages. If you're hitting the performance wall, it's
probably time to look at setting up a dedicated server.

One thing that hasn't been suggested is that you could ask if they
have query caching turned on for the server. Querying caching might
help a little bit by relieving load on the database server.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Joomla MySQL query performance affected by anything non-server related?

Shared hosting is fine.. I don't mind a little "slow" every now and then.
For what this customer needs, a full dedicated server is total overkill.

But the big problem here is that if you exceed a certain amount of CPU and/or
slow query time in a given 60 second window, your site visitors get a big
message saying "CPU/Queries Exceeded!" and they disable your account for
5-10 mins. If they just queued the queries and throttled the usage, that
would be fine, but to be punished for something that isn't (probably) our
fault is unacceptable.

A query that should take 0.025 seconds taking 20 seconds.. and 2 or 3 of
those at a time.. that's all it takes to get shut down for 5-10 minutes and
look like crap to your site visitors.

The only way I can think to prove the problem is for them to look at other
customers on that same server and see who else is bombing out at the same
time. Maybe someone else's crappy queries are causing the problem and it's
affecting us. So maybe they need to lean on that other account a little
heavier to fix their problems.

I've recommended this 2-3 times now, as well as detailing all the things I've
done to alleviate the problem, pointing out the links they sent to me, what
I perceive to be the recommendation and what I did to address it. Showing
that I've done EVERYTHING they've asked me to do. And three times, I've
gotten the same copy/paste response.

Really weird, since their support is usually great. Maybe it's just their
database guys who don't want to work.

-TG

----- Original Message -----
From: "Fergus Gibson"
To: php-db@lists.php.net
Date: Sun, 9 Nov 2008 11:09:22 -0800
Subject: Re: [PHP-DB] Joomla MySQL query performance affected by anything
non-server related?

> On Wed, Nov 5, 2008 at 2:17 PM, Chris wrote:
> > Too many customers on the same server imo. 600 hits a day is nothing and
if
> > it takes 7 secs to update one column, that's just plain ridiculous.
>
> I agree with this consensus, and I agree that it's not clear how you
> would "prove" this. Where I differ with my fellows here is that I
> don't think proving it is an issue. I suspect they know the problem
> and they are blowing smoke, so it's not a question of proving
> anything. Simply call their bullshit in a professional and respectful
> way.
>
> But I'm not sure what the point of that is, really. You're using
> shared hosting, so this is kinda how it goes. I doubt any shared host
> will guarantee any sort of performance. You get a small share of an
> unspecific set of hardware with an unspecified number of other
> customers. Shared hosting is generally geared to hosting small sites
> of static pages. If you're hitting the performance wall, it's
> probably time to look at setting up a dedicated server.
>
> One thing that hasn't been suggested is that you could ask if they
> have query caching turned on for the server. Querying caching might
> help a little bit by relieving load on the database server.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Joomla MySQL query performance affected by anything non-server related?

On Sun, Nov 9, 2008 at 11:58 AM, TG wrote:
> Shared hosting is fine.. I don't mind a little "slow" every now and then.
> For what this customer needs, a full dedicated server is total overkill.
>
> But the big problem here is that if you exceed a certain amount of CPU and/or
> slow query time in a given 60 second window, your site visitors get a big
> message saying "CPU/Queries Exceeded!" and they disable your account for
> 5-10 mins.
[...]

It doesn't sound like you think that's fine. It sounds like you have
a problem with it. A dedicated server will solve that problem. Your
only other realistic option is to dump this provider for another one.
But there's no guarantee you won't have exactly the same problem or
worse on someone else's shared server. The point is that shared
hosting is something you don't control. With a dedicated server, you
can call all the shots. Yes, it's expensive, but it's a trade-off.

I've this sort of bullcrap from other hosts before. It's not limited
to one host. It happens. A company I worked for stopped working with
clients who wanted to put our application on shared hosting because it
was too much of a headache. One client, for example, had his site
disabled because the host felt it generated too many database queries.
Um, well, they didn't publish anything about that in their ToS. You
just sign up, roll the dice, and discover whether the host will work
for you. And in the process, you've got a lot of PITA. So I don't
recommend a shared host for providing a database-driven application of
any significant size.

Good luck trying to persuade your chosen host to amend their policies
or improve their server.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php