Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!!

share|improve this question

5 Answers 5

up vote 3 down vote accepted

MySQL already maintains internal counters:

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

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

share|improve this answer
    
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? –  santiago.basulto May 7 '10 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 '10 at 18:48

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:

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.