Hi
I have a few Percona servers 5.6.25-73.1 with workload about 5-6k qps, especially DML workload. All fine, but sometimes servers hangs. For example i am doing simple querys like: “select * from information_schema.QUERY_RESPONSE_TIME;”.
When all is fine query takes less than 0.001 sec, but when server hangs it takes more then 1-2 secs. And on all graphs i see gap and after it all metrics grows.
And i see it on all graphs that collecting by queries like:
select NAME,COUNT,@@global.innodb_log_file_size*@@global. innodb_log_files_in_group from information_schema.INNODB_METRICS where name in (‘log_lsn_last_flush’, ‘log_lsn_last_checkpoint’);
select NAME,COUNT from information_schema.INNODB_METRICS where NAME like ‘trx%’;
select SUBSTRING(USER, 1, LENGTH(USER) - 1),TOTAL_CONNECTIONS from information_schema.USER_STATISTICS;
so on except query SHOW GLOBAL STATUS.
And it looks like on attached graph.
Sometimes it cause error ‘Too many connections’.
Maybe somebody faced with this problem? Could you help please?
Percona Server config:
[client]
port = 3306
socket = /local/mysql/mysql.sock
[mysqld]
port = 3306
socket = /local/mysql/mysql.sock
datadir = /local/mysql/data/
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 64M
table_open_cache = 10240
table_definition_cache = 131000
table_open_cache_instances = 16
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 512
query_cache_size = 0
query_cache_type = 0
optimizer_search_depth = 0
max_connections = 2048
max_connect_errors = 4294967295
skip-name-resolve
skip-host-cache
character-set-server = utf8
collation-server = utf8_unicode_ci
open_files_limit = 900000
max_heap_table_size = 512M
tmp_table_size = 512M
log-bin = /local/mysql/bin/bin
relay-log = relay-bin
binlog-format = ROW
server-id = 1
expire_logs_days = 1
log-slave-updates = 1
innodb_thread_concurrency = 0
innodb_file_per_table
innodb_file_format = barracuda
innodb_strict_mode = 1
innodb_table_locks = 1
innodb_data_home_dir = /local/mysql/innodb/
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /local/mysql/var/
innodb_buffer_pool_size = 181G
innodb_buffer_pool_instances = 16
innodb_log_file_size = 1G
innodb_log_buffer_size = 128M
innodb_lock_wait_timeout = 50
lock_wait_timeout = 50
innodb_kill_idle_transaction = 300
innodb_buffer_pool_populate = 1
innodb_old_blocks_time = 1000
innodb_stats_on_metadata = 0
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_io_capacity = 800
innodb_io_capacity_max = 2000
innodb_open_files = 65535
back_log = 65535
[mysqld_safe]
numa_interleave = 1
