mysql client (mariadb 10.0),MySQL(MariaDB – 10.0.16-MariaDB-1(Debian)奇怪的性能问题

我在Dual Xeon CPU上运行版本10.0.16-MariaDB-1(Debian GNU / Linux 8.1(jessie)),共有32个内核,128 GB内存.这是一个专用的数据库服务器,最近从Percona数据库服务器迁移过来.我的配置如下所示,

################################################################################

# DATE: 19-01-2014

# DESCRIPTION: MySQL config

# RAM: 128GB RAM dedicated server

# Connections: 1000 connections

################################################################################

[mysql]

# CLIENT #

port = 1979

socket = /var/run/mysqld/mysqld.sock

[mysqld]

## Files

back_log = 300

open-files-limit = 8192

#open-files = 1024

# GENERAL #

user = mysql

default-storage-engine = InnoDB

port = 1979

socket = /var/run/mysqld/mysqld.sock

pid-file = /var/run/mysqld/mysql.pid

# DATA STORAGE #

datadir = /var/lib/mysql

# LOGGING #

log-error = /var/log/mysql/mysql-error.log

log-queries-not-using-indexes = 0

slow-query-log = 0

slow-query-log-file = /var/log/mysql/mysql-slow.log

## Per-Thread Buffers * (max_connections) = total per-thread mem usage

thread_stack = 512K #default: 32bit: 192K, 64bit: 256K

sort_buffer_size = 2M #default: 2M, larger may cause perf issues

read_buffer_size = 2M #default: 128K, change in increments of 4K

read_rnd_buffer_size = 2M #default: 256K

join_buffer_size = 2M #default: 128K

binlog_cache_size = 128K #default: 32K, size of buffer to hold TX queries

## total per-thread buffer memory usage: 17664000K = 17.250GB

## Query Cache

query_cache_size = 64M #global buffer

query_cache_limit = 2M #max query result size to put in cache

## Connections

max_connections = 900 #multiplier for memory usage via per-thread buffers

max_connect_errors = 1000 #default: 10

concurrent_insert = 2

connect_timeout = 10 #default -5.1.22: 5, +5.1.22: 10

max_allowed_packet = 32M #max size of incoming data to allow

## Default Table Settings

sql_mode = NO_AUTO_CREATE_USER

## Table and TMP settings

max_heap_table_size = 1G #recommend same size as tmp_table_size

bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size

tmp_table_size = 1G #recommend 1G min

tmpdir = /dev/shm #Recommend using RAMDISK for tmpdir

## Table cache settings

#table_cache = 512 #5.0.x

#table_open_cache = 512 #5.1.x, 5.5.x

query-cache-type = 1

## Thread settings

thread_concurrency = 32 #recommend 2x CPU cores

thread_cache_size = 400 #recommend 5% of max_connections

## InnoDB Plugin Independent Settings

innodb_data_home_dir = /var/lib/mysql

innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend

innodb_log_file_size = 768M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128

innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4

innodb_buffer_pool_size = 96G #global buffer

innodb_additional_mem_pool_size = 8M #global buffer

innodb_status_file #extra reporting

innodb_file_per_table #enable always

innodb_flush_log_at_trx_commit = 2

innodb_table_locks = 0 #preserve table locks

innodb_log_buffer_size = 128M #global buffer

innodb_lock_wait_timeout = 600

#innodb_thread_concurrency = 0

innodb_commit_concurrency = 8 #recommend 4x num disks

innodb_flush_method = O_DIRECT_NO_FSYNC #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI

innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush

innodb_fast_shutdown = 1

skip-innodb-doublewrite

skip-host-cache

# skip-name-resolve

## Binlog sync settings

## XA transactions = 1, otherwise set to 0 for best performance

sync_binlog = 0

## TX Isolation

#transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA

## Per-Thread Buffer memory utilization equation:

#(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections

## Replication

#server_id = 1

#log_bin = /var/log/mysql/mysql-bin.log

#log_bin_index = /var/log/mysql/mysql-bin.log.index

#relay_log = /var/log/mysql/mysql-relay-bin

#relay_log_index = /var/log/mysql/mysql-relay-bin.index

#expire_logs_days = 10

#max_binlog_size = 100M

#log_slave_updates = 1

#auto-increment-increment = 2

#auto-increment-offset = 1

## Global Buffer memory utilization equation:

# innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size

long_query_time = 3

innodb_checksum_algorithm=NONE

interactive_timeout = 300

wait_timeout = 40

myisam_repair_threads = 8

myisam-block-size = 8096

innodb_adaptive_hash_index = off

innodb_use_sys_malloc

innodb_sync_array_size = 4

innodb_read_ahead_threshold = 56

innodb_purge_threads = 4

innodb_adaptive_flushing = off

innodb_use_native_aio = off

innodb_random_read_ahead

optimizer_search_depth = 24

table_definition_cache = 400

table_open_cache = 2048

delay-key-write = ALL

innodb_checksum_algorithm = innodb

innodb_write_io_threads = 64

innodb_read_io_threads = 64

thread_pool_size = 16

thread_handling = pool-of-threads

innodb_disable_sort_file_cache

innodb_buffer_pool_instances = 32

innodb_adaptive_hash_index = off

[mysqldump]

quick

quote-names

max_allowed_packet = 128M

我有多个主机连接到服务器并启动查询.我面临的问题是随机数据库服务器停止接受连接.所有连接开始堆积,并在大约3或5秒后恢复正常.当我运行show processlist时,我看到很多连接正在等待连接,但由于DB服务器停止接受连接,最终连接池变满,不允许进一步连接.

在这个停电期间,我无法看到DB服务器上发生了什么,因为即使连接已经建立,我也不会给我任何信息. iostat -d -x 1不会显示磁盘异常,CPU也大多闲置. TCP连接堆积起来,因为客户端想要打开连接,因为它们需要推送数据.

我无法弄清楚这里有什么问题.这是我的数据库,机器或配置特有的东西.我在这里需要一些帮助,找出在哪里以及寻找什么来识别和纠正问题.

谢谢.

解决方法:

你的max_connect_errors太低了.你有1000.这意味着连续1000次连接失败后,你无法再连接到MySQL.

如果它快速爬升,你的状态变量Aborted_connects应该是死亡赠品.

当你无法再使用大量开放式数据库连接连接到MySQL时,你必须执行FLUSH HOSTS;并重置为count到0.

我还会考虑降低以下内容

[mysqld]

interactive_timeout = 30

wait_timeout = 30

标签：performance-tuning,performance,linux,mysql,mariadb

来源： https://codeday.me/bug/20190806/1604118.html