List:General Discussion« Previous MessageNext Message »
From:Milan Andric Date:August 2 2009 3:00am
Subject:Re: logging slow queries with time
View as plain text  
Thanks for the quick replies guys. I won't be pulling queries our of
Drupal anytime soon.  The optimizations I will do are minimal.  Sounds
like we might just have to live with mediocre performance for now.

I will definitely looks further at maatkit though.  I actually ran it
mk-query-digest on my slow log and it's pretty amazing output.  Lots
there but it might take me a while to digest it all.  And it does have
a time span for the queries it spots, like:

# Time range 2009-07-31 06:46:48 to 2009-07-31 12:51:21

Which is pretty helpful, but ideally I would like to pass args to
mk-query-digest to list specific queries that were slow between noon
and 1pm for example.  If anyone happens to know how to that it would
be helpful.  Otherwise I will keep grokking maatkit tools.



On Fri, Jul 31, 2009 at 3:14 PM, Johan De Meersman<vegivamp@stripped> wrote:
> On Fri, Jul 31, 2009 at 8:14 PM, Milan Andric <mandric@stripped> wrote:
> Hello,
> I'm serving a burly Drupal install and at some points throughout the
> day the mysql threads go way up and iowait peaks.  I'm not sure which
> is causing which but during this time the server is unresponsive.  I
> would like to determine if there is a poorly optimized query causing
> this.
> Drupal is a disaster :-)
> I'm running a dozen drupals, and we've spent the last eight months heavily
> optimizing every aspect. It's reasonably good now, but I'd still like to
> move away from it.
> Drupal wants to remain compatible with multiple databases, notably both
> MySQL and Postgres, and for v7 also Oracle. This means that they can't
> optimize their DB layer and use the fancy tricks of a single DB, but must do
> things in ways that are compatible with all of them. Postgres doesn't have
> autoincrement ? No autoincrement. Mysql doesn't have sequences ? No
> sequences.
> Some points:
>  - Drupal uses it's own internal unique ID system, not autoincrement, for a
> number of things. This means that every insert that uses this locks all
> other inserts that use this.
>  - Drupal has the bothersome tendency to do full table locks around some
> inserts. This means that even if you move to InnoDB, you'll still get full
> table locks. We patched this out according to info found on and
> other sites.
>  - Drupal's innate caching mechanisms are DB bound. Fun and dandy, but
> rather irritating if as soon as a busy page expires, a hundred threads all
> try to update the same table at the same time. We implemented Memcached.
> Patches, at least partial ones, are on, but I know we did more
> and better in-house.
>  - Drupal can cache fine for anonymous users, but once you're logged in,
> it's nearly impossible to cache. We worked around this by basically making
> 'static' cacheable pages, and pulling any dynamic content in through AJAX.
>  - Get rid of Drupal's views (in drupal, that is, not wat is called a view
> in a database). They generate horribly inefficient queries.
>  - Full text search modules use MySQL's full text indices, but that's not
> the best way of doing things. We implemented Sphinx search, there's an
> existing drupal module for integration.
> There's lots more that was done, but I can't provide all that info because
> a) it's company internal, and b) because I'm not a developer and thus don't
> know half of it :-)
> Suffice it to say, I don't like drupal for high-traffic interactive sites.
> Get away from it if you can.
>>   I'm logging slow queries but is there a way to see when the
>> slow queries take place also?  I'd like to know what queries are being
>> processed during this window of poor response time, usually around
>> noon local time.
> If you want more than just the slow queries, you'll have to enable the full
> log. Be aware that this has noticeable impact on performance, and will spam
> your disks. Log on different spindles if able, and monitor your disk usage
> carefully - mysql stops functioning if it can't write logs.
> --
> Celsius is based on water temperature.
> Fahrenheit is based on alcohol temperature.
> Ergo, Fahrenheit is better than Celsius. QED.
logging slow queries with timeMilan Andric31 Jul
  • RE: logging slow queries with timeGavin Towey31 Jul
  • Re: logging slow queries with timeJohan De Meersman31 Jul
    • Re: logging slow queries with timeMilan Andric2 Aug
      • Re: logging slow queries with timenuno.tavares2 Aug
        • Re: logging slow queries with timeMilan Andric3 Aug
          • Re: logging slow queries with timeMilan Andric3 Aug