Rescue
Posted by: Sameer Gautam
Date: November 09, 2016 11:06PM
Date: November 09, 2016 11:06PM
I have Percona MySQL 5.7.11-4 installed in one of my Ubuntu 14.04.4 LTS cloud instances. It's an r3.2xlarge instance with 60 GB of RAM. I've set up RAID 10 on LVM in order to install the server. Pasted along with is my configuration for my.cnf. As you can see in the config file that I've set innodb_buffer_pool_size to 48 GB (80% of available RAM). However, I'm seeing that MySQL has been gobbling up memory like anything. NewRelic Graph shows that it's consuming as much as 57 GB of memory.
Now, I'd like to know whether this is natural or not. Is the memory consumption in my case a good indicator of optimal usage or is this absurd? Further, if this is not a good sign, how can I get my memory back ?
Any help is much appreciated.
my.cnf
[client]
socket = /var/run/mysqld/mysqld.sock
[mysqld]
### General options
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
user = mysql
basedir = /usr
datadir = /data/mysql
tmpdir = /mnt/mysql-tmp
# sql-mode: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
sql-mode = STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION
sysdate-is-now = 0
# transaction-isolation: Can be set to READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction-isolation = REPEATABLE-READ
default-storage-engine = InnoDB
event-scheduler = OFF
performance_schema = 0
metadata_locks_hash_instances = 256
collation-server = latin1_swedish_ci
character-set-server = latin1
### Percona specific general options
expand_fast_index_creation = OFF
userstat = OFF
thread_statistics = OFF
### Connectivity options
port = 3306
max_user_connections = 400
secure-auth = ON
back_log = 2048
skip-name-resolve = 0
max_connect_errors = 1000000
read_only = OFF
bind-address = 0.0.0.0
# max_allowed_packet: Currently we have to supply value in bytes here to enable the dynamic refresh to work
max_allowed_packet = 16777216
wait_timeout = 14400
max_connections = 500
thread_cache_size = 64
net_write_timeout = 60
net_read_timeout = 30
safe-user-create = 1
interactive_timeout = 14400
### General caches
# key_buffer_size: Currently we have to supply value in bytes here to enable the dynamic refresh to work
#key_buffer_size = 16777216
query_cache_size = 0
# tmp_table_size: Currently we have to supply value in bytes here to enable the dynamic refresh to work
tmp_table_size = 16777216
# max_heap_table_size: Currently we have to supply value in bytes here to enable the dynamic refresh to work
max_heap_table_size = 16777216
table_open_cache = 8192
# query_cache_type: Can be set to 0, off, 1, on, 2, demand. If set to 0, cannot be changed dynamically.
query_cache_type = 0
table_open_cache_instances = 8
### InnoDB options
innodb_buffer_pool_size = 49152M
innodb_open_files = 1024
innodb_log_file_size = 64M
# innodb_file_format: Antelope, Barracuda
innodb_file_format = Barracuda
# innodb_autoinc_lock_mode: Can be set to 0, traditional. 1, consecutive. 2, interleaved.
innodb_autoinc_lock_mode = 2
innodb_stats_on_metadata = OFF
# innodb_file_format_max: Antelope, Barracuda
innodb_file_format_max = Barracuda
innodb_old_blocks_time = 1000
innodb_checksum_algorithm = CRC32
innodb_read_io_threads = 8
innodb_lru_scan_depth = 20000
innodb_thread_concurrency = 0
# innodb_flush_log_at_trx_commit: Can be set to 0, InnoDB log buffer written to the log file every second and flushed to disk. 1, InnoDB log buffer written to the log file on every commit and flushed to disk. 2, InnoDB log buffer written to the log file on every commit and flushed to disk every second
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 75
innodb_log_buffer_size = 32M
innodb_buffer_pool_instances = 1
innodb_io_capacity_max = 3000
# innodb_flush_method: Can be set to fsync, O_DSYNC, O_DIRECT, O_DIRECT_NO_FSYNC (From 5.6.7), async_unbuffered (Windows)
innodb_flush_method = O_DIRECT
innodb = force
innodb_io_capacity = 1000
innodb_write_io_threads = 8
innodb_support_xa = ON
innodb_concurrency_tickets = 3000
innodb_file_per_table = ON
### Replication options
relay_log_info_file = /data/mysql-binlogs/mysql-relay.info
relay-log = /data/mysql-binlogs/mysql-relay
relay_log_index = /data/mysql-binlogs/mysql-relay.index
log-bin = /data/mysql-binlogs/mysql-bin
log-bin-index = /data/mysql-binlogs/mysql-bin.index
master-info-file = /data/mysql-binlogs/master.info
log-slave-updates = 1
skip-slave-start = 1
auto_increment_offset = 1
relay_log_purge = ON
auto_increment_increment = 1
server-id = 900
# max_binlog_size: Currently we have to supply value in bytes here to enable the dynamic refresh to work
max_binlog_size = 104857600
relay_log_recovery = ON
expire_logs_days = 7
# binlog-format: Can be set to STATEMENT, MIXED, ROW
binlog-format = ROW
relay-log-info-repository = TABLE
sync_binlog = 0
### Logging options
general_log_file = /data/mysql-log/mysql_general.log
log-error = /data/mysql-log/mysql_error.log
slow_query_log_file = /data/mysql-log/mysql_slow.log
log-warnings = 1
log-queries-not-using-indexes = ON
log-slow-verbosity = full
long_query_time = 2
log-slow-admin-statements = 1
log-slow-slave-statements = 1
general-log = OFF
slow-query-log = ON
#audit log options
audit_log_file = mysql_audit.log
audit_log_strategy = ASYNCHRONOUS
audit_log_buffer_size = 4096
audit_log_policy = ALL
audit_log_format = JSON
audit_log_handler = FILE
audit_log_rotate_on_size = 300M
audit_log_rotations = 5
### Percona specific logging options
# log_slow_rate_type: session, query
log_slow_rate_type = session
# slow_query_log_use_global_control: Can be set to none, log_slow_filter, log_slow_rate_limit, log_slow_verbosity, long_query_time, min_examined_row_limit, all. 'all' should be avoided as it can't be reflected in the running config
slow_query_log_use_global_control = log_slow_filter,log_slow_rate_limit,log_slow_verbosity,long_query_time,min_examined_row_limit
log_slow_sp_statements = ON
# log_slow_filter: Can be set to qc_miss, full_scan, full_join, tmp_table, tmp_table_on_disk, filesort, filesort_on_disk
log_slow_filter = ''
# log_slow_verbosity: can be set to microtime, query_plan, innodb, minimal, standard, full, profiling, profiling_use_getrusage
log_slow_verbosity = microtime,query_plan
log_slow_rate_limit = 1
slow_query_log_always_write_time = 10
[mysqld_safe]
log-error = /data/mysql-log/mysql_error.log
Now, I'd like to know whether this is natural or not. Is the memory consumption in my case a good indicator of optimal usage or is this absurd? Further, if this is not a good sign, how can I get my memory back ?
Any help is much appreciated.
my.cnf
[client]
socket = /var/run/mysqld/mysqld.sock
[mysqld]
### General options
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
user = mysql
basedir = /usr
datadir = /data/mysql
tmpdir = /mnt/mysql-tmp
# sql-mode: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
sql-mode = STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION
sysdate-is-now = 0
# transaction-isolation: Can be set to READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction-isolation = REPEATABLE-READ
default-storage-engine = InnoDB
event-scheduler = OFF
performance_schema = 0
metadata_locks_hash_instances = 256
collation-server = latin1_swedish_ci
character-set-server = latin1
### Percona specific general options
expand_fast_index_creation = OFF
userstat = OFF
thread_statistics = OFF
### Connectivity options
port = 3306
max_user_connections = 400
secure-auth = ON
back_log = 2048
skip-name-resolve = 0
max_connect_errors = 1000000
read_only = OFF
bind-address = 0.0.0.0
# max_allowed_packet: Currently we have to supply value in bytes here to enable the dynamic refresh to work
max_allowed_packet = 16777216
wait_timeout = 14400
max_connections = 500
thread_cache_size = 64
net_write_timeout = 60
net_read_timeout = 30
safe-user-create = 1
interactive_timeout = 14400
### General caches
# key_buffer_size: Currently we have to supply value in bytes here to enable the dynamic refresh to work
#key_buffer_size = 16777216
query_cache_size = 0
# tmp_table_size: Currently we have to supply value in bytes here to enable the dynamic refresh to work
tmp_table_size = 16777216
# max_heap_table_size: Currently we have to supply value in bytes here to enable the dynamic refresh to work
max_heap_table_size = 16777216
table_open_cache = 8192
# query_cache_type: Can be set to 0, off, 1, on, 2, demand. If set to 0, cannot be changed dynamically.
query_cache_type = 0
table_open_cache_instances = 8
### InnoDB options
innodb_buffer_pool_size = 49152M
innodb_open_files = 1024
innodb_log_file_size = 64M
# innodb_file_format: Antelope, Barracuda
innodb_file_format = Barracuda
# innodb_autoinc_lock_mode: Can be set to 0, traditional. 1, consecutive. 2, interleaved.
innodb_autoinc_lock_mode = 2
innodb_stats_on_metadata = OFF
# innodb_file_format_max: Antelope, Barracuda
innodb_file_format_max = Barracuda
innodb_old_blocks_time = 1000
innodb_checksum_algorithm = CRC32
innodb_read_io_threads = 8
innodb_lru_scan_depth = 20000
innodb_thread_concurrency = 0
# innodb_flush_log_at_trx_commit: Can be set to 0, InnoDB log buffer written to the log file every second and flushed to disk. 1, InnoDB log buffer written to the log file on every commit and flushed to disk. 2, InnoDB log buffer written to the log file on every commit and flushed to disk every second
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 75
innodb_log_buffer_size = 32M
innodb_buffer_pool_instances = 1
innodb_io_capacity_max = 3000
# innodb_flush_method: Can be set to fsync, O_DSYNC, O_DIRECT, O_DIRECT_NO_FSYNC (From 5.6.7), async_unbuffered (Windows)
innodb_flush_method = O_DIRECT
innodb = force
innodb_io_capacity = 1000
innodb_write_io_threads = 8
innodb_support_xa = ON
innodb_concurrency_tickets = 3000
innodb_file_per_table = ON
### Replication options
relay_log_info_file = /data/mysql-binlogs/mysql-relay.info
relay-log = /data/mysql-binlogs/mysql-relay
relay_log_index = /data/mysql-binlogs/mysql-relay.index
log-bin = /data/mysql-binlogs/mysql-bin
log-bin-index = /data/mysql-binlogs/mysql-bin.index
master-info-file = /data/mysql-binlogs/master.info
log-slave-updates = 1
skip-slave-start = 1
auto_increment_offset = 1
relay_log_purge = ON
auto_increment_increment = 1
server-id = 900
# max_binlog_size: Currently we have to supply value in bytes here to enable the dynamic refresh to work
max_binlog_size = 104857600
relay_log_recovery = ON
expire_logs_days = 7
# binlog-format: Can be set to STATEMENT, MIXED, ROW
binlog-format = ROW
relay-log-info-repository = TABLE
sync_binlog = 0
### Logging options
general_log_file = /data/mysql-log/mysql_general.log
log-error = /data/mysql-log/mysql_error.log
slow_query_log_file = /data/mysql-log/mysql_slow.log
log-warnings = 1
log-queries-not-using-indexes = ON
log-slow-verbosity = full
long_query_time = 2
log-slow-admin-statements = 1
log-slow-slave-statements = 1
general-log = OFF
slow-query-log = ON
#audit log options
audit_log_file = mysql_audit.log
audit_log_strategy = ASYNCHRONOUS
audit_log_buffer_size = 4096
audit_log_policy = ALL
audit_log_format = JSON
audit_log_handler = FILE
audit_log_rotate_on_size = 300M
audit_log_rotations = 5
### Percona specific logging options
# log_slow_rate_type: session, query
log_slow_rate_type = session
# slow_query_log_use_global_control: Can be set to none, log_slow_filter, log_slow_rate_limit, log_slow_verbosity, long_query_time, min_examined_row_limit, all. 'all' should be avoided as it can't be reflected in the running config
slow_query_log_use_global_control = log_slow_filter,log_slow_rate_limit,log_slow_verbosity,long_query_time,min_examined_row_limit
log_slow_sp_statements = ON
# log_slow_filter: Can be set to qc_miss, full_scan, full_join, tmp_table, tmp_table_on_disk, filesort, filesort_on_disk
log_slow_filter = ''
# log_slow_verbosity: can be set to microtime, query_plan, innodb, minimal, standard, full, profiling, profiling_use_getrusage
log_slow_verbosity = microtime,query_plan
log_slow_rate_limit = 1
slow_query_log_always_write_time = 10
[mysqld_safe]
log-error = /data/mysql-log/mysql_error.log
Subject
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.