Memory usage by May CTP
Using the same test as before, Apache httpd doesn't seem to grow without bound any more. That's good. This is with a standard Apache and PHP5 combination. Thread safe version of the driver.
When I run the non-thread safe version on a computer that uses the Zend framework, then the php-cgi.exe program has its memory use growing. I don't think that's good since that's our production environment.
Answers
For version 1.0, our driver will rely on the memory management offered by the PHP engine (including its garbage collector). This implies that the memory accumulated over the execution of a script is only released through the PHP garbage collection mechanism. In other words, if the garage collector does not run until the end of the script, the memory usage will continuously increase as the script progresses. If this becomes a critical issue for our customers, then we might need to revisit the design of the driver and re-engineer its memory management.
By default, the Microsoft driver retrieves date/time fields as PHP DateTime objects. To convert SQL date/time fields to DateTime objects, the driver employs some PHP functions such as date_create(). In our opinion, those PHP functions appear to leak memory. The workaround for avoiding those memory leaks is to retrieve date/time fields as strings.
Moreover, the version 1.1 of the driver introduced a new connection option, “ReturnDatesAsStrings". If that option is set to true, then the driver will return the date/time fields as strings without using any of PHP related functions. In our tests, with “ReturnDatesAsStrings” set to true, no memory leak were noticed.
Hope this helps,
Serban
Serban Iliescu - MSFT- Marked As Answer bySerban Iliescu - MSFTAnswererFriday, October 09, 2009 10:54 PM
All Replies
Thanks for the feedback, Charlie.
Are you able to reproduce the problem with a script that executes and fetches the results of a simple query?
Same as in previous posting:
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Database Layer Test</TITLE>
</HEAD>
<BODY TEXT=#000000 BGCOLOR=#FFFFFF LINK=#0000EE VLINK=#551A8B ALINK=#FF0000 MARGINHEIGHT=0 TOPMARGIN=0 MARGINWIDTH=0LEFTMARGIN=0>
<?php
$username = "***";
$password = "***";
$serverName = "***";
$connectionInfo = array( "UID"=>$username,"PWD"=>$password,"Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
$loops = 100000;
$loop = 1;
$sql = "select top 300 * from Sales.SalesOrderHeader";
while ($loop < $loops) {
if ($result = sqlsrv_query( $conn, $sql)) {
$rowcnt = 1;
while ($row = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC)) {
$data1[$rowcnt] = $row;
$rowcnt++;
}
sqlsrv_free_stmt($result);
}
unset($data1);
$loop++;
}
sqlsrv_close( $conn);?>
</body>
</html>- Thank you for the repro, we are currently looking into this, I will respond to this thread once we have further information on this issue.
We are unable to reproduce the memory leakage on the non thread version. Actually, we do not see any difference in behavior between the two versions. We run our tests in two different contexts:
- Web-based tests where the PHP extension is called by HTTP clients (over IIS7/FastCGI).
- Zend-based tests where the PHP extension is accessed directly via the “eval” function exposed by Zend engine.
In both environments, we do not see any differences in the memory footprints yielded by either the thread-safe or not thread-safe version. The memory usage increases at the beginning, but it flattens out in time.
We would appreciate if you can provide some additional details regarding the environment where you experience memory leaks. We would like to know the following:
- Operating system version (and Service Pack number, if any)
- PHP version (we are using 5.2.6)
- How the extension is actually accessed. If it is a web-based scenario, we would like to know what web server is used (including the version number).
Thank you for your support.
The environment is Apache, Zend, FastCGI. I'll get the exact versions tomorrow.
As you can see above, the test is simply a web page. But it loops for a long time on the same query.
It turns out that in the Zend environment, I was running the test against a different table in a different database.
Regardless of thread safe, the serving program (httpd or php-cgi) levels out quickly in memory usage around 22,000 to 24,000 K according to Task manager, when using the test web page shown earlier in this thread. This uses the Sales.SalesOrderHeader table in Adventureworks.
If I change the test program to use the DatabaseLog table in Adventureworks, then the memory use increases. That table is more complex, so is more similar to the production table I used that caused the increased memory use.
I created a table named test with a single int field named test1. Then created three rows with values 1,2,3 Then changed the test program to "select * from test" for the query, and commented out the line that increments the loop variable. That also results in increased memory usage.
Envrionment 1 (thread safe version used in):
Machine - virtual running under VMWare
Windows 2000
PHP 5.2.5
Apache 2.2.4
SQL 2005
Environment 2 (non thread safe version used in):
Machine - virtual
Windows 2003 R2, standard edition, SP2
Zend Platform 3.0.3
Zend Core v2.5
Zend Engine 2.2.0
PHP 5.2.4
Apache 2.2.4
Thank you very much for the detailed info. We see as well a rapid increase of memory usage, but it flattens around 42K. Additionally, we noticed that PHP 5.2.6 eliminated some of the memory leaks that we experienced with 5.2.4 (we never actually used 5.2.5).
Will it be possible to check whether the memory leaks you report are still present with 5.2.6?
Charlie,
Have you had a chance to try to reproduce the memory leak with PHP 5.2.6? If the problem still exists with that configuration, we'd like to take a closer look.
David Sceppa
Program Manager, SQL Server Driver for PHPJust got back from some time off. I'll try to look into it on Friday.
I installed PHP 5.2.6.
I still see the increased memory usage with no flattening out.
This is with Apache 2.2.4, PHP 5.2.6, Windows 2000, SQL server 2005.
I set the memory limit in php.ini to -1 to mean unlimited. If I set it to a fixed value, then PHP stops with a fatal error - memory limit exhausted.
Here is the latest test web page:
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Database Layer Test</TITLE>
</HEAD>
<BODY TEXT=#000000 BGCOLOR=#FFFFFF LINK=#0000EE VLINK=#551A8B ALINK=#FF0000 MARGINHEIGHT=0 TOPMARGIN=0 MARGINWIDTH=0 LEFTMARGIN=0>
<?php
$username = "???";
$password = "???";
$serverName = "???";
$connectionInfo = array( "UID"=>$username,"PWD"=>$password,"Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
$loops = 100000;
$loop = 1;
$sql = "select top 10 * from DatabaseLog";
//$sql = "select top 300 * from Sales.SalesOrderHeader";
while ($loop < $loops) {
if ($result = sqlsrv_query( $conn, $sql)) {
$rowcnt = 1;
while ($row = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC)) {
$data1[$rowcnt] = $row;
$rowcnt++;
}
sqlsrv_free_stmt($result);
} else {
DisplayErrors();
exit;
}
unset($data1);
//$loop++;
}
sqlsrv_close( $conn);function DisplayErrors()
{
$errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
foreach( $errors as $error )
{
echo "Error: ".$error['message']."\n";
}
}?>
</body>
</html>You can see that it no longer increments the loop counter variable. That's because I was also testing against a much smaller table that would cuase the page to finish processing too fast.
Charlie, thank you for your reply. We are investigating this and we will report back to you within a week with our findings.
Hi Charlie,
I would like to clarify whether you expected the memory to remain more or less constant during the test. In other words, you expected that the call to “unset” has an immediate impact on the memory allocation. This assumption would be correct if, and only if, the garbage collector does some work before the page is returned. We do not see any evidence that the garbage collector is triggered prior to ending the script. We executed an almost identical test (with Apache) and we saw as you reported the memory continuously inching up (as high as 800 MB). However, we saw as well that the memory is released at the end of the PHP script.
The original reason I brougt this issue up is that I would run a test that scans through en entire table, a set of rows at a time, and would compare a set of rows read using the old ODBC intreface, and then your new interface. It would exceed the memory space. So I tracked the problem down further. When the looping type page ran using old ODBC (odbc_exec), then the memory usage stays flat and low. When I use the Microsoft driver, then it grew without bound.
When I originally reported the problem, someone later said the problem was fixed in the May CTP. When I ran the same test page, that hit the sales order header page in adventureworks, then indeed, the memory usage flattened out faster and stopped growing.
But then I noticed that against our own database tables, the memory usage still grew. So I simply tried using another table in adventureworks similar to our tables (more complex data types, like text), and found that memory use grew again.
I also thought that perhaps this was all just due to the PHP memory manager. But then why does the old odbc_exec not have this problem? Even though it still reads data into new arrays and then does the unset (to simulate the get/compare/unset that my original test program did). So I decided that it must have to do with something within the Microsoft diver.
Now odbc_exec normally uses server side cursors, and on each row fetch does a SQL server call. I assume the data is buffered on the SQL server side and is only retrieved one row at a time as the PHP code calls the function to fetch the next row. So the amount of memory used on the PHP side is low since it doesn't need to buffer more than a row of data. So I'm thinking that might be why it doesn't grow in memory usage. SQL server profiler easily shows traces of this kind of traffic. This is the type of traffic i was hoping to avoid by using your driver.
My assumption is that your driver gets more data than one row at a time. Similar to using the ADO.Net native SQL server provider. Probably similar to a datareader - forward only, read only cursor, firehose. So perhaps the data gets buffered internal to the driver/PHP, then is passed back one row at a time as the API is called to fetch each row. So I assume that either your driver isn't freeing the memory (low probability), or that even your code must wait for the PHP garbage collector to run. But I wasn't sure about that. I thought that perhaps your code is written in C++ and has its own memory management. So I just reported the problem.
The problem is that if I use the "normal" memory limit that we were using, then tests run out of memory before the page returns. Now this is just a test. But we have production pages that are "report" type pages that can format many rows from the database, based on date ranges, and those pages were subject to timeouts in the past, but would not hit a memory limit.
I suppose that you could just tell people that they must set the memory limit in php.ini to -1. But I don't know that people would like that answer.
Note that if I change the latest test code I gave you to only pull in the first row, then no more, so that the array being unset just has one row in it, then it still increases in memory size.
I guess one could change the test to just fetch each row into the $row variable, and tehn never use it for anything, and then see if memory use still grows. Or just run the query and never fetch any rows?
Sometimes I wish I had your code so I could just delve into it futher myself.
You raised a valid point, regardless whether the problem originates within the Microsoft driver or the PHP runtime. This is the type of real-world scenarios that we’ll need to take into account when considering to refine the driver for further releases.
The driver is written in C++ but it employs the PHP memory management, and therefore it is dependent on the PHP garbage collector. Unfortunately, the PHP runtime (unlike Java and .NET environments) does not provide a mechanism for requesting garbage collection programmatically. In other words, we cannot trigger any garbage collection from within the driver.
When a row is fetched via sqlsrv_fetch_array or sqlsrv_fetch_object, the entire row data is stored within the driver, one single row at a time (forward only cursor). This is different from sqlsrv_fetch, where no data is retrieved and stored within the driver until sqlsrv_get_field is called.
One last note. We fixed some memory leaks, but we have never attempted to address the problem that you actually reported (i.e. continuous increase of memory usage throughout the execution of a PHP script). Our focus was to make sure that all the resources are marked appropriately as “no longer needed”, to enable memory disposal via the PHP garbage collector. The message we take from your post is that we need to do more, to make sure that the memory is released during the execution of the script (and not only at the end of it).
I noticed that there may be a way to select from several different memory manager methods in PHP. But I never had time to try any of them. Maybe one of them would work better.
For version 1.0, our driver will rely on the memory management offered by the PHP engine (including its garbage collector). This implies that the memory accumulated over the execution of a script is only released through the PHP garbage collection mechanism. In other words, if the garage collector does not run until the end of the script, the memory usage will continuously increase as the script progresses. If this becomes a critical issue for our customers, then we might need to revisit the design of the driver and re-engineer its memory management.
- Hi--
Spent last night and most of today tracking down a memory leak that appears to be the one described in the posts above.
It still exists in the CTP 1.1 version of the SQL Server API and is a _serious_ problem if running PHP5 as a Windows Service (or any other long-running PHP process).
I have been coding PHP since PHP3 (10+ years) and use PHP5 in production environments serving hundreds of customers processing millions of records per month in SQL Server.
It appears that the sqlsrv_free_stmt() API function does _not_ free memory properly. You should know that the corresponding mssql_* function mssql_free_result() _does_ free memory properly and I have been using it for many years as part of web-based PHP applications as well as Windows Services. The mssql_free_result() function manual page is here: http://us2.php.net/manual/en/function.mssql-free-result.php
Anyone using sqlsrv_free_stmt() in the same manner will leak memory, but 99.9% of people using PHP will never notice because their scripts exit before the memory leaks accumulate.
As I read through this thread I noticed discussions of PHP doing "garbage collection" to recoup the freed memory. Unless something has changed drastically in PHP 5.3 (which is possible -- I am going to slowly port our production environment from PHP 5.2.10 to PHP 5.3 over the coming month), there is no way to coax a long-running PHP5 script into garbage collecting the way I believe you may think. The only gc calls I know of are related to PHP Sessions. Other ways to recoup memory including using unset() and automatic recouping that occurs for instance when functions are exited (local vars' memory recouped) and of course when the script exits.
We have max memory set to 256 MB for each PHP process and when a Windows Service I have running does enough SQL Server calls over the course of a day it will break this max and PHP shuts down the process -- there is no garbage collection, because the script has _exceeded the maximum memory allowed_.
The same Windows Service using mssql_* functions (including mssql_free_result()) have been running 24/7, 365 days a year for the past three years with no problem whatsoever. mssql_free_result() works great. Why isn't sqlsrv_free_stmt() using the same logic?
To narrow down the problem I put the following simple code in a Windows Service under PHP5 and let 'er rip. Every five minutes it calls this code and every five minutes it chunks up an extra 20 MB (the size of the select * of this table) in Task Manager. I've tried using thread-safe and non-thread-safe versions:
--------------------------------------------- // NOTE: I REPLACED ALL THE SENSITIVE DATA WITH "my*" $host = 'mydbhost'; $opts = array( 'Database' => 'mydbname', 'UID' => 'mydbuser', 'PWD' => 'mydbpwd' ); $tsql = "select * from mydb.dbo.mytable"; // HERE'S WHERE THE ACTUAL WORK HAPPENS $conn = sqlsrv_connect( $host, $opts ); $stmt = sqlsrv_query( $conn, $tsql ); while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC ) ) $value = $row['id']; sqlsrv_free_stmt( $stmt ); sqlsrv_close( $conn ); ---------------------------------------------
I've tried separating the connect/disconnect so it only happens once and not every 5 minutes in the Windows Service main loop.
I've tried putting this code in a completely separate function() in order to try and force PHP to garbage collect since all would be local variables.
I've tried putting unset( $stmt ) after the sqlsrv_free_stm( $stmt ). I've tried using _just_ the unset() and not the sqlsrv_free_stmt() first.
And on and on.
The only workaround I have figured out is to let the service only run X times and then automatically restart (I have a separate service that monitors select services and restarts them if they go down for some unforeseen reason). This is definitely not the best approach but is the only thing that is going to work for now.
So unless something is drastically different in PHP 5.3's garbage collection (and I'll know relatively soon) there appears to be a major problem with sqlsrv_free_stmt() _not_ actually freeing memory. mssql_free_result() works great, so I would suggest doing the same thing they do (I'm not sure how to find out what they did but it sure works). My guess would be you need to free whatever you are allocating and not rely on PHP to garbage collect it -- it's definitely not the same as Java, etc. in how this is done.
Our current environment:
PHP 5.2.10
Windows Server 2003
SQL Server API 1.1
Thanks for your time and energy on making the SQL Server API. I had to do a little work to figure out a couple of things that weren't explained fully in the API documentation (getting stored procedure return codes), but overall I'm very pleased. It's definitely faster than the mssql_* API and _finally_ gives me _complete_ error messages from SQL Server, and at last (once I figured it out) I can get the return codes easily from stored procedures. The only real complaint I have is this issue with memory leaking, because there is no way around it. Keep running the above in a loop and you will pop the script (it will be killed).
Cheers,
Wa Ca
P.S. Tried with PHP 5.3 but since there is no version of win32_services yet from PECL to run with either the VC9 or VC6 version (I tried both anyway), there is no way to test whether or not the memory allocation is still a problem with PHP 5.3. My guess is it is. The only thing in PHP 5.3 I can see as a possibility -- but I don't know precisely what it does -- is "gc_collect_cycles()"...- Edited bywaca Wednesday, October 07, 2009 5:37 AMadded P.S. tried to test with PHP 5.3
- Looks like this is fixed in the October 2009 release of v1.1 -- yay!
I must have _just_ missed it with still having the earlier (September) release.
Wanted to go ahead and reply so anyone else running into this same issue would know it was fixed if they searched the forums.
Cheers,
Wa Ca By default, the Microsoft driver retrieves date/time fields as PHP DateTime objects. To convert SQL date/time fields to DateTime objects, the driver employs some PHP functions such as date_create(). In our opinion, those PHP functions appear to leak memory. The workaround for avoiding those memory leaks is to retrieve date/time fields as strings.
Moreover, the version 1.1 of the driver introduced a new connection option, “ReturnDatesAsStrings". If that option is set to true, then the driver will return the date/time fields as strings without using any of PHP related functions. In our tests, with “ReturnDatesAsStrings” set to true, no memory leak were noticed.
Hope this helps,
Serban
Serban Iliescu - MSFT- Marked As Answer bySerban Iliescu - MSFTAnswererFriday, October 09, 2009 10:54 PM
- Aha. This makes sense as my test SQL query was returning DateTime objects.
I just finished another round of testing using the ReturnDatesAsStrings connection option and I can confirm that the PHP Windows Service no longer leaks memory.
I wonder if there would be a difference in your API between using PHP's date_create() and:
try { $dt = new DateTime( $date_string ); } catch(Exception $e) { // HANDLE EXCEPTION }
Otherwise hopefully any memory leak re date_create() will be fixed in PHP 5.3.1.
Thanks for answering my post and again for building such a nice API into SQL Server.
Cheers,
Wa Ca
