I manage a Windows server that is running Drupal and I recently had trouble with extremely slow database writes. This problem was the most pronounced when I loaded the Modules page (admin/build/modules). The Modules page load took more than 1 minute. At the same time the database was locked preventing any other page from loading. After looking at the database process list I could see numerous update queries on the menu_links and menu_router tables.
There is likely a combination of factors that caused the problem, and there are probably better solutions than I found. Here is the background on the problem.
- The server is running Wampserver 2.0, which includes Apache, MySQL and PHP.
- I am running Drupal 6 with a mostly typical group of modules. The admin_menu module is part of the group. I will mention this again later.
- For no real reason the MySQL storage engine was InnoDB for most of the tables, but a few were MyISAM.
- I had tried to change the my.ini configuration file, but didn't see any improvement. Of course that wasn't really surprising because I didn't know enough about MySQL configuration to know what needed to be changed.
Eventually I tried changing the MySQL storage engine to MyISAM for the menu tables and immediately noticed a huge improvement. I know InnoDB offers more advanced features such as transactions, but Drupal currently doesn't use those features so I changed all of the tables to use MyISAM.
Another factor that probably exacerbates the issue is related to the admin_menu module. The author of the admin_menu module has posted about his frustration with the Drupal menu system (http://drupal.org/node/550254). In that post he explained that admin_menu adds significant load to the menu rebuild process due to issues with the Drupal core menu system.
The result in my case was that simply changing the storage engine caused the page load time for the Modules page to change from about 90 seconds to less than 10 seconds.
