6

I'm trying to count every SELECTS, UPDATES, INSERTs querys in my MySQL server. The main idea is to say, at the end of the day, "we had x querys today, being x1 selects, x2 inserts, etc".

How can i do it?

Thanks!!

5 Answers 5

5

MySQL already maintains internal counters:

mysqladmin ext | grep -e 'Com_\(update\|select\|insert\)'

no need for log files/tables/tcpdumping/maatkit.

2
  • Thanks man, great answer. I've been looking for the meaning of Com_ satus variables, but i can't find it. What those exactly means? May 7, 2010 at 18:38
  • dev.mysql.com/doc/refman/5.1/en/… -- in short, com_select counts select queries, com_update the updates, etc.
    – ggiroux
    May 7, 2010 at 18:48
8

Current MySQL versions support two different kinds of query logs:

First, the general query log tracks all statements executed on the MySQL server, including SELECTs. But it's slow, inefficient, and can easily kill the performance of a production database, so be careful with it.

Depending on which version of MySQL you're running, you should use either the '--log' option or the '--general-log' and '--general_log_file' options, together, to enable the general query log and control where it writes to. The docs have more details:

In case the general query log's runtime penalty is too high, you might consider whether the binary log (binlog) could meet your needs, instead. The binlog tracks any statements that can update data or schema, but not read-only statements like SELECT. But the binlog is really fast and disk-efficient, and it can also be used for replication, backups/restores, etc.

The basic option to enable the binlog is '--log-bin', and there are some other related options that control exactly how it works. The 'mysqlbinlog' utility (part of the core MySQL server installation) can parse the binlog's binary format back into SQL statements, which you can pipe to whatever script or program you use to summarize the queries (e.g., mysqlbinlog <BIN_LOG_FILENAME> | <YOUR_SCRIPT_NAME>. See the relevant doc pages for more info:

2

logs are all fine and dandy - answers mentioned by Irosa and Ryan are great, but you can do it completly passively - without enabling logging. take a look at mk-query-digest from maatkit. it can analyze not only mysql logs but also traffic captured with tcpdump. and 'for free' it will give you nice breakdown of query types/execution time etc.

0

Edit my.cnf and add

log=/path/to/whatever/file/you.want

and restart MySQL.

You will find all the queries logged in the file you specify.

0

Besides the two mentioned solutions (MySQL query log and mk-query-digest) you might want to take a look at MySQL Proxy which can be used to log the queries sent to your mysqld.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .