1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.2 InnoDB , Waiting for table level lock

Discussion in 'XenForo Questions and Support' started by ehsanix, Feb 5, 2014.

  1. ehsanix

    ehsanix Member

    I am a newcomer from VB and have read almost all of the "innodb or myisam" and "mysql tuninig" threads.
    Database is InnoDB (Except those 2-3 table which is MyISAM and few MEMORY) based on this.

    But MySQL hangs every few hours with lots of "Waiting for table level lock". Those queries who are waiting are:
    SELECT post.* , bb_code_parse_cache.parse_tree AS message_parsed, bb_code_parse_cache.cache_version AS message_cache_version, user.* ....
    INSERT INTO xf_session_activity  (user_id, unique_key, ip, controller_name, controller_action, ...
    Any Idea why this happens?
    I think this table level lock is because of some queries which are related to those non-InnoDB tables.
    Should I switch xf_session_activity from MEMORY to InnoDB ?
  2. Mike

    Mike XenForo Developer Staff Member

    In general, memory will be better for the session activity table, but you can try change it if you want to give it a shot.

    I suspect there are actually other queries here that are the root cause. The select wouldn't lock, but a previous write query might. It may be worth running:




    While this is occurring. You want to look at the oldest queries in the process list as likely culprits. I would guess that they may just be deleting a fair amount of data, in which case optimizing your I/O would be the main thing to look at. (You didn't mention how long it gets "stuck" for.)
    Divvens and ehsanix like this.
  3. ehsanix

    ehsanix Member

    Thanks for your reply.
    As this happens a few times a day, I think I have found some more info why this happens. I am suspicious about php-fpm config (which I tried to tune and waiting to check the result)
    The only query when this happen is in the processlist:
    INSERT INTO xf_session_activity (user_id, unique_key, ip, controller_name, controller_action, ...
    which is waiting for table level lock. Around 100 queries. php-fpm is not responding during this issue but the mysql command line is working fine. This is why I think it is because of php-fpm.

    About my xenforo and server:
    around 8M posts , 2000 online users, Content is UTF-8 (persian)
    Database size is 20GB (half used for conversations, Attachments on disk)
    Server is Dual Xeon Hexa core with 72GB Ram, 4x SAS 15k (2 for web , 2 for db , both raid)
    Centminmod (Nginx + php-fpm 5.3.28 + MariaDB 5.5)
    Memcached active + Enhanced search (ElasticSearch)
  4. ehsanix

    ehsanix Member

    Hello Again,
    I have updated some php-fpm settings. Also changed mysql max connection (higher).
    So far so good.
    Mike likes this.
  5. ehsanix

    ehsanix Member

    Now I have deadlock on my server log:
    Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Deadlock found when trying to get lock; try restarting transaction - library/Zend/Db/Statement/Mysqli.php:214

Share This Page