2

We have a MySQL slow query killer that kills process IDs after a specified number of seconds passes and that works fine. However we'd like in our notifications to also see the full query that was being so slow.

The problem is that while mysqladmin -v processlist|grep process_id should work, it truncates queries that have newlines in them e.g.:

SELECT * FROM table WHERE x=y {
  ... stuff
};

Here stuff will be cut off and the query thusly truncated. (I realize that that may not be syntactically correct as I'm not a DBA, but I just wanted to give an example of the kind of query flow we sometimes have to deal with in our applications; please don't complain about the format, it wasn't my decision nor is it under my control.)

Doing a query in information_schema would solve this, I believe, but the team does not want to do this due to the performance impact queries against that database often involve. So is there a better way to approach this then grepping mysqladmin?

hopper
  • 13,060
  • 7
  • 49
  • 53
user578086
  • 321
  • 1
  • 4
  • 12

1 Answers1

2

I would advise to activate the slow log. It displays the statement that is taking long time,, how long and with extra details. Unless its in a STORED PROCEDURE.

You first need to activate the slow log in case it isnt allready.

To enable the slow query log, start mysqld with the --log-slow-queries[=file_name] option or change the values in your config file and restart the service, see below.

If the slow query log file is enabled but no name is specified, the default name is host_name-slow.log and the server creates the file in the same directory where it creates the PID file. If a name is given, the server creates the file in the data directory unless an absolute path name is given to specify a different directory.

You can set the output (where the data can be read) to FILE or TABLE

Change your my.ini file and change/add them

log-output = TABLE
slow-query-log=1
long_query_time = 10
log-queries-not-using-indexes

This will log any query that takes longer than 10 seconds to complete. Plus any query that is not using a index.

If you go with log-output=table then you can simply execute

select * from mysql.slow_log

If you go with log-output=file then you have to open the physical file in the MySql folder.

Mad Dog Tannen
  • 7,129
  • 5
  • 31
  • 55
  • That's not a bad idea, can I query out the process id query from mysql.slow_log? It looks like I can but not sure what rows relate to what I need…is this right? sql_text=the process query, insert_id=the process id (what's server_id?)? Thanks – user578086 Nov 12 '13 at 14:36
  • The thread_id is provided. In either case you should activate this as it will alert you as soon as you have long running queries – Mad Dog Tannen Nov 12 '13 at 14:37
  • Server_id i think is only relevant if you have multiple server setup or slave replication. – Mad Dog Tannen Nov 12 '13 at 17:46