linux - MySQL (MariaDB - 10.0.16-MariaDB-1 (Debian) strange performance issues -

i running version 10.0.16-mariadb-1 (debian gnu/linux 8.1 (jessie)) on dual xeon cpu, total 32 cores 128 gb of ram. dedicated db server , has been migrated on percona db server. configuration looks this,

################################################################################ # 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 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 put in cache  ## connections max_connections = 900   #multiplier 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 allow  ## default table settings sql_mode            = no_auto_create_user  ## table , tmp settings max_heap_table_size         = 1g    #recommend same size tmp_table_size bulk_insert_buffer_size         = 1g    #recommend same size tmp_table_size tmp_table_size                  = 1g    #recommend 1g min tmpdir                         = /dev/shm        #recommend using ramdisk 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 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 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 negate disk flush innodb_fast_shutdown = 1 skip-innodb-doublewrite skip-host-cache # skip-name-resolve  ## binlog sync settings ## xa transactions = 1, otherwise set 0 best performance sync_binlog         = 0  ## tx isolation #transaction-isolation      = repeatable-read #repeatable-read req 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 = 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 

i have multiple hosts connecting server , initiating queries. problem facing randomly db server stops accepting connections. connections start piling , in around 3 or 5 seconds comes normal. when run show processlist see lot of connections piled waiting on connected since db server stops accepting connections, connection pool becomes full, no further connections allowed.

during blackout period, not able see what's going on db server because won't give me info if connection established already. iostat -d -x 1 won't show unusual disks, cpu idle. tcp connections piles since clients wants open connections because need push data.

i not able figure out what's wrong here. specific db, or machine or config. need here on , identify , rectify issue.

thanks.

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

i have not heard of needing more 256k. did discover need 512k? with

max_connections = 900

you potentially need 500mb processes. (granted, not big deal since have 128gb of ram.)

back problem...

turn on slow_log long_query_time = 1. after next occurrence, run pt-query-digest against slowlog. there may surprises there. note queries hung across 3-5 second pause register taking several seconds, if should take milliseconds. there's still risk won't point finger @ real cause of pause.