MySQL

From wiki.mikejung.biz
Jump to: navigation, search

Liquidweb 728x90.jpg

Contents

Overview

Welcome to My MySQL Wiki! There's a ton of stuff on this page that may or may not help you become awesome with MySQL. It's important to remember that MySQL is used by a lot of different software, and the demands are not always the same. Because of this, one does not simply "tune" MySQL by pasting in a ton of settings found in a wiki, even my own. There are still a few important settings that will generally improve performance in most cases, such as innodb_buffer_pool_size, and well, really that's all you need to if you run basic CMS like WordPress or MediaWiki.

I've taken a screenshot of the NewRelic Dashboard for my own site to put some things into perspective when it comes to MySQL performance. You can see in the image below that MySQL only accounts for a small amount of my application's response time. MySQL only took around 18ms (milliseconds) to complete it's work. Meanwhile, PHP took around 100ms to complete it's request, over 5 times longer than MySQL. The point is that there is no point in spending too much time trying to make MySQL faster, it's usually the least of your worries when it comes to website response times. If you notice MySQL taking a significant amount of time to respond to requests, then it might be worth trying to tune it up a little, but if your application response times look similar to mine, then you are probably good to go.

MySQL wiki new relic app response time breakdown.jpg

How to Add Official MySQL Repo to CentOS 6.6

The commands below will install the official repo for MySQL, as well as update the current MySQL install to the latest release (5.6). Only do this if you are sure that the upgrade will not cause issues and make sure you backup any databases before you do this!

wget http://dev.mysql.com/get/mysql-community-release-el6-3.noarch.rpm/from/http://repo.mysql.com/
yum localinstall mysql-community-release-el6-3.noarch.rpm
yum update

MySQL IO Scheduler and Kernel Tweaks

For almost all cases, you should be using deadline or noop for the IO scheduler, this will speed up MySQL. CFQ is the default on a lot of old distros like CentOS 6. Luckily the Linux I/O scheduler is easy enough to change, you simply need to echo the scheduler into a file. The command below only changes the scheduler while the server is onlune, a restart will clear the setting. There are ways to make this permanent, to learn how to apply changes to the I/O scheduler on Ubuntu, please visit the link above.

# First, see what scheduler is in use
cat /sys/block/sd$/queue/scheduler

# To change to deadline or noop
echo deadline > /sys/block/sd$/queue/scheduler

General File System Mount Options for MySQL

Generally, you will want to make sure the volumes used for MySQL Data, or Logs are mounted with the following options. These two mount options tell Linux to not update the file metadata every time a file gets accessed. Normally this isn't a huge performance concern, however you can improve performance by a few percent by disabling access time updates for files and directories.

noatime,nodiratime

MySQL and SSDs

RAID Card Settings

  • Percona suggests disabling ReadAhead, or Read caching on the controller. Typically the OS and MySQL will have their own Read caches, so it's best to leave the RAID card's cache for writes.


General Thoughts and Concerns when running MySQL on SSD

http://www.tocker.ca/2013/05/06/when-does-mysql-perform-io.html

  • Percona recommends that you keep the databases on SSDs, but move over the innodb log files, or bin logs to a RAID array backed by a BBU with write cache enabled. I'm not 100% sure about this, as it sometimes makes more sense to just use one large 8 disk array and store all the data on it, instead of splitting this up and using two 4 disk RAID 10s, one with SSDs, the other with SATA or SAS drives. I will need to do more testing to see how quickly the SSDs wear out.
  • Percona also mentions that since the log writes are very small, which can cause write amplification on an SSD, which could lead to increased latency and quickly degraded drives. I can see how this would happen, however as SSDs get more and more efficient at writes, and more endurance this could be less of an issue. This really seems to depend on what kind of SSDs you use. For instance, you might not run into an issue if you use an Intel DC S3700 drive, but you might kill a cheap consumer SSD in a hurry.


  • This is an example of how you could split up the MySQL files between an SSD and a RAID backed by a BBU with write caching:
On the BBU RAID:
- InnoDB Log Files
- ibdata1
- MySQL Bin logs (If using Replication)

On the SSD:
- Everything else :)

InnoDB Read and Write Threads

  • With SSDs, MySQL can be tuned to allow for higher read and write concurrency than typical spinning disks. You can tune the following to help with performance:
##Default is 4 for both of these, might need to try various numbers to find the best performance
innodb_read_io_threads = 16
innodb_write_io_threads = 16

InnoDB IO Capacity

  • This parameter should be set to approximately the number of I/O operations that the system can perform per second. You probably don't want to set this really high, otherwise the OS might suffer. For most SSDs, setting the capacity to 2000 - 4000 should be just fine, especially if the SSD is dedicated to MySQL. For slower, spinning disk systems you may not want to mess with this value at all. I've found that for the most part 2000 is a pretty decent value to start at.
# Default here is 200
innodb_io_capacity = 2000

InnoDB Log Size AND Block Size

  • When configuring logs on an SSD system, it might make sense to change both of these at the same time since you will need to stop MySQL and move the current logs out of the way.

Typically, you want this to be high enough to improve performance, but low enough so that it doesn't take forever to recover from the logs if there is a crash. However, recovery time is only an issue with spinning disks, since their random IO sucks. The recovery time is MUCH faster when using SSDs, so raising the COMBINED log sizes to around 3-4GB(Version 5.5 or lower) should help. If you have two logs in the group, you would set the size to 2G since 2 x 2GB would hit the max size of 4GB.

innodb_log_file_size = 2047M

If the system is SSD based, it might make sense to change this from the default of 512 to 4096. You will need to stop MySQL, and move the current logs out of the way for this to work.

innodb_log_block_size = 4096

Percona/MariaDB Specific InnoDB Flushing Tweaks

Setting this to "keep_average" will help avoid stalling on SSD systems. Percona added this specifically for SSDs.

# Default is estimate
innodb_adaptive_checkpoint = keep_average

# If this is enabled, it can add a lot of extra latency, if you are using SSDs, percona recommends disabling this.
innodb_flush_neighbor_pages = 0

DoubleWrite Buffer

  • Percona mentions that disabling this can sometimes be safe, however, even they are not 100% sure this is totally safe. It does sound like disabling this will offer a larger performance increase on SSD systems.

General Variables

This section is meant to be an easy reference guide. Not all of these are set in stone as far as what is good or bad. Some of these settings do not even need to be added to my.cnf, some are defaults already. I plan on expanding this section further as I get more information as to what each setting does. A great guide on the basic settings to tune is here:

http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/


key_buffer (previously key_buffer_size)

  • Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.
SHOW STATUS LIKE 'key%';
  • Divide the Key_reads by the Key_read_requests, generally you want this to be less than 25%
key_buffer = 256M  !!Cannot set over 4G at this time

open_files_limit

  • Generally can be set to 8190, but can be raised further if this is mentioned in the logs, or if your table_cache needs to be set higher
open_files_limit = 8190

table_cache

  • 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger.
SHOW GLOBAL STATUS LIKE 'Opened_tables';
table_cache = $amount_of_tables


innodb_buffer_pool_size

  • InnoDB relies heavily on the buffer pool
  • The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 128MB, increased from a historical default of 8MB.
  • The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size.
  • Having a large buffer pool can cause the server to take a LONG time to shutdown.
# For a dedicated MySQL server with 8GB RAM, this is a good starting point (assuming they have all InnoDB tables)
innodb_buffer_pool_size = 5000M
# To take advantage of a larger buffer pool, split this between multiple instances (MySQL 5.5 + only)
innodb_buffer_pool_instances = 5


innodb_log_file_size

This setting determines the size of the log files in use. By default, there are two log files with default size of 5M.Raising this value can improve performance on busy servers.

  • Recommended settings would be between 32M and 64M
  • To apply a new size you need to cleanly shut down MySQL, remove the old logs out of the way(ib_logfile0 ib_logfile1), adjust the size in my.cnf, then start MySQL


innodb_thread_concurrency

  • The correct value for this variable is dependent on environment and workload. Try a range of different values to determine what value works for your applications. A recommended value is 2 times the number of CPUs plus the number of disks.

More here: http://blog.secaserver.com/2011/08/mysql-recommended-my-cnf-settings-innodb-engine/


innodb_log_buffer_size

When innodb makes changes to data, it stores this in the log buffer, which is stored in RAM. Raising this value can reduce bottlenecks, especially if large transactions are being processed. This can cause small queries to lock up.

  • Recommended settings are between 1M and 8M


innodb_flush_log_at_trx_commit

Changed to imrpove performance, but it can result in less durability.

  • 0 = write the log buffer to the log file and flush the log every second, but do nothing at transaction commit.
  • 1 = default setting, guarantees that you won't lose commited transactions
  • 2 = if MySQL crashes, you will not lose transactions. However if the server crashes you can lose 1 second of transactions


innodb_lazy_drop_table

http://www.percona.com/docs/wiki/percona-server:features:misc_system_variables

  • When innodb_file_per_table is set to 1, doing a DROP TABLE can take a long time on servers with a large buffer pool, even on an empty InnoDB table. This is because InnoDB has to scan through the buffer pool to purge pages that belong to the corresponding tablespace. Furthermore, no other queries can start while that scan is in progress.
  • When innodb_lazy_drop_table is ON, XtraDB optimizes that process by only marking the pages corresponding to the tablespace being deleted. It defers the actual work of evicting those pages until it needs to find some free pages in the buffer pool.
  • When innodb_lazy_drop_table is OFF, the usual behavior for dropping tables is in effect.


innodb_file_per_table

This allows you to assign one file per table. generally a good setting to configure on any server.

  • Slow DROP TABLE performance when enabled.
  • Requires innodb to lock and scan the buffer pool while it looks for pages belonging to the table. This is very slow on servers with large buffer pools.
  • To mitigate this, use innodb_lazy_drop_table


innodb_doublewrite

This should always be enabled, as it helps to protect against data corruption. Really should not need to change unless there is a specific reason given. Does not really impact performance that much.


===tmp_table_size and max_heap_table_size=== These need to be set to the same value.

  • A good starting point is 32M for both values


max_connections

For the most part, this is not a huge deal to set. A good default starting point is 500 on busy servers. Watch the max_used_connections variable to find out what this needs to be set to.


thread_cache_size

Watch the threads_connected variable to find out what the average is. Really should not be set higher than 250 in most cases

SHOW STATUS LIKE '%thread%';
SHOW STATUS LIKE 'Connections';
  • You would then use the following formula to attempt to get this number as close to 100% as possible
100 - ((Threads_created / Connections) * 100)
thread_cache_size = 32  !! or higher if you really need to


skip_name_resolve

Disables DNS lookups for hosts connecting to the server. This can be disabled unless you are relying on hostnames in your setup. This can actually cause issues with a lot of dns lookups are failing, so adding this should prevent that from happening.

sort_buffer_size

http://www.xaprb.com/blog/2010/05/09/how-to-tune-mysqls-sort_buffer_size/

Each session that needs to do a sort allocates a buffer of this size. From reading online, this is best left alone, as in don't set it unless there is a good reason. If you do set this, leave this as a small value, no larger than 2MB.

sort_buffer_size = 512K

read_buffer_size

http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/

Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072.

I have not really heard a good argument for changing this from the default. This is not a game changer setting. I'm not putting a recommended value here, just the default. Tune if there is a good reason to do so, otherwise just leave this alone.

read_buffer_size = 128K

read_rnd_buffer_size

http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/

  • When reading rows in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks.
  • However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.
  • Again, this is not really effective unless you are running single queries and really know what is going on. Leaving this at the default should be fine unless there is a specific need.
read_rnd_buffer_size = 256K

myisam_sort_buffer_size

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_myisam_sort_buffer_size

  • The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
  • Might want to raise this if a MyISAM repair is taking a looong time, otherwise might not need to change this.
myisam_sort_buffer_size = 8M

query_cache_size

http://www.howtogeek.com/howto/programming/speed-up-your-web-site-with-mysql-query-caching/

  • The amount of memory allocated for caching query results. The default value is 0, which disables the query cache.
  • The permissible values are multiples of 1024; other values are rounded down to the nearest multiple.
  • Note that query_cache_size bytes of memory are allocated even if query_cache_type is set to 0
  • query_cache_limit – This is the maximum size query that will be cached.
query_cache_type=1
query_cache_size=8M
query_cache_limit=1MB

tmp_table_size and max_heap_table_size

http://www.mysqlperformanceblog.com/2007/01/19/tmp_table_size-and-max_heap_table_size/ http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_tmp_table_size

  • The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.)
  • You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.
  • Below are good starting points. Raise this if you have a good reason to.
max_heap_table_size = 32M
tmp_table_size = 32M

wait_timeout

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_wait_timeout

  • The number of seconds the server waits for activity on a noninteractive connection before closing it.
  • Raise this if you see “MySQL server has gone away”
  • A good starting point
wait_timeout = 30

interactive_timeout

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_interactive_timeout

  • The number of seconds the server waits for activity on an interactive connection before closing it.
  • Good starting point
interactive_timeout = 150

thread_pool_size

http://dev.mysql.com/doc/refman/5.5/en/thread-pool-tuning.html

https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/buffers-caches-and-threads/thread-pool/threadpool-in-55/

According to MySQL, good values here are somewhere around 20 - 30. MariaDB says that this should be closer to the amount of CPUs on the system. This setting probably doesn’t need to be tweaked unless you have tried everything else to improve performance.

thread_pool_size=$value

innodb_flush_method (SSDs / RAID)

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_method

Defaults to fdatasync. If you are using hardware RAID, setting this to O_DIRECT may be the best option.

innodb_flush_method=O_DIRECT

innodb_flush_neighbors (SSDs / RAID)

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_neighbors

Defaults to 1. Should be set to 0 if you are using SSDs.

innodb_flush_neighbors=0

Install MySQL 5.5

wget http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-5.5.27-1.el6.x86_64.rpm/from/http://cdn.mysql.com/
wget http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-5.5.27-1.el6.x86_64.rpm/from/http://cdn.mysql.com/
wget http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-5.5.27-1.el6.x86_64.rpm/from/http://cdn.mysql.com/
wget http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-devel-5.5.27-1.el6.x86_64.rpm/from/http://cdn.mysql.com/

rpm -i MySQL-client-5.5.27-1.el6.x86_64.rpm
rpm -i MySQL-devel-5.5.27-1.el6.x86_64.rpm
rpm -i MySQL-server-5.5.27-1.el6.x86_64.rpm
rpm -i MySQL-shared-5.5.27-1.el6.x86_64.rpm

Install Percona 5.6 Repo on CentOS 6.5

The command below will install the Latest Percona Repo on a system. I recommend stopping MySQL if it's already on the server, then removing any of the old packages to avoid conflicts.

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum update

This command will list all of the available Percona packages. You can choose to install Percona 5.5 or Percona 5.6, I recommend installing Percona 5.6 as it is the biz.

yum search percona


Install Percona 5.6

yum install Percona-Server-server-56.x86_64

Install Mariadb 10.1 on Ubuntu 14.04

Some of the package locations that other sites mention appear to be broken, at least for me, so here is a workaround that I used to install Mariadb 10.1 on Ubuntu 14.04. If the repo add command fails, you can manually add the "deb http://mariadb.biz.net.id/repo/10.1/ubuntu/dists/trusty/main/binary-amd64/ trusty main" line to /etc/apt/sources.list and the install should work.

add-apt-repository 'deb http://mariadb.biz.net.id/repo/10.1/ubuntu/dists/trusty/main/binary-amd64/ trusty main'
apt-get update
apt-get install mariadb-server

Replace MySQL 5.6 with MariaDB 10.1 on cPanel

Please visit the page below

Percona Toolkit

Link to pdf (will need to register): http://form.percona.com/Percona_Toolkit_212_Operations_Manual.html

Install File

http://www.percona.com/redir/downloads/percona-toolkit/LATEST/percona-toolkit-2.2.4.tar.gz

pt-summary

This command will display a TON of useful information about the server. Very useful if you want a quick idea of the system.

pt-summary

pt-mysql-summary

Summarizes the status and configuration of a MySQL database server so that you can learn about it at a glance. It is not a tuning tool or diagnosis tool. It produces a report that is easy to diff and can be pasted into emails without losing the formatting. It should work well on any modern UNIX systems.

  • Basically, this tool is amazingly awesome.

This is the basic usage. This should display more than enough information.

pt-mysql-summary

pt-variable-advisor

  • Analyzes variables and advises on possible problems. Can be useful to get a quick idea if some settings are off base.

Basic Command:

pt-variable-advisor localhost

It will display suggestions such as these:

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.
# WARN innodb_flush_log_at_trx_commit-1: InnoDB is not configured in strictly ACID mode.
# NOTE log_warnings-2: Log_warnings must be set greater than 1 to log unusual events such as aborted connections.
# NOTE max_connect_errors: max_connect_errors should probably be set as large as your platform allows.
# WARN slave_net_timeout: This variable is set too high.
# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.
# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.
# NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance.
# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.


pt-show-grants

Shows all grants:

pt-show-grants

pt-find

  • Finds MySQL tables and executes actions

Examples Find all MyISAM or InnoDB tables on the server:

pt-find --engine MyISAM
pt-find --engine InnoDB

Find all tables and print their total data and index size, and sort largest tables first:

pt-find --printf "%T\t%D.%N\n" | sort -rn

pt-index-usage

  • Read queries from a log and analyze how they use indexes.
  • You must enable the MySQL slow log to analyze queries. You can also use an existing slow log if it is already in place.

"The tool runs two stages. In the first stage, the tool takes inventory of all the tables and indexes in your database, so it can compare the existing indexes to those that were actually used by the queries in the log. In the second stage, it runs EXPLAIN on each query in the query log. It uses separate database connections to inventory the tables and run EXPLAIN, so it opens two connections to the database."

To analyze queries from the slow log:

pt-index-usage /path/to/slow.log --host localhost

Additional Options to specify:

##Useful for specifying the user password for a database
--ask-pass

##Specify a specific database to lok at
--database $database

pt-ioprofile

  • Watch process IO and print a table of file and I/O activity.

This is an intrusive tool and it can freeze / crash / slow down the server. Please use with caution!

  • pt-ioprofile does two things: 1) get lsof+strace for -s seconds, 2) aggregate the results
  • pt-ioprofile uses strace and lsof to watch a process’s IO and print out a table of files and I/O activity. By default, it watches the mysqld process for 30 seconds

Basic usage (watch for 30 seconds)

pt-ioprofile --run-time 30

Example output:

Tracing process ID 25788
     total      pread     pwrite      write      fsync filename
  5.305982   0.000000   0.000000   5.305982   0.000000 /var/log/mysql/slow.log
  3.986494   0.000000   3.726348   0.000000   0.260146 /var/lib/mysql/ib_logfile1
  0.865377   0.452105   0.010684   0.000000   0.402588 /var/lib/mysql/ibdata1
  0.063684   0.000000   0.061789   0.000000   0.001895 /var/lib/mysql/ib_logfile0

Again, this tool should not be used on a production server as it severly slows down MySQL

  • sysbench complex 64 thread 2 minute test without running ioprofile
total:                           1542261
transactions:                        73441  (611.68 per sec.)
read/write requests:                 1395379 (11622.01 per sec.)
approx.  95 percentile:             110.55ms
  • sysbench complex 64 thread 2 minute test while ioprofile running
total:                           1060143
transactions:                        50483  (420.43 per sec.)
read/write requests:                 959177 (7988.15 per sec.)
approx.  95 percentile:             393.11ms

pt-pmp

This can be disruptive, never run this on a live server

  • Considered the "poor man's profiler". Creates and summarizes full stack traces of processes on Linux.
  • Can be very useful for determining what a process is waiting for, or hung up on.
pt-pmp

For more info on what the output means: http://poormansprofiler.org/


General commands

Get info on current settings:

SHOW GLOBAL STATUS;

SHOW GLOBAL VARIABLES;

Get info on tables in a database:

show table status;


See MySQL status. Updates every 1 s

watch -n 1 'mysqladmin proc stat'

Repair and Optimize

Optimize Tables

for i in $(mysql -e "show databases;" | sed 's/Database//') ; do for each in $(mysql -e "use $i; show tables;" \
| sed 's/Tables.*//' ;) ; do mysql -e "use $i ; optimize table $each" ; done ; done

MySQL check that will: check all databases, analyze, optimize and repair. Pretty useful, usually safe

mysqlcheck -Aaor

MySQL check for specified dbs, ie, check dbs that start with a-g, etc

for list in $(ls -lahd /var/lib/mysql/* | cut -d \/ -f5 | grep -e '^[m-p]'); do mysqlcheck -r $list; done

Grants and Privileges

Grant Privileges on a database

GRANT ALL PRIVILEGES ON data_base.* to 'user_name'@'location / ip' IDENTIFIED BY 'whateverthepassis';
SHOW GRANTS ON user_name;

Dump and Import

Dump a database

mysqldump database > database.sql

Import a database

mysql database < database.sql

Connect String

Connect to a database

mysql -u user -h ip -p databasename

Viewing and Deleting Tables and Databases

Look at databases and tables

mysql
> use databasename;
> show tables;

Drop (delete) a database. Can be useful if importing a database and it gives you an error

mysql
> drop database databasename;

Upgrading MySQL

Template:Box Warning

If pre-MySQL 5.0

 mysqlcheck -Aaor

If MySQL 5.0 to 5.1

 mysqlcheck -Agr

Back up all the databases

 mkdir -p /backup/mysqldumps
 cd /backup/mysqldumps
 for i in $(mysql -e "show databases;" | cut -d ' ' -f2 | grep -v Database); do `mysqldump $i > $i.sql`; done


Enabling a Slow Query Log

How to enable a slow query log

touch /var/lib/mysql/slow.log
chown mysql. /var/lib/mysql/slow.log

In the my.cnf file under the mysqld section add this:

log-slow-queries=/var/lib/mysql/slow.log

Then restart mysql and you have a slow query log.

If you want to specify the number of seconds that indicates a long or slow query, use this line in /etc/my.cnf :

long_query_time = 5

changing 5 to whatever number of seconds you want.

Types of data and storage engines

InnoDB

Commands

Show status:

SHOW ENGINE INNODB STATUS\G

Find all INNODB tables:

mysql -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'InnoDB'"

Find all MyISAM tables:

mysql -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'MyISAM'"

http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html


For information on how to convert WordPress tables from MyISAM to Innodb visit this section


  • The InnoDB buffer pool caches table and index data as the data is accessed. Frequently used data is processed directly from memory. This cache applies to so many types of information, and speeds up processing so much, that dedicated database servers assign up to 80% of their physical memory to the InnoDB buffer pool.
  • Enable the innodb_file_per_table option to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace. (This setting is required to use some of the other features, such as table compression and fast truncation.)
  • If your server crashes because of a hardware or software issue, regardless of what was happening in the database at the time, you don't need to do anything special after restarting the database. InnoDB automatically finalizes any changes that were committed before the time of the crash, and undoes any changes that were in process but not committed. Just restart and continue where you left off.

Overview of InnoDB Tablespace and Log Files

  • Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files. If you specify no InnoDB configuration options, MySQL creates an auto-extending 10MB data file named ibdata1 and two 5MB log files named ib_logfile0 and ib_logfile1 in the MySQL data directory.
  • In some cases, database performance improves if the data is not all placed on the same physical disk. Putting log files on a different disk from data is very often beneficial for performance.
  • For example, the following setting explicitly creates a tablespace having the same characteristics as the default:
[mysqld]
innodb_data_home_dir =
innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend
  • By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting is in effect has its own tablespace.
[mysqld]
innodb_file_per_table

Troubleshooting

Too Many MySQL Sleeping Queries

If you notice that many of the available connections for MySQL are being used by a long running query that appears to be sleeping, you might want to lower wait_timeout to 60 or less. For most CMS like WordPress or Magento setting wait_timout to 60 should be fine.

Add this to my.cnf

wait_timeout = 60

Then restart MySQL for the new setting to take effect. If you still notice issues, try lowering this to 30, or even 15. Keep in mind that lowering this too much might cause issues for longer running queries, so make sure you set this to a sane value for your specific workload.

MySQL 5.5 depricated settings

  • MySQL 5.5.xx will not start if you add either of the following two options into my.cnf
safe-show-database
skip-locking

Detecting Mutex Contention

  • You can detect when you suffer from mutex contention in the buffer pool by reading the information provided in the SEMAPHORES section of the output of SHOW INNODB STATUS:
  • Under normal circumstances this section should look like this:
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 50238, signal count 17465
Mutex spin waits 0, rounds 628280, OS waits 31338
RW-shared spins 38074, OS waits 18900; RW-excl spins 0, OS waits 0

If you have a high-concurrency workload this section may look like this:

1 ----------
2 SEMAPHORES
3 ----------
4 OS WAIT ARRAY INFO: reservation count 36255, signal count 12675
5 --Thread 10607472 has waited at buf/buf0rea.c line 420 for 0.00 seconds the semaphore:
6 Mutex at 0x358068 created file buf/buf0buf.c line 597, lock var 0
7 waiters flag 0
8 --Thread 3488624 has waited at buf/buf0buf.c line 1177 for 0.00 seconds the semaphore:
9 Mutex at 0x358068 created file buf/buf0buf.c line 597, lock var 0
10 waiters flag 0
11 --Thread 6896496 has waited at btr/btr0cur.c line 442 for 0.00 seconds the semaphore:
12 S-lock on RW-latch at 0x8800244 created in file buf/buf0buf.c line 547
13 a writer (thread id 14879600) has reserved it in mode  exclusive
14 number of readers 0, waiters flag 1
15 Last time read locked in file btr/btr0cur.c line 442
16 Last time write locked in file buf/buf0buf.c line 1797
[...]
17 Mutex spin waits 0, rounds 452650, OS waits 22573
18 RW-shared spins 27550, OS waits 13682; RW-excl spins 0, OS waits 0

May want to raise innodb_buffer_pool_size if the server has memory to spare.

Locking due to query cache

If we experience query cache contention, the output of SHOW PROCESSLIST will look like this:

Id      User    Host            db      Command Time    State                          Info
2       root    localhost       test    Sleep   2       NULL
3       root    localhost       test    Query   2       Waiting on query cache mutex  SELECT number from t where id > 0;
4       root    localhost       test    Query   1       Waiting on query cache mutex   SELECT number from t where id > 0;

It may be a good idea to disable query cache entirely.

MySQL Locking and Concurrency

MyISAM

  • Locks table on INSERT, UPDATE, DELETE.

InnoDB

  • Locks row on INSERT, UPDATE, DELETE.
  • Supports transactions which will not commit until all the queries are completed.

Types of locking

  • Shared (read only) Statements like SELECT will work, however any statements that change data will need to wait until after the lock is released.
  • Exclusive (read and write) All statements need to wait until the lock is released, typically this is the lock that transactions use.
  • Deadlocking happens when two different users or sessions are attempting to update the same data in a row.

MyISAM and InnoDB repairs

MyISAM

http://dev.mysql.com/doc/refman/5.5/en/repair-table-speed.html

  • REPAIR TABLE for MyISAM tables is similar to using myisamchk for repair operations, and some of the same performance optimizations apply
  • For REPAIR TABLE, the same principle applies, but because the repair is done by the server, you set server system variables instead of myisamchk variables. Also, in addition to setting memory-allocation variables, increasing the myisam_max_sort_file_size system variable increases the likelihood that the repair will use the faster filesort method and avoid the slower repair by key cache method.
  • Please note that most of the time you only need to repair a crashed table, NOT the entire database. To do this do the following, in a screen!
mysql
use $database;
REPAIR $crashed_table;
  • If you notice a repair is taking a long time, check to make sure it's not repairing from key cache:
SHOW PROCESSLIST;
  • Repairing from Key Cache is very slow and raising the following variables could speed things up:
PLEASE NOTE, these are arbitrary values, use common sense 
key_buffer_size = 1GB
sort_buffer_size = 16M
read_buffer_size = 16M
write_buffer_size = 16M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 512M
  • Also, keep an eye on the .err log while the repair is happening. You might need to change the location of tmpdir if you see errors in the log while the repair is going.

InnoDB

http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corruption/

  • Typically InnoDB recovery will happen automatically after a reboot or crash. However, if this is not successful you might need to force recovery
innodb_force_recovery = $1-6
  • innodb_force_recovery is 0 by default (normal startup without forced recovery)
  • Other settings are as follows; PLEASE do not raise this value without understanding what you are doing!
1 (SRV_FORCE_IGNORE_CORRUPT)
Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

2 (SRV_FORCE_NO_BACKGROUND)
Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

3 (SRV_FORCE_NO_TRX_UNDO)
Do not run transaction rollbacks after recovery.

4 (SRV_FORCE_NO_IBUF_MERGE)
Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO)
Do not do the log roll-forward in connection with recovery.
  • If you are able to dump your tables with an option value of at most 4, then you are relatively safe that only some data on corrupt individual pages is lost.
  • Once / IF you are able to get things started, you will want to dump the database then reimport to get things working again. This can get messy, so don't use this as your only guide.