[MDEV-12039] Out of memory with page compression Created: 2017-02-09  Updated: 2023-02-05  Resolved: 2021-12-06

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.2.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Igor Pashev Assignee: Marko Mäkelä
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

Linux


Issue Links:
Relates
relates to MDEV-30572 main.large_pages 'innodb' fails on ar... Closed

 Description   

When importing a dump with page compression enabled for each table, mysqld runs out of memory. 10.1.21 works fine with the same settings, data and environment. There is 5.5G (60%) free disk space.

Config file:

[mysqld]
basedir     = /nix/store/1cr57cz2jai9z8iq79232n35rnjy8vk2-mariadb-10.2.3
init_file   = /nix/store/5z0hmn2kzxijqmws8zggg5050gifqaba-init
pid_file    = /run/mysqld/mysqld.pid
plugin_load = auth_socket
 
datadir = /mariadb/db
ignore_db_dirs = lost+found
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_size = 429496729
innodb_compression_algorithm = lz4
innodb_compression_level = 9
innodb_doublewrite = OFF
innodb_file_format = barracuda
innodb_file_per_table = ON
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_file_size = 314572800
log_bin = /mariadb/binlog/binlog
log_slave_updates = OFF
max_allowed_packet = 134217728
net_read_timeout = 1000
net_write_timeout = 1000
port = 3306
relay_log = /mariadb/relay/relay
server_id = 1
skip_log_bin
skip_networking
sql_mode = NO_TABLE_OPTIONS

Last words from mysqld:

Feb 10 02:08:27 vbox-dumpoo mysqld[4099]: 2017-02-10  2:08:27 140140898430464 [Note] /nix/store/1cr57cz2jai9z8iq79232n35rnjy8vk2-mariadb-10.2.3/bin/mysqld: ready for connections.
Feb 10 02:08:27 vbox-dumpoo mysqld[4099]: Version: '10.2.3-MariaDB'  socket: '/run/mysqld/mysqld.sock'  port: 0  MariaDB Server
Feb 10 02:11:15 vbox-dumpoo mysqld[4099]: 2017-02-10  2:11:15 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6934ms. The settings might not be optimal. (flushed=225 and evicted=0, during the time.)
Feb 10 02:11:54 vbox-dumpoo mysqld[4099]: 2017-02-10  2:11:54 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5048ms. The settings might not be optimal. (flushed=359 and evicted=0, during the time.)
Feb 10 02:12:26 vbox-dumpoo mysqld[4099]: 2017-02-10  2:12:26 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 15622ms. The settings might not be optimal. (flushed=292 and evicted=0, during the time.)
Feb 10 02:13:06 vbox-dumpoo mysqld[4099]: 2017-02-10  2:13:06 140139518973696 [Warning] InnoDB: Compression failed for space: 965 name: jenkins_bob_live_tw/catalog_config len: 16384 err: 0 write_size: 0 compression method: LZ4.
Feb 10 02:13:19 vbox-dumpoo mysqld[4099]: 2017-02-10  2:13:19 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4792ms. The settings might not be optimal. (flushed=291 and evicted=0, during the time.)
Feb 10 02:14:04 vbox-dumpoo mysqld[4099]: 2017-02-10  2:14:03 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6148ms. The settings might not be optimal. (flushed=282 and evicted=0, during the time.)
Feb 10 02:14:32 vbox-dumpoo mysqld[4099]: 2017-02-10  2:14:32 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 9948ms. The settings might not be optimal. (flushed=259 and evicted=0, during the time.)
Feb 10 02:15:04 vbox-dumpoo mysqld[4099]: 2017-02-10  2:15:04 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 14958ms. The settings might not be optimal. (flushed=215 and evicted=0, during the time.)
Feb 10 02:15:45 vbox-dumpoo mysqld[4099]: 2017-02-10  2:15:45 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4619ms. The settings might not be optimal. (flushed=198 and evicted=0, during the time.)
Feb 10 02:16:15 vbox-dumpoo mysqld[4099]: 2017-02-10  2:16:15 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4027ms. The settings might not be optimal. (flushed=171 and evicted=0, during the time.)
Feb 10 02:16:32 vbox-dumpoo mysqld[4099]: 2017-02-10  2:16:32 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4662ms. The settings might not be optimal. (flushed=197 and evicted=0, during the time.)
Feb 10 02:16:49 vbox-dumpoo mysqld[4099]: 2017-02-10  2:16:49 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4979ms. The settings might not be optimal. (flushed=192 and evicted=0, during the time.)
Feb 10 02:17:09 vbox-dumpoo mysqld[4099]: 2017-02-10  2:17:09 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4276ms. The settings might not be optimal. (flushed=204 and evicted=0, during the time.)
Feb 10 02:17:27 vbox-dumpoo mysqld[4099]: 2017-02-10  2:17:27 140139518973696 [Warning] InnoDB: Compression failed for space: 991 name: jenkins_bob_live_tw/catalog_simple len: 16384 err: 0 write_size: 0 compression method: LZ4.
Feb 10 02:18:01 vbox-dumpoo mysqld[4099]: 2017-02-10  2:18:01 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8914ms. The settings might not be optimal. (flushed=187 and evicted=0, during the time.)
Feb 10 02:18:33 vbox-dumpoo mysqld[4099]: 2017-02-10  2:18:33 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 12444ms. The settings might not be optimal. (flushed=194 and evicted=0, during the time.)
Feb 10 02:19:23 vbox-dumpoo mysqld[4099]: 2017-02-10  2:19:23 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 16107ms. The settings might not be optimal. (flushed=217 and evicted=0, during the time.)
Feb 10 02:19:40 vbox-dumpoo mysqld[4099]: 2017-02-10  2:19:40 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4327ms. The settings might not be optimal. (flushed=201 and evicted=0, during the time.)
Feb 10 02:20:16 vbox-dumpoo mysqld[4099]: 2017-02-10  2:20:16 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 10164ms. The settings might not be optimal. (flushed=352 and evicted=0, during the time.)
Feb 10 02:20:48 vbox-dumpoo mysqld[4099]: 2017-02-10  2:20:48 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 9884ms. The settings might not be optimal. (flushed=607 and evicted=0, during the time.)
Feb 10 02:20:58 vbox-dumpoo mysqld[4099]: 2017-02-10  2:20:58 140139518973696 [Warning] InnoDB: Compression failed for space: 1031 name: jenkins_bob_live_tw/cms_item len: 16384 err: 0 write_size: 0 compression method: LZ4.
Feb 10 02:21:18 vbox-dumpoo mysqld[4099]: 2017-02-10  2:21:17 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5175ms. The settings might not be optimal. (flushed=439 and evicted=0, during the time.)
Feb 10 02:22:13 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:13 140139518973696 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5455ms. The settings might not be optimal. (flushed=132 and evicted=0, during the time.)
Feb 10 02:22:42 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:42 140140897567488 [Warning] InnoDB: Retry attempts for writing partial data failed.
Feb 10 02:22:42 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:42 140140897567488 [ERROR] InnoDB: Write to file ./jenkins_bob_live_tw/customer.ibdfailed at offset 30408704, 1048576 bytes should have been written, only 0 were written. Operating system error number 14. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
Feb 10 02:22:42 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:42 140140897567488 [ERROR] InnoDB: Error number 14 means 'Bad address'
Feb 10 02:22:42 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:42 140140897567488 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
Feb 10 02:22:42 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:42 140140897567488 [Warning] InnoDB: Error while writing 1048576 zeroes to ./jenkins_bob_live_tw/customer.ibd starting at offset 30408704
Feb 10 02:22:43 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:43 140139518973696 [Warning] InnoDB: Compression failed for space: 1044 name: jenkins_bob_live_tw/cross_shopping_slave len: 16384 err: 0 write_size: 0 compression method: LZ4.
Feb 10 02:22:50 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:50 140140897567488 [Warning] InnoDB: Retry attempts for writing partial data failed.
Feb 10 02:22:50 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:50 140140897567488 [ERROR] InnoDB: Write to file ./jenkins_bob_live_tw/customer.ibdfailed at offset 31457280, 1048576 bytes should have been written, only 0 were written. Operating system error number 14. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
Feb 10 02:22:50 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:50 140140897567488 [ERROR] InnoDB: Error number 14 means 'Bad address'
Feb 10 02:22:50 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:50 140140897567488 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
Feb 10 02:22:50 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:50 140140897567488 [Warning] InnoDB: Error while writing 1048576 zeroes to ./jenkins_bob_live_tw/customer.ibd starting at offset 31457280
Feb 10 02:22:50 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:50 140140897567488 [Warning] InnoDB: Retry attempts for writing partial data failed.
Feb 10 02:22:50 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:50 140140897567488 [ERROR] InnoDB: Write to file ./jenkins_bob_live_tw/customer.ibdfailed at offset 32505856, 1048576 bytes should have been written, only 0 were written. Operating system error number 14. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
Feb 10 02:22:51 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:50 140140897567488 [ERROR] InnoDB: Error number 14 means 'Bad address'
Feb 10 02:22:51 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:50 140140897567488 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
Feb 10 02:22:51 vbox-dumpoo mysqld[4099]: 2017-02-10  2:22:50 140140897567488 [Warning] InnoDB: Error while writing 1048576 zeroes to ./jenkins_bob_live_tw/customer.ibd starting at offset 32505856
Feb 10 02:23:04 vbox-dumpoo mysqld[4099]: 2017-02-10  2:23:04 140139518973696 [Warning] InnoDB: Compression failed for space: 1048 name: jenkins_bob_live_tw/customer len: 16384 err: 0 write_size: 0 compression method: LZ4.
Feb 10 02:23:06 vbox-dumpoo mysqld[4099]: 2017-02-10  2:23:06 140140897567488 [ERROR] mysqld: Out of memory (Needed 359568 bytes)
Feb 10 02:23:06 vbox-dumpoo mysqld[4099]: 2017-02-10  2:23:06 140140897567488 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
Feb 10 02:23:08 vbox-dumpoo mysqld[4099]: 2017-02-10  2:23:08 140139449784064 [ERROR] InnoDB: tried to purge non-delete-marked record in index `REF_IND` of table `SYS_FOREIGN`: tuple: TUPLE (info_bits=0, 2 fields): {[32]jenkins_bob_live_tw/acl_resource(0x0A050E0B090E030F020F020F0C0906050F04070F01030C0F0205030F05020305),[40]jenkins_bob_live_tw/acl_privilege_ibfk_1(0x0A050E0B090E030F020F020F0C0906050F04070F01030C0F00020906090C0507050F0902060B0F01)}, record: RECORD(info_bits=0, 2 fields): {[32]jenkins_bob_live_tw/acl_resource(0x0A050E0B090E030F020F020F0C0906050F04070F01030C0F0205030F05020305),[40]jenkins_bob_live_tw/acl_privilege_ibfk_1(0x0A050E0B090E030F020F020F0C0906050F04070F01030C0F00020906090C0507050F0902060B0F01)}

Out of memory:

# free -m
              total        used        free      shared  buff/cache   available
Mem:            999         904          63           0          30          19
Swap:          1997        1997           0



 Comments   
Comment by Elena Stepanova [ 2017-02-09 ]

FYI marko

Comment by Marko Mäkelä [ 2021-10-25 ]

ip1981, sorry for the extremely long delay. Is this still repeatable for you?

Generated at Thu Feb 08 07:54:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.