MySQL Forums
Forum List  »  Performance

Performance drops even with select on empty table.
Posted by: Olivier Audo
Date: March 29, 2005 08:54AM

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.

Options: ReplyQuote

Written By
Performance drops even with select on empty table.
March 29, 2005 08:54AM

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.