Out of memory & ini_set('memory_limit', '...')

Topics: Developer Forum, Project Management Forum, User Forum
Apr 23, 2009 at 7:53 AM
Edited Apr 23, 2009 at 10:20 AM
Hello. Very strange situation is happened to me. Generating Excel2007/5 or PDF files ends with 'out of memory' reason. Logs are below. It seems that script can't allocate more then 10MB. It works on my local computer but it got these errors on hosting.

Fatal error: Out of memory (allocated 9961472) (tried to allocate 196608 bytes) in /www/htdocs/lib/PHPExcel/Style/Border.php on line 96
Fatal error: Out of memory (allocated 9961472) (tried to allocate 69 bytes) in /www/htdocs/lib/PHPExcel/Style/Borders.php on line 420
Fatal error: Out of memory (allocated 10223616) (tried to allocate 12 bytes) in /www/htdocs/lib/PHPExcel/Cell.php on line 448

I have some limits on hosting. one of them is memory usage. it is set to 32MB. As I see - limit is set to 32 M, but script breaks, allocating about 10M of mem.

echo ini_get('memory_limit')."; "; returns '-1'. I used ini_set('memory_limit', '25M'); also and it gives no results. 
p.s. lines like "zend_mm_heap corrupted" appears in apache error log file .

Resource limits for class sub-user: cputime 20 secs filesize infinity kB datasize 32768 kB stacksize 8192 kB coredumpsize 0 kB memoryuse 32768 kB memorylocked 8192 kB maxprocesses 16 openfiles 24 sbsize infinity bytes vmemoryuse 32768 kB

Can you advice me something?
Developer
Apr 24, 2009 at 10:15 PM
How much memory does your script use on your local server? Not sure what is the problem with that host, but even 32M might not suffice.

Anyway, will try to commit memory patch, hopefully over the next couple of days. Let's see if it helps.

Apr 27, 2009 at 6:52 AM
you are right, 32M in different situations may not be enough.
On hosting I have limits 32M and 128M on two accounts.

I run script that collects data from mysql and generates reports in excel2007, excel5 or pdf (it depends on user's preferences).
in web form i choose 3 tables and set range of reports from 07/2008 to 02/2009 (as example). Excel2007 file generates successfully.Peak memory usage: 9.25 MB.
then i change range to: 06/2008-02/2009 and it returns:
Fatal error: Uncaught exception 'Exception' with message 'Could not close zip file /...lib/phpxlLEUi52.' in /...lib/PHPExcel/Writer/Excel2007.php:401 Stack trace: #0 /...lib/common.lib.php(4144): PHPExcel_Writer_Excel2007->save('php://output') #1 /...lib/ajax_chain.php(392): site->getExcelSheetByParams('...', Array, '06/2008', '02/2009', 'excel2007') #2 {main} thrown in /...lib/PHPExcel/Writer/Excel2007.php on line 401

changing range to 11/2007-02/2009 (for example) it returns:
Fatal error: Out of memory (allocated 10223616) (tried to allocate 77 bytes) in /...lib/PHPExcel/Calculation.php on line 202

although I tested it on my laptop and it results: Peak memory usage: 109.5 MB on the same conditions. and it's very strange. scripts are identical but memory usage in peak is different in almost 10 times.

I compare sizes of generated xl\worksheets\sheet1.xml files (to be sure that sizes are almost same).
07/2008-02/2009 version was 13 348 bytes length, 11/2007-02/2009 was 17 443 bytes length.


anyway, according to my hosting, I decided to make some tests on it. I run script that adds 100 bytes to an empty variable in cycle for 30 thousand times:
<?php
> echo ini_get('memory_limit')."; ";
> ini_set('memory_limit', '30M');
> echo ini_get('memory_limit')."<br>";
> 
> $a="0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789";
>    
> // 100 bytes
> $b="";
> for ($i=0; $i<40000; $i++)
>   $b .= $a;
> 
> echo (strlen($b)/1024) . " KB";
> echo "<br>Peak memory usage: " . (memory_get_peak_usage(true) / 1024 ) . 
> " KB";
> ?>
results:
> -1; 30M
> 2929.6875 KB
> Peak memory usage: 3328 KB


I added 10k elements more:  for ($i=0; $i<40000; $i++)
> -1; 30M
> *Fatal error*: Out of memory (allocated 4194304) (tried to allocate 
> 3932201 bytes) in 
> */...test/memory.php* on line *9*
and on my laptop last test gives expected result:
128M; 30M
3906.25 KB
Peak memory usage: 4352 KB


what can be wrong? why having limit of 32MB test-script results out of memory error?


Developer
Apr 27, 2009 at 1:28 PM
Edited Apr 27, 2009 at 1:29 PM
You could be hitting an Apache memory limit rather than a PHP memory limit. I think what you see for memory_limit using phpinfo() or ini_get('memory_limit') is not reliable. I have seen similar things on some shared hosts too.

Found a discussion giving some insight:
http://drupal.org/node/371789

May 13, 2009 at 5:28 AM
Edited May 13, 2009 at 5:30 AM

I run some tests under 1.6.7 version with same input data.

here are results of memory_get_peak_usage():

1.6.7
PDF - 38.75M
XLS - 30.75M
XLSx- 30.25M

1.6.6
PDF - 45.50M
XLS - 30.25M
XLSx- 29.75M

 

It looks like mem usage is similar in 1.6.6 and 1.6.7 versions (except pdf)

koyama, is there any success with memory patch?

Developer
May 13, 2009 at 11:54 AM

It looks like mem usage is similar in 1.6.6 and 1.6.7 versions (except pdf)

koyama, is there any success with memory patch?

Correct, PHPExcel 1.6.6 and 1.6.7 are similar. But, if you use the latest source code you should see improvements if you are using styles.

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

 

May 13, 2009 at 12:14 PM

do you mean improvements using styles with memory or with visual things?

Developer
May 13, 2009 at 12:27 PM
do you mean improvements using styles with memory or with visual things?

I mean improvements with memory and speed. Some tests show that peak memory usage typically drops from 100 MB to 20 MB, execution time from 60 seconds to 5 seconds. Depending on how big workbook you have and how many cells that are styled.

There is some overhead of some 8 MB just for the classes to load, this stays the same, however.

May 14, 2009 at 10:34 AM

can you provide me with direct link on that patch? or probably it will be better to download the whole source code?

Developer
May 14, 2009 at 1:46 PM

Yes, best thing is to download the latest source code. Many classes are affected.
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

 

May 15, 2009 at 10:41 AM

ok, thanks. I'll try to check it on this weekend.

May 19, 2009 at 5:55 AM

Well, I used 27561 build. source contains about 600 cells.

here are results of test (i think, it can be useful for somebody):

VERSION          FILE_TYPE          MEM_PEAK          EXECUTION_TIME

1.6.7               EXCEL2007           16,5 Mb                2,4 sec
1.6.7               EXCEL2003           16,5 Mb                1,8 sec
1.6.7               PDF                     31,75 Mb               5,8 sec

27561             EXCEL2007           12,25 Mb               2,9 sec
27561             EXCEL2003           12,75 Mb               2,8 sec
27561             PDF                      26,5  Mb               7,4 sec

 

memory usage dropped, but execution time isn`t. koyama, what can be wrong?

Developer
May 19, 2009 at 6:10 AM

How much styling do you have? And what method have you used for styling? The fast way of styling cells in the forthcoming PHPExcel 1.7.0 will be like this:

$objWorksheet->getStyle('A1:F43')->applyFromArray($styleArray);

or equivalently like this:

$objWorksheet->duplicateStyleArray($styleArray, 'A1:F43');

rather than looping through the cells one by one and styling them individually. Perhaps that is why you are not seeing any gain in speed?

May 19, 2009 at 8:04 AM

I'm also having the same problem with out of memory.

For styling I use

$sharedStyle = new PHPExcel_Style();

$sharedStyle->applyFromArray(
            array('alignment'=>array(
                                'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER,
                                'wrap'=>true)));

$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle, 'A2:L100');

Is this okay or should I use the solution koyama recommends?

Developer
May 19, 2009 at 8:13 AM
Edited May 19, 2009 at 9:16 AM

sjansen4, do like this:

$s = array(
    'alignment' => array(
        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
        'wrap' => true,
    ),
);
$objPHPExcel->getActiveSheet()->getStyle('A2:L100')->applyFromArray($s);

This is the preferred technique for styling. But you will need to use latest source code:
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

EDIT: fixed typo in variable name.

May 19, 2009 at 8:47 AM

Okay, I'll do it like you recommend. Thanks for the reply

May 27, 2009 at 9:45 AM
Edited May 27, 2009 at 9:51 AM

There are some tables per sheet. The following executes for every table.
There are many conditions and different duplicateStyleArray() methods, but these are just as example what is used.
A1:F43 is used as example

 

foreach ($currentTableFirstRow as $dbField => $dbFieldHeader)
{
    $objPHPExcel->getActiveSheet()->duplicateStyleArray(
        array('alignment'     => array('indent'  => 1,  'horizontal'  =>     PHPExcel_Style_Alignment::HORIZONTAL_RIGHT),
              'font'         => array('bold'     => true),
              'numberformat' => array('code'    => '#,##0.000')         )
        ,    "A1:F43"
    );
}

This executes for every sheet. There is currently one sheet per workbook (file)


foreach ($tableProperties['width'] as $param => $value)
    $objPHPExcel->getActiveSheet()->getColumnDimension($param)->setWidth(($value+0.83));        //+0.83 == offset

for ($a=x; $a<=y; $a++)
    $objPHPExcel->getActiveSheet()->getRowDimension($a+$prevRowsCount)->setRowHeight($tableProperties['height']+0.08);        //+0.08 == offset


$objPHPExcel->getActiveSheet()->getStyle($cb1)->applyFromArray(
        array('font'    => array(
                    'bold'    => true,
                    'name'  => 'Tahoma',
                    'size'  =>  10,
                    'color' => array('argb' => PHPExcel_Style_Color::COLOR_WHITE )
        ),
        'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'indent'     => 1,
                'wrap'         => true
        ),
        'fill'        => array(
                'type'  => PHPExcel_Style_Fill::FILL_SOLID,
                'startcolor' => array('argb' => $fillColor)
        ) )
);


$objPHPExcel->getActiveSheet()->getStyle($cb2)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle($cb2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

$objPHPExcel->getActiveSheet()->getStyle("A1")->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle("A1")->getBorders()->getLeft()->getColor()->setARGB('00333399');


$borderStyle = PHPExcel_Style_Border::BORDER_HAIR;
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
    array(
        'borders' => array(
            'top'     => array('style' => $borderStyle,     'color' => array('argb' => '00808080' )),
            'right'   => array('style' => $borderStyle,     'color' => array('argb' => '00808080' )),
            'bottom'  => array('style' => $borderStyle,     'color' => array('argb' => '00808080' )),
            'left'    => array('style' => $borderStyle,     'color' => array('argb' => '00808080' ))
        )
    ),    "A1:F43"
);

 

 

--editedwtf? I can't set style properties to code that I pasted :-(

 

--edited x2
highlighted source code: http://pastebin.com/f527c6e

Developer
May 28, 2009 at 4:19 AM

Your script looks good. You are using the fast duplicateStyleArray() method. So I don't think you can optimize styling much more.

It is possible you can cut down execution time if you use setValueExplicit() rather than setValue(). This can typically cut down execution time by 30%. I suggest trying that if you are not already doing so. Meanwhile we must try to improve the classes further. There are still places where we can optimize.

 

Coordinator
May 29, 2009 at 4:36 PM

Is your workbook performing any calculations, particularly formulae that reference cells across worksheets? I've noticed that this is particularly memory intensive

Jun 29, 2009 at 5:50 AM

sorry for long response.

there are some calculations performing in the workbook. but they don't reference cells across the worksheets. they're are reference cells only across one worksheet. see example:

C1 = B1/B10;
C2 = B2/B10;
C3 = B3/B10;
...

I commented the line in the code that performs such calculations. and I got:

peak memory usage dropped down to 11 Mbytes (it was 11,25 Mb with performing that calculations);
execution time dropped down to 2,6 seconds  (against 2,7 seconds with performing that calculations).

Anyway that execution time is acceptable.

Thanks for the great script!