store old seldom used data?

[Mikael Sundberg <Mi...@artificial-solutions.com>]

Hi, i have a pretty big (20gig+)database that grows pretty fast. This
makes it pretty slow in some of the bigger columns.

So we have been thinking about creating a storage DB with the data
that's hardly used (data is mostly used the first week of its presence.
After that it hardly gets used again. But it happens so cant delete it).


So the plan was to copy the data from one database to another slower
database server with less expensive disk space and so on. But the more I
work on it the stupider it feels. Al the foreign keys and regenerated
keys and so on must be dealt with somehow. 

We are pretty bad at rebuilding indexes and stuff so maybe if we just
got around to do that more often maybe we wouldn't need the storage DB?
Or is there a good way to do this? I am pretty sure im not the first one
with this problem...

 

And also, we have had some problem with compressing tables that they
don't seem to get the same generated keys anymore. And screws up al the
foreign keys and so on. Is there a solution for this?

 

/Micke

RE: store old seldom used data?

[de...@segel.com]

Mikael,

 

That is not a stupid idea and it's a problem that faces database designers
all the time.

 

Does it make sense for an OLTP system like a POS station to store all the
transactions from day 1?

No it doesn't.

 

There are a couple of ways to solve your problem.

 

First you could ship your transaction data from your first database to an
ODS (Operational Data Store) at the time of the transaction. (Assuming you
perform a onetime "clean up" or copy of data to your "slower" system. Then
on a routine basis (nightly/weekly/monthly), you purge the unnecessary data
from your transactional system.

 

As to your compression/key index problem. if you have some maintenance
windows established, after you do a purge/compress, try dropping and
rebuilding the indexes on the fly.

 

HTH

 

-Mikey

 

PS. I said that there are other ways to do this, but the solution I mention
seems to be the easiest to implement and maintain. There are of course
different methods to handle the shipping of transactions too.  Good Luck!

 

 

  _____  

From: Mikael Sundberg [mailto:Mikael.Sundberg@artificial-solutions.com] 
Sent: Monday, November 10, 2008 2:20 AM
To: derby-user@db.apache.org
Subject: store old seldom used data?

 

Hi, i have a pretty big (20gig+)database that grows pretty fast. This makes
it pretty slow in some of the bigger columns.

So we have been thinking about creating a storage DB with the data that's
hardly used (data is mostly used the first week of its presence. After that
it hardly gets used again. But it happens so cant delete it). 

So the plan was to copy the data from one database to another slower
database server with less expensive disk space and so on. But the more I
work on it the stupider it feels. Al the foreign keys and regenerated keys
and so on must be dealt with somehow. 

We are pretty bad at rebuilding indexes and stuff so maybe if we just got
around to do that more often maybe we wouldn't need the storage DB? Or is
there a good way to do this? I am pretty sure im not the first one with this
problem.

 

And also, we have had some problem with compressing tables that they don't
seem to get the same generated keys anymore. And screws up al the foreign
keys and so on. Is there a solution for this?

 

/Micke