Authors: Anil. K. Mathews, Binny K. Thomas
Owners of a meal planning website with a grocery list organizer approached us. They had a steadily growing user base from their target audience in North America. The website was built on Drupal 7 and hosts several tasty recipes created by the founders which proved to be popular with its patrons.
However, they soon began noticing that the site had begun slowing down as the number of users grew. Adding to these the site began having frequent outages. They approached several Drupal service providers who recommended a complete rewrite of the custom features of the site. Since they had already invested in the site, rewriting it would have been very uneconomical and they started looking for alternatives which led them to us.
A Million Nodes
When we received access to the site we immediately checked the custom code available on the site since Drupal was built to be scalable and not supposed to slow down. A quick review confirmed our suspicions and revealed the following problems.
- The site had more than a million nodes of a particular content type which did not seem right as it was way more than the current user was capable of generating at that time.
- The code in the custom modules contained multiple instances of expensive node_load operations within loops capable of bringing down the site when the number of users increased exponentially.
- Cron was not running correctly and there were a number of pending operations including clearing of the database.
- Almost 90 percent of the hits to the site came from countries other than North America which were not the target customer base of the site. They looked suspicious and was likely to be spam.
- Caching of the different Views instances was not enabled.
After taking into account the above problems faced by the site, we concluded that the site need not be rebuilt immediately but portions of the site need to be reworked according to Drupal recommended standards to improve performance. This would help the client continue attracting new users without affecting the existing user base.
The solution involved a two-pronged approach where configuration level changes were done immediately followed by code based optimizations as follows.
- Views caching was enabled to reduce the load on the Views engine.
- CloudFlare DNS was used to block all suspicious traffic from countries other than North America and Canada before they even reached the site.
- We replaced Poormans cron which was intended for smaller sites on shared hosting with no shell access with crontab. Poormans cron was initiated when the first users on the site accessed the site which unfortunately were spambots.
- The million nodes was a result of the logging system used to track the subscription updates when users make a financial transaction on the site . This was only useful for developer debugging with no real benefit to the site. We created a custom database table to store the information using the Drupal database API and it was added to the site as an update using hook_update. Then the existing data from those million nodes was migrated to a single custom database table. The existing nodes were deleted programmatically after migration.
- The bulk of the code level optimization effort included going through each line of custom code, formatting it to meet Drupal coding standards, removal of the node_load statements within SQL queries.
A New Breath of Life
The usage of the DNS based CDN to block all suspicious traffic brought in immediately noticeable benefits to the site with zero instances of the frequent downtime that was so severely paralyzing its operations. The code optimization led to users immediately noticing less latency when the menus were being generated improved. The removal of the seven million nodes did not produce any immediate results. However, it would have been a bottleneck when the site scaled up and would have required more maintenance and server resources. All of these changes enabled the existing site to run smoothly allowing them to focus on improving the user experience and attracting more users.