Hi Folks,

I had try to use cell caching , so i need help , cause i dont know do i make mistake on my side or this class has some problem.

Here i give short sample of my script :

<?php
require_once('phpexcel/Classes/PHPExcel.php') ;
set_time_limit(0) ;

$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp; //PHPExcel_CachedObjectStorageFactory::cache_to_discISAM ; //
$cacheSettings = array( 'memoryCacheSize' => '32MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("mega_list.xlsx"); // my file has 10 columns and 25200 rows

?>

I have a box with 2 GB ram only , i have free for use 1.4 GB. I will use this so that i use so low memory as possible

So i have following problems :

1.) What Caching method should i use (speed is not so importaint)

2.) What means "memoryCacheSize"=>"32MB" ? I have understand if memory usage is greater than "32MB" rest will be stored in file on disk ? If this is right i have problem that the script try to use all my memory what i have

3.) After my script has finished, it would be aprox. 350MB RAM memory not freed , it goes to NIRVANA somewhere in APACHE2, even after 1 Hour the memory is not freed. I must restart my apache to get this memory free , if my customers start this script few times , i must reboot my box

What could be here wrong ?

misic wrote:

1.) What Caching method should i use (speed is not so importaint)

APC, Memcache or  WinCache offer the best reduction in memory usage, but are also the slowest performance. DiskIsam and PHPTemp offer the next best memory performance.

misic wrote:

2.) What means "memoryCacheSize"=>"32MB" ? I have understand if memory usage is greater than "32MB" rest will be stored in file on disk ? If this is right i have problem that the script try to use all my memory what i have

 Your understanding is basically correct. PHPTemp will use up to 32MB of memory; and if trying to store more information than will fit in 32MB of memory, then it will use disk storage for the rest. Note that all cache mechanisms retain an "in memory" index of cells, and it is the cell objects that are cached. When using in memory cells, my rule of thumb is 1k memory/cell (about 1.6k when using 64-bit PHP), doubled if writing the workbook rather than just reading... actual values will depend on the value of the cell, and the styles used. When using DiskISAM or PHPTemp, this 1k is reduced to about 320 bytes/cell to maintain the index.

misic wrote:

3.) After my script has finished, it would be aprox. 350MB RAM memory not freed , it goes to NIRVANA somewhere in APACHE2, even after 1 Hour the memory is not freed. I must restart my apache to get this memory free , if my customers start this script few times , i must reboot my box

 After the script has finished executing, PHP itself clears all the memory that was used by the script. That is not a function of PHPExcel, and I'm not aware that PHP itself has any bugs regarding the garbage collection on termination of the script (although it has known problems with garbage collection and cyclic references while the script is still running). If memory isn't being cleared by PHP/Apache, then it is a much more serious problem. If anything, this should be raised with the Apache or PHP development teams.

3.) You are right for garbage collection , must see to fix the settings via ini_set  before my script runs , and then when the next session started should clear garbage collection. Must test this and write my result in forum , for other customers if they have such problem too.

misic wrote: 

2.) What means "memoryCacheSize"=>"32MB" ? I have understand if memory usage is greater than "32MB" rest will be stored in file on disk ? If this is right i have problem that the script try to use all my memory what i have

 Your understanding is basically correct. PHPTemp will use up to 32MB of memory; and if trying to store more information than will fit in 32MB of memory, then it will use disk storage for the rest. Note that all cache mechanisms retain an "in memory" index of cells, and it is the cell objects that are cached. When using in memory cells, my rule of thumb is 1k memory/cell (about 1.6k when using 64-bit PHP), doubled if writing the workbook rather than just reading... actual values will depend on the value of the cell, and the styles used. When using DiskISAM or PHPTemp, this 1k is reduced to about 320 bytes/cell to maintain the index.

I have 64 Bit box , so what would be the best solution for saving memory ? Is possible to get it so that my script use only 32MB (or 64MB) ram and rest goes to cache file ?

misic wrote:

I have 64 Bit box , so what would be the best solution for saving memory ? Is possible to get it so that my script use only 32MB (or 64MB) ram and rest goes to cache file ?

 The only solution that I can think of to guarantee that a large workbook would run in 32MB or 64MB of memory would be to write a custom cache that would also store the cell index outside of the PHP memory. I did try this when I introduced cell caching, but the performance really was impossible to work with, but it might be practical to use (for example) DiskISAM or PHPTemp for the actual cell object storage, and APC or MemCache for storing the cell index (with entries for each individual cell rather than stored as an array). This would be a case of a very specific caching method, with very limit usage generally, but could be the best way to optimise memory usage for a very specific instance.

While cells themselves are cached, there are still shared string and style arrays maintained in memory. For optimum memory performance, these should also be cached.

Reducing memory is a situation where I have to provide a series of general options that will provide solutions to a majority of PHPExcel users, but every solution provides a trade off between memory usage and pereformance. At the moment, I'm working on the performance of the Writers, trying to increase speed and reduce their memory usage... but there are no quick solutions to the memory problems.

Maybe cuould you put something like this in future releases....

Cause phpExcel is very memory "hungry" and with more user at the same time on 1 box can cause trouble if you have limited resources