[Warning] Aborted connection 11203 to db: 'ide' user: 'nuc' host: 'prd01.mb.com' (Got an error writi
来源:互联网 发布:centos gcc使用 编辑:程序博客网 时间:2024/05/19 06:51
PS:一台物理机扯分了3个虚拟机,一个主db,一个主备,一个从备。
切换到0301的时候
Sep 6 09:16:16 prddb0301 mysqld: 130906 9:16:16 [Warning] Aborted connection 11203 to db: 'ide' user: 'nuc' host: 'prd01.mb.com' (Got an error writing communication packets)Sep 6 09:16:16 prddb0301 mysqld: 130906 9:16:16 [Warning] Aborted connection 12498 to db: 'ide' user: 'nuc' host: 'prd02.mb.com' (Got an error writing communication packets)
Sep 6 09:16:16 prddb0301 mysqld: 130906 9:16:16 [Warning] Aborted connection 13503 to db: 'ide' user: 'nuc' host: 'prd03.mb.com' (Got an error writing communication packets)
Sep 6 09:16:17 prddb0301 mysqld: 130906 9:16:17 [Warning] Aborted connection 6681 to db: 'ide' user: 'nuc' host: 'prd11.mb.com' (Got an error writing communication packets)
Sep 6 09:16:18 prddb0301 mysqld: 130906 9:16:18 [Warning] Aborted connection 15070 to db: 'ide' user: 'nuc' host: 'prd12.mb.com' (Got an error writing communication packets)
刚才切换到0301的时候,connection 1800的时候,后台error报错。
大概原因:shard卡住会让app server都卡住,最差情况下,所以即使一个shard可能影响也挺大,比较担心。
1 check 用show engine innodb status\G;进行分析
看到:所有的thread都在d estimating records in index range;有很多类似的很多的SQL等待:
select ENTITLEMENT_ID, USER_ID, PRODUCT_ID, GRANT_DATE, EXPIRATION_DATE, DATE_CREATED, STATUS, CREATED_BY, MODIFIED_BY, DATE_MODIFIED, STATUS_REASON_CODE, ENTITLEMENT_TAG, VERSION, PRODUCT_CA
TALOG, USE_COUNT, GROUP_ID, ENTITLEMENT_SOURCE, ENTITLEMENT_TYPE, PROJECT_ID, DEVICE_ID, MANAGED_LIFECYCLE, CONSUMABLE, ORIGIN_PERMISSIONS, EXTERNAL_TYPE, EXTERNAL_ID from ide.entil
T as ent where 1 = 1 and STATUS = 1 and USER_ID = 2331523206069 and DATE_MODIFIED >= '2013-09-06 01:39:00' order by ENTITLEMENT_ID DESC limit 0, 5000
我觉得还是内存问题,把hugepage 拿掉,现在的行为就是机器不繁忙但所有的资源都消耗在iowait上,导致卡死,刚才我们改动过的就是内存。
http://www.51testing.com/?uid-225738-action-viewspace-itemid-235472
2 check memory
[ed@prdkvm35 ~]$ free -g
total used free shared buffers cached
Mem: 125 124 1 0 1 0
-/+ buffers/cache: 122 3
看到物理机器上面一共3G内存,把物理机器上面的vm内存再弄小点,给物理机器的内存设置大一些。修改完后,重启动vm,然后启动Mysql,并且failover writer 从0302到0301上面。
3 failover之后,connection过多
failover过后,看到0301的db上面的conntion client猛增到2000多个,超过正常范围值500多4倍了,而且client还在不停的增长,马上failover writer 0301db到0302db,connection恢复正常了。
4 check,检查0301的my.cnf配置文件
将login audit去掉,然后改小innodb_buffer_pool_size,从60G改称48G。再进行failover writer 从0302到0301上面。
5 check again
failover还是暴增到2000多个,超过正常范围值500多4倍了,而且client还在不停的增长,马上failover writer 0301db到0302db,connection恢复正常了。
6 内存再设置小一些,
把主备vm和从备vm的内存再设置小一些,每个去掉4G,修改完后,再次重启vm以及vm上面的MySQL服务。再failover writer 从0302到0301上面。check之后发现conntions还是t猛增到2000多个,超过正常范围值500多4倍了。而且IO wait好高啊!然后failover回去到db0302 ,failover writer 从0302到0301上面。
7 每次IO好高,那么去看看my.cnf里面的innodb_flush_log_at_trx_commit之类innodb writer的参数是否合理。
发现my.cnf里面innodb_flush_log_at_trx_commit = 1; God,貌似找到问题所在了,马上修改my.cnf 将innodb_flush_log_at_trx_commit = 0;
ok,然后再failover writer 从0302到0301上面,check conntion clients,猛增到800多后稳定下来1分钟后,connection clients稳定在500左右。OK,成了!
PS:这个prod上面的my.cnf是原来的DBA设置的,在每次failover的时候,都需要保持 Seconds_Behind_Master=0;
追得太慢NOC又call我了,看到sync_binlog=1 了,我改成0了,这样很快Seconds_Behind_Master=0了。
- [Warning] Aborted connection 11203 to db: 'ide' user: 'nuc' host: 'prd01.mb.com' (Got an error writi
- [Warning] Aborted connection 11203 to db: 'ide' user: 'nuc' host: 'prd01.mb.com' (Got an error writi
- MYSQL [Warning] Aborted connection 42355 to db
- Error - Connection to host as user oracle failed: ERROR: NMO not setuid-root (Unix-only)
- Host Credentials报错"Connection to host as user oracle failed: ERROR: Wrong password for user"的解决一例
- [Warning] Aborted connection问题探究
- Connection to host as user oracle failed: ERROR: NMO not setuid-root
- Hadoop error: Bad connection to FS. command aborted.
- Got error 157 'Connection to NDB failed' from NDBINFO
- ssh: connect to host github.com port 22: Connection refused
- sh: connect to host github.com port 22: Connection refused
- ssh: connect to host github.com port 22: Connection refused
- mongodb 避免Out of semaphores to get db connection error
- hadoop Bad connection to FS. command aborted
- 手动部署Oracle RAC EM报错 WARNING: Error during db connection : ORA-12514
- mysqldump: Got error: 1142: SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table
- Unknown SSL protocol error in connection to github.com:443
- Unknown SSL protocol error in connection to github.com:-9800
- common library in nova (1)
- s3c2440的LCD应用
- 为了安全如何隐藏nginx和php的版本号等信息呢?
- CI加载目录_CI框架
- 一个简单的iBatis入门例子
- [Warning] Aborted connection 11203 to db: 'ide' user: 'nuc' host: 'prd01.mb.com' (Got an error writi
- VS2008 LINK : fatal error LNK1000: Internal error during IncrBuildImage
- 如何正确的关闭 MFC 线程
- exit与atexit
- 类加载器
- C# socket 服务端与客户端通信演示代码
- 产品经理的七种武器
- iframe模拟html页面弹出框
- android4.0平台通过echo命令控制LED-基于x210v3开发板