Hello We're having some locking problems with our vBulletin 3.0 forum. Snapshots of mysql process list show that some simple queries linger too much in the "end" state, locking subsequent updates:
Id User Host db Command Time State Info .......... 33753 rcgroups 12.34.56.78:56520 forums Query 7 end UPDATE session\n\t\tSET useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)', lastactivity = 1095853377, styleid = 0, bypass = 0\n\t\t ,inforum = 129, inthread = 151687, incalendar = 0, badlocation = 0\n\t\t WHERE sessionhash = 'b71a2c3f73f507e4cd548efedb836887' ......... "session" table is a simple HEAP table which usually holds 2500-3500 records at the peak time: CREATE TABLE `session` `sessionhash` varchar(32) NOT NULL default '', `userid` int(10) unsigned NOT NULL default '0', `host` varchar(15) NOT NULL default '', `useragent` varchar(100) NOT NULL default '', `lastactivity` int(10) unsigned NOT NULL default '0', `location` varchar(255) NOT NULL default '', `styleid` smallint(5) unsigned NOT NULL default '0', `althash` varchar(32) NOT NULL default '', `badlocation` smallint(5) unsigned NOT NULL default '0', `inforum` smallint(5) unsigned NOT NULL default '0', `inthread` int(10) unsigned NOT NULL default '0', `incalendar` int(10) unsigned NOT NULL default '0', `loggedin` smallint(5) unsigned NOT NULL default '0', `idhash` varchar(32) NOT NULL default '', `bypass` smallint(6) NOT NULL default '0', PRIMARY KEY (`sessionhash`), KEY `userid` (`userid`), KEY `location` (`location`), KEY `lastactivity` (`lastactivity`) ) TYPE=HEAP IMHO 7 seconds is a bit too much for a simple UPDATE on primary key. DB server (dual 2.4GHz Xeon Redhat9 box with 2G of RAM) load average is rarely going over 0.8. Swapping is minimal. Disk IO activity is well within acceptable bounds, with peak rate of 1Mb/sec. Mysql is 4.0.21 installed from official mysql.com's RPMs. Any other ideas what's going on here? Documentation on possible thread states seems a bit outdated, as it doesn't even include "end" and "statistics" states (which is another one we're seeing often). my.cnf follows: [mysqld] back_log=128 ft_min_word_len=3 max_connections=300 key_buffer_size=320M myisam_sort_buffer_size=256M join_buffer_size=4M read_buffer_size=2M read_rnd_buffer_size=1M sort_buffer_size=4M table_cache=2048 thread_cache_size=150 wait_timeout=1800 connect_timeout=5 max_allowed_packet=16M max_connect_errors=512 tmp_table_size=64M query_prealloc_size=16384 query_cache_limit = 512K query_cache_size = 48M query_cache_type = 1 skip-innodb skip-name-resolve skip-external-locking log-bin ... replication setup directives skipped ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
