Uncommon but not unimportant MySQL optimizations for Drupal

Auto-Increment locking mode

https://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

In most cases (if not all for Drupal), it is not necessary for auto-increment values to be consecutive. You can settle for monotonically increasing. By using the “interleaved” lock mode (2), greater scalability can be gained because of reduction in locks.

innodb_autoinc_lock_mode = 2

This mode will only work in conjunction with replication, iff the binlog format is set to eiter "row" or "mixed". "statement"-based replication will not work with "interleaved" lock mode.

Isolation level

http://dev.mysql.com/doc/refman/5.1/en/dynindex-isolevel.html

The default isolation level in MySQL is REPEATABLE-READ. The isolation level READ-COMMITED is a bit more relax wrt locking and thus scales better. There's nothing in Drupal that requires REPEATABLE-READ and the default level for Postgres is also READ-COMMITTED. The current trend for Drupal also seems to be moving towards the READ-COMMITED isolation level

transaction-isolation = READ-COMMITTED