我在 win 2008 R2 x64 标准版上运行 MySql server 5.1.52-community。它是生产服务器。当应用程序层“另一台服务器”引入过多请求/tcp 连接/“每秒超过 2000 个查询”时，应用程序层就会停止。
应用层是非常稳定的企业产品，已在太多企业中使用。并且支持团队明确地说是数据库服务器出了问题。
因此我多次调整了 mysql 配置，但在高负载情况下应用程序层仍然停止。
服务器有 16 GB 内存，但 mysql 仅使用大约 5 GB。所以第一个问题是如何让 mysql 服务器服务使用高达 12 GB。
我注意到一件奇怪的事情，mysqld 进程有超过一百万个句柄“1,114,345”，这非常不正常，任何正常进程最多只能获得 2000 个句柄！所以专家们可以接受吗？如果不可以，那么如何解决这个问题。
该数据库是没有视图或 SP 的 innoDB。
请帮忙，谢谢，
编辑：查看专家的评论后，这是当前的 mysql 配置：
[client]
port=3306
[mysql]
default-character-set=utf8
# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this
# file.
#
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Autonomy/MySQL/"
#Path to the database root
datadir="D:/MySQL Datafiles/data/"
# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=utf8
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=400
# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=84M
# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_cache=256
# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=369M
# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before. This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size=8
#*** MyISAM Specific options
# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size=100G
# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer the
# key cache method. This is mainly used to force long character keys in
# large tables to use the slower key cache method to create the index.
myisam_sort_buffer_size=738M
# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=320M
# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size=64K
read_rnd_buffer_size=256K
# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
sort_buffer_size=256K
#*** INNODB Specific options ***
innodb_data_home_dir="D:/MySQL Datafiles/"
# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
#skip-innodb
# Additional memory pool that is used by InnoDB to store metadata
# information. If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS. As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size=26M
# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=2
# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=13M
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
#innodb_buffer_pool_size=1243M
innodb_buffer_pool_size=4096M
# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=622M
# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=18
#Enter a name for the query log file. Otherwise a default name will be used.
# incase of remove command # for log sql queries will be logged
#log
#innod table extend
innodb_data_file_path=ibdata1:18M;inno_db_001:400M:autoextend
#innodb_log_group_home_dir="D:/MySQL Datafiles/"
lower_case_table_names=1
innodb_file_io_threads=4
innodb_lock_wait_timeout=50
#replication configuration
log-bin=mysql-bin
server-id = 1
所以我认为如果我将innodb_buffer_pool_size=4096M更改
为
innodb_buffer_pool_size=12G，
则将有 12GB 分配给 mysqld 服务/进程。对吗？还应该更改什么？
我还发现，如果查询花费的时间超过 15 分钟，应用程序将认为该尝试失败，尽管 mysql 仍在努力获取它！也许这就是导致句柄变得非常大的原因！
此外，我认为一些查询必须从映射的网络驱动器读取。但什么也无法改变这一点。这完全不受我的控制。
答案1
句柄有时会让人很头疼。对于遇到此问题的客户，我通常会采取一种快速而简单的临时解决方案：运行以下命令：
FLUSH TABLES;
这将关闭所有表上打开的句柄，然后再将其打开。我确信未正确关闭的句柄会消失，特别是当一个表有数十个句柄打开，但只有一两个句柄实际在使用时。我曾在运行此操作后立即看到我构建的 MONyog 图表中的句柄下降。
您可能正在运行看起来很正常的查询，其 EXPLAIN 计划什么也没告诉您。但是，将足够多的查询放在一起，单独堆积打开的句柄，您可能会遇到竞争条件，其中句柄打开的速度比关闭的速度快。可见的净效应是慢查询日志中有很多条目，这些查询在独立执行时工作正常。此外，您将储存查询，这些查询只是空转，执行复制到临时表或一些间歇性排序。
以下是 Handles 的示例状态变量
Handler_read_last：读取索引中最后一个键的请求数。使用 ORDER BY 时，服务器将发出第一个键请求，然后发出几个下一个键请求，而使用 ORDER BY DESC 时，服务器将发出最后一个键请求，然后发出几个上一个键请求。此变量是在 MySQL 5.5.7 中添加的。
现在这在 MySQL 5.1.52 中不存在，但这里有一个问题：您是否有执行 ORDER BY ... DESC 的查询？如果有，那么您希望有 Handler_read_last。既然您没有，MySQL 会对 ORDER BY ... DESC 做什么？它会遍历整个索引以到达最后一行，收集键并对其进行排序。您的应用程序中是否有很多这种性质的查询？？？（您研究的问题）
以下是 Handles 的另一个状态变量：
Handler_read_first：读取索引中第一个条目的次数。如果此值很高，则表明服务器正在执行大量完整索引扫描；例如，SELECT col1 FROM foo，假设 col1 已编入索引。
您研究的另一个问题：您是否有很多执行完整索引扫描的查询???
另一个猜测是：检查你的打开文件限制和复制代码。如果打开文件数量的上限正在生成新句柄并留下其他句柄，则可能需要增加其中一个或两个。
答案2
mysqld 进程有超过一百万个句柄“1,114,345”
看起来很高。
查询花费的时间超过 15 分钟，应用程序将认为它失败，尽管 mysql 仍在努力获取它！
呃。
你没有为 mysql 配置超时吗？
逻辑层在做什么？它使用持久连接吗？您可以切换到非持久连接吗？
我认为有些查询必须从映射的网络驱动器读取
天啊！如果数据必须存在于单独的服务器上，那么让 MySQL 在该服务器上运行并使用联合驱动程序或集群复制。这是让它表现得像一个设计良好的系统的唯一方法（除了 iscsi，它实际上并不适合在文件系统上运行）。
一旦您解决了远程驱动器问题，我建议：
1）尽可能使用非持久连接
2) 查询调优 - 如果你的查询都运行得足够快，那就不是问题了。我使用了一些自制脚本以及这段 Perl 代码确定需要做哪些工作。
3）在您的服务器上运行mysqltuner.pl（也使用perl）。
有一个适用于 MSWindows 的免费 perl 实现这里