Performance drops even with select on empty table.

[Olivier Audo]

Hi dear all,

My performance problem seems to be due to wasted space and fragmented index file.

Here is the picture.
I have a MyISAM table with :
- ip charchar(15)
- blob blob
- timestamp timestamp
Indexes are (ip, timestamp)

3 programs use this table concurrently.

-> A program stores IP lines are stored with current timestamp (NOW()).
-> Another program reads them with the following simple query:
"SELECT ip, blob, timestamp FROM infos WHERE ip=? LIMIT 10;"
-> A third program removes expired rows (check the timestamp of rows and delete older than 10 hours for example).

After a while, the reader program sees its performance dramatically dropped.

I stopped the one than stores rows and the one that deletes expired ones, so MySQL is accessed only by the reader program. Even if my number of rows is 0 and the performance are still bad.

I'm pretty sure this is due to wasted space in binary/index files since the "show status table from database" query still shows me allocated data and index files (respectively 1 Go and 80 Mo) even with empty table and the use of DELETE query without WHERE clause or OPTIMIZE TABLE makes the performances come back to normal.

I could regularly OPTIMIZE the table, but I can't afford blocking my other programs until the DELETE or OPTIMIZE finished ...

Is there a solution for optimizing the index while keeping the database active ?
- will innodb help ?
- how to limit the number of rows MySQL can store in the data/index file (to 1 M rows for example)?
- something else ...

Thanks for your help.