Server crashing, slow indexing

The Problem

oxbowbooks.com was running three Magento stores using a top end cloud hosting plan from another host. They found that just reindexing one Magento store would make all the sites very slow, and trying to reindex two stores would cause the cloud environment to crash. This was a cloud environment that included 12 cores and lots of memory.

They found reindexing would take hours in Magento, and their MySQL databases had tables with millions of rows.

What LexiConn Provided

We provided them with a managed dedicated server, utilizing hardware RAID-10 with four SATA II hard drives with write back cache enabled (protected by a battery backup unit). The server had 16 GB of RAM, with a high end 8 core CPU.

We tuned both MySQL and APC for proper caching and fast processing.

We found improper crons set up for log rotations, as well as a bloated URL rewrite table. We set up proper cron execution, enabled log rotations, and cleaned the url rewrite table prior to our indexing of Magento.

The Result

We were able to decrease the time it took to reindex the Magento database from over an hour to under 6 minutes for each store. They could now reindex all 3 stores at the same time and have it all completed in under 10 minutes, with no impact on the website's performance.

Web page load times decreased, resulting in much faster page loads throughout each site. After we cleaned the URL rewrite tables, we were able to get the tables back down to tens of thousands of rows instead of millions of rows. Proper log rotation resulted in reducing the database sizes considerably.

Overall, we helped this ecommerce merchant solve their issues with slow page loads, and an inability to reindex their stores without slowing down the sites or crashing the server. We also solved their bloated database issues that were impacting performance.