By Huaxiong Song, from the ApsaraDB RDS for MySQL kernel team
Part I and Part II of the introduction to MySQL memory allocation and management introduce the memory allocation, usage, and management of MySQL. In practice, it is important to control memory usage and reduce the risk of OOM. Part III will interpret the MySQL memory limit feature. The code is based on version 8.0.28.
Four new variables are added to the new memory limit feature. The following table describes the meanings and values of the variables.
variable_name | Description |
global_connection_memory_limit | The memory limit for global connections. [1, 18446744073709551615] |
connection_memory_limit | The memory limit for a single connection. [1, 18446744073709551615] |
connection_memory_chunk_size | The minimum change unit of the memory statistics, which is used to control the update frequency. [1, 10241024512]. Default value: 8912. |
global_connection_memory_tracking | The switch to control the enabling and tracing of global memory counters. |
MySQL [(none)]> show variables where variable_name in ('global_connection_memory_limit', 'connection_memory_limit', 'connection_memory_chunk_size', 'global_connection_memory_tracking'); # The memory limit variables.
+-----------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------+----------------------+
| connection_memory_chunk_size | 8912 |
| connection_memory_limit | 18446744073709551615 |
| global_connection_memory_limit | 18446744073709551615 |
| global_connection_memory_tracking | OFF |
+-----------------------------------+----------------------+
MySQL [(none)]> show status like "Global_connection_memory"; # The memory usage.
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Global_connection_memory | 0 |
+--------------------------+-------+
This feature changes some existing data structures, including adding a member object related to memory usage statistics and modifying PFS_thread, PSI_thread_service_v5, and THD.
class PFS_thread {
...
+ THD *m_cnt_thd // THD for updating memory counters.
...
};
// ------------------------------------------------------------- //
struct PSI_thread_service_v5 {
...
+ set_mem_cnt_THD_v1_t set_mem_cnt_THD;
};
typedef void (*set_mem_cnt_THD_v1_t)(THD *thd, THD **backup_thd);
// ------------------------------------------------------------- //
class THD {
...
+ Thd_mem_cnt *mem_cnt; // The memory counter object.
+ bool enable_mem_cnt(); // Enable the memory counter.
+ void disable_mem_cnt(); // Close the memory counter.
...
};
// Create a Thd_mem_cnt_conn.
thd_prepare_connection()
| thd->enable_mem_cnt() {
| | Thd_mem_cnt *tmp_mem_cnt = new Thd_mem_cnt_conn(this);
| | mem_cnt = tmp_mem_cnt;
| }
// Release the Thd_mem_cnt_conn.
~THD()
| THD::release_resources()
| | disable_mem_cnt() {
| | | mem_cnt->flush(); // Clear the memory count information of the current THD and deduct the corresponding global data.
| | | delete mem_cnt;
| | }
As mentioned earlier, the memory counter object Thd_mem_cnt is the most important data structure introduced in WL. It is responsible for saving and updating related memory usage information. In the process of memory limitation, it is its subclass Thd_mem_cnt_conn that plays a role. The following is a further introduction to Thd_mem_cnt_conn.
Before introducing Thd_mem_cnt_conn, we first need to know the introduced counting mode. The counting operation and error handling under different modes are different. The combination of multiple modes can be realized by bitwise operation.
enum enum_mem_cnt_mode {
MEM_CNT_DEFAULT = 0U, // Do not count.
MEM_CNT_UPDATE_GLOBAL_COUNTER = (1U << 0), // The global information is updated.
MEM_CNT_GENERATE_ERROR = (1U << 1), // The OOM error message is generated.
MEM_CNT_GENERATE_LOG_ERROR = (1U << 2) // The OOM error message is generated and written to the log.
};
The key data structure of Thd_mem_cnt_conn is shown in the preceding figure. mem_count, max_conn_mem, and glob_mem_counter respectively refer to the memory requested by the connection, the maximum memory of the connection (this value is not a specified value and will change with mem_count), and the value passed by the connection to the global counter. The following figure shows the connecting and changing process of parameters. mem_count is related to the actual memory usage of the connection. glob_mem_counter grows in units, and the growth is related to the connection_memory_chunk_size parameter.
Q: Why use glob_mem_counter? Can I directly accumulate the current mem_count to the global memory counter?
A: Changes to global count information must be atomistic. Frequent changes will cause contention for global counters and locks and affect concurrency. The parameter connection_memory_chunk_size ensures that the memory data summarized to the global memory counter each time is an integer multiple of chunk_size. In other words, glob_mem_counter = connection_memory_chunk_size n, and it increases by the size of connection_memory_chunk_size m. Passing a sufficient amount of memory counts to the global counter in advance for each connection can reduce the number of changes to the global counter and avoid frequent changes to global data caused by increasing the amount of scattered memory each time. Global data is written only when mem_count > glob_mem_counter, and the glob_mem_counter is added to an integer multiple of connection_memory_chunk_size.
Therefore, the frequency of global counter updates can be controlled by connection_memory_chunk_size. When connection_memory_chunk_size is set to a large value, the memory information data aggregated to the global counter each time will be large, which will be mistaken for OOM risk and cause OOM error in advance. Therefore, the value of connection_memory_chunk_size should not be set too large. However, this value cannot be set too small, otherwise the global counter will be frequently updated.
The mode parameter is a combination of enum_mem_cnt_mode. For example, the mode of a SUPER user is MEM_CNT_UPDATE_GLOBAL_COUNTER when a connection is established, while the mode of a normal user is MEM_CNT_UPDATE_GLOBAL_COUNTER | MEM_CNT_GENERATE_ERROR | MEM_CNT_GENERATE_LOG_ERROR. This judgment bit is used during memory counting to determine whether to generate an error and kill the connection. In other words, SUPER users are not limited by the limit parameter when performing queries, but common users are affected by these parameters.
static void prepare_new_connection_state(THD *thd) {
...
thd->mem_cnt->set_orig_mode(is_admin_conn ? MEM_CNT_UPDATE_GLOBAL_COUNTER // Set the mode based on identity.
: (MEM_CNT_UPDATE_GLOBAL_COUNTER |
MEM_CNT_GENERATE_ERROR |
MEM_CNT_GENERATE_LOG_ERROR));
...
}
The feature of alloc_cnt() is to update or add the memory count information at the connection and global levels. This function is called along with memory requests and mainly involves the following operations:
bool Thd_mem_cnt_conn::alloc_cnt(size_t size) {
mem_counter += size;
max_conn_mem = std::max(max_conn_mem, mem_counter);
// The error message at the connection level.
if (mem_counter > m_thd->variables.conn_mem_limit) {
(void)generate_error(ER_DA_CONN_LIMIT, m_thd->variables.conn_mem_limit,
mem_counter);
}
// The following conditions are specified: enabling global update, enabling memory tracking, and the storage capacity exceeding the capacity prepared in advance.
if ((curr_mode & MEM_CNT_UPDATE_GLOBAL_COUNTER) &&
m_thd->variables.conn_global_mem_tracking &&
max_conn_mem > glob_mem_counter) {
// Control the update frequency of global counters.
const ulonglong curr_mem =
(max_conn_mem / m_thd->variables.conn_mem_chunk_size + 1) *
m_thd->variables.conn_mem_chunk_size;
ulonglong delta = curr_mem - glob_mem_counter;
ulonglong global_conn_mem_counter_save;
ulonglong global_conn_mem_limit_save;
{
MUTEX_LOCK(lock, &LOCK_global_conn_mem_limit);
global_conn_mem_counter += delta;
global_conn_mem_counter_save = global_conn_mem_counter;
global_conn_mem_limit_save = global_conn_mem_limit;
}
glob_mem_counter = curr_mem;
max_conn_mem = std::max(max_conn_mem, glob_mem_counter);
// The error message at the global level.
if (global_conn_mem_counter_save > global_conn_mem_limit_save) {
(void)generate_error(ER_DA_GLOBAL_CONN_LIMIT, global_conn_mem_limit_save,
global_conn_mem_counter_save);
}
}
return true;
}
Unlike the way statistics are added, this function has a single function, and only performs subtraction on mem_counters at the connection level. How can the global count information be reduced? Global data is modified in the reset() function, which is also used to reduce global resource contention. Obviously, in most cases, the global count information will lag behind the connection count information.
void Thd_mem_cnt_conn::free_cnt(size_t size) {
mem_counter -= size;
}
The free_cnt() operation only subtracts the memory count at the connection level. The global count data is updated in the reset() function, which ensures that the current global count is up-to-date. The function mainly involves the following operations:
int Thd_mem_cnt_conn::reset() {
// Reset the mode.
restore_mode();
max_conn_mem = mem_counter;
// Update the count data.
if (m_thd->variables.conn_global_mem_tracking &&
(curr_mode & MEM_CNT_UPDATE_GLOBAL_COUNTER)) {
ulonglong delta;
ulonglong global_conn_mem_counter_save;
ulonglong global_conn_mem_limit_save;
if (glob_mem_counter > mem_counter) {
delta = glob_mem_counter - mem_counter;
MUTEX_LOCK(lock, &LOCK_global_conn_mem_limit);
assert(global_conn_mem_counter >= delta);
global_conn_mem_counter -= delta;
global_conn_mem_counter_save = global_conn_mem_counter;
global_conn_mem_limit_save = global_conn_mem_limit;
} else {
delta = mem_counter - glob_mem_counter;
MUTEX_LOCK(lock, &LOCK_global_conn_mem_limit);
global_conn_mem_counter += delta;
global_conn_mem_counter_save = global_conn_mem_counter;
global_conn_mem_limit_save = global_conn_mem_limit;
}
glob_mem_counter = mem_counter;
if (is_connection_stage &&
(global_conn_mem_counter_save > global_conn_mem_limit_save))
return generate_error(ER_DA_GLOBAL_CONN_LIMIT, global_conn_mem_limit_save,
global_conn_mem_counter_save);
}
if (is_connection_stage && (mem_counter > m_thd->variables.conn_mem_limit))
return generate_error(ER_DA_CONN_LIMIT, m_thd->variables.conn_mem_limit,
mem_counter);
is_connection_stage = false;
return 0;
}
flush() clears the memory count of the current connection and deducts the global memory count. Before deleting the counter object, you must call this function to ensure that the count information is correct.
void Thd_mem_cnt_conn::flush() {
max_conn_mem = mem_counter = 0;
if (glob_mem_counter > 0) {
MUTEX_LOCK(lock, &LOCK_global_conn_mem_limit);
global_conn_mem_counter -= glob_mem_counter;
}
glob_mem_counter = 0;
}
Taking the simplest handle_connection as an example (non-thread pool model), the following figure shows the memory limit operations corresponding to the connection establishment, statement execution, and connection closure processes.
...
if (thd_prepare_connection(thd))
handler_manager->inc_aborted_connects();
else {
while (thd_connection_alive(thd)) {
if (do_command(thd)) break;
}
end_connection(thd);
}
close_connection(thd, 0, false, false);
...
Operations such as construction and destruction of the counter and update of the count information are introduced earlier. In this section, the processing logic of memory requests is described. The memory involved in connection establishment and query execution is basically performed through the my_malloc() (such as the structure data and sort buffer) and allocate_from() (temporary tables) interfaces. The corresponding release functions are my_free() and deallocate_from(). The processing logic for the counter in the two memory application manners is the same. In this section, the counter operation logic is further described by taking my_malloc() and my_free() as an example.
my_malloc() involves the following operations:
void *my_malloc(PSI_memory_key key, size_t size, myf flags) {
// Call malloc to allocate a memory block that contains the header information.
my_memory_header *mh;
size_t raw_size;
raw_size = PSI_HEADER_SIZE + size;
mh = (my_memory_header *)my_raw_malloc(raw_size, flags);
// Initialize the data structure of the header and call the pfs_memory_alloc_vc to assign a value to head->key.
if (likely(mh != nullptr)) {
void *user_ptr;
mh->m_magic = PSI_MEMORY_MAGIC;
mh->m_size = size;
// Call the service.
mh->m_key = PSI_MEMORY_CALL(memory_alloc)(key, raw_size, &mh->m_owner);
user_ptr = HEADER_TO_USER(mh);
MEM_MALLOCLIKE_BLOCK(user_ptr, size, 0, (flags & MY_ZEROFILL));
return user_ptr;
}
return nullptr;
}
This function is the entry for adding count data. It involves the following work:
PSI_memory_key pfs_memory_alloc_vc(PSI_memory_key key, size_t size,
PSI_thread **owner) {
// Obtain the PFS_memory_class corresponding to the key.
PSI_memory_key result_key = key;
...
PFS_memory_class *klass = find_memory_class(key);
// Enable the thread monitoring dimension and non-global monitoring mode.
if (flag_thread_instrumentation && !klass->is_global()) {
PFS_thread *pfs_thread = my_thread_get_THR_PFS();
// Determine whether to enable the counter during the PFS_memory_class initialization phase.
if (klass->has_memory_cnt()) {
if (pfs_thread->m_cnt_thd != nullptr && pfs_thread->mem_cnt_alloc(size)) // The entry for adding memory information.
// Flag bit to mark whether the key has been processed by the counter.
result_key |= PSI_MEM_CNT_BIT;
}
// Update statistics.
...
*owner_thread = pfs_thread;
} else {
// Update statistics.
...
*owner_thread = nullptr;
}
return result_key;
}
Contrary to the preceding two functions, my_free() first calls pfs_memory_free_vc() to release the key, including the deduction and update of the counter information, and then releases the entire memory area, including the header.
Q: Which memory will be counted by the counter?
A: In psi_memory_key.cc, the PSI_FLAG_MEM_COLLECT flag bit introduced by the new feature labels the memory that needs to be limited in the all_server_memory array.
create user RDS_test identified by 'RDS_test';
grant select on test.* to RDS_test;
use test;
create table t(id int primary key, c longtext);
insert t values (1, lpad('RDS', 6000000, 'test'));
set global connection_memory_limit=1024 * 1024 * 2;
MySQL [test]> show variables like "connection_memory_limit";
+-----------------------------------+--------------+
| Variable_name | Value |
+-----------------------------------+--------------+
| connection_memory_limit | 2097152 |
+------------------------------------+-------------+
MySQL [test]> select count(c) from t group by c;
ERROR 4082 (HY000): Connection closed. Connection memory limit 2097152 bytes exceeded. Consumed 7079568 bytes.
MySQL [test]> show variables like "connection_memory_limit";
+----------------------------------+------------+
| Variable_name | Value |
+----------------------------------+------------+
| connection_memory_limit | 2097152 |
+----------------------------------+-------------+
MySQL [test]> select count(c) from t group by c;
+----------+
| count(c) |
+----------+
| 1 |
+----------+
After introducing this feature, the memory usage of common users is limited. When exceeding the limit value, the connection is directly killed. However, the memory usage of SUPER users is still not limited.
The new memory limit feature brought by MySQL 8.0.28 can be summarized as follows:
• For SUPER users and common users, the memory limit differs. The former is not limited and may face OOM errors.
• Global data updates lag behind connection data updates, reducing contention for global locks. During the memory statistics increase phase, connection_memory_chunk_size controls the update frequency. During the memory statistics decrease phase, connection and global information update through free_cnt() and reset(), respectively.
• If a large connection_memory_chunk_size is set, it may easily report an OOM error in advance and, therefore, kill the connection. A small connection_memory_chunk_size may lead to frequent access to the global lock.
• Memory statistics and throttling operations rely on PFS_thread, and counter data updates first pass through this object.
Combining Part I and Part II, it is easy to see that InnoDB's memory is mostly controllable. Most of the memory is controlled by a specified size, and additional memory generated can also be roughly inferred. During MySQL service, many memory losses occur that cannot be accurately measured. If they are not well-controlled, OOM errors may occur. The official introduction of memory usage limits at the connection and global levels provides optimization to reduce OOM risks. However, the OOM issue cannot be entirely avoided and requires further optimization. In the latest MySQL 8.0.31 release notes, the official added some monitoring information to the memory limit. It is believed that new features and improvements will be introduced in the future, and the RDS MySQL kernel team will continue to optimize the OOM problem.
Additionally, other MySQL structures, such as net_buffer, join_buffer, and sort_buffer, occupy a significant amount of memory during operation. Moreover, many temporary memories for recovery and initialization are generated during server startup, requiring further research and discussion.
New Features of PolarDB-X HTAP: Clustered Columnar Index (CCI)
ApsaraDB - April 1, 2024
ApsaraDB - April 1, 2024
ApsaraDB - February 22, 2022
ApsaraDB - February 21, 2023
Alibaba Cloud Community - June 3, 2024
Alibaba Cloud Community - December 4, 2023
An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilities
Learn MoreAn on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreTair is a Redis-compatible in-memory database service that provides a variety of data structures and enterprise-level capabilities.
Learn MoreAlibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn MoreMore Posts by ApsaraDB