• 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.
• BINLOG_FORMAT=ROW.

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.

Summary:

• 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.

Possible causes

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!