Mysql
MySQL 在正常情況下連接過多（Percona，InnoDB）
在正常情況下，我們遇到了太多的連接錯誤，所以我假設這是一個配置問題。四個網路伺服器，不使用持久連接，部分原因是這會觸發太多連接錯誤太快。在 PHP/mySQL 中查看有關 PHP 和持久連接的更多資訊：如何調試“連接過多”錯誤？
該機器託管在亞馬遜上。一些 Web 伺服器與 DB 不在同一個 AZ 中。1a->1d。
奇怪的是，硬編碼的值為
max_connections400，但在重新啟動時將其設置為 214。我能夠將全域從 mysql 客戶端更改回 400。但超越似乎過分了。從顯示狀態：
| Connections | 38587 | | Max_used_connections | 31 |該應用程序是 PHP (fcgi/apache)。
我們的數據庫大小約為 54G。這台機器有 36Gigs 記憶體。Free 告訴我有 13G 免費，甚至包括所有用於記憶體/緩衝區的記憶體。當我嘗試為“innodb_buffer_pool_size”分配 32G 時，由於 mmap 的可分配記憶體不足，MySQL 無法啟動。緩衝池目前為 22G。
mysqltuner 的輸出提到了緩衝池大小和 join_buffer_size - 它不在下面的配置中。
這是 Percona 5.5。
我還設置了一個http://jeremy.zawodny.com/blog/archives/011421.html max_connect_errors=1844674407370954751
max_used_connections在 SHOW STATUS 的輸出中是 21。max_user_connections設置為 0 …沒有限制。編輯：這可能與 Linux 系統上的打開文件/頁面限制有關嗎？
配置文件：
# # 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/顯示狀態
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)
我們有一個單獨的數據庫用於會話，它被配置為使用來自 PHP 的持久連接，這就是錯誤消息的來源。
畢竟，**關閉持久連接可能是答案。**以上來自於無法找到主數據庫配置的任何異常錯誤。