Hello,
I am new to the forums, so I may be a little off with the proper post ‘format.’ Feel free to correct me, I will not take it personal.
So, I have a session table typically has 20K rows in it and on every webpage hit (a few per second), one SELECT and one UPDATE are executed in the same connection. In addition, a ‘DELETE on data more than 2 weeks old’ is performed 5% of the time.
Sometimes, I can get locked out for 100+ sec, meanwhile, the person sitting next to me can access the data immediately.
Here is the info for the table, and the slow queries:
-
show create table session:
session | CREATE TABLEsession
(
ctr
int(10) unsigned NOT NULL auto_increment,
session_id
varchar(40) NOT NULL,
ip_address
varchar(16) NOT NULL,
user_agent
varchar(50) NOT NULL,
referrer_user_id
int(10) unsigned default NULL,
referrer_addr
varchar(100) default NULL,
last_activity
int(10) unsigned NOT NULL,
session_data
text NOT NULL,
PRIMARY KEY (ctr
),
UNIQUE KEYidx_session_id
(session_id
),
KEYidx_selects
(session_id
,user_agent
,last_activity
)
) ENGINE=InnoDB AUTO_INCREMENT=24402 DEFAULT CHARSET=latin1 | -
slow select:
Query_time: 85 Lock_time: 0 Rows_sent: 1 Rows_examined: 18370
SELECT *
FROM session
WHERE session_id = ‘06a05c42d9a58515d2bac592c3bdfc8b’
AND last_activity > 1234638681
AND user_agent = ‘Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv’;
- slow update:
Query_time: 128 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE session SET last_activity = 1235848241, user_agent = ‘Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US
; rv’, ip_address = ‘69.15.182.225’, referrer_addr = ‘’, session_data = ‘a:2:{s:11:“url_history”;a:5:{i:0;s:3
5:"/support/view/highestSupportAmount/";i:1;s:37:"/support/view/highestSupportAmount/72\ “;i:2;s:37:”/suppor
t/view/highestSupportAmount/36";i:3;s:40:"/support/design/38/skulls-are-still-kool\ “;i:4;s:34:”/support/view
/highestSupportAmount";}s:16:“referrer_user_id”;N;}’ WHERE session_id = ‘a73bc2035d2ca7a58844e4ec52e48c3a’;
- slow delete:
Query_time: 94 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
DELETE FROM session WHERE last_activity < 1235606350;
I am not sure if this is a locking issue, it looks like it, but I don’t know how to investigate further.