Mysql

MySQL 在正常情況下連接過多(Percona,InnoDB)

  • August 28, 2012

在正常情況下,我們遇到了太多的連接錯誤,所以我假設這是一個配置問題。四個網路伺服器,使用持久連接,部分原因是這會觸發太多連接錯誤太快。在 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

調試mysql連接太多問題

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 的持久連接,這就是錯誤消息的來源。

畢竟,**關閉持久連接可能是答案。**以上來自於無法找到主數據庫配置的任何異常錯誤。

引用自:https://serverfault.com/questions/421968