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.
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:
> 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,
> 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.
>> On Fri, Jul 31, 2009 at 3:14 PM, Johan De Meersman<vegivamp@stripped>
>> > On Fri, Jul 31, 2009 at 8:14 PM, Milan Andric <mandric@stripped>
>> > 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
>> > 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,
>> > 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
>> > 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
>> > 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
>> > in a database). They generate horribly inefficient queries.
>> > - Full text search modules use MySQL's full text indices, but that's
>> > 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
>> > 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
>> >> 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
>> > log. Be aware that this has noticeable impact on performance, and will
>> > 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