If you are using MySQL 5.1+, you can create a scheduled event in MySQL or a cronjob to poll the processlist from the information schema.
What queries do you look for in the processlist? Any DB Connection that ...
- runs longer that 30 seconds
- not used for replication
- runs a SELECT query
Here is that query:
SELECT id FROM information_schema.processlist
WHERE time > 30 and user <> 'system user' and LEFT(info,6)='SELECT';
Here is a Stored Procedure to collect those IDs and terminate the query using the KILL command:
CREATE DATABASE myutil;
USE myutil
DELIMITER $$
DROP PROCEDURE IF EXISTS `Kill_Long_Queries` $$
CREATE PROCEDURE `Kill_Long_Queries` ()
BEGIN
CREATE TABLE IF NOT EXISTS IDs_To_Kill;
(ndx INT NOT NULL AUTO_INCREMENT PRIMARY KEY,id INT NOT NULL) ENGINE=MyISAM;
TRUNCATE TABLE IDs_To_Kill;
INSERT INTO IDs_To_Kill (ProcessID)
SELECT id FROM information_schema.processlist
WHERE time > 30 and user <> 'system user' and LEFT(info,6)='SELECT';
SET @ndxnext = 0;
SELECT MAX(ndx) INTO @ndxlast FROM IDs_To_Kill;
WHILE @ndxnext < @ndxlast DO
SET @ndxnext = @ndxnext + 1;
SELECT id INTO @ProcessID from IDs_To_Kill WHERE ndx = @ndxnext;
SET @SQL = CONCAT('KILL QUERY ',@ProcessID);
PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;
END WHILE;
END $$
DELIMITER ;
If you want to go beyond terminating the query and just terminate the DB Connection, replace
SET @SQL = CONCAT('KILL QUERY ',@ProcessID);
with this
SET @SQL = CONCAT('KILL ',@ProcessID);
Launch Stored Procedure Using MySQL Event
USE myutil
DELIMITER $$
CREATE EVENT kill_query_event
ON SCHEDULE EVERY 1 MINUTE
STARTS (NOW() + INTERVAL 1 MINUTE + INTERVAL SECOND(NOW()) SECOND) DO
BEGIN
CALL Kill_Long_Queries();
END $$
DELIMITER ;
Launch Stored Procedure Using cron
In the OS, set up the crontab to go off every minute as follows
0 * * * * mysql -u... -p... -Dmyutil -ANe"CALL Kill_Long_Queries()"
CAVEAT for MySQL 5.0 Users
Since there are no events in the MySQL 5.0 and prior, you will have to do something like this:
for PROC_TO_KILL in `mysql -h... -u... -p... -ANe"SHOW PROCESSLIST" | grep -v "system user" | awk '{print $1}'` ; do mysql -h... -u... -p... -ANe"KILL QUERY ${PROC_TO_KILL}" ; done
SPECIAL CAVEAT
Without a MySQL Event, you would have to connect to mysql each time and poll the processlist. In the event that there are too many connections or if the mysqld starts blocking connection due to connect errors, your only hope is to write a perl script whose sole job is to kill long running query and using only one live session at the same time.
I wrote a perl script two years to run flush hosts every 10 minutes (How do you tell which host is close to being blocked in MySQL?)
Here is that script
#!/usr/bin/perl
use DBI;
use DBD::mysql;
my ($SLEEP_TIME_INTERVAL,$HEARTBEAT_CYCLE) = @ARGV;
$check_replication = 1;
$username = "whateveruser";
$password = "whateverpassword";
$MYSQL_HEARTBEAT_FILE = "/tmp/MySQLHeartBeat.txt";
$MYSQL_FLATLINE_FILE = "/tmp/MySQLFlatLine.txt";
$MYSQL_PROCESSLIST_FILE = "/tmp/MySQL_Processlist.txt";
if ( $SLEEP_TIME_INTERVAL == 0 )
{
open(fh_output,"> $MYSQL_FLATLINE_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
printf fh_output "DONE !!!\n";
close fh_output;
exit 0;
}
$db = DBI->connect("DBI:mysql:information_schema;host=127.0.0.1",$username,$password)
or die "Cannot connect to the DB information_schema\n",$DBI->errstr(),"\n";
$st_globalstatus = $db->prepare("SHOW GLOBAL STATUS") or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";
$st_flush_hosts = $db->prepare("FLUSH HOSTS") or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";
$heartbeat_count = 0;
$my_heart_is_beating = 1;
while ( $my_heart_is_beating )
{
$st_globalstatus->execute() or die "Cannot Execute SQL Statement\n",$DBI->errstr(),"\n";
{
$StatusCount = 0;
while ( my $row = $st_globalstatus->fetchrow_hashref() )
{
my $uptime = $row->{Value};
}
$st_globalstatus->finish();
open(fh_output,"> $MYSQL_HEARTBEAT_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
printf fh_output "%s : %s\n",$uptime;
close fh_output;
}
for ($i = 0; $i < $SLEEP_TIME_INTERVAL; $i++)
{
if ( -f $MYSQL_FLATLINE_FILE )
{
unlink $MYSQL_HEARTBEAT_FILE;
unlink $MYSQL_FLATLINE_FILE;
$my_heart_is_beating = 0;
}
sleep 1;
}
$heartbeat_count++;
if ( $heartbeat_count == $HEARTBEAT_CYCLE )
{
$heartbeat_count = 0;
$st_flush_hosts->execute();
}
}
$db->disconnect();
I am not a perl expert, but I suggest that you alter this script to collect and kill the process IDs. Save it to a file called killquery.pl
. All you need to do is launch this once from the command line as a background process.
nohup perl killquery.pl 5 30 &
This makes the perl script heartbeat every 5 sec, and run the Stored Procedure every 30 second
Give it a Try !!!
I think I got the answer:
You need to put those options under the [mysqld]
section
[mysqld]
slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1
and restart mysql
UPDATE 2013-03-05 16:36 EST
I don't know why this is still happening, but please try this:
service mysql stop
rm -f /var/logs/my.slow.log
touch /var/logs/my.slow.log
chown mysql:mysql /var/logs/my.slow.log
service mysql start
then run SELECT SLEEP(10);
and see if it lands in /var/logs/my.slow.log
Best Answer
"0.002" sounds like the Query cache came into play.
When the QC is turned on exactly the same
SELECT
is run a second time before any changes happen to the table(s) involved, the resultset is looked up in a Hash (called the Query Cache). This will take on the order of 1ms regardless of how long the query normally takes.Or...
Since you are using MyISAM, virtually any operation that touches the same table(s) will 'lock' the table and cause a normally fast query to stall for as long as the other query (or queries) take. Switching to InnoDB is likely to help.
If you would like to discuss it further, please provide the query,
SHOW CREATE TABLE
andEXPLAIN SELECT ...
,SHOW VARIABLES LIKE 'query%';
,SHOW GLOBAL STATUS LIKE 'Qc%';
The infomation in those may lead to eliminating one or the other of the above explanations.