0

Our database start dropping connection during the peak hours. There are about 3% connection dropped and mysql return message "mysqli::mysqli(): (HY000/2003): Can't connect to MySQL server on '10.88.***' (99)" . I have tuned several mysql and kernel parameters, but it does not help: -/etc/sysctl.conf:

net.ipv4.tcp_max_tw_buckets = 524288 net.core.somaxconn = 2048 net.ipv4.ip_local_port_range = 1024 65000 net.ipv4.tcp_max_syn_backlog=30000 net.ipv4.tcp_syncookies = 1 net.ipv4.tcp_synack_retries = 2

-/etc/my.cnf:

open_files_limit = 65536 max_connections=3600
connect_timeout=60
wait_timeout=2400
back_log=4096

-/etc/security/limits.conf

mysql soft nofile 65536 mysql hard nofile 65536 mysql soft nproc 65536 mysql hard nproc 65536

Our database handle >40000 query and > 8000 connection per second by one database machine, with no sharding and connection pool. What do i missing? Does any one know what may cause this bottleneck? Thanks.

Here is the content in my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

skip_name_resolve = off
open_files_limit = 65536

max_allowed_packet = 1073741824
expire_logs_days = 2

binlog_format = row
innodb_file_per_table = 1
innodb_flush_method=O_DIRECT

innodb_buffer_pool_instances = 16
innodb_buffer_pool_size = 43G
innodb_thread_concurrency = 0

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
slave-skip-errors = 1062,1032

max_connections=3600
connect_timeout=60
wait_timeout=2400

back_log=4096

thread_cache_size=48

symbolic-links=0

user=mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
!includedir /etc/my.cnf.d
Chris
  • 47
  • 6
  • The obvious question to ask is how many sessions do you have open when the drops start happening? – Tim Biegeleisen Aug 03 '15 at 05:11
  • 3600 connections? what is RAM of your server...if you share all parameters of your mysql configuration file then may get some clue... – Zafar Malik Aug 03 '15 at 05:14
  • We have 64GB RAM on the database server. I am not sure how many sessions when the problem occur – Chris Aug 03 '15 at 05:29
  • @ZafarMalik not a big deal. RAM is cheap enough in our days – M0rtiis Aug 03 '15 at 06:06
  • you should not increase max_connections as it consumes server resources per connection, but you have to check if connections are going in sleep mode on your server, Also connections are using properly by your application...wait timeout is too high, you should reduce it to 120 or 240 if there is no specific reason. – Zafar Malik Aug 03 '15 at 10:42

1 Answers1

1

so.. try to increase your max_connections to > 8000 ?

in my.cnf

max_connections=9000

  • Thank you for the response. one question, We have > 4000 connection in non-peak hours, which is already exceed the max-connection i have set, but why mysql have not throw the error? – Chris Aug 03 '15 at 05:26
  • i believe you are not using persistent connections? php? –  Aug 03 '15 at 05:32
  • we are using php mysqli but not using persistent connection – Chris Aug 03 '15 at 06:15