Re: Re: how do you get to do multiple mysql queries concurrently?
| From: | Nathan Rixham | Date: | Sat, 26 Jan 2008 04:10:00 +0000 |
| Subject: | Re: Re: how do you get to do multiple mysql queries concurrently? | ||
| References: | 1 2 3 4 5 6 7 8 9 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-268146@lists.php.net to get a copy of this message | ||
If you run the scripts through the CLI you can multithread your queries very easily.. you can wrap it in a while(1) block with a sleep(X) aswell to keep it going forever-ish..
<?php
$child = 0;
$pid = pcntl_fork();
if(!$pid) {
$db = new your_mysql_handler('connection gubbins');
$work = $db->select("select work_to_do from my_database WHERE work_done=0;");
if($work) {
$worker_threads = array();
$jobs = array_chunk($work, 1000, true);
foreach($jobs as $dex => $work) {
$child++;
$workerpid=pcntl_fork();
$worker_threads[$dex] = $workerpid;
if(!$workerpid) {
foreach($work as $ig => $row) {
#VERY important otherwise mysql/php kill your app after a few connections
$db = new your_mysql_handler('connection gubbins');
#do some work
}
echo "\tworker thread $child complete \n";
exit();
} else {
echo "\tlaunched worker thread $child : ".$workerpid."\n";
}
}
foreach($worker_threads as $dex => $workerpid) {
pcntl_waitpid($workerpid, $status);
}
echo "done the work\n";
}
} else {
echo "\nlaunched my worker";
}
?>
Back on the mysql side of things, try using geometry columns rather than numerical primary keys, with spatial indexes.. it's a MASSIVE performance upgrade (I've cut 5 second queries down to 0.005 by using geo columns)
Nath
Per Jessen wrote:
Jochem Maas wrote:have you thought of batch processing via cron? you can have a parent process run off children to handle each long running query and package the results in format that's quick to output - thereby minimizing the time taken to batch process and then have the summary page merely use the latest packaged results.Yeah, we're already doing quite a bit of this. We don't do it with cron, but we cache the long running results for 15min. That way at least the summary page appears to be quite responsive (most of the time). Excellent suggestion by the way - we are just trying to reduce this type of batching/caching to be more real-time and/or responsive. /Per Jessen, Zürich