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

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:

httpd process screen shot

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!

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

First get the Raw query and run it on a workbench or phpMyAdmin to see if the query itself is too heavy. (quite not likely).

second. check if you using caching. also could be the problem

The first thing that I noticed was the use of the wildcard for the columns. Many SQL implementations have cautioned against this due to performance reasons. However you are doing dynamic SQL (another performance hit there) and may need to do this if the where clause can operate on any column.

If you can remove the wildcard and specify specific columns, omitting ones that will not be used in the where clause, that may help. You can also try running a MySQL Explain on your query for various where clauses and see what the execution plan looks like. You may glean some info there.

You should also consider indexing frequently used columns if you haven't already, or even use a covered index which is indexing all the columns in your table. That may sound extreme but I've seen many cases where covered indexing improved performance dramatically.