| Bug #96197 | Unable to use innodb_buffer_pool_chunk_size > 4G with large pages | ||
|---|---|---|---|
| Submitted: | 13 Jul 2019 11:12 | Modified: | 18 Jul 2019 13:02 |
| Reporter: | Sergei Fabrichnikov | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.7.26 | OS: | Oracle Linux (kernel 4.14.35-1844.4.5.el7uek.x86_64) |
| Assigned to: | CPU Architecture: | x86 | |
[15 Jul 2019 13:16]
MySQL Verification Team
Hi Mr. Fabrichnikov, Thank you for your bug report. However, I can't repeat your results: SELECT pool_size * @@innodb_page_size / (1024 * 1024), @@innodb_buffer_pool_chunk_size / (1024 * 1024), @@innodb_buffer_pool_size/(1024 * 1024) FROM information_schema.INNODB_BUFFER_POOL_STATS; +------------------------------------------------+-------------------------------------------------+-----------------------------------------+ | pool_size * @@innodb_page_size / (1024 * 1024) | @@innodb_buffer_pool_chunk_size / (1024 * 1024) | @@innodb_buffer_pool_size/(1024 * 1024) | +------------------------------------------------+-------------------------------------------------+-----------------------------------------+ | 5120.0000 | 5120.0000 | 5120.0000 | +------------------------------------------------+-------------------------------------------------+-----------------------------------------+
[15 Jul 2019 14:06]
MySQL Verification Team
https://bugs.mysql.com/bug.php?id=94747 ?
[16 Jul 2019 1:01]
Sergei Fabrichnikov
Hello Sinisa, please confirm that you're using "large-pages" my.cnf parameter and 5.7.26 version. Also please do the following to ensure that we are doing the same: 1. Attach mysqld.log of MySQL startup to ensure that mysqld doesn't try to allocate innodb buffer pool from conventional memory. 2. Show output of the following command after mysqld startup: grep -i hugepages /proc/meminfo Thank you in advance, Sergei.
[16 Jul 2019 12:26]
MySQL Verification Team
Hi, It will take us some time to reserve a Linux server and to configure it properly for the large pages. Before we attempt that, please send us the output from all the commands described in this section of our 8.0 manual: 8.12.3.2 Enabling Large Page Support
[17 Jul 2019 4:53]
Sergei Fabrichnikov
Hello, Requested info follows: shell> echo 2630 > /proc/sys/vm/nr_hugepages shell> cat /proc/meminfo | grep -i huge HugePages_Total: 2630 HugePages_Free: 2630 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB shell> # hugetlb users group id shell> cat /proc/sys/vm/hugetlb_shm_group 27 shell> # mysql user is a member of this group shell> getent passwd 27 mysql:x:27:27::/var/lib/mysql:/bin/false shell> cat /proc/sys/kernel/shmmax 81184948224 shell> cat /proc/sys/kernel/shmall 18446744073692774399 shell> cat /etc/security/limits.d/99-mysql.conf # Remove limit on number of locked pages for mysql user mysql - memlock unlimited After mysqld startup: shell> cat /proc/meminfo | grep -i huge HugePages_Total: 2630 HugePages_Free: 2614 HugePages_Rsvd: 500 HugePages_Surp: 0 Hugepagesize: 2048 kB SELECT pool_size * @@innodb_page_size / (1024 * 1024), @@innodb_buffer_pool_chunk_size / (1024 * 1024), @@innodb_buffer_pool_size/(1024 * 1024) FROM information_schema.INNODB_BUFFER_POOL_STATS; +------------------------------------------------+-------------------------------------------------+-----------------------------------------+ | pool_size * @@innodb_page_size / (1024 * 1024) | @@innodb_buffer_pool_chunk_size / (1024 * 1024) | @@innodb_buffer_pool_size/(1024 * 1024) | +------------------------------------------------+-------------------------------------------------+-----------------------------------------+ | 1000.0625 | 5000.0000 | 5000.0000 | +------------------------------------------------+-------------------------------------------------+-----------------------------------------+
[17 Jul 2019 12:39]
MySQL Verification Team
Hi, Thank you for your feedback. Seems that report on the usage of the large pages and the actual buffer pool size are in the perfect accordance. Both show that 1 Gb of large pages are used. Hence, there is some system restriction that prohibits usage of more large pages. Hence, take a look at your error log and OS system log and see whether there is any warning on this subject. If there is none, please increase the verbosity for the error log and restart the server.
[18 Jul 2019 0:36]
Sergei Fabrichnikov
Hello Sinisa, this is duplicate of https://bugs.mysql.com/bug.php?id=94747 Regards, Sergei
[18 Jul 2019 13:02]
MySQL Verification Team
Thank you Mr. Fabrichnikov. This is a duplicate of the bug https://bugs.mysql.com/bug.php?id=94747.
Description: When I try to allocate innodb buffer pool with chunks larger than 4G it silently allocates smaller buffer pool with smaller chunks without any errors or warnings in mysqld.log. Diagnostics shows that MySQL internally calls os_mem_alloc_large to allocate each chunk from large page pool. os_mem_alloc_large aligns requested size to os_large_page_size as follows: size = ut_2pow_round(*n + (os_large_page_size - 1), os_large_page_size); Since type of os_large_page_size is uint, and macro ut_2pow_round is defined as: #define ut_2pow_round(n, m) ((n) & ~((m) - 1)) upper double word of result is zeroed. E.g. if we call os_mem_alloc_large to allocate 0x000000013ffa4000 bytes (for innodb_buffer_pool_chunk_size = 5000M), it allocates only 0x40000000 bytes. How to repeat: Set the following parameters in my.cnf and restart mysqld: innodb_buffer_pool_size = 5000M innodb_buffer_pool_instances = 1 innodb_buffer_pool_chunk_size = 5000M large-pages The following query shows discrepancy between reported innodb buffer pool size of 5000MB and actual size of 1000.0625MB: mysql> SELECT pool_size*@@innodb_page_size/1024/1024 as "real pool size (MB)", -> @@innodb_buffer_pool_chunk_size/1024/1024 as "requested chunk size (MB)", -> @@innodb_buffer_pool_size/1024/1024 as "requested pool size (MB)" -> FROM information_schema.INNODB_BUFFER_POOL_STATS\G' *************************** 1. row *************************** real pool size (MB): 1000.06250000 requested chunk size (MB): 5000.00000000 requested pool size (MB): 5000.00000000 1 row in set (0.00 sec)