Mysql
MySQL (MariaDB - 10.0.16-MariaDB-1 (Debian) 奇怪的性能問題
我在雙 Xeon CPU 上執行版本 10.0.16-MariaDB-1 (Debian GNU/Linux 8.1 (jessie))，共有 32 個核心和 128 GB RAM。這是一個專用的數據庫伺服器，最近從 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 <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但由於數據庫伺服器停止接受連接，最終連接池變滿，不允許進一步的連接。在這個停電期間，我無法看到數據庫伺服器上發生了什麼，因為即使已經建立了連接，我也不會給我任何資訊。
iostat -d -x 1不會顯示磁碟有任何異常，CPU 也大多處於空閒狀態。TCP 連接堆積起來，因為客戶端想要打開連接，因為他們需要推送數據。我無法弄清楚這裡出了什麼問題。這是特定於我的數據庫、機器還是配置的東西。我需要一些幫助來確定和糾正問題的地點和內容。
謝謝。
你的max_connect_errors太低了。你有它在 1000。這意味著在連續 1000 次連接失敗後，你不能再連接到 MySQL。如果它快速攀升，您的狀態變數Aborted_connects應該是致命的贈品。
當即使有一堆打開的數據庫連接也無法連接到 MySQL 時，您將不得不執行
FLUSH HOSTS;並將其重置為 0。我會將max_connect_errors提高到 1000000000（10 億）。
我也會考慮降低以下
[mysqld] interactive_timeout = 30 wait_timeout = 30