Question regarding "session" table, locks and table size...
 

Hey everyone.

Experiencing some odd behavior. The server runs great and then suddenly mysql processes shoot up to the max limit and the site dies... even though server load is very low and everything else seems fine. This has happened quite often recently and it's pretty confusing. I'll post the server config and stuff in the appropriate area shortly.

Anyways, during these times, I notice a lot of locks happening on this table "vb_session". The table is stored in memory so it should be pretty fast. Here is the table I am referring too:

-----
| vb_session | CREATE TABLE `vb_session` (
`sessionhash` char(32) NOT NULL DEFAULT '',
`userid` int(10) unsigned NOT NULL DEFAULT '0',
`host` char(15) NOT NULL DEFAULT '',
`idhash` char(32) NOT NULL DEFAULT '',
`lastactivity` int(10) unsigned NOT NULL DEFAULT '0',
`location` char(255) NOT NULL DEFAULT '',
`useragent` char(100) NOT NULL DEFAULT '',
`styleid` smallint(5) unsigned NOT NULL DEFAULT '0',
`languageid` smallint(5) unsigned NOT NULL DEFAULT '0',
`loggedin` smallint(5) unsigned NOT NULL DEFAULT '0',
`inforum` smallint(5) unsigned NOT NULL DEFAULT '0',
`inthread` int(10) unsigned NOT NULL DEFAULT '0',
`incalendar` smallint(5) unsigned NOT NULL DEFAULT '0',
`badlocation` smallint(5) unsigned NOT NULL DEFAULT '0',
`bypass` tinyint(4) NOT NULL DEFAULT '0',
`profileupdate` smallint(5) unsigned NOT NULL DEFAULT '0',
`in_game` varchar(32) NOT NULL,
PRIMARY KEY (`sessionhash`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
-----

The db was restarted very recently and this table was only "127472" after restart. Now, just a few hours later the table has grown to "769952".

I'm taking a stab in the dark and guessing that maybe the locks we are seeing is due to this table hitting the max heap size during some write operation... causing everything to hang... I've upped max_heap_table_size and tmp_table_size to 512mb, hoping to avoid this problem in the future.

One thing I am wondering though... is there perhaps some setting that we are overlooking that will limit the size of this table or flush it more regularly?

Or am I barking up the wrong tree here?

For those who are curious, here are the processes running during these locks:

-----
| 2032011 | vbuser | localhost | cleanvb | Query | 995 | Locked | DELETE FROM vb_session WHERE userid = 215367|
| 2032014 | vbuser | localhost | cleanvb | Query | 995 | update | INSERT IGNORE INTO vb_session
(sessionhash, userid, host, idhash, lastactivity, location, |
| 2032015 | vbuser | localhost | cleanvb | Query | 995 | Locked | INSERT INTO vb_threadviews (threadid)
VALUES (93502) |
| 2032025 | vbuser | localhost | cleanvb | Query | 995 | update | INSERT INTO vb_threadviews (threadid)
VALUES (93794) |
| 2032028 | vbuser | localhost | cleanvb | Query | 995 | Locked | UPDATE vb_session
SET lastactivity = 1242784934, location = '/forums/forumdisplay.php?f=9 |
| 2032029 | vbuser | localhost | cleanvb | Query | 994 | Locked | UPDATE vb_session
SET lastactivity = 1242784934, location = '/forums/index.php', inforum |
| 2032031 | vbuser | localhost | cleanvb | Query | 995 | Locked | SELECT *
FROM vb_session
WHERE sessionhash = 'd825a459fb6e696bfb0fe93319d44790'
A |
-----

After this is another 1000+ selects on the vb_session table that are locked as well. This clogs up all available processes and effectively kills the site. As I said, server load is fine during this time, no swapping, doesn't appear to be an IO issue (big 8 drive sas array)....

I'm pretty new to vB, looked around quite a bit but can't much info on this. Any tips appreciated.