I am experiencing issue.
Someone 'attacked' my server: simply by searching the same phrase with multiple requests.
As it is text search request and database indices are not used, the engine searches through every row. In general I will deal with myisam full-text indices, but I am curious, is there any way to block/disconnect/interrupt the query which is longer than x seconds?
P.S. I have wait_timeout
set to 30
, is there anything else I can set(I don't think interactive
timeout will be useful).
Best Answer
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 ...
Here is that query:
Here is a Stored Procedure to collect those IDs and terminate the query using the KILL command:
If you want to go beyond terminating the query and just terminate the DB Connection, replace
with this
Launch Stored Procedure Using MySQL Event
Launch Stored Procedure Using cron
In the OS, set up the crontab to go off every minute as follows
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:
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
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.This makes the perl script heartbeat every 5 sec, and run the Stored Procedure every 30 second
Give it a Try !!!