How to do basic MySQL optimization

MySQL – Intro

Pronounced “my ess cue el” (each letter separately).

MySQL is an open-source high-performance, multi-threaded, multi-user relational database management system (RDBMS) built around a client- server architecture.

It was designed specifically for speed and stability and has become one of the most popular RDBMS for database-driven Web application.

Information is stored in “Tables” which can be thought of as the equivalent of Excel spreadsheets. A single MySQL database can contain many tables at once and store thousands of individual records. It’s fast, reliable and flexible.

MySQL Variables

max_connections

MySQL is multi-threaded, so there may be many clients issuing queries to a single table simultaneously.

The number of simultaneous client connections allowed. By default, this is 100.

max_user_connections

The maximum number of simultaneous connections allowed to any given MySQL account. A value of 0 means “no limit”.

key_buffer_size

key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.

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.

join_buffer_size

The size of the buffer that is used for joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes.

Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible.

One join buffer is allocated for each full join between two tables.

read_buffer_size

Each request that performs a sequential scan of a table allocates a read buffer.

sort_buffer_size

Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations.

table_cache

The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.

thread_cache_size

How many threads the server should cache for reuse. When a client disconnects, the client’s threads are put in the cache if there are fewer than thread_cache_size threads there.

Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created.

Increasing the value to improve performance if we have a lot of new connections.

wait_timeout

The number of seconds the server waits for activity on a non-interactive connection before closing it.

This timeout applies only to TCP/IP and Unix socket file connections, not to connections made via named pipes, or shared memory.

connect_timeout

The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.

max_allowed_packet

The maximum size of one packet or any generated/intermediate string.

max_connect_errors

If there are more than this number of interrupted connections from a host, that host is blocked from further connections.

You can unblock blocked hosts with the FLUSH HOSTS statement.

query_cache_limit

Don’t cache results that are larger than this number of bytes. The default value is 1MB.

query_cache_size

The amount of memory allocated for caching query results. The default value is 0, which disables the query cache.

The allowable values are multiples of 1024; other values are rounded down to the nearest multiple.

open_files_limit

The number of files that the operating system allows mysqld to open.

Sample values for optimizing MySQL

[mysqld]

max_connections = 400
key_buffer = 128M (128MB for every 1GB of RAM)
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M (1MB for every 1GB of RAM)
sort_buffer_size = 3M (1MB for every 1GB of RAM)
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M (32MB for every 1GB of RAM)
query_cache_type = 1
tmp_table_size = 16M

thread_concurrency=2 (Number of CPUs x 2)

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M