XF 1.4 MySQL Waiting For Table Level Lock

[Ghan_04]

So we've got a fairly large board here and we're averaging around 1000 queries per second to the database total, so probably 900-950 of those will be from this single xenForo board. I'm occasionally seeing in the MySQL status that there are transactions waiting for a table level lock, and sometimes I can see quite a few of these stack up. I'm wondering if this might be causing slight performance degradation and if there is anything I can do about it.
We don't have any serious performance issues, but sometimes I feel that pages could load a bit faster. I have an except from an InnoDB status that I pulled where there are a number of these transactions:

Spoiler: Transaction List

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 67318, OS thread handle 0x7f40d42ea700, query id 1355464 localhost root init
show engine innodb status
---TRANSACTION 3282854343, not started
mysql tables in use 3, locked 0
MySQL thread id 67287, OS thread handle 0x7f40d8094700, query id 1355382 localhost rpnation_user Waiting for table level lock
SELECT session_activity.*
,
user.*,
user_profile.*,
user_option.*
FROM xf_session_activity AS session_activity

LEFT JOIN xf_user AS user ON
(user.user_id = session_activity.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = user.user_id)
LEFT JOIN xf_user_option AS user_option ON
(user_option.user_id = user.user_id)
WHERE (session_activity.view_date > 1407890037)
ORDER BY session_activity.view_date DESC
---TRANSACTION 3282854300, not started
mysql tables in use 4, locked 0
MySQL thread id 67279, OS thread handle 0x7f3fa00d8700, query id 1355007 localhost rpnation_user Waiting for table level lock
SELECT post.*
,
user.*, IF(user.username IS NULL, post.username, user.username) AS username,
user_profile.*,
session_activity.view_date AS last_view_date,
liked_content.like_date
FROM xf_post AS post

LEFT JOIN xf_user AS user ON
(user.user_id = post.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = post.user_id)
LEFT JOIN xf_session_activity AS session_activity ON
(post.user_id > 0 AND session_activity.user_id = post.user_id)
LEFT JOIN xf_liked_content AS liked_content
ON (l
---TRANSACTION 3282854265, not started
mysql tables in use 3, locked 0
MySQL thread id 67277, OS thread handle 0x7f40d40a1700, query id 1354757 localhost rpnation_user Waiting for table level lock
SELECT post.*
,
user.*, IF(user.username IS NULL, post.username, user.username) AS username,
user_profile.*,
session_activity.view_date AS last_view_date,
0 AS like_date
FROM xf_post AS post

LEFT JOIN xf_user AS user ON
(user.user_id = post.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = post.user_id)
LEFT JOIN xf_session_activity AS session_activity ON
(post.user_id > 0 AND session_activity.user_id = post.user_id)
WHERE post.thread_id = '35091'
AND (post.position >= 4480 AND po
---TRANSACTION 3282854241, not started
mysql tables in use 3, locked 0
MySQL thread id 67263, OS thread handle 0x7f3fa021d700, query id 1354606 localhost rpnation_user Waiting for table level lock
SELECT message.*,
user.*, IF(user.username IS NULL, message.username, user.username) AS username,
user_profile.*
,
session_activity.view_date AS last_view_date
FROM xf_conversation_message AS message
LEFT JOIN xf_user AS user ON
(user.user_id = message.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = message.user_id)

LEFT JOIN xf_session_activity AS session_activity ON
(message.user_id > 0 AND session_activity.user_id = message.user_id)
WHERE message.conversation_id = '89645'
ORDER BY m
---TRANSACTION 3282854211, not started
MySQL thread id 67246, OS thread handle 0x7f40d8116700, query id 1354412 localhost rpnation_user Waiting for table level lock
INSERT INTO xf_session_activity
(user_id, unique_key, ip, controller_name, controller_action, view_state, params, view_date, robot_key)
VALUES
('17589', '17589', 'H2T?', 'XenResource_ControllerPublic_Resource', 'View', 'valid', 'resource_id=5917', '1407893636', '')
ON DUPLICATE KEY UPDATE
ip = VALUES(ip),
controller_name = VALUES(controller_name),
controller_action = VALUES(controller_action),
view_state = VALUES(view_state),
params = VALUES(params),
view_date = VALUES(view_date),
robot_key = VALUES(robot_key)
---TRANSACTION 3282854177, not started
mysql tables in use 3, locked 0
MySQL thread id 67220, OS thread handle 0x7f40d8198700, query id 1354173 localhost rpnation_user Waiting for table level lock
SELECT message.*,
user.*, IF(user.username IS NULL, message.username, user.username) AS username,
user_profile.*
,
session_activity.view_date AS last_view_date
FROM xf_conversation_message AS message
LEFT JOIN xf_user AS user ON
(user.user_id = message.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = message.user_id)

LEFT JOIN xf_session_activity AS session_activity ON
(message.user_id > 0 AND session_activity.user_id = message.user_id)
WHERE message.conversation_id = '45344'
AND messa
---TRANSACTION 3282854154, not started
MySQL thread id 67216, OS thread handle 0x7f3fa029f700, query id 1353996 localhost rpnation_user Waiting for table level lock
INSERT INTO xf_session_activity
(user_id, unique_key, ip, controller_name, controller_action, view_state, params, view_date, robot_key)
VALUES
('17846', '17846', 'Bq', 'XenResource_ControllerPublic_Resource', 'View', 'valid', 'resource_id=4270', '1407893636', '')
ON DUPLICATE KEY UPDATE
ip = VALUES(ip),
controller_name = VALUES(controller_name),
controller_action = VALUES(controller_action),
view_state = VALUES(view_state),
params = VALUES(params),
view_date = VALUES(view_date),
robot_key = VALUES(robot_key)
---TRANSACTION 3282854151, not started
MySQL thread id 67215, OS thread handle 0x7f3fa025e700, query id 1353975 localhost rpnation_user Waiting for table level lock
INSERT INTO xf_session_activity
(user_id, unique_key, ip, controller_name, controller_action, view_state, params, view_date, robot_key)
VALUES
('16562', '16562', 'P???', 'XenResource_ControllerPublic_Resource', 'View', 'valid', 'resource_id=5808', '1407893636', '')
ON DUPLICATE KEY UPDATE
ip = VALUES(ip),
controller_name = VALUES(controller_name),
controller_action = VALUES(controller_action),
view_state = VALUES(view_state),
params = VALUES(params),
view_date = VALUES(view_date),
robot_key = VALUES(robot_key)
---TRANSACTION 3282854080, not started
MySQL thread id 67178, OS thread handle 0x7f3fa0321700, query id 1353514 localhost rpnation_user Waiting for table level lock
INSERT INTO xf_session_activity
(user_id, unique_key, ip, controller_name, controller_action, view_state, params, view_date, robot_key)
VALUES
('0', '?LB', '?LB', 'XenForo_ControllerPublic_Forum', 'Forum', 'error', 'node_id=159', '1407893635', '')
ON DUPLICATE KEY UPDATE
ip = VALUES(ip),
controller_name = VALUES(controller_name),
controller_action = VALUES(controller_action),
view_state = VALUES(view_state),
params = VALUES(params),
view_date = VALUES(view_date),
robot_key = VALUES(robot_key)
---TRANSACTION 3282854069, not started
MySQL thread id 67167, OS thread handle 0x7f40d8157700, query id 1353433 localhost rpnation_user Waiting for table level lock
INSERT INTO xf_session_activity
(user_id, unique_key, ip, controller_name, controller_action, view_state, params, view_date, robot_key)
VALUES
('20821', '20821', 'A])?', 'XenResource_ControllerPublic_Resource', 'Updates', 'valid', 'resource_id=5403', '1407893635', '')
ON DUPLICATE KEY UPDATE
ip = VALUES(ip),
controller_name = VALUES(controller_name),
controller_action = VALUES(controller_action),
view_state = VALUES(view_state),
params = VALUES(params),
view_date = VALUES(view_date),
robot_key = VALUES(robot_key)

I hit the post character limit, so I'll see if I can continue this status output in another post.

So, some things that come to mind for me are:
- Why are SELECT statements waiting on a table level lock?
- Do they need this kind of a lock just to pull info?
- What is holding a lock on an entire table?
- Can I tell if this is a read lock, write lock, both, or other, or does it not even matter?
- What other information can I glean from this status that I might have missed?

I'm hoping that someone with a better eye and more experience than I might be able to help explain what's going on. Let me know if I can provide any other useful information.

Thanks,
Ghan

 

[Ghan_04]

Here's the rest, if it's any important:

Spoiler: More Transaction List

---TRANSACTION 3282854024, not started
mysql tables in use 4, locked 0
MySQL thread id 67159, OS thread handle 0x7f40d4227700, query id 1353285 localhost rpnation_user Waiting for table level lock
SELECT post.*
,
user.*, IF(user.username IS NULL, post.username, user.username) AS username,
user_profile.*,
session_activity.view_date AS last_view_date,
liked_content.like_date
FROM xf_post AS post

LEFT JOIN xf_user AS user ON
(user.user_id = post.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = post.user_id)
LEFT JOIN xf_session_activity AS session_activity ON
(post.user_id > 0 AND session_activity.user_id = post.user_id)
LEFT JOIN xf_liked_content AS liked_content
ON (like
---TRANSACTION 3282854009, not started
mysql tables in use 3, locked 0
MySQL thread id 67154, OS thread handle 0x7f40d4123700, query id 1353227 localhost rpnation_user Waiting for table level lock
SELECT message.*,
user.*, IF(user.username IS NULL, message.username, user.username) AS username,
user_profile.*
,
session_activity.view_date AS last_view_date
FROM xf_conversation_message AS message
LEFT JOIN xf_user AS user ON
(user.user_id = message.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = message.user_id)

LEFT JOIN xf_session_activity AS session_activity ON
(message.user_id > 0 AND session_activity.user_id = message.user_id)
WHERE message.conversation_id = '86331'
ORDER BY m
---TRANSACTION 3282853988, not started
mysql tables in use 4, locked 0
MySQL thread id 67149, OS thread handle 0x7f40d8053700, query id 1353070 localhost rpnation_user Waiting for table level lock
SELECT post.*
,
user.*, IF(user.username IS NULL, post.username, user.username) AS username,
user_profile.*,
session_activity.view_date AS last_view_date,
liked_content.like_date
FROM xf_post AS post

LEFT JOIN xf_user AS user ON
(user.user_id = post.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = post.user_id)
LEFT JOIN xf_session_activity AS session_activity ON
(post.user_id > 0 AND session_activity.user_id = post.user_id)
LEFT JOIN xf_liked_content AS liked_content
ON (like
---TRANSACTION 3282853956, not started
MySQL thread id 67130, OS thread handle 0x7f3fa019b700, query id 1352838 localhost rpnation_user Waiting for table level lock
INSERT INTO xf_session_activity
(user_id, unique_key, ip, controller_name, controller_action, view_state, params, view_date, robot_key)
VALUES
('0', '?LE', '?LE', 'XenForo_ControllerPublic_Thread', 'Index', 'valid', 'thread_id=57446&page=92', '1407893634', '')
ON DUPLICATE KEY UPDATE
ip = VALUES(ip),
controller_name = VALUES(controller_name),
controller_action = VALUES(controller_action),
view_state = VALUES(view_state),
params = VALUES(params),
view_date = VALUES(view_date),
robot_key = VALUES(robot_key)
---TRANSACTION 3282854006, not started
MySQL thread id 67125, OS thread handle 0x7f40d4268700, query id 1353210 localhost rpnation_user Waiting for table level lock
INSERT INTO xf_session_activity
(user_id, unique_key, ip, controller_name, controller_action, view_state, params, view_date, robot_key)
VALUES
('16953', '16953', '|?s=', 'XenForo_ControllerPublic_Conversation', 'View', 'valid', 'conversation_id=86331&page=32', '1407893634', '')
ON DUPLICATE KEY UPDATE
ip = VALUES(ip),
controller_name = VALUES(controller_name),
controller_action = VALUES(controller_action),
view_state = VALUES(view_state),
params = VALUES(params),
view_date = VALUES(view_date),
robot_key = VALUES(robot_key)
---TRANSACTION 3282853970, not started
MySQL thread id 67101, OS thread handle 0x7f40d41a5700, query id 1352904 localhost rpnation_user Waiting for table level lock
INSERT INTO xf_session_activity
(user_id, unique_key, ip, controller_name, controller_action, view_state, params, view_date, robot_key)
VALUES
('15900', '15900', 'GR2', 'XenForo_ControllerPublic_Thread', 'Index', 'valid', 'thread_id=55709', '1407893634', '')
ON DUPLICATE KEY UPDATE
ip = VALUES(ip),
controller_name = VALUES(controller_name),
controller_action = VALUES(controller_action),
view_state = VALUES(view_state),
params = VALUES(params),
view_date = VALUES(view_date),
robot_key = VALUES(robot_key)
---TRANSACTION 3282853973, not started
MySQL thread id 67099, OS thread handle 0x7f40d4060700, query id 1352933 localhost rpnation_user Waiting for table level lock
INSERT INTO xf_session_activity
(user_id, unique_key, ip, controller_name, controller_action, view_state, params, view_date, robot_key)
VALUES
('0', 'B?E?', 'B?E?', 'XenForo_ControllerPublic_Thread', 'Index', 'valid', 'thread_id=20880&page=4', '1407893633', 'google')
ON DUPLICATE KEY UPDATE
ip = VALUES(ip),
controller_name = VALUES(controller_name),
controller_action = VALUES(controller_action),
view_state = VALUES(view_state),
params = VALUES(params),
view_date = VALUES(view_date),
robot_key = VALUES(robot_key)
---TRANSACTION 3282853949, not started
MySQL thread id 67077, OS thread handle 0x7f3fa02e0700, query id 1352975 localhost rpnation_user Waiting for table level lock
INSERT INTO xf_session_activity
(user_id, unique_key, ip, controller_name, controller_action, view_state, params, view_date, robot_key)
VALUES
('16430', '16430', 'I??_', 'XenForo_ControllerPublic_Forum', 'Index', 'valid', 'node_name=', '1407893633', '')
ON DUPLICATE KEY UPDATE
ip = VALUES(ip),
controller_name = VALUES(controller_name),
controller_action = VALUES(controller_action),
view_state = VALUES(view_state),
params = VALUES(params),
view_date = VALUES(view_date),
robot_key = VALUES(robot_key)
---TRANSACTION 3282853972, not started
MySQL thread id 67041, OS thread handle 0x7f3fa015a700, query id 1352919 localhost rpnation_user Waiting for table level lock
INSERT INTO xf_session_activity
(user_id, unique_key, ip, controller_name, controller_action, view_state, params, view_date, robot_key)
VALUES
('20840', '20840', '2q@?', 'XenForo_ControllerPublic_Conversation', 'View', 'valid', 'conversation_id=88647&page=241', '1407893633', '')
ON DUPLICATE KEY UPDATE
ip = VALUES(ip),
controller_name = VALUES(controller_name),
controller_action = VALUES(controller_action),
view_state = VALUES(view_state),
params = VALUES(params),
view_date = VALUES(view_date),
robot_key = VALUES(robot_key)

 

[Mike]

xf_session_activity is a memory table, so it only has table level locking. If you have well optimized I/O, you can look at changing it to InnoDB if you prefer. In terms of reducing its (read) usage, you should turn off the online indicator in posts (which requires it to be accessed when displaying a thread).