硬件环境:CPU ==》X5672*2 内存:32G 硬盘 :240G SSD
系统:LINUX CentOS 6.8
软件环境:
PHP:5.6 , nginx,1.14
MYSQL:5.6
分别贴出php 配置和 mysql 配置
[global] pid = /www/server/php/56/var/run/php-fpm.pid error_log = /www/server/php/56/var/log/php-fpm.log log_level = notice [www] listen = /tmp/php-cgi-56.sock listen.backlog = -1 listen.allowed_clients = 127.0.0.1 listen.owner = www listen.group = www listen.mode = 0666 user = www group = www pm = dynamic pm.status_path = /phpfpm_56_status pm.max_children = 300 pm.start_servers = 30 pm.min_spare_servers = 30 pm.max_spare_servers = 300 request_terminate_timeout = 100 request_slowlog_timeout = 30 slowlog = var/log/slow.log
[client] #password = your_password port = 3306 socket = /tmp/mysql.sock [mysqld] binlog_cache_size = 192K thread_stack = 384K join_buffer_size = 4096K query_cache_type = 0 max_heap_table_size = 1024M port = 3306 socket = /tmp/mysql.sock datadir = /www/server/data skip-external-locking performance_schema_max_table_instances=400 table_definition_cache=400 key_buffer_size = 512M max_allowed_packet = 100G table_open_cache = 1024 sort_buffer_size = 2048K net_buffer_length = 8K read_buffer_size = 2048K read_rnd_buffer_size = 1024K myisam_sort_buffer_size = 64M thread_cache_size = 192 query_cache_size = 0M tmp_table_size = 1024M explicit_defaults_for_timestamp = true #skip-networking skip-name-resolve skip-grant-tables max_connections = 16384 max_connect_errors = 100 open_files_limit = 65535 sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #log-bin=mysql-bin #binlog_format=mixed server-id = 1 expire_logs_days = 10 slow_query_log=1 slow-query-log-file=/www/server/data/mysql-slow.log long_query_time=3 back_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 = 1 innodb_lock_wait_timeout = 100 innodb_max_dirty_pages_pct = 90 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_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使用情况如图
linux 链接情况:
当出现大量操作语句的时候,会导致数据库连接失败,不仅仅服务器,本地的navicat 也链接不上,考虑到是否是linux的连接数太大造成的,本人另外测试了在当前机器上发布一版本,利用jmeter 做链接请求,200个线程,每个线程请求200次,本地网络不是特别好,跑了大概一分钟左右,刚开始没有出现链接不上的情况,而且并发处理速度特别快,跑了一万次请求的时候,开始,就变卡,变慢,然后出现数据库连接不上的情况。
请问各位mysql 或PHP 大神指教下,我改如何修正
cpu 和 内存均消耗不超过30%,基本上在20%-30%之间,(次数据是用linux工具查看的,不一定非常准确)
看日志,自己不写日志 —— 多半数据库连接不足。
max_connections=16384 最大值了,线程也开了,应该不至于 二十多万个链接
@铜锣湾-猪哥: 日志说明一切。