Thanks a ton Rick. The 100+ sec lock contention sounds right, how can I investigate this further, and solve it. When I start up my sql server, here is what I get in my error file:
Current dir: /var/lib/mysql/
Running threads: 0 Stack size: 196608
Current locks:
lock: 0xa5f4170:
lock: 0xa5f40c8:
lock: 0xa5f3050:
lock: 0xa5f4770:
lock: 0xa603428:
...( repeats for about 30 rows w/ different addresses)...
lock: 0xa598ab0:
lock: 0xa593c40:
Key caches:
default
Buffer_size: 16777216
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 38
not flushed: 0
w_requests: 0
writes: 0
r_requests: 363
reads: 38
handler status:
read_key: 1755
read_next: 1218
read_rnd 295
read_first: 26
write: 988
delete 0
update: 210
Table status:
Opened tables: 40
Open tables: 34
Open files: 36
Open streams: 0
Alarm status:
Active alarms: 0
Max used alarms: 4
Next alarm time: 0
090228 16:23:10 [Warning] 'user' entry 'repl@%.cameesa.com' ignored in --skip-na
me-resolve mode.
090228 16:23:10 [Warning] 'user' entry 'repl@mysweetshirt.com' ignored in --skip
-name-resolve mode.
How could I look into this further?
In addition, I have provided the info you asked:
1. SHOW CREATE TABLE AAA\G;
*************************** 1. row ***************************
Table: AAA
Create Table: CREATE TABLE `AAA` (
`id` varchar(40) NOT NULL,
`ip` varchar(16) NOT NULL,
`agent` varchar(50) NOT NULL,
`referrer` int(10) unsigned default NULL,
`referrer_add` varchar(100) default NULL,
`last` int(10) unsigned NOT NULL,
`session` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
2. mysql> SHOW TABLE STATUS LIKE 'AAA'\G;
*************************** 1. row ***************************
Name: AAA
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 19475
Avg_row_length: 350
Data_length: 6832128
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2009-02-26 18:08:48
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 65536 kB
1 row in set (0.02 sec)
3. # Time: 090301 15:38:17
# User@Host: prod_cameesa_web[prod_cameesa_web] @ localhost []
# Query_time: 22 Lock_time: 0 Rows_sent: 1 Rows_examined: 20010
mysql> explain SELECT *
-> FROM AAA
-> WHERE id = '06a05c42d9a58515d2bac592c3bdfc8b'
-> AND last > 1234730275
-> AND agent = 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv';
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | AAA | ALL | NULL | NULL | NULL | NULL | 19478 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
Thank you for your help ahead of time.