在PHP中执行的MySQL查询导致极大的httpd线程

Question

I'm working on an application which, based on a MySQL database, dynamically creates a UI that can be used to create and execute queries against that database. The database I am currently using contains about 400,000 rows with 77 columns.

When I execute a query that will return about half or more of the database an httpd thread is created that continues to grow in size, quickly taking up all the memory on my machine. The raw CSV I imported to create the database is only around 150MB.

I am developing with the Yii framework for PHP. Here is what my query code looks like:

$queryResults = 
    Yii::app()->db->createCommand()
          ->select('*')
          ->from($table_name)
          ->where($where)
          ->queryAll();

In this case $where is a value being passed via AJAX. After performing some checks on the query I then send it back to the client as such:

$response['success'] = true;
$response['results'] = $queryResults;
echo json_encode($response);

Here is a screenshot of what the httpd request created by a large query looks like:

When the process is initially created it jumps up to around 90% CPU usage. This query stayed at around 1.8GB real memory usage and still brought my machine to a halt until I manually stop the process. The machine I am working on at work only has 4GB of RAM.

I'm not sure what I am doing wrong. I have been looking into seeing if apache is configured incorrectly or if I should be using an unbuffered query in PHP but I'm still not sure what I must to do fix this memory issue. Any advice would be greatly appreciated. Thank you!

Answer 1

If you look at the code you are effectively creating three copies of the results.

1. $queryResults array, which is the results of your query.
2. $response['results'], which is a copy of $queryResults
3. The implicit copy created by the json_encode.

You could try reducing this by creating the JSON on the fly.

However, to drastically reduce memory issues, use the iterator.

$dataProvider = new CActiveDataProvider('User');   // <-- This is the model name
$resultIterator = new CDataProviderIterator($dataProvider);
foreach($resultIterator as $userRecord) {
   print_r($category);
}

Reference : http://www.yiiframework.com/doc/api/1.1/CDataProviderIterator