5 min read 1186 words Updated Mar 09, 2026 Created May 09, 2026
##cpus

MySQL

MySQL Performance

Reading Room

Filesystem/Disk

  • Use RAID-10 storage & put datadir on JFS - fastest all-around journaling FS for Linux - See Filesystem Performance
  • Mount datadir storage using noatime
  • Use the noop scheduler - see this. Especially true on systems with RAID controller (to avoid double-reordering problem)
  • Use LVM volume with some spare room (10-20%) on the volume group to utilize snapshots for backup purpose. WARNING - snapshots can drag performance down, use 'em and destroy ASAP.
  • Put logs (bin-logs and relay-logs) on separate disk spindles if possible, or forego them altogether.
  • By all means benchmark your system with bonnie++, ubench, mysqlbench etc before going into production - you'll have important baselines you can refer back to.

Configuration (my.cnf)

General performance improvements

  • Set table_cache size appropriately... see http://www.itslot.com/calculate_mysql_memory for a write-up, but generally 256 or higher. The default of 64 is almost always too low
  • enable slow query logging with log-slow-queries - examine tables/fields in slow queries and ensure indexes exist, or create them.
  • skip-external-locking (does not prevent internal locking, though...which is necessary)
  • skip-name-resolve
  • set thread_concurrency = 4 ##use #cpus x 2
  • Run phpmyadmin and check the status page - look for BOLD RED items and follow advice
  • Run MySQL Tuning Primer and follow the recommendations.

Table check & optimization

I run this daily via crontab to check/fix broken tables

mysqlcheck --all-databases --silent --auto-repair 2>&1 | \
mail -e -s "$(hostname -f) mysqlcheck" root

Remove the --silent if running interactively

mysqlcheck --all-databases --auto-repair

This I run too, but less often like weekly.

mysqlcheck --analyze --optimize --force --all-databases

MyISAM table specifics

  • low-priority-updates
  • Set key_buffer appropriately

InnoDB specifics

Here are some reasonable settings to use on a LAMP system with one CPU core, 512MB running apache, mysql (with InnoDB tables) and php.

table_cache = 256
thread_concurrency = 2
innodb_buffer_pool_size = 64M
innodb_log_file_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

The table-type (ENGINE) dilemma - MyISAM vs InnoDB

Choosing between MyISAM or InnoDB tables?

MySQL Backups

This section talks about how to take a backup of your mysql database(s).

See MySQL Backup Scheme...

I use either mysqldump or LVM snapshots if possible...

See here and here.

Other backup softwares for MySQL:

Root password

Here is how to change the root password from command line using mysqladmin.

mysqladmin -uroot -p password
Enter password:

If that doesn't work i.e. you don't know the old-password, you can take the alternative way. Add skip-grant-tables to the my.cnf file and restart mysqld

Now you can connect without credentials. Run these commands

use mysql;
update user set password=PASSWORD("the-new-password") where user='root';
flush privileges;

Now remove skip-grant-tables from my.cnf and restart mysqld.

Useful SQL queries & commands

Examine the table to find out what engine (MyISAM, InnoDB, etc) are used for them.

mysql> use information_schema;
mysql> SELECT count(engine),engine,table_schema FROM tables where table_schema != 'information_schema' group by engine ;
+---------------+--------+--------------+
| count(engine) | engine | table_schema |
+---------------+--------+--------------+
| 73 | InnoDB | puppet |
| 1 | MEMORY | wikidb |
| 41 | MyISAM | bpwp |
+---------------+--------+--------------+
3 rows in set (0.88 sec)

Show variables

mysql> show variables;
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
...

| version | 5.0.41-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+----------------------------------------+
230 rows in set (0.08 sec)

Show status

mysql> show status;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 331 |
| Aborted_connects | 2344 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 9987 |
...
| Threads_running | 13 |
| Uptime | 4747 |
| Uptime_since_flush_status | 4747 |
+-----------------------------------+------------+
252 rows in set (0.04 sec)

Show status subset

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 4582115 |
| Table_locks_waited | 12394 |
+-----------------------+---------+
2 rows in set (0.00 sec)

Show a table schema

mysql> desc comments;
+------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| created_at | datetime | NO | MUL | | |
| slide_id | int(11) | NO | MUL | 0 | |
| ip | varchar(13) | NO | | | |
| user_id | int(11) | NO | MUL | 0 | |
| text | text | YES | | NULL | |
| slideshow_id | int(11) | NO | MUL | 0 | |
| video_embed_code | text | YES | | NULL | |
+------------------+-------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

Show details about a table - such as row type and table size

mysql> SHOW TABLE STATUS LIKE 'ranks';

Show indexes on a table

mysql> show index from comments;
+----------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| comments | 0 | PRIMARY | 1 | id | A | 52195 | NULL | NULL | | BTREE | |
| comments | 1 | slide_id | 1 | slide_id | A | 26097 | NULL | NULL | | BTREE | |
| comments | 1 | user_id | 1 | user_id | A | 26097 | NULL | NULL | | BTREE | |
| comments | 1 | created_at | 1 | created_at | A | 52195 | NULL | NULL | | BTREE | |
| comments | 1 | slideshow_id | 1 | slideshow_id | A | 26097 | NULL | NULL | | BTREE | |
+----------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

Show InnoDB status

mysql> show innodb status;

Convert a table to MyISAM (was InnoDB)

mysql> ALTER TABLE table_name type=MyISAM;

Convert all database tables into InnoDB
Here is a shell script that will do such a thing. It assumes you have your credentials setup (otherwise, add -u and -p as needed). Pass it the name of the db.

#!/bin/sh
db=$1 rm -f tmp.Tables echo -n "Converting tables in ${db}"
sleep 3
mysql --skip-column-names -s -e 'show tables' ${db} > tmp.Tables for t in \`cat tmp.Tables\`; do echo -n "...${t}"
mysql -e "ALTER TABLE ${t} type=innodb" ${db}
done
rm -f tmp.Tables
echo

See also