mysql 并发 链接失败_php连接 mysql,稍微有点并发量的时候会出现数据库链接失败的情况...

硬件环境:CPU ==》X5672*2    内存:32G   硬盘 :240G SSD

系统:LINUX CentOS 6.8

软件环境:

PHP:5.6  ,  nginx,1.14

MYSQL:5.6

分别贴出php 配置和 mysql 配置

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

[global]

pid= /www/server/php/56/var/run/php-fpm.piderror_log = /www/server/php/56/var/log/php-fpm.loglog_level=notice

[www]

listen= /tmp/php-cgi-56.sock

listen.backlog = -1listen.allowed_clients = 127.0.0.1listen.owner =www

listen.group =www

listen.mode = 0666user=www

group=www

pm=dynamic

pm.status_path = /phpfpm_56_status

pm.max_children = 300pm.start_servers = 30pm.min_spare_servers = 30pm.max_spare_servers = 300request_terminate_timeout= 100request_slowlog_timeout= 30slowlog= var/log/slow.log

PHP-cgi配置

[client]#password = your_password

port = 3306socket= /tmp/mysql.sock

[mysqld]

binlog_cache_size=192K

thread_stack=384K

join_buffer_size=4096K

query_cache_type= 0max_heap_table_size=1024M

port= 3306socket= /tmp/mysql.sock

datadir= /www/server/data

skip-external-locking

performance_schema_max_table_instances=400table_definition_cache=400key_buffer_size=512M

max_allowed_packet=100G

table_open_cache= 1024sort_buffer_size=2048K

net_buffer_length=8K

read_buffer_size=2048K

read_rnd_buffer_size=1024K

myisam_sort_buffer_size=64M

thread_cache_size= 192query_cache_size=0M

tmp_table_size=1024M

explicit_defaults_for_timestamp= true

#skip-networking

skip-name-resolve

skip-grant-tables

max_connections= 16384max_connect_errors= 100open_files_limit= 65535sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#log-bin=mysql-bin#binlog_format=mixed

server-id = 1expire_logs_days= 10slow_query_log=1slow-query-log-file=/www/server/data/mysql-slow.loglong_query_time=3back_log= 900

#log_queries_not_using_indexes=on

#loose-innodb-trx=0#loose-innodb-locks=0#loose-innodb-lock-waits=0#loose-innodb-cmp=0#loose-innodb-cmp-per-index=0#loose-innodb-cmp-per-index-reset=0#loose-innodb-cmp-reset=0#loose-innodb-cmpmem=0#loose-innodb-cmpmem-reset=0#loose-innodb-buffer-page=0#loose-innodb-buffer-page-lru=0#loose-innodb-buffer-pool-stats=0#loose-innodb-metrics=0#loose-innodb-ft-default-stopword=0#loose-innodb-ft-inserted=0#loose-innodb-ft-deleted=0#loose-innodb-ft-being-deleted=0#loose-innodb-ft-config=0#loose-innodb-ft-index-cache=0#loose-innodb-ft-index-table=0#loose-innodb-sys-tables=0#loose-innodb-sys-tablestats=0#loose-innodb-sys-indexes=0#loose-innodb-sys-columns=0#loose-innodb-sys-fields=0#loose-innodb-sys-foreign=0#loose-innodb-sys-foreign-cols=0

default_storage_engine=InnoDB

innodb_data_home_dir= /www/server/data

innodb_data_file_path= ibdata1:10M:autoextend

innodb_log_group_home_dir= /www/server/data

innodb_buffer_pool_size=20480M

innodb_log_file_size=512M

innodb_log_buffer_size=128M

innodb_flush_log_at_trx_commit= 1innodb_lock_wait_timeout= 100innodb_max_dirty_pages_pct= 90innodb_read_io_threads= 16innodb_write_io_threads= 16innodb_io_capacity=3000[mysqldump]

quick

max_allowed_packet=16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size=256M

sort_buffer_size=4M

read_buffer=2M

write_buffer=2M

[mysqlhotcopy]

interactive-timeout

数据库表:类型:MyISAM

数据库引擎用的是:InnoDB

在高并发的时候:数据库查看最大使用连接数是:5400

没有拥堵的执行语句,基本上都是用 insert和update 的比较多一些,查询量非常小,

linux使用情况如图

aa0a40df2bf6a00b6288b32ee54b3c2e.png

3ea86540afdee3994656017a446f3be7.png

linux 链接情况:

98a3a4ee3628bca3240b6d63c59672c4.png

当出现大量操作语句的时候,会导致数据库连接失败,不仅仅服务器,本地的navicat 也链接不上,考虑到是否是linux的连接数太大造成的,本人另外测试了在当前机器上发布一版本,利用jmeter 做链接请求,200个线程,每个线程请求200次,本地网络不是特别好,跑了大概一分钟左右,刚开始没有出现链接不上的情况,而且并发处理速度特别快,跑了一万次请求的时候,开始,就变卡,变慢,然后出现数据库连接不上的情况。

请问各位mysql 或PHP 大神指教下,我改如何修正

cpu 和 内存均消耗不超过30%,基本上在20%-30%之间,(次数据是用linux工具查看的,不一定非常准确)


版权声明:本文为weixin_33050117原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接和本声明。