Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is the format of the mysql query log? In particular, for lines like:

133 Query     commit
133 Query     rollback

What does '133' stand for? And is it true that each line represents a round trip communication to the database (i.e., they are not batched)?

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

A typical general log format looks like this:

Time              Id Command    Argument
110208 12:12:06   771 Query rollback

Multiple queries can appear at particular time-stamp also ID indicates MySQL connection thread id which has executed a query Command of type "Query" which indicates query has been executed and Argument is actually a query itself.

share|improve this answer
add comment

In the MySQL General Query Log, the fields are these:

yymmdd hh:mm:ss thread_id command_type query_body

The timestamp only appears each time it changes.

In your example "133" would be the thread id of the connected client, as shown in SHOW PROCESSLIST;.

Each line represents a query issued, but not necessarily a round-trip from the client to the database, because queries issued within stored procedures and stored functions are also logged, and a proc or function, while running, could issue many queries in response to a single query from a client.

Also, the MySQL C-API, which is the library underlying some languages' connectors to MySQL, supports multiple statement execution, which would be another case where multiple queries don't necessarily represent multiple round-trips since multiple queries can be send to the database as a "batch"... but that's only a factor if this capability is used by the calling application.

The general log can also be directed to write to the mysql.general_log table, which is easier to parse and analyze than the file-based log, but the same general observations, above, apply when using that format.

The slow query log includes more information and so provides a more informative log for understanding how the server is performing, and can be used as a log of all queries by configuring MySQL to interpret all queries as meeting the "slow" criteria. This is done by setting long_query_time to '0' after enabling the slow query log.

share|improve this answer
add comment

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.