I have several database tables which regularly near the operating system 4GB
file limit. Until now I have been using mysqldump/'delete from
<tablename>'/'optimise table <tablename>' to backup a large chunk of the oldest
data and remove it from the table. I have been doing this every couple of
months.
Approximately 172800 rows are added to each table daily. Each row contains a
enum field 'status' which hold the current state of the row. All rows have
this enum field updated as they pass through various states before hitting the
final state within 4 days of insertion. All the fields in these tables are of
fixed lengths. The database server is using MySQL version id 3.22.
Now that merge tables have had plenty time to become stable I am considering
upgrading the database server to 3.23 and would like to automate the archiving
process so that a script runs daily in the following manner:
1) All rows stay in the 'most recent' table while they are being updated
2) All rows which are older than 5 days are copied using 'insert select from'
to another 'final state' table.
3) The copied rows are then deleted from the 'most recent' table.
The two tables are bound together using merge tables, so that they appear as
one for reporting purposes. As the 'final state' table reaches its maximimum
size a new 'final state' table will be added.
My question relates to optimise table command. Will I need to run it
on the 'most recent' table after each daily delete? The number of rows in
the first table will stay fairly constant so if I never run it will the
table/index space kept tracking the deleted rows locations eventually be reused
or will the table/index size grow constantly until one of the files hits the
operating system limit?
Many Thanks,
Nigel Wood
---------------------------------------------------------------------
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
