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的RAM。这是一个专用DB服务器,并已从最近的Percona DB服务器迁移过来。我的配置是这样的,MySQL(MariaDB - 10.0.16-MariaDB-1(Debian)奇怪的性能问题
################################################################################
# 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 <default: 64>
#table_open_cache = 512 #5.1.x, 5.5.x <default: 64>
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
我看到很多连接堆积等待connected
但由于数据库服务器停止接受连接,最终连接池变满,不允许进一步的连接。
在这个停电期间,我无法看到DB服务器上发生了什么,因为即使连接已建立,我也不会给我任何信息。 iostat -d -x 1
不会显示任何不寻常的磁盘,CPU也大多是空闲的。 TCP连接堆积如山,因为客户端需要打开连接,因为它们需要推送数据。
我无法弄清楚这里有什么问题。这是特定于我的数据库,或机器或配置的东西。在这里,我需要一些帮助来确定和纠正问题。
谢谢。
thread_stack = 512K#默认:32位:192K,64位:256K
我还没有听说过需要超过256K的。你是否发现需要512K?加上
的max_connections = 900
您可能需要过程500MB。 (当然,这是不是一个大问题,因为你有公羊128GB)
回到问题...
打开与long_query_time时间= 1的下一个出现后slow_log,运行PT-查询针对慢速记录进行摘要。那里可能会有一些惊喜。请注意,在3-5秒暂停期间挂起的查询将注册为几秒钟,即使它们只需要几毫秒。还有一个风险是,它不会指出暂停的真正原因。
对于http://dba.stackexchange.com/ – BK435
,这个问题可能会更好一些似乎很明显与线程池有关......如果启用'--extra-port'选项,您应该能够进行管理与摊位无关的连接。 https://mariadb.com/kb/en/mariadb/thread-pool-in-mariadb/ –