MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
sujin190
V2EX  ›  MySQL

mysql 奇怪的死锁问题求解

  •  
  •   sujin190 ·
    snower · Jun 13, 2016 · 5452 views
    This topic created in 3645 days ago, the information mentioned may be changed or developed.
    InnoDB: Warning: a long semaphore wait:
    --Thread 139666312808192 has waited at buf0buf.cc line 2576 for 342.00 seconds the semaphore:
    S-lock on RW-latch at 0x7f077d5e08c0 created in file buf0buf.cc line 1069
    number of readers 0, waiters flag 0, lock_word: 100000
    Last time read locked in file btr0cur.cc line 3592
    Last time write locked in file /export/home/pb2/build/sb_0-17604105-1452827438.0/rpm/BUILD/mysql-5.6.29/mysql-5.6.29/storage/innobase/buf/buf0buf.cc line 3691
    InnoDB: Warning: a long semaphore wait:
    --Thread 139665275766528 has waited at buf0buf.cc line 2576 for 331.00 seconds the semaphore:
    S-lock on RW-latch at 0x7f077d5e08c0 created in file buf0buf.cc line 1069
    number of readers 0, waiters flag 0, lock_word: 100000
    Last time read locked in file btr0cur.cc line 3592
    Last time write locked in file /export/home/pb2/build/sb_0-17604105-1452827438.0/rpm/BUILD/mysql-5.6.29/mysql-5.6.29/storage/innobase/buf/buf0buf.cc line 3691
    InnoDB: Warning: a long semaphore wait:
    --Thread 139661653473024 has waited at buf0buf.cc line 2576 for 313.00 seconds the semaphore:
    S-lock on RW-latch at 0x7f0701de88c0 created in file buf0buf.cc line 1069
    number of readers 0, waiters flag 0, lock_word: 100000
    Last time read locked in file row0sel.cc line 3220
    Last time write locked in file /export/home/pb2/build/sb_0-17604105-1452827438.0/rpm/BUILD/mysql-5.6.29/mysql-5.6.29/storage/innobase/buf/buf0buf.cc line 3691
    InnoDB: Warning: a long semaphore wait:
    --Thread 139665089767168 has waited at buf0buf.cc line 2576 for 302.00 seconds the semaphore:
    S-lock on RW-latch at 0x7f0701de88c0 created in file buf0buf.cc line 1069
    number of readers 0, waiters flag 0, lock_word: 100000
    Last time read locked in file row0sel.cc line 3220
    Last time write locked in file /export/home/pb2/build/sb_0-17604105-1452827438.0/rpm/BUILD/mysql-5.6.29/mysql-5.6.29/storage/innobase/buf/buf0buf.cc line 3691
    InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
    InnoDB: Pending preads 2, pwrites 0
    

    这可能是什么问题导致的呢?读并发太高了? mysql 从库

    我在后面输出的内容中看到了这个

    267.14 inserts/s, 149.65 updates/s, 0.00 deletes/s, 362984.67 reads/s

    这个的意思是真的有 36 万的查询

    3 replies    2016-06-13 21:58:39 +08:00
    Infernalzero
        1
    Infernalzero  
       Jun 13, 2016
    LATEST DETECTED DEADLOCK 那段你没贴哪知道是什么原因
    sujin190
        2
    sujin190  
    OP
       Jun 13, 2016
    @Infernalzero 没有这段啊。。不过有这个 INNODB MONITOR OUTPUT
    tcsky
        3
    tcsky  
       Jun 13, 2016
    感觉这边提技术类问题回答的人越来越少了..
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Solana   ·   1014 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 23:15 · PVG 07:15 · LAX 16:15 · JFK 19:15
    ♥ Do have faith in what you're doing.