一：專案描述
專案是一個網盤+門戶網站+CS客戶端架構。 系統環境采用suse Linux、apache2.2、mysql5.5.6版本。服務器配置為4顆intel Xeon E7-4820 V4 10C。配置64GB DDR4 2400MHz記憶體。
專案首先為一個網盤存盤分享系統，其次針對門戶網站的資訊發布采取以檔案傳輸到固定本地檔案夾（欄目）的方式，通過.doc轉換為html呈現。舉例說明，當A發布一篇資訊時，其他有權限的人都會同步A發布的.doc正文同步到本地。服務端有一個整體設定客戶端輪巡檢測的時間周期設定，當前采用5分鐘輪巡一次。 前端門戶資料庫引擎采用MyIsam、網盤引擎采用Innodb。
現在用戶每天在線人數總量在350--400之間。遇到的問題就是，有的時候會在一天當中發生某一個時間點，httpd的執行緒數會突然從400增加到1000上下，占總記憶體的30%-40%之間（記憶體倒不是問題，因為還有余量），cpu使用率從600%---800%突然上升到1000%---3000%。 mysql的話記憶體占用非常低，只占3%的記憶體，而mysql平時的cpu使用率在1000%----3000%之間。在卡頓程序中，查詢tcp鏈接的情況，也會出現close_wait的情況。下面我貼一下ATOP的圖（卡頓狀態下）。
卡頓時ATOP監控狀態
卡頓時TCP查詢狀態圖
當前資料庫組態檔
# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /data/mysql/mysql.socket
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /data/mysql/mysql.socket
slow_query_log=1
open_files_limit = 30000
skip-external-locking
key_buffer_size = 2048M
max_allowed_packet = 32M
table_open_cache = 4096
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 1000
query_cache_size = 0
query_cache_type = 0
back_log=600
max_connections=8000
tmp_table_size = 64M
max_heap_table_size = 64M
binlog_cache_size = 2M
join_buffer_size = 8M
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency = 80
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
#master-host=192.168.2.81
#master-user=gl
#master-password=123456
#master-port=3306
#master-connect-retry=60
#binlog-do-db=dbone
#binlog-ignore-db=information_schema
#binlog-ignore-db=mysql
#replicate-do-db=dbone
#replicate-ignore-db=mysql,information_schema
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
# 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 = 6G
#innodb_log_files_in_group=2
innodb_thread_concurrency=0
#innodb_additional_mem_pool_size = 80M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 900M
innodb_log_buffer_size = 20M
innodb_flush_log_at_trx_commit = 2
innodb_write_io_threads=9
innodb_read_io_threads=9
innodb_io_capacity=500
innodb_purge_threads=1
innodb_additional_mem_pool_size = 50M
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
apache 組態檔 httpd-mpm.conf
#
# Server-Pool Management (MPM specific)
<IfModule !mpm_netware_module>
PidFile "logs/httpd.pid"
</IfModule>
#
# The accept serialization lock file MUST BE STORED ON A LOCAL DISK.
#
<IfModule !mpm_winnt_module>
<IfModule !mpm_netware_module>
LockFile "logs/accept.lock"
</IfModule>
</IfModule>
#
# Only one of the below sections will be relevant on your
# installed httpd. Use "apachectl -l" to find out the
# active mpm.
#
# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_prefork_module>
ServerLimit 6000
StartServers 400
MinSpareServers 400
MaxSpareServers 650
MaxClients 6000
MaxRequestsPerChild 30000
</IfModule>
apache采用 perfork模式
linux sysctl.conf 設定 基本注釋了后面加上的一些引數，當時加了沒有啥效果
# Disable response to broadcasts.
# You don't want yourself becoming a Smurf amplifier.
net.ipv4.icmp_echo_ignore_broadcasts = 1
# enable route verification on all interfaces
net.ipv4.conf.all.rp_filter = 1
# enable ipV6 forwarding
#net.ipv6.conf.all.forwarding = 1
# increase the number of possible inotify(7) watches
fs.inotify.max_user_watches = 65536
# avoid deleting secondary IPs on deleting the primary IP
net.ipv4.conf.default.promote_secondaries = 1
net.ipv4.conf.all.promote_secondaries = 1
#開啟重用
#net.ipv4.tcp_tw_reuse=1
#開啟tcp連接中的快速回收
#net.ipv4.tcp_tw_recycle=1
#net.ipv4.tcp_keepalive_time=1800
#減少超時前的探測次數
#net.ipv4.tcp_keepalive_probes=5
#開啟SYN Cookies 當SYN等待佇列溢位時，啟用cookies，0為關閉，1為開啟
#net.ipv4.tcp_syncookies=1
#net.ipv4.tcp_keepalive_intvl=15
#優化網路設備接收佇列
#net.core.netdev_max_backlog=6000
#SYN佇列長度，默認1024，加大佇列
#net.ipv4.tcp_max_syn_backlog=8192
#如果套接字由本地端要求關閉，這個引數決定了他保持在FIN-WAIT-2狀態的時間
#net.ipv4.tcp_fin_timeout=30
#net.ipv4.tcp_max_tw_buckets=5000
#net.ipv4.tcp_synack_retries=2
#net.ipv4.tcp_syn_retries=2
#net.ipv4.ip_local_port_range=1024 65000
uj5u.com熱心網友回復：
看下slowlog吧uj5u.com熱心網友回復：
慢日志里沒有資料- -uj5u.com熱心網友回復：
這個需要看看資料庫是否當時存在嚴重的鎖征用轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/34993.html
標籤：MySQL
下一篇：mysql limit 問題