In my work MySQL plays a big part of the dynamic web data chain, especially for many PHP based sites (like Drupal). Here are tips I've collected along the way and of course YMMV (your mileage may vary).

MySQL Tuning notes

  • Disable or remove any extensions (or modules) not being used.
  • Separate the database onto its own server (Master), and try to have a (Slave) copy as a backup or for redundancy.
  • Master-Master configurations are not easy.
  • Use EXPLAIN to dig into queries and find where optimizations can be made for performance.
  • Profile MySQL during development using the SET PROFILING flag. Enable profiling, query information_schema table to review results. Disable profiling when done.
  • Enable and tune the Query cache.
  • Enable Table cache.
  • Enable (or raise) Key buffer.
  • Enable Slow Query Log to find bottleneck queries.
  • Use MyISAM table types for fast reads, but stick with InnoDB for transactional data.

Tools, Utilities, and Resources

mysqltuner - http://blog.mysqltuner.com -

Useful Commands

EXPLAIN <QUERY>

SHOW FULL PROCESS LIST;
SHOW PROCESS LIST;

Config changes

Here is some examples of tuned MySQL settings for a Drupal site (taken from random places). InnoDB table types are recommended over MyISAM. Consider looking at mysqlnd when using PHP 5.3 on your web server(s); it's a replacement for libmysql.
key_buffer = 48M
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 4M
table_cache = 750
#table_cache_size = 1000 # Newer MySQL can handle 1000+

# Separate disk spindle
datadir = /data/mysql

innodb-file-per-table
innodb-buffer_pool_size = 256M

long_query_time = 2

thread_stack = 128K
thread_cache_size = 64
max_allowed_packet = 16M
max_connections = 100
max_heap_table_size = 256M
tmp_table_size = 256M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M

long_query_time = 5
thread_stack = 128K
thread_cache_size = 64
#max_connections = 100 #TBD

Tools

mysqlreport - Perl shell script that displays stats.

Db tuning - shell script that reads variables from MySQL.

mtop - like top but for MySQL. Real time monitoring. Shows slow queries and locks.

mytop - also like top but for MySQL. Real time monitoring. Shows slow queries and locks.

`mysqladmin processlist` or `mysqladmin -v processlist`

0
Your rating: None

Tags: MySQL