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:
[EMAIL PROTECTED]:~/mysql/mysql-5.0-bk] $ mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help 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/en/slow-query-log.html
Regards
Mark
--
Mark Leith, Manager of Support, Americas
MySQL AB, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
