PDA

View Full Version : mysql table lock issues advice on tuning or a fix


helpdesk@purch
12-22-2015, 03:51 PM
Hey Guys -

I'm getting the occasional table lock and was hoping for some advice on fine tuning our setup - The database does not seem overloaded and has plenty of cpu/memory

below is the queries that are locking up the table, any suggestions?

/rdsdbbin/mysql/bin/mysqld, Version: 5.6.22-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 151222 4:06:06
# User@Host: vBulletin[vBulletin] @ [10.210.62.243] Id: 98240505
# Query_time: 718.353914 Lock_time: 417.029493 Rows_sent: 7008 Rows_examined: 31528
use db_name;
SET timestamp=1450757166;
SELECT
user.username, (user.options & 512) AS invisible, user.usergroupid,
session.userid, session.inforum, session.lastactivity, session.badlocation,
IF(user.displaygroupid=0, user.usergroupid, user.displaygroupid) AS displaygroupid, infractiongroupid

FROM vb_session AS session
LEFT JOIN vb_user AS user ON(user.userid = session.userid)

WHERE session.lastactivity > 1450752816

Dave
12-22-2015, 04:06 PM
What is your session timeout set at in vBulletin?
Does the vb_session table contain a lot of rows?

helpdesk@purch
12-22-2015, 06:39 PM
Session timeout is set at 3600
The vb_session table contains 2065 rows

Dave
12-22-2015, 06:47 PM
What storage engine are you using? InnoDB is recommended to be used instead of MyISAM.
Also this may be of interest to you: http://linuxbox.co.uk/vbulletin_performance_tuning.php