Hi,

Our MySQL server used for our development environment is slowing down,
certainly because of a massive query execution by different processes.
We are trying to determine which kind of query is the most executed and
on which database instance(s).

We are quite novice in administrating MySQL. We imagine that we can
configure MySQL so that it logs every queries executed in its
queries.log file. However is there any analysis tool that would import
this log file and that would generate a complete report providing the
number of similar queries (same DML order on the same table) per
databases per minute?

P.S.: we are using mytop and innotop, but it seems that they don't
support such a feature. We see a lot of different queries executed
against MySQL but we can't figure out, which kind of queries is the most
executed, on which database.

Thanks,

--
Daniel

Daniel Caune wrote:
> Hi,
>
> Our MySQL server used for our development environment is slowing down,
> certainly because of a massive query execution by different processes.
> We are trying to determine which kind of query is the most executed and
> on which database instance(s).
>
> We are quite novice in administrating MySQL. We imagine that we can
> configure MySQL so that it logs every queries executed in its
> queries.log file. However is there any analysis tool that would import
> this log file and that would generate a complete report providing the
> number of similar queries (same DML order on the same table) per
> databases per minute?
>
> P.S.: we are using mytop and innotop, but it seems that they don't
> support such a feature. We see a lot of different queries executed
> against MySQL but we can't figure out, which kind of queries is the most
> executed, on which database.
>

Turn on the slow query log (log_slow_queries), set long_query_time to 1
(second), and then use the mysqldumpslow tool to aggregate all of the
slow queries:

[markleith@medusa:~/mysql/mysql-5.0-bk] $ mysqldumpslow --
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

--verbose verbose
--debug debug
-- write this text to standard output

-v verbose
-d debug
-s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be
wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup
script)
-l don't subtract lock time from total time

http://dev.mysql.com/doc/refman/5.0/...query-log.html

Regards

Mark

--
Mark Leith, Manager of Support, Americas
MySQL AB, www.mysql.com
Are you MySQL certified? www.mysql.com/certification

> -----Message d'origine-----
> De: Mark Leith [mailto:mleith@mysql.com]
> Envoyé: dimanche, décembre 16, 2007 04:53
> À: Daniel Caune
> Cc: mysql@lists.mysql.com
> Objet: Re: Monitoring and analysis tool
>
> Daniel Caune wrote:
> > Hi,
> >
> > Our MySQL server used for our development environment is slowing down,
> > certainly because of a massive query execution by different processes.
> > We are trying to determine which kind of query is the most executed and
> > on which database instance(s).
> >
> > We are quite novice in administrating MySQL. We imagine that we can
> > configure MySQL so that it logs every queries executed in its
> > queries.log file. However is there any analysis tool that would import
> > this log file and that would generate a complete report providing the
> > number of similar queries (same DML order on the same table) per
> > databases per minute?
> >
> > P.S.: we are using mytop and innotop, but it seems that they don't
> > support such a feature. We see a lot of different queries executed
> > against MySQL but we can't figure out, which kind of queries is the most
> > executed, on which database.
> >
>
> Turn on the slow query log (log_slow_queries), set long_query_time to 1
> (second), and then use the mysqldumpslow tool to aggregate all of the
> slow queries:
>

Thanks. However how can I trace every queries run against MySQL traced into slow-queries.log in order to use the mysqldumpslow tool? "The minimum and default values of long_query_time are 1 and 10, respectively." It seems that I can't set 0 for long_query_time.

--
Daniel

Daniel,

On Dec 16, 2007 12:37 PM, Daniel Caune <daniel.caune@ubisoft.com> wrote:
> > -----Message d'origine-----
> > De: Mark Leith [mailto:mleith@mysql.com]
> > Envoyé: dimanche, décembre 16, 2007 04:53
> > À: Daniel Caune
> > Cc: mysql@lists.mysql.com
> > Objet: Re: Monitoring and analysis tool
> >
> > Daniel Caune wrote:
> > > Hi,
> > >
> > > Our MySQL server used for our development environment is slowing down,
> > > certainly because of a massive query execution by different processes..
> > > We are trying to determine which kind of query is the most executed and
> > > on which database instance(s).
> > >
> > > We are quite novice in administrating MySQL. We imagine that we can
> > > configure MySQL so that it logs every queries executed in its
> > > queries.log file. However is there any analysis tool that would import
> > > this log file and that would generate a complete report providing the
> > > number of similar queries (same DML order on the same table) per
> > > databases per minute?
> > >
> > > P.S.: we are using mytop and innotop, but it seems that they don't
> > > support such a feature. We see a lot of different queries executed
> > > against MySQL but we can't figure out, which kind of queries is the most
> > > executed, on which database.
> > >
> >
> > Turn on the slow query log (log_slow_queries), set long_query_time to 1
> > (second), and then use the mysqldumpslow tool to aggregate all of the
> > slow queries:
> >
>
> Thanks. However how can I trace every queries run against MySQL traced into slow-queries.log in order to use the mysqldumpslow tool? "The minimum and default values of long_query_time are 1 and 10, respectively." It seems that I can't set 0 for long_query_time.

Correct. But if you are willing to patch your server, you can:

http://www.mysqlperformanceblog.com/...l-performance/

Baron Schwartz wrote:
> Correct. But if you are willing to patch your server, you can:
>
> http://www.mysqlperformanceblog.com/...l-performance/
>

This is in 5.1 as well now

Regards

Mark

--
Mark Leith, Manager of Support, Americas
MySQL AB, www.mysql.com
Are you MySQL certified? www.mysql.com/certification