I am trying to restore SQL dump file of around 750 MB on Ubuntu 16.04 VM with 1GB RAM and MySQL 5.7.21.
I have configured innodb_buffer_pool_size = 768MB
, innodb_buffer_pool_chunk_size = 128M
and innodb_buffer_pool_instances = 1
.
To stop killing from OOM-killer OOMScoreAdjust=-1000
is added to mysql.sqevice
file.
When restoring is started mysqld utilized all memory.
root@IA6-OA-DB-01:~# free -m
total used free shared buff/cache available
Mem: 987 925 11 0 50 19
Swap: 632 621 11
It starts to kill all processes.
root@IA6-OA-DB-01:~# mysql -uroot -proot -D OA2017 < OA2017.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
Killed
Syslog
Apr 14 13:13:35 IA6-OA-DB-01 kernel: [84990.058935] Out of memory: Kill process 24895 (mysql) score 17 or sacrifice child
Apr 14 13:13:35 IA6-OA-DB-01 kernel: [84990.477043] Out of memory: Kill process 2226 (sssd_be) score 3 or sacrifice child
Apr 14 13:13:35 IA6-OA-DB-01 kernel: [84990.747574] Out of memory: Kill process 2283 (sssd_be) score 3 or sacrifice child
Apr 14 13:13:36 IA6-OA-DB-01 kernel: [84990.903214] Out of memory: Kill process 1587 (smbd) score 3 or sacrifice child
Apr 14 13:13:36 IA6-OA-DB-01 kernel: [84990.971431] Out of memory: Kill process 1587 (smbd) score 3 or sacrifice child
Apr 14 13:13:36 IA6-OA-DB-01 kernel: [84991.208088] Out of memory: Kill process 858 (sssd) score 3 or sacrifice child
Apr 14 13:13:36 IA6-OA-DB-01 kernel: [84991.329594] Out of memory: Kill process 858 (sssd) score 3 or sacrifice child
Apr 14 13:13:38 IA6-OA-DB-01 kernel: [84993.369239] Out of memory: Kill process 858 (sssd) score 3 or sacrifice child
Apr 14 13:13:39 IA6-OA-DB-01 kernel: [84994.124215] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:13:41 IA6-OA-DB-01 kernel: [84995.928253] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:13:44 IA6-OA-DB-01 kernel: [84999.295787] Out of memory: Kill process 1587 (smbd) score 2 or sacrifice child
Apr 14 13:13:48 IA6-OA-DB-01 kernel: [85003.091554] Out of memory: Kill process 858 (sssd) score 3 or sacrifice child
Apr 14 13:13:49 IA6-OA-DB-01 kernel: [85003.498436] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:13:50 IA6-OA-DB-01 kernel: [85005.624213] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:13:55 IA6-OA-DB-01 kernel: [85007.717901] Out of memory: Kill process 858 (sssd) score 3 or sacrifice child
Apr 14 13:14:02 IA6-OA-DB-01 kernel: [85014.623037] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:14:02 IA6-OA-DB-01 kernel: [85016.130481] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:14:02 IA6-OA-DB-01 kernel: [85017.218094] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:14:09 IA6-OA-DB-01 kernel: [85024.577427] Out of memory: Kill process 858 (sssd) score 3 or sacrifice child
Apr 14 13:14:09 IA6-OA-DB-01 kernel: [85024.857977] Out of memory: Kill process 321 (systemd-journal) score 2 or sacrifice child
Apr 14 13:14:11 IA6-OA-DB-01 kernel: [85025.128881] Out of memory: Kill process 14831 (bash) score 2 or sacrifice child
Apr 14 13:14:11 IA6-OA-DB-01 kernel: [85025.173233] Out of memory: Kill process 14831 (bash) score 2 or sacrifice child
Apr 14 13:14:34 IA6-OA-DB-01 kernel: [85048.414654] Out of memory: Kill process 15546 (bash) score 2 or sacrifice child
Apr 14 13:14:34 IA6-OA-DB-01 kernel: [85048.545329] Out of memory: Kill process 1057 (nginx) score 1 or sacrifice child
Apr 14 13:14:34 IA6-OA-DB-01 kernel: [85049.430812] Out of memory: Kill process 15513 (sshd) score 1 or sacrifice child
Apr 14 13:14:46 IA6-OA-DB-01 kernel: [85060.595291] Out of memory: Kill process 1629 (sshd) score 1 or sacrifice child
Apr 14 13:14:46 IA6-OA-DB-01 kernel: [85060.884189] Out of memory: Kill process 1629 (sshd) score 1 or sacrifice child
Apr 14 13:14:46 IA6-OA-DB-01 kernel: [85061.268085] Out of memory: Kill process 1061 (nginx) score 1 or sacrifice child
Apr 14 13:14:46 IA6-OA-DB-01 kernel: [85061.470392] Out of memory: Kill process 1280 ((sd-pam)) score 1 or sacrifice child
After this VM gets rebooted.
What could be the problem? and What variables do i need to change to make it run?
Best Answer
As per MySQL documentation here Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you configure innodb_buffer_pool_size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.
innodb_buffer_pool_size
InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and row data. The bigger you set this the less disk I/O is needed to access data in tables. On a dedicated database server you may set this parameter up to
80%
of the machine physical memory size. Do not set it too large, though, because competition of the physical memory may cause paging in the operating system. Note that on32bit
systems you might be limited to2-3.5G
of user level memory per process, so do not set it too high.innodb_buffer_pool_instances
The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages.
For example,
innodb_buffer_pool_size
is set to8G
, andinnodb_buffer_pool_instances
is set to16
.innodb_buffer_pool_chunk_size
is128M
, which is the default value.8G
is a validinnodb_buffer_pool_size
value because8G
is a multiple ofinnodb_buffer_pool_instances
=16 *innodb_buffer_pool_chunk_size
=128M, which is 2G.innodb_buffer_pool_chunk_size
innodb_buffer_pool_chunk_size
defines the chunk size for InnoDB buffer pool resizing operations. The innodb_buffer_pool_size parameter is dynamic, which allows you to resize the buffer pool without restarting the server.To avoid copying all buffer pool pages during resizing operations, the operation is performed in
chunks
. By default, innodb_buffer_pool_chunk_size is128MB
(134217728 bytes). The number of pages contained in a chunk depends on the value of innodb_page_size. innodb_buffer_pool_chunk_size can be increased or decreased in units of 1MB (1048576 bytes).For further your ref here