We're getting too many connections errors in normal conditions, so I'm assuming it's a configuration issue. Four webservers, not using persistant connections partly because that triggers to the too many connections error too quickly. See more about PHP and persistant connections at PHP/mySQL: how to debug a 'too many connections' error?
The machine is hosted on Amazon. Some web servers are not in the same AZ as the DB. 1a->1d.
Strangely, the hardcoded value of max_connections is 400, but sets it to 214 on restart. I am able to change that global from mysql client back to 400. But going beyond seems excessive.
From SHOW STATUS:
| Connections | 38587 |
| Max_used_connections | 31 |
The app is PHP (fcgi/apache).
Our database size is about 54G. The machine has 36Gigs ram. Free tells me there is 13G free, even including all ram used for cache/buffers. When I tried to give 32G to "innodb_buffer_pool_size" MySQL failed to start because of mmap running out of allocatable memory. The buffer pool is currently at 22G.
The output of mysqltuner mentioned bufferpool size, and join_buffer_size - which is not in the config below.
This is Percona 5.5.
I've also set a http://jeremy.zawodny.com/blog/archives/011421.html max_connect_errors=1844674407370954751
Debugging mysql too many connections problem
max_used_connections is 21 in output of SHOW STATUS.
max_user_connections is set to 0 ... no limit.
Edit: Could this be related to the open file/page limit on Linux systems?
Config file:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
# bl server specific HIGH PRIORITY
nice = -5
[mysqld]
#
# * Basic Settings
#
#
# * IMPORTANT
# If you make changes to these settings and your system uses apparmor, you may
# also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 64M
max_allowed_packet = 128M
thread_stack = 192K
thread_cache_size = 18
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 400
table_cache = 512M
tmp_table_size = 512M
max_heap_table_size = 512M
#http://jeremy.zawodny.com/blog/archives/011421.html
max_connect_errors=1844674407370954751
connect_timeout=15
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 64M
query_cache_size = 2048M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
log_error = /var/log/mysql/error.log
# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 1
#http://dev.mysql.com/doc/refman/5.1/en/binary-log.html
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 3
max_binlog_size = 200M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#### InnoDB ## use the precompiled shared library
#ignore_builtin_innodb
#plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
#http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html
innodb_thread_concurrency = 10
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 22000M
innodb_additional_mem_pool_size = 256M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 1024M
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
default-storage-engine=InnoDB
innodb_file_per_table=1
innodb_file_format=barracuda
innodb_strict_mode=1
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 64M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 64M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
SHOW STATUS
mysql> SHOW STATUS ;
+------------------------------------------+-------------+
| Variable_name | Value |
+------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 43 |
| Binlog_cache_use | 13919 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 95 |
| Bytes_received | 3177 |
| Bytes_sent | 135644 |
[com_* truncated]
| Com_show_status | 2 |
| Com_show_storage_engines | 0 |
| Com_show_table_statistics | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 1 |
| Com_show_temporary_tables | 0 |
| Com_show_thread_statistics | 0 |
| Com_show_triggers | 0 |
| Com_show_user_statistics | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 14529 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 29 |
| Created_tmp_tables | 2 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flashcache_enabled | OFF |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 143 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 141 |
| Innodb_adaptive_hash_cells | 45653879 |
| Innodb_adaptive_hash_heap_buffers | 2078 |
| Innodb_adaptive_hash_hash_searches | 6808835 |
| Innodb_adaptive_hash_non_hash_searches | 1390813 |
| Innodb_background_log_sync | 4350 |
| Innodb_buffer_pool_pages_data | 128339 |
| Innodb_buffer_pool_pages_dirty | 56 |
| Innodb_buffer_pool_pages_flushed | 43003 |
| Innodb_buffer_pool_pages_LRU_flushed | 0 |
| Innodb_buffer_pool_pages_free | 1277581 |
| Innodb_buffer_pool_pages_made_not_young | 0 |
| Innodb_buffer_pool_pages_made_young | 80 |
| Innodb_buffer_pool_pages_misc | 2079 |
| Innodb_buffer_pool_pages_old | 47395 |
| Innodb_buffer_pool_pages_total | 1407999 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 7542 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 275698257 |
| Innodb_buffer_pool_reads | 117954 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 156336 |
| Innodb_checkpoint_age | 10748 |
| Innodb_checkpoint_max_age | 1738160825 |
| Innodb_checkpoint_target_age | 1683843300 |
| Innodb_data_fsyncs | 30470 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2090881024 |
| Innodb_data_reads | 127629 |
| Innodb_data_writes | 69275 |
| Innodb_data_written | 1439578624 |
| Innodb_dblwr_pages_written | 43003 |
| Innodb_dblwr_writes | 700 |
| Innodb_deadlocks | 0 |
| Innodb_dict_tables | 143 |
| Innodb_have_atomic_builtins | ON |
| Innodb_history_list_length | 2394 |
| Innodb_ibuf_discarded_delete_marks | 0 |
| Innodb_ibuf_discarded_deletes | 0 |
| Innodb_ibuf_discarded_inserts | 0 |
| Innodb_ibuf_free_list | 1385 |
| Innodb_ibuf_merged_delete_marks | 285 |
| Innodb_ibuf_merged_deletes | 18 |
| Innodb_ibuf_merged_inserts | 2521 |
| Innodb_ibuf_merges | 2235 |
| Innodb_ibuf_segment_size | 1387 |
| Innodb_ibuf_size | 1 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 37129 |
| Innodb_log_writes | 24787 |
| Innodb_lsn_current | 73821527998 |
| Innodb_lsn_flushed | 73821527998 |
| Innodb_lsn_last_checkpoint | 73821517250 |
| Innodb_master_thread_1_second_loops | 4290 |
| Innodb_master_thread_10_second_loops | 427 |
| Innodb_master_thread_background_loops | 27 |
| Innodb_master_thread_main_flush_loops | 27 |
| Innodb_master_thread_sleeps | 4289 |
| Innodb_max_trx_id | 49591271 |
| Innodb_mem_adaptive_hash | 399297584 |
| Innodb_mem_dictionary | 92552066 |
| Innodb_mem_total | 23699456000 |
| Innodb_mutex_os_waits | 262 |
| Innodb_mutex_spin_rounds | 9713 |
| Innodb_mutex_spin_waits | 5692 |
| Innodb_oldest_view_low_limit_trx_id | 49591190 |
| Innodb_os_log_fsyncs | 25225 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 30232064 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 856 |
| Innodb_pages_read | 127483 |
| Innodb_pages_written | 43003 |
| Innodb_purge_trx_id | 49591179 |
| Innodb_purge_undo_no | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_current_row_locks | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 1 |
| Innodb_rows_deleted | 720 |
| Innodb_rows_inserted | 4710 |
| Innodb_rows_read | 331834870 |
| Innodb_rows_updated | 8203 |
| Innodb_s_lock_os_waits | 9507 |
| Innodb_s_lock_spin_rounds | 286903 |
| Innodb_s_lock_spin_waits | 9919 |
| Innodb_truncated_status_writes | 0 |
| Innodb_x_lock_os_waits | 288 |
| Innodb_x_lock_spin_rounds | 9739 |
| Innodb_x_lock_spin_waits | 147 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 53585 |
| Key_blocks_used | 24 |
| Key_read_requests | 22116 |
| Key_reads | 0 |
| Key_write_requests | 14076 |
| Key_writes | 0 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 21 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 56 |
| Open_streams | 0 |
| Open_table_definitions | 178 |
| Open_tables | 250 |
| Opened_files | 85241 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 15802 |
| Qcache_free_memory | 2090180712 |
| Qcache_hits | 333110 |
| Qcache_inserts | 250475 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 61087 |
| Qcache_queries_in_cache | 30167 |
| Qcache_total_blocks | 76233 |
| Queries | 695077 |
| Questions | 142 |
| Rpl_status | AUTH_MASTER |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 2 |
| Slave_heartbeat_period | 0.000 |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 401832 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 10 |
| Threads_connected | 11 |
| Threads_created | 21 |
| Threads_running | 2 |
| Uptime | 4357 |
| Uptime_since_flush_status | 4357 |
| binlog_commits | 14014 |
| binlog_group_commits | 14013 |
+------------------------------------------+-------------+
370 rows in set (0.00 sec)
mysql> SHOW STATUS WHERE Variable_name LIKE '%con%';
+----------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------+-------+
| Aborted_connects | 1 |
| Com_show_contributors | 0 |
| Connections | 38587 |
| Innodb_master_thread_1_second_loops | 10818 |
| Innodb_master_thread_10_second_loops | 1077 |
| Max_used_connections | 31 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 8 |
+----------------------------------------+-------+
12 rows in set (0.00 sec)
