Using MySQL 3.23.40
App is logging db with 130 clients to a central MySQL db
with 2 million events per day. Aged events of over 7 days
are no longer required. The central table is myisam type
with fixed size (datetime, enum and foreign keys only).
Selects are done infrequently while inserts are done
throughout the day. What is the fastest delete possible
and still keep table optimized?
Here are two ways that were developed. Either algorithm
is done once a day when server activity is at a minimum.
The first technique is in the documentation, the second
technique is twice as fast.
Delete rows and optimize
------------------------
DELETE FROM table WHERE etime<"2002-02-25";
OPTIMIZE TABLE table;
The time to delete 2 million rows is 24 minutes.
The time to optimize is 18 minutes.
Total time is 42 minutes.
Transfer only newest data to no-index temporary table
----------------------------------------------------
LOCK TABLES table t READ;
INSERT INTO table_tmp
SELECT * FROM table WHERE etime>"2002-02-25";
TRUNCATE TABLE table;
UNLOCK TABLES;
INSERT INTO table
SELECT * FROM table_tmp;
The time to insert 10 million rows into temporary table
is 3 minutes.
The time to truncate table is 5 seconds.
The time to insert from temporary table back to primary
table is 18 minutes.
Total time is 21 minutes.
Does anyone know of a different approach of deleting
rows while keeping the table optimized? Would welcome
any comments.
David
PS1 Optimized table is defined as no deletes to table
without a subsequent optimize. If deletes and inserts
are done simultaneously, query times go up drastically.
This slowdown is documented.
PS2 Hardware is a 4 cpu Solaris with key_buffer=1024M &
thread_concurrency=8. Only other db in mysql is used
infrequently.
---
David E Lopez
email: [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
