MySQL

From ConShell
Jump to navigation Jump to search


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 <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 & 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

Retrieved from "https://conshell.net/wiki/index.php?title=MySQL&oldid=5825"