Not the answer you need?
Register and ask your own question!

cluster crashing daily - oom killer

marciomarcio ContributorCurrent User Role Contributor
Hello!

My config: 3 nodes, 10GB RAM each, Centos 7.4, XtraDB Cluster 5.7.20 and pmm-client.

All nodes still idle, not accepting connections yet, all for the purpose of tests.

To prevent oom-killer from killing mysql daily, I must explicitly disable the performance_schema in my.cnf.


Question: Why must disable performance_schema ? It's a normal condition ? It's safe to do ?


innodb-buffer-pool-size is about 50% (5G) of RAM.

Bellow the graphics from zabbix, where we can see the memory usage increased and when the mysql is killed and my.cnf

Thanks



my.cnf
[mysqld]

performance_schema = OFF

query_response_time_stats = ON

log_timestamps = SYSTEM
disable-partition-engine-check = ON
explicit_defaults_for_timestamp = 1

server-id = 1
datadir = /data/mysql
socket = /data/mysql/mysql.sock
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
symbolic-links = 0
binlog_format = ROW
max-binlog-size = 500M
log_slave_updates = on
expire_logs_days = 1
tmpdir = /var/tmp

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 150
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 5G

thread_handling = pool-of-threads
innodb_log_file_size = 80M
innodb_autoinc_lock_mode = 2

default_storage_engine = InnoDB

max_allowed_packet = 16M
wait_timeout = 300

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 3

[client]
socket=/data/mysql/mysql.sock

!includedir /etc/my.cnf.d/
[[email protected] ~]$ vi my.cnf
[[email protected] ~]$ cat my.cnf
[mysqld]

performance_schema = OFF

query_response_time_stats = ON
log_timestamps = SYSTEM
disable-partition-engine-check = ON
explicit_defaults_for_timestamp = 1
server-id = 1
datadir = /data/mysql
socket = /data/mysql/mysql.sock
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
symbolic-links = 0
binlog_format = ROW
max-binlog-size = 500M
log_slave_updates = on
expire_logs_days = 1
tmpdir = /var/tmp

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 150
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 5G
thread_handling = pool-of-threads
innodb_log_file_size = 80M
innodb_autoinc_lock_mode = 2
default_storage_engine = InnoDB
max_allowed_packet = 16M
wait_timeout = 300
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 3

[client]
socket=/data/mysql/mysql.sock

!includedir /etc/my.cnf.d/

Comments

  • krunalbauskarkrunalbauskar Advisor Inactive User Role Novice
    Performance Schema is used to track some of the instruments that help gain insight on how the server is performing especially under load.
    It should be generally safe to disable it given that you are hitting OOM issue.

    Depending on the eventual load you may need to re-configure your machine to have enough memory.
  • marciomarcio Contributor Current User Role Contributor
    Hi @krunalbauskar.

    I have worked with other mysql-like brands and never need touch this parameter and never have problems with oom-killer. This situation is very new to me, and I was afraid of having to deal with oom-killer at any time, especially at the time the db is in full use. :(

    Thank you.
  • Kenn TakaraKenn Takara Percona Percona Staff Role
    This is a known memory leak that occurs when using the performance schema and thread-pooling.

    https://jira.percona.com/browse/PS-1096

    The only workarounds that I can think of are to either disable the performance schema or thread pooling.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.