Mysql – Memory(buffer pool) not populating on thesql start on Percona 5.6.28

innodbmemoryMySQLnumapercona

We are testing Percona 5.6.28 at the moment, during our testing we found out even though the innodb_buffer_pool_populate is set to ON, it doesn't populate the memory(buffer pool) during mysql start.
There is the new parameter that was added in 5.6.27, innodb_numa_interleave, even with that ON the buffer pool doesn't populate on mysql.

We even tried with enabling numa at hardware level, but still same results

Memory: 512G
innodb_buffer_pool_size: 420G

We have a 420G innodb_buffer_pool_size, but following are the memory results after mysql start, it doesn't allocate the memory during mysql start

NUMA: Allocated memnodemap from d000 - d440
NUMA: Using 30 for the hash shift.
pci_bus 0000:00: on NUMA node 0
pci_bus 0000:20: on NUMA node 1
pci_bus 0000:40: on NUMA node 2
pci_bus 0000:60: on NUMA node 3
[root@db001 ~]# numactl --hardware
available: 4 nodes (0-3)
node 0 cpus: 0 1 2 3 4 5 6 7 32 33 34 35 36 37 38 39
node 0 size: 131037 MB
node 0 free: 104737 MB
node 1 cpus: 8 9 10 11 12 13 14 15 40 41 42 43 44 45 46 47
node 1 size: 131072 MB
node 1 free: 95004 MB
node 2 cpus: 16 17 18 19 20 21 22 23 48 49 50 51 52 53 54 55
node 2 size: 131072 MB
node 2 free: 115600 MB
node 3 cpus: 24 25 26 27 28 29 30 31 56 57 58 59 60 61 62 63
node 3 size: 131071 MB
node 3 free: 125262 MB
node distances:
node   0   1   2   3 
  0:  10  21  21  21 
  1:  21  10  21  21 
  2:  21  21  10  21 
  3:  21  21  21  10

Around 74G used memory, 430G free and 6G in cache

Most of the memory is free and not allocated

innodb_buffer_pool_dump_at_shutdown anf innodb_buffer_pool_load_at startup are ON

    [root@db001 ~]# grep innodb_buffer_pool /etc/my.cnf
    innodb_buffer_pool_size        = 420G
    innodb_buffer_pool_dump_at_shutdown = ON
    innodb_buffer_pool_load_at_startup  = ON
    innodb_buffer_pool_instances   = 64

Buffer pool is fully loaded at the moment because the server has been up for almost 3 days now

    root@db001 [(none)]> show global status like 'innodb_buffer_pool_%';
    | Variable_name                           | Value                                              |
    | Innodb_buffer_pool_dump_status          | not started                                      |
    | Innodb_buffer_pool_load_status          | Buffer pool(s) load completed at 160603 16:10:29 |
    | Innodb_buffer_pool_pages_data           | 27381056                                         |
    | Innodb_buffer_pool_bytes_data           | 448611221504                                     |
    | Innodb_buffer_pool_pages_dirty          | 994204                                           |
    | Innodb_buffer_pool_bytes_dirty          | 16289038336                                      |
    | Innodb_buffer_pool_pages_flushed        | 105665319                                        |
    | Innodb_buffer_pool_pages_LRU_flushed    | 0                                                |
    | Innodb_buffer_pool_pages_free           | 65522                                            |
    | Innodb_buffer_pool_pages_made_not_young | 9919036                                          |
    | Innodb_buffer_pool_pages_made_young     | 4488881                                          |
    | Innodb_buffer_pool_pages_misc           | 78478                                            |
    | Innodb_buffer_pool_pages_old            | 5453509                                          |
    | Innodb_buffer_pool_pages_total          | 27525056                                         |
    | Innodb_buffer_pool_read_ahead_rnd       | 0                                                |
    | Innodb_buffer_pool_read_ahead           | 784                                              |
    | Innodb_buffer_pool_read_ahead_evicted   | 0                                                |
    | Innodb_buffer_pool_read_requests        | 10840103331                                      |
    | Innodb_buffer_pool_reads                | 17722410                                         |
    | Innodb_buffer_pool_wait_free            | 0                                                |
    | Innodb_buffer_pool_write_requests       | 1822727016                                       |

    root@db001 [(none)]> show global status like 'uptime';
    +---------------+--------+
    | Variable_name | Value  |
    +---------------+--------+
    | Uptime        | 256907 |
    +---------------+--------+
    1 row in set (0.00 sec)

    root@db001 [(none)]> set global innodb_buffer_pool_load_now=1;
    Query OK, 0 rows affected (0.03 sec)

    root@db001 [(none)]> show global status like 'innodb_buffer_pool_%';
    +-----------------------------------------+--------------------------------------------------+
    | Variable_name                           | Value                                            |
    +-----------------------------------------+--------------------------------------------------+
    | Innodb_buffer_pool_dump_status          | not started                                       |
    | Innodb_buffer_pool_load_status          | Buffer pool(s) load completed at 160606 15:25:12 |
    | Innodb_buffer_pool_pages_data           | 27381012                                         |
    | Innodb_buffer_pool_bytes_data           | 448610500608                                     |
    | Innodb_buffer_pool_pages_dirty          | 990362                                           |
    | Innodb_buffer_pool_bytes_dirty          | 16226091008                                      |
    | Innodb_buffer_pool_pages_flushed        | 105742542                                        |
    | Innodb_buffer_pool_pages_LRU_flushed    | 0                                                |
    | Innodb_buffer_pool_pages_free           | 65522                                            |
    | Innodb_buffer_pool_pages_made_not_young | 9930674                                          |
    | Innodb_buffer_pool_pages_made_young     | 4497569                                          |
    | Innodb_buffer_pool_pages_misc           | 78522                                            |
    | Innodb_buffer_pool_pages_old            | 5453502                                          |
    | Innodb_buffer_pool_pages_total          | 27525056                                         |
    | Innodb_buffer_pool_read_ahead_rnd       | 0                                                |
    | Innodb_buffer_pool_read_ahead           | 784                                              |
    | Innodb_buffer_pool_read_ahead_evicted   | 0                                                |
    | Innodb_buffer_pool_read_requests        | 10847012782                                      |
    | Innodb_buffer_pool_reads                | 17730031                                         |
    | Innodb_buffer_pool_wait_free            | 0                                                |
    | Innodb_buffer_pool_write_requests       | 1823928823                                       |
    +-----------------------------------------+--------------------------------------------------+

My question is more on populating the buffer pool on startup, with 5.6.19 the memory used on the server stayed close to our buffer pool size(around 420G) right after mysql start and it took around 7-8 minutes to start mysql.
With 5.6.28, used memory after mysql start is close to 50G and then it starts loading with time and mysql starts in less than 30 seconds for a 420G buffer pool.

Best Answer

Amit, the problem is that this variable is set to reload a copy of the buffer pool dumped to disk during a stop/restart process. In order to achieve this you need to set innodb_buffer_pool_dump_at_shutdown to ON prior to restart your server, please check this link for further info: https://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.html Regarding NUMA configuration this has nothing to do with buffer pool fill but how memory dedicated to buffer pool is balanced between CPU nodes (this is useful only for physical servers) so in modern hardware where you have more than one physical CPU you will avoid having swapping issues by balancing large portions of memory allocation (like buffer pool normally has) between nodes. Hope this helps.

QUERY #1 : Disk Usage by Storage Engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

QUERY #2 : Disk Usage by Storage Engine

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(
FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM
(SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

I got responses 10-20 seconds. How? The client uses innodb_file_per_table=0.

I REPEAT : innodb_file_per_table=0

WHAT DO YOU DO NOW ???

Here is why I said "heartbreaking": You would have put all your InnoDB data back inside ibdata1. Would you like to know all the steps to do this ? Here we go...

STEP #1

Add these to /etc/my.cnf

[mysqld]
innodb_file_per_table = 0
innodb_fast_shutdown = 0

STEP #2

service mysql restart

STEP #3

Create a script that converts InnoDB to InnoDB

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > RemakeInnoDB.sql
cat RemakeInnoDB.sql

STEP #4

Execute the script

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} < RemakeInnoDB.sql

EPILOGUE

When everything is done, all the .ibd files will be gone. All the data and index pages will be inside ibdata1. You will find accessing the INFORMATION_SCHEMA against all InnoDB tables shockingly fast.

Here is the InnoDB Architecture

InnoDB Architecture

Note that ibdata1 (a.k.a. System Tablespace will house)

Data Pages
Index Pages
Data Dictionary
Double Write Buffer (Needed for crash recovery)
Insert Buffer (changes to secondary indexes)
Rollback Segments
Undo Space

Don't worry about putting 106G inside ibdata1. MySQL was running with innodb_file_per_table=0 before MySQL 5.5. It can still handle it.

Best Answer

Related Solutions

Mysql – Capabilities of InnoDB INSERT Performance

Mysql – Innodb buffer pool indexes versus data usage