- The original content of GreatSQL community cannot be used without authorization. Please contact Xiaobian and indicate the source for reprint.
Question 1, is there a recommended reference for the best configuration of MGR operation
At the sharing meeting of Guangzhou station of "3306 π" community on May 22, Lou Shuai, CTO of Wanli database, gave his recommended configuration reference. Let's take a look at the following:
group_replication_single_primary_mode=ON log_error_verbosity=3 group_replication_bootstrap_group=OFF group_replication_transaction_size_limit=<The default value is 150 MB，But it is recommended to lower it to 20 MB Within, do not use large transactions> group_replication_communication_max_message_size=10M group_replication_flow_control_mode=OFF #The official version of the flow control mechanism is unreasonable. In fact, it can be considered to be closed group_replication_exit_state_action=READ_ONLY group_replication_member_expel_timeout=5 #If the network environment is bad, it can be raised appropriately
In addition, other suggestions for using MGR are:
Just use the InnoDB table.
Each table must have a primary key.
The number of nodes is odd.
Ensure network reliability, low delay environment, and do not deploy across cities (generally, it is recommended that the network delay be less than 1ms).
Use single master mode.
More about the best use of MGR, you can focus on the "3306 Pi" community pai3306 (official account) to get Lou Shuai's share.
Question 2: which monitoring and collection indicators are recommended to be enabled in MySQL Performance Schema (except the indicators that are automatically enabled by default)
Let me start with my opinion: it is generally recommended to only turn on the lock to monitor the relevant monitoring indicators.
# Start MDL monitoring indicator mysql> CALL sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl'); # Turn on all Lock related monitoring indicators mysql> CALL sys.ps_setup_enable_instrument('%lock%');
Other monitoring indicators, such as Memory, Statement and Transaction, need to be enabled temporarily. Since since MySQL 5.7, PFS supports online dynamic opening and closing, it is not recommended to open it all at once if it is not necessary.
Generally speaking, if the monitoring indicators in PFS are fully opened, the impact on performance is generally about 5%, and the memory consumption is about 1G. The overall performance is controllable.
The known problem is that in the Percona branch version, if PFS and thread pool are turned on at the same time, OOM is easy to occur.
It can be fully opened if necessary.
Limited impact on performance.
However, it is recommended to only unlock those related to monitoring.
Question 3: how to troubleshoot the mysqld process that occupies too much memory
In an extreme case, InnoDB_ buffer_ pool_ The size value is only set to 2GB, but the mysqld process occupies 25GB of memory.
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 45305 mysql 20 0 28.4g 25g 8400 S 48.5 81.4 64:46.82 mysqld
Later, there will be a special article to introduce the detailed analysis and troubleshooting process. Here, we will directly talk about the possible causes and solutions.
1. The session level memory buffer parameter is set too high, and the number of connections is also set too high, for example
read_buffer_size = 64M read_rnd_buffer_size = 32M sort_buffer_size = 64M join_buffer_size = 64M tmp_table_size = 1G max_heap_table_size = 1G max_connections=2000
When the number of connections is small, the memory consumption is not much.
However, when encountering burst traffic, the number of possible concurrent connections will be close to full. In addition, inefficient SQL that may generate temporary tables and additional sorting occurs frequently, which is easy to lead to rapid growth of memory occupation.
Therefore, it is recommended to lower the session level buffer parameter value and effectively control the number of concurrent connections. The following is a general setting value reference:
read_buffer_size = 4M read_rnd_buffer_size = 4M sort_buffer_size = 4M join_buffer_size = 4M tmp_table_size = 32M max_heap_table_size = 32M max_connections = 512
2. Too many detection indicators are enabled in PFS, resulting in excessive memory consumption.
As mentioned above, about 1GB of memory may be required after all PFS is turned on. However, in the case of high concurrency and frequent inefficient SQL, more memory may be consumed.
3. The MyISAM engine may also be used, and the key_ buffer_ The size setting is too large.
But now MyISAM engine is generally used less.
4. Program memory leak risk.
You can use the valgrind tool to check whether this problem exists. If so, you can consider upgrading the MySQL version, or restarting the mysqld instance regularly during maintenance time, or restarting the risky instance through high availability switching.
5. glibc's memory manager itself is defective.
In short, after the memory applied by calling glibc is used, it is returned to the OS without being recycled normally, but becomes fragments. With the continuous growth of fragments, we can see that the memory occupied by mysqld process is increasing. At this time, we can call the function to actively recycle and release these fragments.
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 45305 mysql 20 0 28.4g 25g 8400 S 48.5 81.4 64:46.82 mysqld [root@mysql#] gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)' PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 45305 mysql 20 0 28.4g 5.2g 8288 S 2.7 17.0 64:56.82 mysqld
This is like the practice of actively implementing OPTIMIZE TABLE to rebuild the table after too many fragments are generated in the InnoDB table.
Enjoy MySQL 😃
This article is composed of blog one article multi posting platform OpenWrite release!