One of the worst parts of a web application can be the variability of mysql queries that get sent into your database. You can add indices, tweak hardware configurations, etc., but wouldn’t it be nice to simply kill any database query that takes longer than whatever you deem is “too long”?
Well, no, not any query. I would never want to kill a write — just a read-only query; in particular: search queries.
So, as it turns out, the hurdles for this are immense, and, because my solution uses PHP’s pcntl_fork() function, even my solution, while it works, it has to make assumptions and is not perfect.
That being said, it would seem easy enough for this to be built into PHP or for some similar mechanism to be built into MySQL: Execute this query but only if it takes less than n seconds. If not, kill it. This is not the case, however, so we’re left to our own cleverness.
There are hundreds of reasons why you would never want to do this, but I only need one reason to want to do it to try to implement it.
So here is my solution steps in techno-layman’s terms, followed by the necessary code:
- Call a function to execute a MySQL query (again, preferably read-only)
- Open a shared memory space so that we can pass the query results back to the parent from the child
- Store process state information in a database
- Fork, and execute the query in the child process
- Keep time in the parent process
- Kill the child process if it takes longer than n seconds
- Return the results
Just to forwarn, I have tested this as proof of concept, but I am uncertain about the particulars of PHP’s shared memory and am not confident how reliable the shared memory implementation will in a production environment. I plan to try it out, but right now I’m just getting the info out there.
So the state database will be defined as follows:
CREATE TABLE IF NOT EXISTS `pcntlFork` (
`idx` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`status` enum('0','1') NOT NULL DEFAULT '0',
`output` longblob NOT NULL,
PRIMARY KEY (`idx`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=58 ;
The PHP class is available to download here: http://mysql-restrictor.googlecode.com/files/mysqlRestrictor.class.php (I had it inline, but WordPress did not want to format it properly)
And you would use it something like this
$m = new mysqlRestrictor();
$results = $m->dbQuery("SELECT * FROM `table`");
var_dump($results);
Since this took me an extremely long time to build and just verify that it even works, I welcome comments for improvement, and by all means use it for yourself. Let me know how it does in production!
A simple question on Sandusky and Penn State’s chopping block
I have seen a few posts generally asking the question, but I want to weigh in on it as well. The United States is still equipped with a system called due process, but Penn State has already pulled the trigger on many people (most notably Joe Paterno) before any legal judgment has been handed to Sandusky.
Not that I think it will happen, but what if Sandusky is cleared of all charges? Will that not completely invalidate all the action taken by the University?
Posted in Commentary