MySQL database installation and common optimization settings

This article is based on the gifted network course collation, web-oriented developers, content to practical, professional DBA can bypass.

If you're in a big company, you might have a dedicated DBA to do these things, and if you're an architect or a technical director in a small company, or you start your own business, you have to do the DBA's job. Let's talk about basic MySQL installation and optimization.

One: MySQL installation and basic configuration

Installed on Linux, can be installed with package management tools, relatively simple:
RedHat series: yum-y install MySQL Mysql-server
Debian series: sudo apt-get install MySQL mysql-server

After installation do not know where MySQL installed to do, with Whereis mysql command to find. Find the default configuration file for MySQL first. In general, there are several alternate configurations after installation:

?

1	my-huge.cnf my-innodb-heavy-4G.cnf my-large.cnf my-medium.cnf my-small.cnf

As for the 2014 machine configuration, we use MY-HUGE.CNF directly. Copy the MY-HUGE.CNF to/etc/under the name of MY.CNF. The configuration file is available, and then start MySQL:/etc/init.d/mysqld start.

After installation, the default account is root and the password is empty. The first thing we need to do is change the root password.
Enter Mysql:mysql-uroot-p
Select database: Use MySQL
Change Password: UPDATE user SET Password = Password (' xxxx ') WHERE user = ' root ';
Refresh permissions: Flush privileges;

Now the database is installed, account and permissions are set, is it possible to use it, but also to check a few configurations. Open configuration file vim/etc/my.cnf

Skip-networking to close.
bind-address = 127.0.0.1 This line is to be closed or modified to allow IP
Skip-name-resolve prohibit DNS resolution, only with IP connection, this can be opened.

If your MySQL is running over a period of time and it's over the peak of the visit, let's check to see if the configuration is appropriate. The configuration below is meant to be checked after a certain period of time. If it is not long, MySQL running status is not representative, not as a reference. It doesn't make sense to check configuration parameters.

Two: Connection number (Connection) configuration

Max_connections can set the maximum number of concurrent connections. When MySQL's concurrent connection reaches this setting, the new connection will be rejected ("Can not connect to MySQL server. Too many connections "-mysql 1040 Error,"). When it is found that MySQL has the ability to handle more concurrency, it is recommended to increase this value, corresponding to the server to bring a higher load (cpu/io/memory).

View the maximum number of connections that are set:

?

1 2 3 4 5 6 7 8 9

mysql> show variables like ‘max_connections‘; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | 允许的最大连接数 +-----------------+-------+   看当前连接数：show status like ‘threads_connected‘;最大连接数: show status like "max_used_connections";

　　

If Max_used_connections is close to max_connections, it means max_connections is too small. It doesn't fit.

There are also some configurations related to the number of connections:
Back_log=50
The number of connections that can be staged by MySQL. This works when the primary MySQL thread gets very many connection requests in a very short period of time. If the MySQL connection data reaches max_connections, the new request will be present in the stack, waiting for a connection to release the resource, the number of that stack is back_log, and if the number of waiting connections exceeds back_log, the connection resource will not be granted.

The Back_log value indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request. Only if you expect to have a lot of connections in a short period of time, you need to increase it, in other words, the size of the listening queue for incoming TCP/IP connections.

Different operating systems have its own limitations on this queue size. Attempting to set a limit of back_log above your operating system will be invalid. The default value is 50. For Linux systems, the recommended setting is an integer less than 512.

If the number of links exceeds Max_connections+back_log, an error occurs.

max_connect_errors=10
When the client connects to the service-side timeout (over connect_timeout), the server logs an error to the client, and the client is locked out when the number of errors reaches max_connect_errors. Unless the flush hosts command is executed.

Connect_timeout=5
Number of seconds the connection timed out

Three: Query cache (query_cache) configuration

A query cache is a chunk of memory that stores the user's SQL text and related query results. Typically, the next time the user queries, if the SQL text used is the same, and since the last query, the relevant record has not been updated, the database will be directly in the cache content. It is hundreds of times times faster to read from memory than from the hard disk.

MySQL query cache is used to cache select query results, and the next time the same query request, no longer perform the actual query processing and return the results directly, there is such a query cache can improve the speed of queries, so that query performance is optimized.

To use caching, there are several conditions.
The first is that the SQL statement used is the same. Each time the statement is different, the cache must not be used. Like a statement with the current number of seconds where ctime > xxx
The second is that the table data has not changed. No structure, no update,insert.
Three: The client is the same as the server's default character set

So it can be seen that the use of good caches, there are a large number of the same query, and rarely change the table data, otherwise it is not necessary to use this feature.

To view the settings for the query cache:

?

1 2 3 4 5 6 7 8 9 ten page

show VARIABLES like '%query_cache% ' ; +------------------------------+----------+ | variable_name | Value | +------------------------------+----------+ | Have_query_cache | YES | | Query_cache_limit | 1048576 | If a single query result is greater than this value, the cache is not | Query_cache_min_res_unit | 4096 | The size of the memory allocated for each QC result | query_cache_size | 33554432 | | Query_cache_type | On | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+   query_ cache_type=1

　　

If set to 1, all results will be cached unless your SELECT statement uses Sql_no_cache to disable query caching.
If set to 2, only the queries that require caching are specified by Sql_cache in the SELECT statement.

Query_cache_size default is 32M, too small, can be adjusted to 128M or 256M. The value of the Qcache_lowmem_prunes variable can be used to check whether the current value satisfies the load of your current system.

How Query_cache_size works: When a select query works in DB, the DB caches the statement, and when the same SQL comes back to the DB, the DB returns the result from the cache to the client when the table is not changed. There is a shut-down point, that is, when DB is working with Query_cache, it requires that the table involved in the statement not be changed during this time period. So what happens to the data in Query_cache if the table is changed? The first thing to do is to invalidate the Query_cache and the table-related statements, and then write the update. So if the query_cache is very large, the query structure of the table is more, the query statement invalidation is slow, an update or insert will be very slow, so see is update or insert how slow. Therefore, in the database write volume or update volume is also relatively large system, this parameter is not suitable for allocation too large. And in the high concurrency, write a large-scale system, built to disable the function.

Qcache_lowmem_prunes can check if the settings are too small.

Query_cache_limit default is 1M, depending on the data result size of your favorite query. If the returned data is small, you can set it smaller.
Setting a big value is good for big data queries, but if your queries are small data queries, it's easy to create memory fragmentation and waste.

See how the Cache works:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

show status like ‘%Qcache%‘; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 160 | 目前还处于空闲状态的 Query Cache中内存 Block 数目，数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。 | Qcache_free_memory | 23147296 | 缓存中的空闲内存总量。 | Qcache_hits | 52349 | 缓存命中次数。 | Qcache_inserts | 8827 | 缓存失效次数。 | Qcache_lowmem_prunes | 0 | 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。 | Qcache_not_cached | 2446 | 没有被cache和不适合进行缓存的查询的数量，通常是由于这些查询不是SELECT语句以及由于query_cache_type设置的不会被Cache的查询。show,use,desc | Qcache_queries_in_cache | 5234 | 当前被cache的SQL数量。 | Qcache_total_blocks | 10796 | 缓存中块的数量。 +-------------------------+----------+show global status like ‘Com_select‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select | 12592 | com_select 变量记录的是无缓存的查询次数+错误查询+权限检查查询。 +---------------+-------+

　　

MySQL Query cache Hit rate no official algorithm, only the experience of the previous summary
Hit Rate ≈qcache_hits/(qcache_hits + com_select)

Cache Fragmentation Rate = Qcache_free_blocks/qcache_total_blocks * 100%, if the fragmentation rate is too high, 20%, you can flush the QUERY cache to defragment the cache, or try to reduce the Query_cache_min_ Res_unit, if your query is a small amount of data.

Query Cache utilization = (query_cache_size–qcache_free_memory)/query_cache_size * 100%
#查询缓存利用率在25% below indicates that the Query_cache_size setting is too large, can be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 description query_cache_ Size may be a little bit small, or too much fragmentation.

How to increase the hit ratio:
1: The character set is the same, the 2:SQL statement is as fixed as possible (SQL statement avoids random number, number of seconds, etc.) 3 increase cache Space 4: Appropriate table, static and dynamic separation,

Four: Temporary table cache (tmp_table_size) configuration

When MySQL makes complex queries or makes advanced group by operations, the system generates some temporary tables in order to optimize the query. Set the size of the temporary table to occupy space by setting the Tmp_table_size option.

We use explain to parse SQL, and if you see the using temporary in the extra column, it means that the temporary table is used.

The MySQL temp table is divided into "Memory temp table" and "Disk temp table", in which the memory temp table uses MySQL storage engine, the disk temp table uses MySQL's MyISAM storage engine;

In general, MySQL will first create a temporary memory table, but when the memory temp table exceeds the configured value, MySQL will export the memory temp table to the disk temp table

Temporary tables will exist during your connection to MySQL. When you disconnect, MySQL automatically deletes the table and frees up the space used.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> SHOW VARIABLES LIKE ‘%tmp_table_size%‘; +----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 33554432 | +----------------+----------+ 1 row in set (0.00 sec)mysql> show global status like ‘created_tmp%‘; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Created_tmp_disk_tables | 690421 | 服务器执行语句时在硬盘上自动创建的临时表的数量 | Created_tmp_files | 755473 | mysqld已经创建的临时文件的数量 | Created_tmp_tables | 14372959 | 服务器执行语句时自动创建的内存中的临时表的数量。如果Created_tmp_disk_tables较大，你可能要增加tmp_table_size值使临时 表基于内存而不基于硬盘 +-------------------------+----------+

　　

Each time a temporary table is created, created_tmp_tables increases, and if the temporary table size exceeds tmp_table_size, the temporary table is created on disk, Created_tmp_disk_tables also increases, CREATED_TMP_ Files represent the number of temporary file files created by the MySQL service, and the ideal configuration is:

Created_tmp_disk_tables/created_tmp_tables * 100% <= 25%
For example, the server above Created_tmp_disk_tables/created_tmp_tables * 100% =1.20%, should be quite good

Default size is 32M, adjustable to 64-256 best, thread exclusive, too large may not have enough memory I/O clogging

Another configuration associated with temporary tables is max_heap_table_size,
The maximum capacity allowed for a separate memory table that the user can create. This variable does not apply to user-created memory tables.
SHOW VARIABLES like '%max_heap_table_size% '; We want the temp table to be put into memory. So this value sets the temporary table to cache the same space as the row.

(The actual limiting effect is the minimum value of Tmp_table_size and Max_heap_table_size.) If the temp table is larger than either of these two, the hard disk cache will be saved: automatically convert it to a disk-based MyISAM table, stored in the specified Tmpdir directory

V: Index buffer (key_buffer_size) configuration

Key_buffer_size is one of the most influential parameters for MyISAM table performance. key_buffer_size Specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. By checking the status values key_read_requests and Key_reads, you can see if the key_buffer_size settings are reasonable. Proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better

Key_buffer_size only works on MyISAM tables. Even if you do not use the MyISAM table, the internal temporary disk table is the MyISAM table and this value is used. You can use the Check status value created_tmp_disk_tables to learn more.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> show variables like ‘key_buffer_size‘; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | key_buffer_size | 67108864 | 索引缓冲区的大小 +-----------------+----------+show variables like ‘key_cache_block_size‘;mysql> show global status like ‘key%‘; +------------------------+------------+ | Variable_name | Value | +------------------------+------------+ | Key_blocks_not_flushed | 0 |索引缓存内已经更改，但还没有清空到硬盘上的索引的数据块数量。 | Key_blocks_unused | 0 | 索引缓存内未使用的块数量。你可以使用该值来确定使用了多少键缓存 | Key_blocks_used | 53585 | 索引缓存内使用的块数量。该值为高水平线标记，说明已经同时最多使用了多少块。 | Key_read_requests | 4952122733 | 一共有XXX个索引读取请求， | Key_reads | 11879 | 索引读取请求在内存中没有找到，直接从硬盘读取索引 | Key_write_requests | 10508455 | 将索引的数据块写入缓存的请求数。 | Key_writes | 6042774 | 将索引向硬盘写入数据块的物理写操作的次数。 +------------------------+------------+

　　

Proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better
If the key_reads is too large, then the MY.CNF should be key_buffer_size larger. You can calculate the cache failure rate with key_reads/key_read_requests
Key_writes/key_write_requests: The ratio is close to 1 better

The experience of others is that there is a server around 4GB This parameter can be set to 384M or 512M. You can figure out the index file size of your own database. Note: This parameter value setting is too large to be the overall efficiency of the server down!

Cache Hit Ratio:

?

1	1 - Key_reads / Key_read_requests

The usage of Key buffer

?

1	100 – ( (Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size )