XF 1.2 InnoDB , Waiting for table level lock

[ehsanix]

Hello
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.* ....

and

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 ?

 

[Mike]

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:

SHOW FULL PROCESSLIST;

and

SHOW ENGINE INNODB STATUS;

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.)

 

[ehsanix]

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)

 

[ehsanix]

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

 

[ehsanix]

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