MySQL
From ConShell
Contents |
MySQL Performance
Reading Room
- Read http://www.mysqlperformanceblog.com/, specifically...
What to tune in MySQL Server after installation]
- Read A Few Common Performance Mistakes by Big DBA Head!
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.
- Use LVM volume with some spare room (10-20%) on the volume group to utilize snapshots for backup purpose.
- Put logs (bin-logs and relay-logs) on separate disk spindles if possible
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
MyISAM table specifics
- low-priority-updates
InnoDB specifics
Here are some reasonable settings to use on a LAMP system with on CPU, 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?
- http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB
- Review InnoDB vs MyISAM vs Falcon benchmarks
- Review this if using InnoDB and you have memory (swap) issues.
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...
Root password
Here is how to change the root password from command line using mysqladmin.
mysqladmin -uroot -p password <type-the-new-password> Enter password: <type-the-old-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
Examine the table to find out what engine (MyISAM, InnoDB, etc) are used for them.
mysql> use information_schema; mysql> SELECT count(engine),engine FROM tables group by engine; +---------------+--------+ | count(engine) | engine | +---------------+--------+ | 2 | InnoDB | | 14 | MEMORY | | 117 | MyISAM | +---------------+--------+ 3 rows in set (0.09 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 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;
See also
- MySQL Backup Scheme
- http://www.xaprb.com/blog/2006/07/16/how-we-enabled-threading-in-mysql/
- http://codemonkey.ravelry.com/
- http://hackmysql.com/ source of mysqlreport, mysqla and mysqlidxchk, lot's of good stuff there.
