Ok, as you might have guessed, I am working on something, that involves
gathering more or less real time data, every 5 minutes, and storing it
in a table.

So the table will grow...and grow...

each entry has an ID, autoindexed, and a timestamp. Plus about 10
numerical entries stored as floats.

After say - 5 years, I might want to delete data older than 5 years.


Will that release disk space? I.e. would the ISAM engine re-use disk
space of deleted rows?

And would there be any impact on speed of retrieval?

The normal search is for the latest n records...

i.e.
select * from table order by id desc limit n;

which are then processed to perform various statistics upon, and draw
graphs.

OK I have a few years in which to get it right..but its always nice to
know what to expect, in case there is something to be done to speed
things up now.

On May 27, 8:28*pm, The Natural Philosopher <t... (AT) invalid (DOT) invalid>
wrote:

1) use Partitioning.
http://dev.mysql.com/doc/refman/5.1/...ning-list.html

2) Use the DATA DIRECTORY and INDEX DIRECTORY directives
Example:
(PARTITION p4c VALUES LESS THAN (201112) DATA DIRECTORY = '/opt/data/'
INDEX DIRECTORY =
'/opt/index/' ENGINE = MYISAM,
PARTITION p51 VALUES LESS THAN (201201) DATA DIRECTORY = '/opt/data/'
INDEX DIRECTORY =
'/opt/index/' ENGINE = MYISAM,
....
3) Then when it is time, use ALTER TABLE TRUNCATE PARTITION then ALTER
TABLE DROP PARTITION. This "should" delete the data files associated
with those partitions.

As always, not all solutions fit every scenario - YMMV.
To ensure it works the way you think that it works, TEST, TEST, TEST
and TEST again.

On 28-05-2011 02:28, The Natural Philosopher wrote:
http://dev.mysql.com/doc/refman/5.5/...ize-table.html
OPTIMIZE TABLE should be used if you have deleted a large part of a
table or if you have made many changes to a table with variable-length
rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns).
Deleted rows are maintained in a linked list and subsequent INSERT
operations reuse old row positions. You can use OPTIMIZE TABLE to
reclaim the unused space and to defragment the data file. After
extensive changes to a table, this statement may also improve
performance of statements that use the table, sometimes significantly.


--
Luuk

On 28-05-2011 03:38, onedbguru wrote:
you forgot to say he should test it.....



--
Luuk

The Natural Philosopher <tnp (AT) invalid (DOT) invalid> wrote:

Do yourself a favor and have a look at rrdtool.
This is nearly perfect for time series data.

If you really need to put your data into a RDBMs,
then go for InnoDB, not MyISAM. The PK should be
(timestamp, datasource). Then the clustering on
the PK will automagically give fast access for
time-range queries and low fragmentation when
deleting historical data.


XL

Axel Schwenke wrote:

But thanks for the advice.

PK is on those fields anyway.

Retrieval is almost non time critical. I can stand a minute if I ever
need to grab a huge chunk.

Gathering the data is the most important thing, and that's up and
running..if I need to port to another dB later, well that's not a
problem either.


> XL

On 5/28/2011 11:58 AM, The Natural Philosopher wrote:
No more or less professional than using MyISAM. And anyone with half an
inkling wouldn't have to spend much (if any) time "researching". A
quick read of the manual would suffice.

But your comments aren't surprising, considering the source.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================