Hi Folks,
We’re moving up from PXC8.0 to PXC8.4 and have noticed that the initial memory usage of the mysqld process in PXC8.4 seems much higher compared to, say, PXC8.0.35.
Example running on a 32GB RAM RHEL8.10 server where we allow mysql pretty much 80% of system ram for innodb_buffer_pool - these are the only innodb settings we explicitly set in my.cnf:
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 1024M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 25347M
innodb_buffer_pool_instances = 3
innodb_autoinc_lock_mode = 2
On startup in PXC8.0.35 (and with no connections or workload and essentially empty DB) the mysqld process consumes around 2.7GB RAM (as reported by top) but in PXC8.4.3 its using 22.8GB right off the bat.
We would find that, in PXC8.0.35, over time and under load the mysqld process would obviously consume up to and beyond 25GB RAM but I’m interested to know if anything obvious has changed in PXC8.4 to account for it seemingly grabbing the memory up front.
The “How MySQL Uses Memory” chapter in the official MySQL docs states that " InnoDB allocates memory for the entire buffer pool at server startup, using malloc() operations." in both the 8.0 and 8.4 variants.
Is it the case then that PXC8.0 is in the wrong here and should have been grabbing the memory straight away?
If its useful I did a pt-config-diff of the ‘innodb%’ variables from both versions:
[root@qatest1 ~]# pt-config-diff /tmp/pxc8035.cnf /tmp/pxc843.cnf
12 config differences
Variable /tmp/pxc8035.cnf /tmp/pxc843.cnf
========================= ================ ===============
innodb_adaptive_hash_i... ON OFF
innodb_buffer_pool_in_... ON OFF
innodb_change_buffering all none
innodb_doublewrite_files 6 2
innodb_doublewrite_pages 4 128
innodb_io_capacity 200 10000
innodb_io_capacity_max 2000 20000
innodb_log_buffer_size 16777216 67108864
innodb_numa_interleave OFF ON
innodb_purge_threads 4 1
innodb_use_fdatasync OFF ON
innodb_version 8.0.35-27 8.4.3-3
Thanks in advance for any pointers/wisdom.
Neil
@Neil_Billett
Yes, the buffer pool allocation should have done at startup and the same can be observed by using below PS query.
mysql> select * from memory_summary_global_by_event_name where event_name like '%buf_buf_pool%'\G;
I tried to do a small test and observed the same behaviour what you are telling. From the below outputs of both 8.4.3 vs 8.0.35 it appears the older 8.0.35 not resembling the full buffer pool memory in the RSS output.
8.4.3
mysql> select @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
| 16.000000000000 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from memory_summary_global_by_event_name where event_name like '%buf_buf_pool%'\G;
*************************** 1. row ***************************
EVENT_NAME: memory/innodb/buf_buf_pool
COUNT_ALLOC: 132
COUNT_FREE: 0
**SUM_NUMBER_OF_BYTES_ALLOC: 18115215360**
SUM_NUMBER_OF_BYTES_FREE: 0
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 132
HIGH_COUNT_USED: 132
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 18115215360
HIGH_NUMBER_OF_BYTES_USED: 18115215360
1 row in set (0.01 sec)
[root@node0 /]# ps aux --sort=-rss
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 5783 13.4 9.6 21173948 **19136464** ? Ssl 07:36 0:23 /usr/sbin/mysqld
VS
8.0.35
mysql> select * from memory_summary_global_by_event_name where event_name like '%buf_buf_pool%'\G;
*************************** 1. row ***************************
EVENT_NAME: memory/innodb/buf_buf_pool
COUNT_ALLOC: 128
COUNT_FREE: 0
**SUM_NUMBER_OF_BYTES_ALLOC: 17566269440**
SUM_NUMBER_OF_BYTES_FREE: 0
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 128
HIGH_COUNT_USED: 128
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 17566269440
HIGH_NUMBER_OF_BYTES_USED: 17566269440
1 row in set (0.01 sec)
ps aux --sort=-rss
USER PID %CPU %MEM VSZ **RSS** TTY STAT START TIME COMMAND
mysql 5689 4.1 0.8 20064892 **1734232** ? Ssl 07:57 0:16 /usr/sbin/mysqld
Just to correlate what you seeing, would it be possible for you to share the similar output from your both the environments ?
Also, is your new 8.4.3 instance is loaded with the data or its just empty at the moment ?
Hi Anil,
Thank you for the response. I can confirm that we are talking about empty instances here.
So here’s the output you requested:
PXC 8.0.35
mysql> select * from performance_schema.memory_summary_global_by_event_name where event_name like '%buf_buf_pool%'\G
*************************** 1. row ***************************
EVENT_NAME: memory/innodb/buf_buf_pool
COUNT_ALLOC: 201
COUNT_FREE: 0
SUM_NUMBER_OF_BYTES_ALLOC: 27584532480
SUM_NUMBER_OF_BYTES_FREE: 0
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 201
HIGH_COUNT_USED: 201
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 27584532480
HIGH_NUMBER_OF_BYTES_USED: 27584532480
1 row in set (0.00 sec)
[root@qatest1 ~]# ps aux --sort=-rss
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 195724 9.2 8.6 36232312 2792344 ? Ssl 09:30 0:53 /usr/sbin/mysqld --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
top output:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
195724 mysql 20 0 34.6g 2.7g 42920 S 0.0 8.6 1:01.85 mysqld
PXC 8.4.3
mysql> select * from performance_schema.memory_summary_global_by_event_name where event_name like '%buf_buf_pool%'\G
*************************** 1. row ***************************
EVENT_NAME: memory/innodb/buf_buf_pool
COUNT_ALLOC: 201
COUNT_FREE: 0
SUM_NUMBER_OF_BYTES_ALLOC: 27584532480
SUM_NUMBER_OF_BYTES_FREE: 0
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 201
HIGH_COUNT_USED: 201
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 27584532480
HIGH_NUMBER_OF_BYTES_USED: 27584532480
1 row in set (0.01 sec)
[root@qatest2 ~]# ps aux --sort=-rss
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 783334 1.0 74.8 36188928 24279804 ? Ssl 09:31 0:06 /usr/sbin/mysqld --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
top output:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
783334 mysql 20 0 34.5g 23.1g 23636 S 0.0 74.8 0:06.50 mysqld
I have carried out some further investigations and it turns out to be the new default of ON for the innodb_numa_interleave property that is making the difference.
If I add this to my.cnf in PXC8.0.43:
innodb_numa_interleave = OFF
…and restart the node, the initial memory usage comes right down to match that of PXC8.0.35:
[root@qatest2 ~]# ps aux --sort=-rss
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 788660 4.8 8.7 35758744 2828164 ? Ssl 09:44 0:01 /usr/sbin/mysqld --wsrep_start_position=a979befd-eb7f-11ef-a086-72c0b84441db:1661
top output:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
788660 mysql 20 0 34.1g 2.7g 46296 S 0.3 8.7 0:01.58 mysqld
Guess I’ve got to try and understand that property and why we might want to accept the new default now 
thanks,
Neil
@Neil_Billett
You got a point. Perhaps I should have pointed out that previously.
When the numa setting is disabled the buffer pool allocation would be less at startup but this might have an negative impact as well especially if you have a server with NUMA architecture and multiple nodes as the memory access will be imbalanced between them (this is not optimized for performance).
innodb_numa_interleave=0
When numa is enabled the memory allocation at startup could be even beyond the original buffer pool size as there is some overhead/cost in doing the buffer pool allocation at startup.
innodb_numa_interleave=1:
With the help of below command you can compare the allocations under both the numa settings for your clarity.
numactl --hardware
numastat -cm