Compressing DB while other process is using it

[indianiec <in...@tlen.pl>]

All,

I encountered a big problem. We recently released new software and have big
problems with disk space. When we developed it we did not notice that you
need to reclaim space after you delete rows. We need to find a solution to
that problem without releasing new software version. We just got no time. We
cannot release patch either. Too much testing is needed and issue needs to
be resolved ASAP.

We could write a script compressing DB, but because it's embedded only one
process can access it. That means stopping main application to be able to do
that.

Option number two is deleting whole database (it's used for caching and
application can recreate it no probs), but that sounds like hack. Whe may
need to do that if no other choise is left.

Ideas?

Cheers
-- 
View this message in context: http://www.nabble.com/Compressing-DB-while-other-process-is-using-it-tp15483146p15483146.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

RE: Compressing DB while other process is using it

[indianiec <in...@tlen.pl>]

Thanks guys, I got some stuff to think about now :)

Cheers
-- 
View this message in context: http://www.nabble.com/Compressing-DB-while-other-process-is-using-it-tp15483146p15541527.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

RE: Compressing DB while other process is using it

[Jim Newsham <jn...@referentia.com>]

Although Derby doesn't immediately release space freed up by deleted
records, it will eventually re-use that space (when new records are
inserted).  So the issue that you have is really only a problem if you are
deleting a large number of records at once and you need that space back
immediately.  If you are performing inserts and deletes interspersed with
each other, then it shouldn't be much of an issue.  Using a database as a
cache might exhibit this pattern depending on the design.

Here are some ideas:

- If you need to run an external script that accesses the database at the
same time, your application can run in embedded+server mode.
- Alternatively, it's probably esier just to put the compress "script" in
the same process.  Just open another connection to the database in another
thread.
- I have had problems compressing tables while other connections were
accessing the same tables.  To solve this, I stop all other database
activity while compressing.  I use exclusive locking (Java concurrency
utils) in my database layer to prevent concurrent access.
- If you feel forced to delete the entire database, you might consider
dropping individual tables as an alternative that gives you the flexibility
to keep some of your data.  A clever approach might be to use dynamically
named tables for your cache, and roll through tables, creating new tables
for new cached data, and deleting old tables whose data are no longer
relevant.

Hope that helps,
Jim
 
> -----Original Message-----
> From: indianiec [mailto:indianiec69@tlen.pl]
> Sent: Thursday, February 14, 2008 7:39 AM
> To: derby-user@db.apache.org
> Subject: Compressing DB while other process is using it
> 
> 
> All,
> 
> I encountered a big problem. We recently released new software and have
> big
> problems with disk space. When we developed it we did not notice that you
> need to reclaim space after you delete rows. We need to find a solution to
> that problem without releasing new software version. We just got no time.
> We
> cannot release patch either. Too much testing is needed and issue needs to
> be resolved ASAP.
> 
> We could write a script compressing DB, but because it's embedded only one
> process can access it. That means stopping main application to be able to
> do
> that.
> 
> Option number two is deleting whole database (it's used for caching and
> application can recreate it no probs), but that sounds like hack. Whe may
> need to do that if no other choise is left.
> 
> Ideas?
> 
> Cheers
> --
> View this message in context: http://www.nabble.com/Compressing-DB-while-
> other-process-is-using-it-tp15483146p15483146.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
> 

Re: Compressing DB while other process is using it

[Stanley Bradbury <St...@gmail.com>]

indianiec wrote:
> All,
>
> I encountered a big problem. We recently released new software and have big
> problems with disk space. When we developed it we did not notice that you
> need to reclaim space after you delete rows. We need to find a solution to
> that problem without releasing new software version. We just got no time. We
> cannot release patch either. Too much testing is needed and issue needs to
> be resolved ASAP.
>
> We could write a script compressing DB, but because it's embedded only one
> process can access it. That means stopping main application to be able to do
> that.
>
> Option number two is deleting whole database (it's used for caching and
> application can recreate it no probs), but that sounds like hack. Whe may
> need to do that if no other choise is left.
>
> Ideas?
>
> Cheers
>   
You will need to release something to effect a change and it sounds like
1) releasing a script will be acceptable and
2) deleting any data in the cache tables will not be a problem. 

If so here's and idea:  you can minimize the downtime needed to do the 
compress by overlaying the expanded DB (lets call it usedDB) with an 
already prepared set of files (lets call in freshDB).  The script would 
need to shutdown the application and database, copy usedDB to an archive 
location (just in case - I am paranoid), then copy freshDB to the 
location where the application expects it to be and restart the 
application.