SupportSages

Mysql server optimization requires customization of various values based on the requirement and availability of resources. An admin needs to be shrewd while making a decision about the values to be placed for each variable to fine-tune it to provide excellent performance. To avoid the overhead of calculating each variable, most admins prefer to copy and use any of the my.cnf file available on the net or in the forums. Honestly, I too belonged to the class :-).

In this article, I am trying to explain the system variables which contributes significantly to the performance of a MySQL server. You can see some values as eg., please make sure to change it in view of your requirement.

MySQL index

Indexes are used to find rows with specific column values quickly. Indexes are essential for fast and effective conditional queries. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. This creates high resource consumption and delays if the table is large. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data.Hence make sure indexing is enabled on the DB you use.

MySQL system Variables

MySQL system variables are the tools used for MySQL server optimization. The number of variables is quite large and you have the freedom to use them in accordance with your requirement. In this article, I am trying to make some descriptions about common variables being used on shared web hosting environment. For default installations, the configuration file for MySQL server is /etc/my.cnf . Each variable needs to be specified in the file under the appropriate section. The file location will be different for custom installations and need to use those files in such occasions.

Skip-networking

This option restricts the scope of MySQL server for the applications hosted on the server. In other words, no connections to MySQL server or to port 3306 will be permitted from remote machines or from the external world. Normally the database will be used only by locally installed PHP applications. Disabling connections to the MySQL port from external world will not harm the applications as local communication is still possible to throw the mysql.sock socket.

This will limit possibilities of attacking the MySQL database by direct TCP/IP connections from other hosts. Enabling the option is most recommended if you are concerned about security. But some servers require remote connections if they need to check the DB server status remotely ( like Nagios) or need to perform backups to remote server. In such cases, we may need to keep the remote connections active.

Eg:

[mysqld]

skip-networking

skip-locking

Table locking enables many sessions to read from a table at the same time, but if a session wants to write to a table, it must first get exclusive access. During the update, all other sessions that want to access this particular table must wait until the update is done. Since MySQL makes a system lock for each table before access (its a slower process), it reduces the MySQL operation speed. The -skip-locking option disables file locking between SQL requests and hence provides great speed.

Eg:

[mysqld]
skip-locking

max_connections

This value determines the maximum concurrent connections MySQL server can handle. If MySQL connections reach to it then you can see errors like “too many connections”.

Mysqld actually permits max_connections+1clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege or to MySQL root. This setting enables MySQL root to access the mysql server and manage it irrespective of the no. of active connections.

By default max_connections is set to 150. Linux or Solaris should be able to support at 500 to 1000 simultaneous connections routinely and if you have high RAM availability you can increase the values still higher.

A value of 500 should be sufficient normally for busy shared servers.

Eg:

max_connections=500

connect_timeout

The variable defines the number of seconds that the MySQL server waits for a connect packet before discarding the connection. The default value is 10 seconds. In situations of network congestion (either at the client or server), it’s possible for an initial connection to take several seconds to complete. If it exceeds the connect_timeout value then connection errors will occur.

A value of 15-20 seconds will be sufficient normally.

Eg:

connect_timeout=15

skip-name-resolve

MySQL does a reverse DNS lookup on every incoming connection by default. This will increase the query execution time and reduces the server performance. If you use this option, all Host column values in the grant tables must be IP addresses or localhost.

Eg:

skip-name-resolve

key_buffer_size

The key_buffer_size is probably the most useful single variable to tweak. To minimize disk I/O, the MyISAM storage engine employs a cache mechanism to keep the most frequently accessed table blocks in memory and is called as index blocks. An index block is a contiguous unit of access to the MyISAM index files. For index blocks, a special structure called the key cache (or key buffer) is maintained. The structure contains a number of block buffers where the most-used index blocks are placed.

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.

Up to 25% of the machine’s total memory is an acceptable value for this variable.A value beyond 4 GB is not supported in 32 bit operating Systems and a high value will decrease the performance.

Normally values ranging from 128M-512Mwill be sufficient

Eg:

key_buffer_size = 128M

join_buffer_size

The join_buffer_size is a per-thread memory buffer that is used when a query must join two sets of table data and no index is used. Setting this variable to a large value reduces the performance impact. Since it is used only on join operations alone, using the default size ( 131072 Bytes or 130 KB) wouldn’t hurt normally. If you have busy server, values from 1 MB-4MB should provide reasonable performance though you can allocate a maximum of 4 GB

Eg:

join_buffer_size = 1M

max_allowed_packet

A MySQL communication packet is a single SQL statement sent to the MySQL server. It can be a single row that is sent to the client, or a binary log event sent from a master replication server to a slave. The largest possible packet that can be transmitted to or from a MySQL 5.5 server or client is 1GB.

A higher value for this variable will not create any performance issues. However, setting it to a higher value will avoid errors during bigger DB operations especially if special data types like blob are involved.

Eg

max_allowed_packet = 100M

table_cache

MySQL is multi-threaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session.table_cache limits the maximum number of tables that can be opened at once. Whenever MySQL accesses a table, it places the table in the cache. Accessing data from cache improves performance. MySQL puts tables in cache until they are explicitly closed.

The default table_cache=64 is sufficient for small servers. However, you may need to raise the value if you have an active server and a large number of tables to be accessed.

Eg

table_cache = 10000

max_heap_table_size

MySQL creates internal temporary tables while processing queries. The result set may be of varying dimensions and it enables MySQL to use Memory ( Formerly known as Heap) storage engine or MyISAM storage engine.The Memory engine operates or stores the tables in primary memory ie on RAM. This quite fast but is volatile.The MyISAM table writes data to the disk. Disk operations have a low speed but it has high stability. A table created using the MEMORY engine can be automatically converted by the MySQL server if it exceeds the defined threshold.This variable max_heap_table_size defines the maximum size of a MySQL MEMORY storage engine table and beyond that limit, the table will be moved to disk-based operations. Users have no direct control over when the server creates an internal temporary table or which storage engine the server uses to manage it.

The default max_heap_size value is 16M

Eg:

max_heap_table_size = 128M

tmp_table_size

The maximum size of internal in-memory temporary tables is specified by this variable. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. If an internal heap or memory table ( created by Memory Storage engine) exceeds the size of tmp_table_size, MySQL handles this automatically by changing the in-memory heap table to a disk-based MyISAM table as necessary.

MySQL compares tmp_table_size and max_heap_table_size variable values and uses lower value as a limit to for in memory temporary table after which it will be converted to MyISAM. Hence it is logical to set same value for both variables

The default value for tmp_table_size is system dependent.

Since the variables are assigned at session level, it is good practice to increase the value of tmp_table_size only when is really needed.

Eg:

tmp_table_size = 128M

sort_buffer_size

The sort_buffer_sizeis a per session buffer and do not belong to any specific storage engine . This memory is assigned per connection/thread. MySQL will allocate sort_buffer_size for every sort operations(required most of the times for ORDER BY and GROUP BY queries). In most cases, the operation requires small memories unless it is working on a huge DB or table. Allocating higher value for this variable can create resource bottle necks due to its allocation per session. Hence it’s better to use a smaller value.

For most servers, the default value of 512K would be sufficient

Eg.

sort_buffer_size = 512K

net_buffer_length

This variable specifies the initial size of the buffer for client/server communication. When a connection has established a buffer with the size specified for net_buffer_length will be initiated. It can grow up to the size of max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length bytes after each SQL statement.

The communication buffer is reset to this size between queries. This should not normally be changed, but if you have very little memory, you can set it to the expected size of a query.

The default value of 16 K should be sufficient for normal servers

Eg:

net_buffer_length = 16K

read_buffer_size

Each thread that does a sequential read allocates a buffer of this size (in bytes) for each table it scans or reads. MySQL built-in default is 128K and this should be fine for most cases. If you want fast full table scans or bulk reads for the large table you should set this variable to some high value. The value of this variable should be a multiple of 4KB.

Eg:

read_buffer_size = 256K

read_rnd_buffer_size

This buffer is used to hold data that is read as the result of a sorting operation. This differs from the read_buffer_size that reads data sequential based on how this is stored on disk. Increasing this buffer is applicable only when performing large ORDER BY statements. Since this buffer is allocated for each thread , increasing the value without proper evaluation will lead to bottle necks.

It defaults to the same size as the read_buffer_size, which defaults to 128KB. However it is not necessary to keep these two values identical. It can be adjusted based on your requirements.

Eg:

read_rnd_buffer_size = 512K

myisam_sort_buffer_size

myisam_sort_buffer_size used by MyISAM to perform index sorting on relatively rare table-wide modifications like ALTER/REPAIR TABLE. The maximum size is 4 GB.This variable should be set as large as the largest index in the table for index builds if there is sufficient RAM and it is not over 4 GB.

The default value is 8M

Eg:

myisam_sort_buffer_size = 96M

query_cache_limit

Query caching caches commonly used SQL queries in memory for virtually instant access to the next page that makes the same request. The variable query_cache_limit specifies the maximum size of the query (in bytes) that will be cached. The default value for this is 1M it tells MySQL what is the biggest query it should cache.

A high value may not create significant improvements as queries which are beyond 1 M are quite low.

Eg:

query_cache_limit=1M

query_cache_size

query_cache_size is the size of the cache in bytes. The default value is 0, which disables the query cache. An optimum query_cache_size improves performance for read intensive applications. Values from32M to 512Mnormally make sense.

Eg:

query_cache_size=128M

query_cache_type

The query_cache_type allows you to define the behaviour of your queries with the query cache.

Setting the value to:

0 – switches off or disable the caching

1 – Enables caching activity

2 – The caching is available only on demand

The normal value for improved server performance is “1”

Eg:

query_cache_type=1

thread_cache_size

Before users can even begin to start selecting data, they need to connect to the database. If your application makes a lot of separate connections to the database over short periods of time (like most PHP based applications) this can cause a lot of overhead with allocating and deallocating the associated “stack” for each specific “Connection” or “Thread”.

thread_cache_size specifies the number of 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. This variable can be increased to improve performance if you have a lot of new connections. If your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads.

The default value for the variable thread_cache_sizeis 0. However availability of thread pool improves performance.

Eg:

thread_cache_size=16

max_user_connections

max_user_connections limits the number of simultaneous connections that can be made by a database user to a MySQL database, but places no limits on what a client can do once connected.The default value for max_user_connections is 0 which means unlimited. But this is not a recommnetd value and it is always recommneded to set this value to a logical one

Eg:

max_user_connections = 25

max_connect_errors

This variable determines how many interrupted connections ( not successful) can occur from a host. After max_connect_errors failed requests, mysqld assumes that something is wrong (for example, that someone is trying to break in), and blocks the host from further connections until you execute a mysqladmin flush-hosts command or issue a FLUSH HOSTS statement.

The main reason max_connect_errors exists is to help against DOS attacks. A malicious attacker could continuously connect to mysql and drop again, causing mysql to work up its connections and get swamped with connect errors.

The default value is 10, a high value for this value is not recommended based on the security aspects and a low value can make clients unhappy.

A busy shared server can with the value as shown below

Eg:

max_connect_errors=10000

innodb_buffer_pool_size

This is very important variable to tune on Innodb engines. It is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.

local_inifile

It’s possible to execute statements in MySQL from a text file from the command line or from the MySQL command line shell. Suppose the user has a .txt or excel file named ‘dump.txt’ , it contains one record per line and separated by tabs and arranged in order as the columns listed in the table.

MySQL provides a LOAD DATA statement that acts as a bulk data loader. Here’s an example statement that reads a file dump.txtfrom your current directory and loads it into the table mydatain the current database:

mysql> LOAD DATA LOCAL INFILE ‘dump.txt’ INTO TABLE mydata;

The LOAD DATA statement can load a file that is located on the server host, or it can load a file that is located on the client host when the LOCAL keyword is specified. According to Mysql manual, there are two potential security issues with supporting the LOCAL version of LOAD DATA statements:

The transfer of the file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server’s choosing rather than the file named by the client in the LOAD DATA statement. Such a server could access any file on the client host to which the client user has read access.

In a Web environment where the clients are connecting from a Web server, a user could use LOAD DATA LOCAL to read any files that the Web server process has read access to (assuming that a user could run any command against the SQL server). In this environment, the client with respect to the MySQL server actually is the Web server, not the remote program being run by the user who connects to the Web server.

By default, all MySQL clients and libraries in binary distributions are compiled with the –enable-local-infile option.

To prevent against unauthorized reading from local files we need to disable the use of LOAD DATA LOCAL INFILE command.

For that purpose, the following parameter should be added in the [mysqld] section in my.cnf:

set-variable=local-infile=0

Eg

local-infile=0

slow_query_log

Non-optimised SQL commands take too more time to execute and create resource bottle neck. This results poor MySQL server performance and leaves more queries in the process queue waiting for resource allocation. This increases the number of active connections and when connection limit is reached, new connections will be dropped.

MySQL has built-in functionality to capture slow query log. The slow query log is used to find queries that take a long time to execute and are therefore candidates for optimization. To enable slow query log, simply add the following line to MySQL configuration file

Eg

slow_query_log = 1

After enabling slow query log, MySQL will create, capture and log to the log file ( by default to host_name-slow.log )with all SQL statements that took more than long_query_time seconds to execute, which is by default set to 10 seconds.

open_files_limit

The open file limit is an important variable for performance when MySQL server has to open several files at once. If open_files_limit is set to a lower value then MySQL query might fail causing other problems or might take too long to execute.

The normal value for this variable is 2-3 times table_cache . In this article I use 10000 for table cache and hence the value is

Eg

open_files_limit = 30510

innodb_file_per_table

InnoDB tables by default store data and indexes into a shared tablespace (/var/lib/mysql/ibdata1). This file will grow as it is used but will never shrink, even if you drop a very large INNODB table. Due to the shared tablespace, data corruption for one InnoDB table can result in MySQL failing to start up on the entire machine.

Enabling the option innodb_file per_table makes InnoDB to store each newly created table in its own tbl_name.ibd file in the appropriate database directory.

Unlike the MyISAM storage engine, with its separate tbl_name.MYD and tbl_name.MYI files for indexes and data, InnoDB stores the data and the indexes together in a single .ibd file. The tbl_name.frm file is still created as usual.

If you remove the innodb_file_per_table line from my.cnf and restart the server, InnoDB creates any new tables inside the shared tablespace files means the single server file. You can always access both tables in the system tablespace and tables in their own tablespaces, regardless of the file-per-table setting.

InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The .ibd files alone are not sufficient for InnoDB to operate.

When a table is moved out of the system tablespace into its own .ibd file, the data files that make up the system tablespace remain the same size. The space formerly occupied by the table can be reused for new InnoDB data, but is not reclaimed for use by the operating system dynamically. In other words, you can’t see any difference in the available free space or file size.

The practical advantage of using this option is that you can specify multiple file paths using multiple innodb_file_per_table options in my.cnf. This avoids the risk of a huge file on a small disk space and enables you to “spread the load” over multiple partitions or hard drives.

Eg:

innodb_file_per_table = 1

As mentioned earlier, there are hundreds of configuration directives and you need to evaluate them against the requirement and resource pool available. An MySQL server  optimization will significantly improve server performance and stability.