Memory limit exceeded while preloading attribute options

[Nierjesh]

Hi Quafzi

I just downloaded the code and update accordingly but now search stps working and generating following error." Allowed memory size of 1073741824 bytes exhausted (tried to allocate 32 bytes) in /var/www/html/raptor_supplies/lib/Zend/Db/Statement/Pdo.php on line 290"
I am using Magento 1.9.1
I did some R&D and found that in "Table.php" under _preloadOptions($storeId) function $options = $collection->getData(); is not working.
Please suggest .Did I miss something???

[quafzi]

@Nierjesh So your memory_limit was exceeded. Would you please let me know the size (e.g. number of rows) of your eav/entity_attribute_option_collection table?

[Nierjesh]

@quafzi in eav_attribute_option table there are 549645 records.

[Nierjesh]

@erfanimani ???.Is it not correct???

[erfanimani]

I'm not sure - it's interesting. Seems like php memory limit is set to 1GB, which is more than enough. It's a big table, but nothing it shouldn't be able to handle. Seems like all the attribute options are preoloaded to avoid a lot of SQL queries later on (I could be wrong). Seems like a good trade-off between processing vs memory, except in your (fairly extreme) case - I haven't come across any store with more than half a million eav attribute options before..

[Nierjesh]

@erfanimani I have store which is having 10 Lacs+ products and in layered navigation it is taking around 10 sec without caching to load the page so when i googled then I found this but unfortunately it is not supporting .can u suggest anything???

[quafzi]

Phew, that's a damn high number. What kind of shop is that? :)

So what could you do:

• disable that particular feature (so you wouldn't get the expected - and probably needed - performance boost)
• try to preload the attribute options in a cron job and store them somewhere else to share them across requests
• @erfanimani any other suggestions?

[Nierjesh]

@quafzi @erfanimani : I was thinking to use the sphinx to load the eav_attribute_option table data but how it will integrate with magento that I dont know.does this idea make sense??or I am going into wrong direction.

[erfanimani]

I'm not sure.. Like Thomas said, I would probably disable this particular feature, and do the usual checks, make sure the whole store is optimized (no unnecessary load calls, flat tables are on, etc). I would also heavily make use of Lesti FPC and put some decent hardware behind it (even though it's frowned upon to solely rely on FPC).

What do you mean with 10 Lacs+ ?

[Nierjesh]

@erfanimani I am using Lesti FPC for all actions except layered navigation,I was trying to get the speed of less than 5 seconds in layered navigation without using Lesti FPC.
I just created one sphinx instance and tried to pull the all data from sphinx against layered navigation but unfortunately i could not see the good outcome.
I mean in my store there are 10 lacs 54 thousand 686 products.