MySQL using all RAM

Question

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?

Rick James · Answer 1 · 2019-04-23T22:37:36.460

One big problem: On a 1GB machine, innodb_buffer_pool_size = 768MB is much too big. It is leading to swapping (terrible for performance) or OOM (even worse). 200M might be OK.

Some amount of space is used for each connection; I would not set max_connections higher than 20.

What other settings have you raised? If anything, you should lower settings -- MySQL, out of the box, is not designed for a tiny 1GB machine (or VM).

And no more than 1 for innodb_buffer_pool_instances.

Make the changes in the [mysqld] section of my.cnf and restart (if it has not crashed again).

RolandoMySQLDBA · Answer 2 · 2019-06-05T14:11:30.467

This problem could have been solved without reconfiguring: Recreate the mysqldump using --skip-extended-insert. This will cause each row in the dump to the to be inserted one-by-one.

This will control the amount of memory dedicated to doing inserts without expanding MySQL Packets.

I once did this at my old job with a bare-metal PC (really old job) that had 1 GB RAM. I had to load a MySQL database on a Windows Machine. It would crash repeatedly because of doing bulk inserts as mysqldumps usually do. I had to recreate the dump with --skip-extended-insert. Then, I was able to load the database without crashing.

There were two downsides to this:

The dump was 10 times bigger (This of INSERT INTO for every row written as text)
It longer 4 times longer to load

Other than a bigger VM, redoing the mysqldump is the best option.

I have suggested this before

Jul 10, 2015 : Windows crashes on doing sql dump
Apr 15, 2015 : mysqldump Error 2013
Feb 13, 2015 : When does creating a .sql backup with mysqldump become too large?

Md Haidar Ali Khan · Answer 3 · 2019-04-14T11:53:03.213

MySQL using all RAM

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 on 32bit systems you might be limited to 2-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 to 8G, and innodb_buffer_pool_instances is set to 16. innodb_buffer_pool_chunk_size is 128M, which is the default value.

8G is a valid innodb_buffer_pool_size value because 8G is a multiple of innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M, which is 2G.

shell> mysqld --innodb-buffer-pool-size=8G --innodb-buffer-pool-instances=16

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           8.000000000000 |
+------------------------------------------+

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 is 128MB (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).

Important: Care should be taken when changing innodb_buffer_pool_chunk_size, as changing this value can automatically increase the size of the buffer pool. Before changing innodb_buffer_pool_chunk_size, calculate the effect it will have on innodb_buffer_pool_size to ensure that the resulting buffer pool size is acceptable.

For further your ref here

MySQL using all RAM

3 Answers3