Ghan_04
Active member
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:
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
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:
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)
---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)
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
