How to solve Magento CE 1.7 indexing timing issues with several cron jobs?

Hello Everybody,

I am using Magento CE and data is imported from 3rd party through Cron jobs with Custom scripts. Website catalog is more than 30K. The issue I am facing is with the indexing. As I need to run the Crons on daily basis and one(inventory update) on hourly basis, due to slow indexing, it is not possible to run the inventory update script on hourly basis as it takes around 25-30 minutes for the indexing. Same is the case with other cron scripts so I have to run one script per day. My requirement is to run all the cron scripts on daily basis. I have set the indexing mode to Manual mode because otherwise updating 30K rows takes forever. I have tried few extensions to decrease the indexing time but in vain. Is there anything that can be done to decrease the indexing time significantly or is it possible that upgrading to Enterprise version will help solving this issue. This is critical issue as I need to sync the data real time with the 3rd party supplier but due to this indexing issue I cannot run all crons on daily basis resulting in data inconsistency with the 3rd party supplier data. Suggestions and help will be appreciated. Thanks in advance.

Kind regards
Sukhwant

Hi Sukhwant,

How 's your server configuration? CPU, RAM and your caching solution?

Hi David,

Thanks. Here is my server configuration:
Linode 2GB
2 GB RAM
8 CPU (4x priority)
4 TB Transfer

I am using Magento default caching along with a Full page cache paid extension.

I hope this information will help.

Kind regards
Sukhwant

With 30k SKUs, your database is going to start getting large. I recommend you upgrade to a 4G linode instance ( I use them all the time, they're great! ), and spend some time feeding MySQL most of the extra memory. There's a simple checking script ( https://launchpad.net/mysql-tuning-primer ) called tuning-primer.sh which will get you into the right ballpark. Basically, once there are enough buffers ( especially innodb ) available, then very little load is put on the disks at all. Also, ensure the log tables aren't getting too large: that can be a significant bottleneck too. Also, what Magento calls reindexing is actually more than that... there's plenty of PHP-based processing in there too. So ensure you're making plenty of memory available to that ( Mage recommends min 384MB?? ), and use an opcode cacher. I'm a nginx convert, and APC seems to be best with that, but eAccelerator seems best for apache based systems.

To be honest, your import scripts should be managing the indexing method... it's simple to programattically switch to manual for the duration (: