CHAPTER 23 ■ OPTIMIZING DRUPAL

Table_locks_immediate is the number of times that a table lock was acquired immediately, and Table_locks_waited is the number of times a table lock could not be acquired immediately and a wait was needed. If the Table_locks_waited value is high, and you are having performance problems, you may want to split up large tables; for example, you might create a dedicated cache table for a custom module or consider ways to reduce the sizes or the frequency of the table lock commands. One way to reduce table sizes for some tables, such as the cache_*, watchdog, and accesslog tables, is by reducing the lifetime of the data. This can be done within the Drupal administrative interface. Also, making sure cron is being run as often as once an hour will keep these tables pruned.

Because Drupal can be used in many different ways, it is impossible to give an across-the-board recommendation as to which tables should use which engine. However, in general, good candidates for conversion to InnoDB are the cache, watchdog, sessions, and accesslog tables. Fortunately, the conversion to InnoDB is very simple:

ALTER TABLE accesslog TYPE='InnoDB';

Of course, this conversion should be done when the site is offline and your data has been backed up, and you should be informed about the different characteristics of InnoDB tables.

For MySQL performance tuning, check out the performance tuning script at www.day32.com/MySQL/, which provides suggestions for tuning MySQL server variables.

Summary

In this chapter, you learned the following:

•How to troubleshoot performance bottlenecks.

•How to optimize a web server.

•How to optimize a database.

•Drupal-specific optimizations.

•Possible multiserver architectures.

523