Hi!
I'm fairly new at both PHP and Flourish, so hopefully my question is an easy one :-)
Here's my code:
$dbThis=new fDatabase('mysql',$globDatabaseName,$globDbUsername,$globDbPassword,$globDbServer);
$strSql="SELECT l.LanguageId, Text ".
"FROM entLanguages AS l, entTexts AS t WHERE TextNum=LanguageDescTextNum AND t.LanguageId=1 ".
"ORDER BY l.LanguageId ASC";
$stmtThis=$dbThis->prepare($strSql);
$rsThis=$dbThis->query($stmtThis);
foreach ($rsThis as $row){
$strLanguages.=HtmlFix($row["Text"])."||";
$strLanguageIds.=$row["LanguageId"]."||";
}
If I add in lines to display memory_get_usage() after every time I create a new instance, I notice that $rsThis eats up about 50MB of RAM, which seems a lot, given that sure, the Text column is a MySQL MEDIUMTEXT field, but it currently never holds anything above 200 chars (UTF-8).
The real problem is, I can't seem to get that memory back.
I've tried:
unset($rsThis);
$rsThis->destruct();
$rsThis=null;
...none of which make any difference, alone or in different combinations.
Any help would be greatly appreciated!
Thanks in advance,
Per.
Well, you are using a prepared statement where you don't need one, and I bet the database extension being selected stores the results related to the prepared statement object.
You really only want to use a prepared statement if the query requires it (32+ KB strings for DB2 or Oracle), or if you are going to execute the query multiple times. Otherwise the separate prepare and query will most likely take longer than a query.
Try this instead:
$dbThis=new fDatabase('mysql',$globDatabaseName,$globDbUsername,$globDbPassword,$globDbServer);
$strSql="SELECT l.LanguageId, Text ".
"FROM entLanguages AS l, entTexts AS t WHERE TextNum=LanguageDescTextNum AND t.LanguageId=1 ".
"ORDER BY l.LanguageId ASC";
$rsThis=$dbThis->query($strSql);
foreach ($rsThis as $row){
$strLanguages.=HtmlFix($row["Text"])."||";
$strLanguageIds.=$row["LanguageId"]."||";
}
Thanks for the reply!
Yup, doing it without a statement certainly works well, memory gets released nicely.
As you say, in this case, doing a prepared statement is pointless, I just use them all the time to make sure I don't forget to when I *do* need them. And there does seem to be a memory leak or release problem with fResult:
if ($boolShowMem) DbugMem("before languages");
$dbThis=new fDatabase('mysql',$globDatabaseName,$globDbUsername,$globDbPassword,$globDbServer);
if ($boolShowMem) DbugMem("having just created dbThis");
$strSql="SELECT l.LanguageId, Text ".
"FROM entLanguages AS l, entTexts AS t WHERE TextNum=LanguageDescTextNum AND t.LanguageId=1 ".
"ORDER BY l.LanguageId ASC";
$stmtThis=$dbThis->prepare($strSql);
if ($boolShowMem) DbugMem("having just created stmtThis");
if ($boolShowMem) DbugMem("before creating rsThis");
$rsThis=$dbThis->query($stmtThis);
if ($boolShowMem) DbugMem("having just created rsThis");
foreach ($rsThis as $row){
$strLanguages.=HtmlFix($row["Text"])."||";
$strLanguageIds.=$row["LanguageId"]."||";
}
if ($boolShowMem) DbugMem("after languages");
unset($row);
if ($boolShowMem) DbugMem("after unset(".chr(36)."row)");
unset($rsThis);
if ($boolShowMem) DbugMem("after unset(".chr(36)."rsThis)");
unset($dbThis);
if ($boolShowMem) DbugMem("after unset(".chr(36)."dbThis)");
(the value returned by DbugMem() is just what memory_get_usage() returns)
And the output is:
Memory before languages: 494kB (506832 bytes)
Memory having just created dbThis: 1055kB (1080716 bytes)
Memory having just created stmtThis: 1395kB (1428916 bytes)
Memory before creating rsThis: 1395kB (1428900 bytes)
Memory having just created rsThis: 50646kB (51861876 bytes)
Memory after languages: 50646kB (51862048 bytes)
Memory after unset($row): 50646kB (51861988 bytes)
Memory after unset($rsThis): 50643kB (51858596 bytes)
Memory after unset($dbThis): 50643kB (51858528 bytes)
...I simply don't get the memory back, and if I do nested queries, PHP runs out of its default 128MB of allocated RAM.
Sorry, forgot to unset the statement object in that last snippet, but unsetting it doesn't make a difference, new output:
Memory before languages: 495kB (507804 bytes)
Memory having just created dbThis: 1056kB (1081684 bytes)
Memory having just created stmtThis: 1396kB (1429884 bytes)
Memory before creating rsThis: 1396kB (1429868 bytes)
Memory having just created rsThis: 50647kB (51862856 bytes)
Memory after languages: 50647kB (51863028 bytes)
Memory after unset($row): 50647kB (51862968 bytes)
Memory after unset($rsThis): 50644kB (51859576 bytes)
Memory after unset($stmtThis): 50644kB (51859508 bytes)
Memory after unset($dbThis): 50643kB (51859440 bytes)
So this seems to be somewhat of an issue with the mysqli extension. When grabbing values in a non-buffered manner, it allocates enough ram to work with the maximum data that can fit in a column. This gets big for MEDIUMTEXT, MEDIUMBLOB, LONGTEXT and LONGBLOB.
In r895 I've committed a fix that will cause it to only allocate what is needed to the fit the content for normal query()
calls, but then unbufferedQuery()
will continue to use the current non-stored functionality. Another option is to explicitly call fStatement::__destruct(). This will free the memory.
Thanks for taking the time to dive into this. Can you tell me the output of the following PHP?
fCore::expose($dbThis->getExtension());
With this info I should be able to get to the bottom of it.
That returns "mysqli".
I can confirm that if I do
$stmtThis->__destruct();
before doing unset($stmtThis) - all RAM is returned, so that's a feasible workaround.
But it seems to my noob mind unset() should be enough?
As I mentioned in my previous content, you don't need to do anything as of r895. If you are using an older version, or ->unbufferedQuery()
and want to free memory before the end of the script, call ->__destruct()
.
unset()
removes a variable reference to a value, but __destruct()
will only be called when all references have been destroyed. It seems that PHP thinks there is still some references to your fStatement object.
Sorry for being so stubborn :-)
Yes, it *might* be a bug in PHP that makes the following page eat all this RAM:
<?php
require_once($_SERVER['DOCUMENT_ROOT'].'/../inc/flourish/init.php');
$globDbServer="*****************";
$globDbUsername="*****************";
$globDbPassword="*****************";
$globDatabaseName="*****************";
$boolCallDestructor=true;
function Dbug($strOut){
echo "<p class=\\"Dbug\\">".$strOut."</p>\\n";
}
function DbugMem(){
$strText="";
if (func_num_args()==1){
$strText=" ".trim(func_get_arg(0)," "); // Get first argument from list
}
$numBytes=memory_get_usage();
$numKbytes=intval($numBytes/1024);
Dbug("Memory".$strText.": <b>".$numKbytes."kB</b> (".$numBytes." bytes)");
}
DbugMem("before languages");
$dbThis=new fDatabase('mysql',$globDatabaseName,$globDbUsername,$globDbPassword,$globDbServer);
DbugMem("having just created dbThis");
$strSql="SELECT l.LanguageId, Text ".
"FROM entLanguages AS l, entTexts AS t WHERE TextNum=LanguageDescTextNum AND t.LanguageId=1 ".
"ORDER BY l.LanguageId ASC";
$stmtThis=$dbThis->prepare($strSql);
DbugMem("having just created stmtThis");
$rsThis=$dbThis->query($stmtThis);
DbugMem("having just created rsThis");
foreach ($rsThis as $row){
}
DbugMem("after languages");
unset($row);
DbugMem("after unset(".chr(36)."row)");
unset($rsThis);
DbugMem("after unset(".chr(36)."rsThis)");
if ($boolCallDestructor){
$stmtThis->__destruct();
DbugMem("after ".chr(36)."stmtThis->_destruct()");
}
unset($stmtThis);
DbugMem("after unset(".chr(36)."stmtThis)");
unset($dbThis);
DbugMem("after unset(".chr(36)."dbThis)");
?>
(this is the complete page, no other includes, all I've done before posting it here was removing database credentials)
(set $boolCallDestructor to false to see the memory not being released)
I fail to see what other references to these objects PHP can see, isn't it possible that this is indeed a bug in Flourish, potentially related to MEDIUMTEXT fields?
Did you upgraded Flourish to r896 yet?
My results are:
Without explicit __destruct()
call:
Memory before languages: <b>6802kB</b> (6965652 bytes)
Memory having just created stmtThis: <b>6804kB</b> (6967364 bytes)
Memory having just created rsThis: <b>6818kB</b> (6981748 bytes)
Memory after languages: <b>6818kB</b> (6981748 bytes)
Memory after unset($row): <b>6818kB</b> (6981748 bytes)
Memory after unset($rsThis): <b>6816kB</b> (6979628 bytes)
Memory after unset($stmtThis): <b>6815kB</b> (6979560 bytes)
With explicit __destruct()
call:
Memory before languages: <b>6802kB</b> (6965652 bytes)
Memory having just created stmtThis: <b>6804kB</b> (6967364 bytes)
Memory having just created rsThis: <b>6818kB</b> (6981748 bytes)
Memory after languages: <b>6818kB</b> (6981748 bytes)
Memory after unset($row): <b>6818kB</b> (6981748 bytes)
Memory after unset($rsThis): <b>6816kB</b> (6979628 bytes)
Memory after $stmtThis->_destruct(): <b>6815kB</b> (6978936 bytes)
Memory after unset($stmtThis): <b>6815kB</b> (6978868 bytes)
With five iterations of creating a statement and then :
Memory before languages: <b>6803kB</b> (6966964 bytes)
Memory having just created stmtThis: <b>6805kB</b> (6969004 bytes)
Memory having just created rsThis: <b>6818kB</b> (6982464 bytes)
Memory after languages: <b>6818kB</b> (6982464 bytes)
Memory after unset($row): <b>6818kB</b> (6982464 bytes)
Memory after unset($rsThis): <b>6816kB</b> (6980344 bytes)
Memory after unset($stmtThis): <b>6816kB</b> (6980276 bytes)
Memory having just created stmtThis: <b>6817kB</b> (6980700 bytes)
Memory having just created rsThis: <b>6818kB</b> (6982480 bytes)
Memory after languages: <b>6818kB</b> (6982480 bytes)
Memory after unset($row): <b>6818kB</b> (6982480 bytes)
Memory after unset($rsThis): <b>6816kB</b> (6980360 bytes)
Memory after unset($stmtThis): <b>6816kB</b> (6980292 bytes)
Memory having just created stmtThis: <b>6816kB</b> (6980556 bytes)
Memory having just created rsThis: <b>6818kB</b> (6982460 bytes)
Memory after languages: <b>6818kB</b> (6982500 bytes)
Memory after unset($row): <b>6818kB</b> (6982500 bytes)
Memory after unset($rsThis): <b>6816kB</b> (6980352 bytes)
Memory after unset($stmtThis): <b>6816kB</b> (6980284 bytes)
Memory having just created stmtThis: <b>6817kB</b> (6980700 bytes)
Memory having just created rsThis: <b>6818kB</b> (6982460 bytes)
Memory after languages: <b>6818kB</b> (6982548 bytes)
Memory after unset($row): <b>6818kB</b> (6982548 bytes)
Memory after unset($rsThis): <b>6816kB</b> (6980348 bytes)
Memory after unset($stmtThis): <b>6816kB</b> (6980280 bytes)
Memory having just created stmtThis: <b>6817kB</b> (6980704 bytes)
Memory having just created rsThis: <b>6818kB</b> (6982476 bytes)
Memory after languages: <b>6818kB</b> (6982476 bytes)
Memory after unset($row): <b>6818kB</b> (6982476 bytes)
Memory after unset($rsThis): <b>6816kB</b> (6980360 bytes)
Memory after unset($stmtThis): <b>6816kB</b> (6980292 bytes)
I'm not seeing a memory leak. If there was one, the memory usage should go up with each iteration. Also, if you change your profiling code to use get_memory_usage(TRUE)
I see no changes whatsoever.
In terms of the slight changes in emalloced memory, this is normal for PHP. Try the following code to see an example:
echo memory_get_usage() . ' bytes<br />';
$vals = array();
for ($i=0; $i < 100; $i++) {
$vals[] = new stdClass;
}
echo memory_get_usage() . ' bytes<br />';
unset($vals);
echo memory_get_usage() . ' bytes<br />';
I'm fairly sure I'm properly freeing the the result resources associated with mysqli.
Hi again!
o/ Just upgraded from r891 to r898, and now it's not only releasing the memory, it's hoggin 1300K instead of 50M! :-)
Thanks so much, we'll now be using Flourish at work, and we'll be saving lots of time. I'm as happy as a pig on xmas eve.
Cheers,
Per.