MySQL Forums
Forum List  »  MyISAM

Re: would like best table/index structure advice?
Posted by: Kamil Chmielewski
Date: March 03, 2009 09:08AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: would like best table/index structure advice?
2846
March 03, 2009 09:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.