Lock tables - best solution to a high traffic slaving system?

[Consolas]

Hi Everyone!

 

I've got a problem here. I have a replication system, with a master and one slave.

 

Records are inserted by web/perl/etc into the master and then replicated to the slave.

 

I only make reads on the slave and things go smooth until i reach 17:55 - 18:00 each day.

During this time i have an extremely large traffic(?) of information. Lots and Lots of records are inserted and when this happen i can't make any select to the database, including the one i say next - it's just takes forever to get some info (via web is impossible..., on the shell takes more than 10 minutes..!). Nevertheless after i analyze table and optimize it takes 1.5 seconds if so..

 

My query explanation is the following:

mysql> EXPLAIN SELECT msisdn FROM Incoming where receiveDate BETWEEN '2007-12-20 00:00:00' AND '2007-12-20 23:59:59' AND Alias = '760200300' ORDER BY receiveDate,idIncoming ASC LIMIT 0,10;

+----+-------------+----------+-------+---------------+-----------+---------+------+---------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------+-------+---------------+-----------+---------+------+---------+-----------------------------+

| 1 | SIMPLE | Incoming | range | DataAlias | DataAlias | 62 | NULL | 1444142 | Using where; Using filesort |

+----+-------------+----------+-------+---------------+-----------+---------+------+---------+-----------------------------+

1 row in set (0.03 sec)

 

 

My index on the table ( i've rearranged them to optimize as much this query ) is the following:

 

 

mysql> SHOW INDEX FROM Incoming;

+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Incoming | 0 | PRIMARY | 1 | idIncoming | A | 41888471 | NULL | NULL | | BTREE | |

| Incoming | 1 | DataAlias | 1 | receiveDate | A | 3222190 | NULL | NULL | YES | BTREE | |

| Incoming | 1 | DataAlias | 2 | alias | A | 3490705 | NULL | NULL | YES | BTREE | |

+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

3 rows in set (0.00 sec)

 

 

First: I don't understand why it says using filesorte on the explanation and not using index...

 

 

So...maybe the indexes are wrong or maybe i could implement my idea: i was wondering how reliable it was to lock the table when i'm making a select and unlock it after the select it's done. What do you think?

 

Ricardo

 

Please, help me..i don't have much more ideas.

 

 

 

mysql 5.0.15-max-log

[fenway]

You're ordering by two columns, you only have one in your index.

[Consolas]

Well, idIncoming is Primary Key, thus i think its automatically indexed.

 

right?

 

I've read something about PK being add to all mutiple collumn indexes, so when i create index (date,number) it has a third index PRIMARY KEY, in this case idIncoming.

 

Is this wrong?

[fenway]

Yes, but it's not being used for the order by clause... and you have two indexes called date alias/

[Consolas]

I just have one. Better, i have one index (receiveDate,alias) and the primary key.