0

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!

Community
  • 1
  • 1
Jacthoma
  • 111
  • 1
  • 1
  • 8

3 Answers3

1

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

crafter
  • 6,246
  • 1
  • 34
  • 46
volkinc
  • 2,143
  • 1
  • 15
  • 19
1

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.

mister270
  • 366
  • 1
  • 15
  • Thank you for the response! My goal is to return every column with every query so that the end user can download that file and use it in Excel. I'll look into MySQL Explain and indexing columns. – Jacthoma May 17 '14 at 00:08
  • Hope it goes well. volkinc's answer and mine were similar ideas, isolate the query and make sure it's not the problem. Accept the answer if you think it's the one you want to go with. – mister270 May 17 '14 at 00:11
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

crafter
  • 6,246
  • 1
  • 34
  • 46
  • Thank you for the response! This is certainly seems like the right thing to do. I'm having trouble figuring out how to get my query accessible from the CDataProviderIterator. So far I've tried querying the database using CSQLDataProvider and passing that in while initializing the CDataProviderIterator. Could you point me in the right direction to access the query? thank you! – Jacthoma May 20 '14 at 00:18
  • Have a look here : http://stackoverflow.com/questions/19973624/foreach-for-a-large-table-with-less-memory-used-yii-framwork – crafter May 20 '14 at 01:03
  • And a solution that fetches in batch - interesting - http://stackoverflow.com/questions/12170102/how-to-iterate-over-yii-cactivedataprovider-object – crafter May 20 '14 at 01:05