List:General Discussion« Previous MessageNext Message »
From:Milan Andric Date:August 3 2009 6:28pm
Subject:Re: logging slow queries with time
View as plain text  
Getting the slow query data in the database was a breeze with
mk-query-digest, but now does anyone happen to know of scripts out
there that will generate an html page to view the output?   This is
probably a better question for the maatkit mailing list but figure
someone here might have a link.

Thanks,

Milan

On Sun, Aug 2, 2009 at 11:16 PM, Milan Andric<mandric@stripped> wrote:
> Nuno, thanks for the tips. I think I will work on getting
> mk-query-digest to log to a db table and run it periodically.  Sounds
> like a very useful thing to have.
>
> --
> Milan
>
> On Sun, Aug 2, 2009 at 10:02 AM, <nuno.tavares@stripped> wrote:
>> Hi Milan,
>>
>> I can see many ways of accomplish what you want:
>> * I'm almost sure mk-query-digest will allow you to do so;
>> * Either crop the slow query log for the desired timespan (that's a couple of
>> shell scripting commands) and run mk-query-digest against it;
>> * Set the query log file to a filename which is a link to /dev/null and set a
>> cron script to relink it to a real filename at noon and another to relink it to
>> /dev/null at 1pm - and then run the scripts you want.
>> * In newer versions you can log the slow queries to tables for analysis;
>> * Ultimately, you can also try a patched version of mysqldumpslow I was using
>> for some time, explained here:
>> http://gpshumano.blogs.dri.pt/2009/07/04/analysing-mysql-slow-queries/
>>
>> In the last two options you'll be logging to a table, so it will be easy to
>> select a timed range of queries for consideration.
>>
>> Hope that helps,
>> -NT
>>
>> Quoting Milan Andric <mandric@stripped>:
>>
>>> 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.
>>>
>>> Thanks,
>>>
>>> Milan
>>>
>>>
>>> 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 drupal.org
> 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 drupal.org, 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.
>>> >
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>>
>>>
>>
>>
>> --
>> Nuno Tavares
>> +351 93 618 40 86
>> dri Consultoria Informatica
>>
>>
>
Thread
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