MySQL optimization under the CentOS server

Original link:

MySQL optimization under the CentOS server

First, the optimization of MySQL thinking

MySQL optimization is divided into two ways:

1. Optimization prior to server use

2. Optimization in service usage

Second, MySQL's basic optimization steps

1. Hardware-Level optimization

(1). Preferably MySQL uses a physical server on its own

(2). Memory and CPU, give MySQL server large enough memory and enough CPU cores as required

(3). Avoid using swap swap partitioning – it is slow to read from the hard disk when switching, and some DBAs do not install the swap partition when installing the system

(4). If it is the MySQL main library, the hard disk can choose a better high-speed hard disk, the system with SSD, the data disk with SAS instead of SATA hard disk, the operating system and data partition separation

(5). MySQL-generated logs and databases are also placed on different disk partitions

(6). When you format a MySQL database hard disk, you can specify a smaller hard disk block

(7). As far as RAID is concerned, the main library makes RAID10 as much as possible, which improves the reading and writing speed of data and also mentions the security of data.

(8). Two-wire dual-power server, to ensure stable server operation, not because of sudden power outage affecting the business and damage to disk data

2. mysql Database design optimization

(1). Choose the right storage engine according to your needs, such as read very hard to use Mysam, if the transactional requirements of InnoDB

(2). Set a reasonable field type and field length, for example, you have this field more than 20 fields you set to varchar (255) is a waste of disk space

(3). The default value uses not NULL as much as possible, and if there are too many null values, it can have an impact on MySQL queries, especially if the query statement is written

(4). Use the Varchar,text,blob three fields as little as possible

(5). Add appropriate index (index) [Four kinds: normal index, primary key index, unique index unique, full-text index]

(6). Do not misuse indexes, large table indexes, small tables do not index

(7). Rationalization of table design (3NF compliant)

3. Optimization of MySQL configuration parameters

Here is the mysql5.5 version of the configuration file

VI my.cnf

[Client]
Port = 3306 #mysql客户端连接时的默认端口
Socket =/tmp/mysql.sock #与mysql服务器本地通信所使用的socket文件路径

Default-character-set = UTF8 #指定默认字符集为utf8
[MySQL]
No-auto-rehash #auto-rehash is the meaning of automatic completion, as we enter commands in the Linux command line, the use of the TAB key function is the same, here is the default does not auto-complete
Default-character-set = UTF8 #指定默认字符集为utf8
[Mysqld]
user = MySQL
Port = 3306

Character-set-server = UTF8 #设置服务器端的字符编码
Socket =/tmp/mysql.sock
Basedir =/application/mysql
DataDir =/mysqldata
Skip-locking #避免MySQL的外部锁定 to reduce the chance of error and increase stability.
Open_files_limit = 10240 #MySQL打开的文件描述符限制, default minimum 1024, when Open_files_limit is not configured, compare max_connections*5 and Ulimit-n values, Which big use which, when Open_file_limit is configured, compares the value of Open_files_limit and Max_connections*5, which is big with which.
Back_log = #back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可 to be present in the stack. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have its own limitations on this queue size. Attempts to set the 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.
Max_connections = #MySQL的最大连接数, if the server has a large number of concurrent connection requests, it is recommended that this value be raised to increase the amount of concurrent connections, although this is based on the fact that the machine can support, because if the number of connections is more, the connection between MySQL will provide a slow Flush area, it will cost more memory, so adjust this value properly, you can not blindly increase the value of the set. The ' conn% ' wildcard character can be used to view the number of connections in the current state to decide the size of the value.
Max_connect_errors = #对于同一主机, if there is an interrupt error connection that exceeds the number of values for this parameter, the host will be blocked from connecting. To unblock the host, execute: FLUSH host.
Table_cache = 614 #物理内存越大, the larger the setting. Default is 2402, set to 512-1024 best
external-locking = FALSE #使用 –skip-external-locking mysql option to avoid external locking. This option is turned on by default
Max_allowed_packet =8m #设置最大包, restricting the size of packets accepted by the server, avoiding the problem of long SQL execution there is a default value of 16M, when the MySQL client or mysqld server receives a greater than max_allowed_ Packet bytes of information packets, a "packet is too large" error is emitted and the connection is closed. For some clients, if the communication packet is too large, you may encounter a "missing connection to the MySQL server" error during query execution. The default value is 16M.
Sort_buffer_size = 6M #用于表间关联缓存的大小, the size of the buffer that can be used when the query is sorted. Note: The allocated memory for this parameter is per-connection exclusive, and if there are 100 connections, then the actual allocated total sort buffer size is 100x6 = 600MB. Therefore, the recommended setting for a server that has around 4GB is 6-8m.
Join_buffer_size = 6M #联合查询操作所能使用的缓冲区大小, as with Sort_buffer_size, the allocated memory for this parameter is also exclusive for each connection.
Thread_cache_size = #服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量, when disconnected, if there is room in the cache, then the client's thread will be placed in the cache, and if the thread is requested again, the request will be read from the cache. If the cache is empty or a new request, then the thread will be recreated, and if there are many new threads, adding this value can improve system performance. By comparing the variables of the Connections and threads_created states, you can see the effect of this variable
Thread_concurrency = 8 #设置thread_concurrency的值的正确与否, has a great impact on MySQL performance, in the case of multiple CPUs (or multicore), the error setting the value of Thread_concurrency, will cause M Ysql can not take full advantage of multi-CPU (or multicore), appearing at the same moment only one CPU (or core) is working. The thread_concurrency should be set to twice times the number of CPU cores. For example, there is a dual-core CPU, then the thread_concurrency should be 4; 2 Dual-core CPUs, the value of thread_concurrency should be 8, is a key optimization parameter
Query_cache_size = 2M #指定MySQL查询缓冲区的大小, 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.
Query_cache_limit = 1M #默认是4KB, setting a large value is good for big data queries, but if your queries are small data queries, it is easy to create memory fragmentation and waste
Query_cache_min_res_unit = 2k #MySQL参数中query_cache_min_res_unit查询缓存中的块是以这个大小进行分配的, use the following formula to calculate the average size of the query cache, set the variable based on the result of the calculation , MySQL will use the query cache more efficiently, cache more queries, and reduce the waste of memory.
Default_table_type = InnoDB #默认表的引擎为InnoDB
Thread_stack = 192K #限定用于每个数据库线程的栈大小. The default setting is sufficient for most applications transaction_isolation = Read-committed #设定默认的事务隔离级别. The available levels are as follows:

read-uncommitted, read-committed, Repeatable-read, serializable,1.read uncommitted-READ UNCOMMITTED 2.READ Committe-Read submitted 3.REPEATABLE read-repeatable read 4. SERIALIZABLE-Serial
Tmp_table_size = 246M #tmp_table_size The default size is 32M. If a temporary table exceeds that size, MySQL produces an error in the form of the table tbl_name is, and if you do many advanced GROUP by queries, increase the tmp_table_size value.
Max_heap_table_size = 246M #内存表, memory tables do not support transactions, memory tables use hashed indexes to store data in memory, so it is extremely fast and suitable for caching small to medium sized databases, but with some limitations on usage
Long_query_time = 1#记录时间超过1秒的查询语句
Log_long_format #
Log-error =/logs/error.log #开启mysql错误日志, this option specifies the location where the error log file mysqld saved
Log-slow-queries =/logs/slow.log #慢查询日志文件路径
Pid-file =/pids/mysql.pid
Log-bin =/binlog/mysql-bin #binlog日志位置以及binlog的名称
Relay-log =/relaylog/relay-bin #relaylog日志位置以名称
Binlog_cache_size = 1M #binlog_cache_size is two points: a transaction, when no commit (uncommitted), the resulting log, recorded in the cache; Wait until the transaction commits (committed When a commit is required, the log is persisted to disk, and the default is 32K.
Max_binlog_cache_size = 32M #binlog缓存最大使用的内存
Max_binlog_size = 2M #一个binlog日志的大小
Expire_logs_days = 7 #保留7天的binlog
Key_buffer_size = 124M #索引缓存大小: It determines the speed of database index processing, especially the speed of index reads
Read_buffer_size = 16M #MySql读入缓冲区大小. A request to sequentially scan a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If the sequential scan requests for a table are frequent and you think that frequent scans are going too slowly, you can improve their performance by increasing the value of the variable and the size of the memory buffer
Read_rnd_buffer_size = 2M #MySQL的随机读缓冲区大小. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriate to avoid excessive memory overhead
Bulk_insert_buffer_size = 1M #批量插入数据缓存大小, can effectively improve the insertion efficiency, the default is 8M
Myisam_sort_buffer_size = 1M #MyISAM表发生变化时重新排序所需的缓冲
Myisam_max_sort_file_size = 10G #MySQL重建索引时所允许的最大临时文件的大小 (when REPAIR, ALTER TABLE or LOAD DATA INFILE). If the file size is larger than this value, the index is created (slower) by a key-valued buffer
Myisam_repair_threads = 1 #如果一个表拥有超过一个索引, MyISAM can use more than one thread to fix them by using parallel sorting. This is a good choice for users with multiple CPUs and a large memory situation.
Myisam_recover #自动检查和修复没有适当关闭的 MyISAM Table
Lower_case_table_names = 1 #让mysql不区分大小写
Skip-name-resolve #禁用DNS解析, the connection speed will be much faster. However, it is not possible to use the hostname in the MySQL authorization table and only use the IP format.
#slave-skip-errors = 1032,1062 #这是选填项让slave库跳过哪些错误继续同步
#replicate-ignore-db=mysql #选填, which database is not synchronized when synchronizing settings
Server-id = 1

Innodb_additional_mem_pool_size = 4M #InnoDB The memory pool size of the stored data directory information and other internal data structures. The more tables in the application, the more memory you need to allocate here, the default is 2M
Innodb_buffer_pool_size = 2048M #这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默 认的 key_buffer_size 设置下运行的可以，然而Innodb在默认的 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了
Innodb_file_io_threads = 4 #文件IO的线程数，一般为 4
Innodb_thread_concurrency = 8 #你的服务器CPU有几个就设置为几, recommended by default is generally 8
Innodb_flush_log_at_trx_commit = 2 #默认为1,如果将此参数设置为1，将在每次提交事务后将日志写入磁盘。为提供性能，可以设置为0或2，但要承担在发生故障时丢失数据的风险。设置为0表示事务日志写入日志文件，而日志文件每秒刷新到磁盘一次。设置为2表示事务日志将在提交时写入日志，但日志文件每次刷新到磁盘一次。
Innodb_log_buffer_size = 2M #此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据.MySQL开发人员建议设置为1－8M之间
Innodb_log_file_size = 4M #此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间
Innodb_log_files_in_group = 3 #为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3M
innodb_max_dirty_pages_pct = 90 #Buffer_Pool中Dirty_Page所占的数量，直接影响InnoDB的关闭时间。参数 innodb_max_dirty_pages_pct可以直接控制了Dirty_Page在Buffer_Pool中所占的比率，而且幸运的是 innodb_max_dirty_pages_pct是可以动态改变的。所以，在关闭InnoDB之前先调小，强制数据块Flush一段时间，则能够大大缩短MySQL关闭的时间。
Innodb_lock_wait_timeout = 120 #InnoDB 有其内置的死锁检测机制，能导致未完成的事务回滚。但是，如果结合InnoDB使用MyISAM的lock tables 语句或第三方事务引擎,则InnoDB无法识别死锁。为消除这种可能性，可以将innodb_lock_wait_timeout设置为一个整数值，指示 MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)
innodb_file_per_table = 0 #独享表空间（关闭）
[Mysqldump]
Quick
Max_allowed_packet = 16M

4. Architecture optimization

(1). Front-end sharing of database pressure with Memcached,redis cache

(2). Database read/write separation, load balancing

(3). Database Sub-Library sub-table

(4). Storage can take a distributed

5. Post-optimization

Mainly is more observation, the latter is maintenance work, observe the server load is the need to add hardware, or there is a statement there is a problem ah, or parameters to be modified.

6. Query optimization (excerpt from others)

63. Use the slow query log to find slow queries.
64. Use the execution plan to determine whether the query is functioning correctly.
65. Always test your queries to see if they are running optimally – over time performance will always change.
66. Avoid using COUNT (*) on the entire table, which may lock the entire table.
67. Keep the query consistent so that subsequent similar queries can use the query cache.
68. Use GROUP by instead of distinct in appropriate cases.
69. In where, the GROUP by and ORDER BY clauses use indexed columns.
70. Keep the index simple and do not include the same column in multiple indexes.
71. Sometimes MySQL uses the wrong index, and use index for this case.
72. Check for problems with Sql_mode=strict.
73. For indexed fields where the number of records is less than 5, the use of limit at Union is not the OR.
74. To avoid the pre-update Select, use Insert on DUPLICATE key or insert IGNORE, do not use update to implement.
75. Do not use the index field and the ORDER BY clause with MAX.
76. Avoid using ORDER by RAND ().

77. LIMIT m,n can actually slow down the query in some cases, using it sparingly.
78. Use union in the WHERE clause instead of a subquery.
79. For updates (update), use SHARE mode (shared mode) to prevent exclusive locks.
80. After restarting the MySQL, remember to warm your database to make sure your data is fast in memory and query.
81. Use the Drop table,create table Delete from to remove all data from the table.
82. Minimizing data in querying the data you need, using * consumes a lot of time.
83. Consider persistent connections, not multiple connections, to reduce overhead.
84. Baseline queries, including the use of the load on the server, sometimes a simple query can affect other queries.
85. When the load increases on your server, use show processlist to view slow and problematic queries.
86. All suspicious queries that are tested in the mirrored data generated in the development environment.

MySQL optimization under the CentOS server