The words written in the front
Whether as an operation and maintenance or as a DBA, our job is not to write SQL and engage in business . More is how to achieve a good and fast way to provide a database environment and ensure data security for development. In the previous article, the separation of reading and writing, high availability, indexing, and sub-database sub-table are all similar to changing the business structure to improve the performance and stability of the database. This chapter introduces how to directly improve the performance of the server at the lowest cost by modifying the my.cnf configuration file.
About MySQL optimization
Optimization criteria:< /p>
1. Optimization is risky, and configuration needs to be cautious.
2. Optimization is never a single person’s problem, it requires development, operation and maintenance, and DBA to intervene together.
3. Stability is more important than performance. Optimization without significant improvement will fail.
4. The solution of one problem is often accompanied by the emergence of another problem, and optimization is not always good.
The direction of optimization: safety and performance
The scope of optimization:
1. Hardware and System: such as machine configuration, network, system optimization, etc.
2. Business program optimization: such as indexes, locks, SQL performance, etc.
3. Database optimization: such as database architecture, database configuration parameters, etc.
Operating system level optimization
1. CPU Usage: top command –> press 1
I chose 4 cores here, so you can see 4 CPUs.
Main indicators:
us: use, the CPU occupied by the user program is running The ideal ratio is 70%, which is the best way to make full use of the equipment’s performance.
sy: sys, system resources, general kernel calls, this high may be BUG, poisoning or The database is locked.
id: idle, the percentage of free time.
wa: wait, waiting time, high reasons may be lock, IO, index problems, etc.
Some CPU in MySQL is busy and others are idle. It may be a problem with MySQL concurrency parameters.
2. Memory/Swap usage:
total: total / free: free / userd: use / buffer/cache: buffer cache
For MySQL, it is not recommended to configure the swap partition , The recommended memory configuration is 70%-80% of the host memory.
In the Linux default buffer recovery strategy, you can check how much memory is used and start using swap:
cat /proc/sys/vm/swappiness
Result:
< /p>
This means that when the memory reaches 70%, the system will use Swap instead of immediately recycling buffer/cache.
So in MySQL, we choose to close Swap or even not configure the Swap partition.
# Configuration
echo 0 >/proc/sys/vm/swappiness
echo 'vm.swappiness = 0' >>/etc/sysctl.conf
# effective
sysctl -p
This parameter can only represent the higher weight of the system to choose to release and recycle the cache, and cannot avoid the use of swap.
When innodb_flush_method is set to O_DIRECT, the buffer pool will bypass the cache to access the disk.
3. IO usage:
Method of testing host IO:
【1】dd command:
< div class="code">
dd if=/dev/zero of=/tmp/bigfile bs=1M count=4096
< /div>
Result:
【2】iostat command:
yum -y install sysstat
# View
iostat -dm 1
Result:
You can dynamically view the read and write status!
It is normal for CPU / IO to be high.
High CPU and low IO may be performing functions, sorting, grouping and other non-addition, deletion, and modification operations.
High CPU wa / sy, low IO may be due to IO problem or database lock.
IOPS: When purchasing cloud server disks, different disks can often see an IOPS The parameter represents the number of reads and writes that the disk can execute per second, and is the upper limit of the hardware.
Optimize scheduling strategy: CentOS 7 default deadline
# CentOS 6 optimization
echo deadline >/sys/block/sda/queue/scheduler
vi /boot/grub/grub.conf
# Change to the following:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet
In addition, the system recommends using ext4 or xfs, not lvm. Finally, use better hardware.
Host hardware level optimization
1. Equipment supply Quotient optimization. Choose products from major manufacturers, such as DELL Huawei, IBM, etc. instead of pheasant products.
2. The cloud product is Ali’s RDS / DRDS.
3. CPU selection: CPU-intensive, that is, more computing, you can use I series CPU, IO-intensive, high concurrency can choose Xeon series CPU.
4. Memory configuration: The general recommendation is 2-3 times the number of CPU cores.
5. Disk selection (IOPS order): SAS (max 900G, generally 600) / FC / SSD (SATA) / PCI-E / SSD / Flash
6. Turn off RAID The BBU (Battery Backup Unit) of the card.
7. RAID selection: R0 has high performance, but not safe, R1 is safe, R5 has high read performance, low write performance, R10 has high security and performance, and uses high IO.
8. Network: Select the hardware, double network ports for network card binding, and switch stacking.
MySQL parameter optimization
First create a 10 million Data test table:
create database testdb1 charset utf8mb4 collate utf8mb4_bin;
use testdb1;
create procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1 +floor(rand()*61), 1),substring(str,1+floor(rand() *61),1< /span>));
set str4=concat(substring(str,1 +floor(rand()*61), 2),substring(str,1+floor(rand() *61),2< /span>));
set i=i+1;
insert into t_100w values (i,floor(rand() *num),str2,str4,now());
end while;
end;
//
delimiter;
-- Generate data
call rand_data(10000000);
commit;
1. Maximum number of connections: max_connections
show variables like "%max_connections%";
Result:
< p>MySQL will allocate memory for each connection, so this number is not arbitrarily set, and the performance of a server is also a bottleneck.
It can be adjusted according to the maximum number of connections used during peak business periods: Max_used_connections
< div class="code">
show status like "Max_used_connections";
Result:
Finally modify my.cnf, you can set one at the beginning Relatively large value, adjust later.
There is also another parameter: back_log
show variables like "back_log";
Result:
< img alt="Share pictures" src="/wp-content/uploads/images/database/mysql/1626816522953.png" >
The function of this parameter is that when the number of requests reaches max_connections, we allow The number of queued connections. If the queue exceeds this value, the request is rejected.
You can check through show processlist;, if there are many sleep requests, you need to increase this value or increase the maximum number of connections.
2. Timeout: wait_timeout and interactive_timeout
show variables like "%timeout%";
Result:
wait_timeout: MySQL is closing a Non-interactive The time to wait before connecting. For example, the service connects to the database. It is not recommended to set too long, which will cause slow connection opening, and there are many connections during show processlist.
interactive_timeout: MySQL is closing a InteractiveThe time to wait before connecting. For example, our terminal connects to the database.
If no operation is performed at the set time, the connection will be closed.
wait_timeout=60
interactive_timeout=1200
3. Index buffer size: key_buffer_size
show variables like "key_buffer_size";
Result:
This value specifies the size of the index buffer and determines the index processing speed. Especially the speed of index reading. Mainly for MyISAM table indexes and temporary tables (jion union subqueries, etc.).
Parameter setting basis:
show status like "key_read%";
Result:
< p>
The result means: a total of 10 index read requests, There are 5 requests that are not found in the memory and choose to read them directly from the disk.
Although this value is only valid for MyISAM, the temporary tables inside the system will also be used.
show status like "created_tmp%< /span>";
Result:
We usually use: Created_tmp_disk_tables / ( Created_tmp_tables + Created_tmp_disk_tables)
Calculating the utilization of temporary tables, the result is recommended to be 5%-10%, so this means that we want Created_tmp_disk_tables Keep it as small as possible.
This formula does not need to be paid attention to when mysqldump, the configuration method:
< /p>
4. Query cache: query_cache_size
show variables like "query_cache_size" ;
Result:
The function of this value is that when the first query is over, SQL will be hashed into an ID, and stored in the cache together with the query result, when the same query comes again next time At that time, the cache can be used directly.
If there is no such ID in the cache, then go to the database to perform the query.
The value is set according to:
show status like "%Qcache%";
Result:
If Qcache_hits / (Qcache_inserts + Qcache_not_cached + Qcache_hits) The value is too low, indicating that there is actually no need for caching. Because the hit rate is low. Caching is meaningless. If you still need to cache, it is recommended to use redis and so on.
At the same time, you can check whether the memory is enough through the result.
Check the specific configuration:
show variables like '%query_cache%';
Result:
by query_cache_type It can be seen that this feature is not enabled in MySQL 5.7, which is not recommended.
query_cache_limit: Queries exceeding this size will not be cached.
query_cache_min_res_unit: The default is 4K, a large value is good for large queries, but small queries waste resources.
query_cache_size: query cache size.
query_cache_type: cache type, 0 is off, no cache. 1 Cache all, unless SQL_NO_CACHE is specifically specified in SQL. 2 Only cache the specified SQL_CACHE.
Configuration method:
query_cache_size = 128M
query_cache_type = 1
5. Maximum connection error: max_connect_errors
This value is a security parameter to prevent others from cracking the password by brute force. When the number of times reaches the specified number of errors, the host will be denied access.
You can only visit again until MySQL restarts or flush hosts. Configuration method:
max_connect_errors = 2000
6. Sort buffer size: sort_buffer_size
show variables like "sort_buffer_size";
Result:
This value defines each order (order by / group by / union / distinct) The thread needs the buffer size to speed up the query.
But the value is not larger or smaller. The value x maximum number of connections is equal to the memory that will be consumed, too large will cause the memory to run out. Configuration method:
7. The maximum packet size that the server can accept: max_allowed_packet
show variables like "max_allowed_packet";
Result:
Sometimes large inserts will be affected by this value, as mentioned above inserting 10 million data. Setting method:
8. Set the size of the associated query cache: join_buffer_size
show variables like "join_buffer_size";
Result:
Similar to sort_buffer_size, but this is to set join related queries. You should try to build indexes on the associated columns to optimize SQL.
9. Server thread cache: thread_cache_size
show variables like "thread_cache_size";
Result:
This value is used to configure the number of server threads that can be reused in the cache. When the connection is disconnected, it is not immediately reclaimed, but stored in the cache, waiting for the next connection.
If it does not exist in the cache or when it is a new request, a new thread is created. If there are many database threads, increasing this value can improve performance.
Configuration basis: View the number of connection threads:
show status like ' threads_%';
Result:< /p>
Threads_cached: Threads that are idle in the cache at the moment quantity.
Threads_connected: Represents the number of threads currently established.
Threads_created: Represents the number of threads currently created. If the value is too large, it means that the system creates more threads and consumes more CPU and system resources. You can increase thread_cache_size.
Threads_running: represents the number of threads in the active state, not the total number of threads, because there are still sleep.
Configuration method:
10. Specify innodb Cache size: innodb_buffer_pool_size
show variables like "innodb_buffer_pool_size";
Result:
The function of this value is similar to key_buffer_size, but this one is for innodb, and the other is MyISAM. Used to specify the memory size of the buffer and index.
This value is generally set to about 80% of the server memory. Configuration method:
innodb_buffer_pool_size = 2048M
11. Time to flush log to disk: innodb_flush_log_at_trx_commit
show variables like "innodb_flush_log_at_trx_commit";
Result:
Double One The standard one is used to control the point in time when innodb writes the data in the log buffer to the log and flushes it to the disk.
0: The transaction is committed, no write operation is performed, but it is automatically written once per second.
1: Automatic log write and write to disk every time you submit.
2: Write the log immediately after each submission, but write the data to the disk only once every second.
This value greatly affects data insertion. When inserting 1000 pieces of data at a time, 0 may take 1 second, 2 may take 2 seconds, and 1 may take hundreds of seconds.
So MySQL recommends combining multiple inserts into one transaction to operate, which can increase the speed. When a large amount of data is imported, this configuration can be changed to increase the import speed.
Of course, there is a certain risk in this, but we can verify the integrity when importing the data, so don’t worry. Configuration method:
innodb_flush_log_at_trx_commit = 1
12. Number of concurrent threads in innodb: innodb_thread_concurrency
show variables like "innodb_thread_concurrency";
Result:
This parameter is used to set the number of concurrent innodb threads, 0 means no limit.
The official recommendation is that when the number of concurrent users is less than 64, it is recommended to set it to 0. When the workload is high, it is recommended to set it to 128 first, and then slowly adjust it according to the actual situation.
Setting standards:
show status like 'threads_%';
and top and other view threads, CPU usage Situation, and then adjust the value.
Use top to view the usage of each CPU. If the allocation is uneven, you can set the value to the number of CPUs, and then double the test. Know the fit.
Configuration method:
innodb_thread_concurrency = 8
13. Log cache Size: innodb_log_buffer_size
show variables like "innodb_log_buffer_size";
Result:
A larger buffer can improve performance. For larger transactions, The cache size can be increased. Setting method:
innodb_log_buffer_size = 128M
14. The size of the data log file: innodb_log_file_size
show variables like "innodb_log_file_size";
< p>Result:
That is the definition The size of a single ib_logfile0 file. Larger files help improve performance. Configuration method:
innodb_log_file_size = 100M
15. Number of data log files: innodb_log_files_in_group
show variables like "innodb_log_files_in_group";
< p>Result:
The default is ib_logfile0 And ib_logfile1 can be increased appropriately to improve the writing performance. Configuration method:
innodb_log_files_in_group = 3
16. Read buffer size: read_buffer_size
show variables like "read_buffer_size";
Result:
This configuration is exclusive to each connection, when the request for sequential scan of the table is assigned to Buffer to improve performance. If the request is very frequent, you can increase this value. Configuration method:
17. Random read buffer size: read_rnd_buffer_size
show variables like "read_rnd_buffer_size";
Result:
When reading rows in any order, the thread will allocate a random read buffer area for query , The buffer will be scanned first to avoid disk search and increase the speed. If you need to sort a large amount of data, you can increase the value appropriately.
Setting method:
read_rnd_buffer_size = 1M
18. Batch insert Buffer size: bulk_insert_buffer_size
show variables like "bulk_insert_buffer_size"; pre>
Result:
The size of the data cache is inserted in batches, which can effectively improve the insertion efficiency. Configuration method:
bulk_insert_buffer_size = 8M
19. Binary log optimization:
< div class="code">
# Binary log storage location
log-bin = /data/logs/mysql/bin-log/mysql-bin
# The memory allocated by each session is used during the transaction Cache for storing binary logs
binlog_cache_size = 2M
# The maximum cache memory size that binlog can use
max_binlog_cache_size = 8M
# Specify the size of the binlog log file
max_binlog_size = 512M
# The number of days for binary logs to be automatically deleted, the default is 0 and no automatic deletion< /span>
expire_logs_days = 7
# Format method
binlog_format = row
# Double 1 standard, when to refresh the binlog to disk, every time Transaction commit
sync_binlog = 1
20. Security optimization:
< strong>Innodb_flush_method:
When the value is fync , the data page When persisting, write to the os buffer first, and write to the disk when the os decides.
When the value is O_DIRECT , data page persistence is directly written to disk, but redo buffer persistence You need to write to the os buffer first, and then the os decides when to write to the disk.
Of course, if innodb_flush_log_at_trx_commit = 1 is set, the commit will be written directly to the disk.
The safest mode:
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
Highest performance mode:
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = fsync
Generally we prefer security, after all, it is a database (double-one standard):
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_flush_method = O_DIRECT
Optimize configuration file template
You can modify it according to your actual needs:
[mysqld]
############## ##############################################< /span>
# Basic configuration
############################# ################################
port=3306
server-id=111
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
max_connections=1024
back_log=128
################## ###########################################< span style="color: #008000;">
# Basic directory definition
############################# ################################
basedir=/data/services/mysql
datadir=/data/data/mysql
socket=/data/logs/mysql/mysql.sock
log-error=/data/logs/mysql/error-log/mysql.log
############## ##############################################< /span>
# bin log configuration
############################# ################################
binlog_format=row
log-slave-updates=1
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
log_bin=/data/logs/mysql/bin-log/mysql-bin
############## ##############################################< /span>
# slow log configuration
############################# ################################
slow_query_log=1
long_query_time=2
log_queries_not_using_indexes=1
slow_query_log_file=/data/logs/mysql/slow-log/slow.log
############## ##############################################< /span>
# relay log configuration
############################# ################################
relay_log_purge=0
relay_log=/data/logs/mysql/relay-log/relay-bin
############## ##############################################< /span>
# Optimize configuration
############################# ################################
# Non-interactive / interactive timeout period / number of connection errors
wait_timeout=60
interactive_timeout=7200
max_connect_errors=20
# MyISAM and temporary table buffer / sort buffer / associated query buffer size, etc.
key_buffer_size=16M
sort_buffer_size=2M
join_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
# Query cache
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
# Maximum data packet / thread buffer number
max_allowed_packet=32M
thread_cache_size=200
# innodb configuration
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
# The maximum time a transaction waits to obtain resources. The timeout returns a failure, the default is 50s< /span>
innodb_lock_wait_timeout = 30
# Log all deadlocks in error_log
innodb_print_all_deadlocks = 1
# Log retention time
expire_logs_days=7
[client]
port=3306
socket=/data/logs/mysql/mysql.sock
prompt=3306/\u [\d]>
Summary
The optimization parameters here are only a part of them, and it takes some learning before they can be added.
cat /proc/sys/vm/swappiness
# Configuration
echo 0 >/proc/sys/vm/swappiness
echo 'vm.swappiness = 0' >>/etc/sysctl.conf
# effective
sysctl -p
dd if=/dev/zero of=/tmp/bigfile bs=1M count=4096
yum -y install sysstat
# View
iostat -dm 1
# CentOS 6 optimization
echo deadline >/sys/block/sda/queue/scheduler
vi /boot/grub/grub.conf
# Change to the following:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet
create database testdb1 charset utf8mb4 collate utf8mb4_bin;
use testdb1;
create procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1 +floor(rand()*61), 1),substring(str,1+floor(rand() *61),1< /span>));
set str4=concat(substring(str,1 +floor(rand()*61), 2),substring(str,1+floor(rand() *61),2< /span>));
set i=i+1;
insert into t_100w values (i,floor(rand() *num),str2,str4,now());
end while;
end;
//
delimiter;
-- Generate data
call rand_data(10000000);
commit;
show variables like "%max_connections%";
show status like "Max_used_connections";
max_connections = 1024
show variables like "back_log";
back_log = 1024
show variables like "%timeout%";
wait_timeout=60
interactive_timeout=1200
show variables like "key_buffer_size";
show status like "key_read%";
show status like "created_tmp%";
key_buffer_size = 64M
show variables like "query_cache_size ";
show status like "%Qcache%";
show variables like '%query_ cache%';
query_cache_size = 128M
query_cache_type = 1
max_connect_errors = 2000
show variables like "sort_buffer_size";
sort_buffer_size = 1M
show variables like "max_allowed_packet";
max_allowed_packet = 32M
show variables like "join_buffer_size";
show variables like "< /span>thread_cache_size";
< pre>show status like 'threads_%';
thread_cache_size = 32
show variables like "innodb_buffer_pool_size";
innodb_buffer_pool_size = 2048M
< /p>
show variables like "innodb_flush_log_at_trx_commit";
innodb_flush_log_at_trx_commit = 1
show variables like "innodb_thread_concurrency";
show status like 'threads_%';
innodb_thread_concurrency = 8
show variables like "innodb_log_buffer_size span>";
innodb_log_buffer_size = 128M
show variables like "innodb_log_file_size ";
innodb_log_file_size = 100M
show variables like "innodb_log_files_in_group";
innodb_log_files_in_group = 3
show variables like "read_buffer_size";
read_buffer_size = 1M
< /p>
show variables like "read_rnd_buffer_size";
read_rnd_buffer_size = 1M
show variables like "bulk_insert_buffer_size";
bulk_insert_buffer_size = 8M
# Binary log save location
log-bin = /data/logs/mysql/bin-log/mysql-bin
# The memory allocated by each session is used during the transaction Cache for storing binary logs
binlog_cache_size = 2M
# The maximum cache memory size that binlog can use
max_binlog_cache_size = 8M
# Specify the size of the binlog log file
max_binlog_size = 512M
# The number of days for binary logs to be automatically deleted, the default is 0 and no automatic deletion< /span>
expire_logs_days = 7
# Format method
binlog_format = row
# Double 1 standard, when to refresh the binlog to disk, every time Transaction commit
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = fsync
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_flush_method = O_DIRECT
[mysqld]
############## ##############################################< /span>
# Basic configuration
############################# ################################
port=3306
server-id=111
skip-name-resolve
gtid-mode=on
enforce-gtid-consistency=true
max_connections=1024
back_log=128
################## ###########################################< span style="color: #008000;">
# Basic directory definition
############################# ################################
basedir=/data/services/mysql
datadir=/data/data/mysql
socket=/data/logs/mysql/mysql.sock
log-error=/data/logs/mysql/error-log/mysql.log
############## ##############################################< /span>
# bin log configuration
############################# ################################
binlog_format=row
log-slave-updates=1
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
log_bin=/data/logs/mysql/bin-log/mysql-bin
############## ##############################################< /span>
# slow log configuration
############################# ################################
slow_query_log=1
long_query_time=2
log_queries_not_using_indexes=1
slow_query_log_file=/data/logs/mysql/slow-log/slow.log
############## ##############################################< /span>
# relay log configuration
############################# ################################
relay_log_purge=0
relay_log=/data/logs/mysql/relay-log/relay-bin
############## ##############################################< /span>
# Optimize configuration
############################# ################################
# Non-interactive / interactive timeout period / number of connection errors
wait_timeout=60
interactive_timeout=7200
max_connect_errors=20
# MyISAM and temporary table buffer / sort buffer / associated query buffer size, etc.
key_buffer_size=16M
sort_buffer_size=2M
join_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
# Query cache
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
# Maximum data packet / thread buffer number
max_allowed_packet=32M
thread_cache_size=200
# innodb configuration
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
# The maximum time a transaction waits to obtain resources. The timeout returns a failure, the default is 50s< /span>
innodb_lock_wait_timeout = 30
# Log all deadlocks in error_log
innodb_print_all_deadlocks = 1
# Log retention time
expire_logs_days=7
[client]
port=3306
socket=/data/logs/mysql/mysql.sock
prompt=3306/\u [\d]>
