Ad Widget

Collapse

Long semaphore wait problem with MySQL-InnoDB

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • [mm]
    Junior Member
    • Dec 2009
    • 6
    #1

    Long semaphore wait problem with MySQL-InnoDB

    Hi, I'm using Zabbix v1.8.2 to survey a total of 9 servers. I'm using MySQL with InnoDB tables and the following InnoDB-Settings:

    Code:
    innodb_buffer_pool_size	= 512M
innodb_log_buffer_size	= 4M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method	= O_DIRECT
innodb_log_file_size    = 64M
    My Problem is that I get the MySQL-Error "InnoDB: Warning: a long semaphore wait:" almost once a day with transactions like:

    Code:
    ------------
TRANSACTIONS
------------
Trx id counter 0 916566392
Purge done for trx's n:o < 0 916565583 undo n:o < 0 0
History list length 130
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 9882, OS thread id 1165756752
MySQL thread id 884280, query id 1300624799 127.0.0.1 mailuser
---TRANSACTION 0 916377346, not started, process no 9882, OS thread id 1158568272
MySQL thread id 17, query id 1302251990 localhost zabbix
---TRANSACTION 0 916566391, ACTIVE 188 sec, process no 9882, OS thread id 1088555344 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 495, query id 1300624872 localhost zabbix Sending data
select hostid,status from hosts where host='web2' and status in (0,1) and proxy_hostid=0 and hostid between 000000000000000 and 099999999999999
---TRANSACTION 0 916566390, ACTIVE 201 sec, process no 9882, OS thread id 1159633232 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 499, query id 1300432513 localhost zabbix Sending data
select druleid,iprange,name,unique_dcheckid from drules where proxy_hostid=0 and status=0 and (nextcheck<=1287974653 or nextcheck>1287974653+delay) and mod(druleid,1)=0 and druleid between 000000000000000 and 099999999999999
Trx read view will not see trx with id >= 0 916566391, sees < 0 916566333
---TRANSACTION 0 916566389, ACTIVE 208 sec, process no 9882, OS thread id 1159366992 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 498, query id 1300326337 localhost zabbix Sending data
select hostid,status from hosts where host='lb1' and status in (0,1) and proxy_hostid=0 and hostid between 000000000000000 and 099999999999999
...
    Since these are all InnoDB issues I'm thinking about switching the engine to MyISAM. Has anyone else had this problem?
    Tags: innodb, long semaphore wait
    • MrKen
      Senior Member
      • Oct 2008
      • 652
      #2
      Did you try Googling "InnoDB: Warning: a long semaphore wait:" (I just did)

      It would appear to be a problem in your my.cnf

      Your innodb_buffer_pool_size is quite low. How much RAM do you have?

      How many Max_connections do you have set?

      Try the sql forums.

      MrKen
      Disclaimer: All of the above is pure speculation.

        Comment

        • [mm]
          Junior Member
          • Dec 2009
          • 6
          #3
          I have 4GB of RAM available but the server does some other stuff as well, do you really think 512MB is too low? As far as I can remember the default is 8MB.

          My Connection-Limit is 100 connections but as far as I can tell Zabbix is using 18 persistent connections so this shouldn't be a problem either.

          I'm using InnoDB with a lot of other applications as well without problems so I was just wondering if someone had the same problem with Zabbix.

            Comment

            Previous template Next
            Working...