Re: [sqlite] Archive SQLite Database?

[Michael Black]

You can also just copy the entire file (best to do when app is not running).
So you'd only be down for as long as it takes to copy it.
If your database is updated frequently .backup may never finish.

Then you can archive from the copy and delete the archived rows from the
active DB in a non-interference way (i.e. small batches).

I still think you may benefit greatly from some indexes but we need more
info.

Somebody will eventually tell you to run EXPLAIN on your queries so we can
see what they are doing.
http://www.sqlite.org/eqp.html






___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Archive SQLite Database?

[Simon Slavin]

On 8 Jan 2013, at 2:05pm, "Jimmy Martin"  wrote:

>I have recently taken over a position that uses a SQLite
> database.  The database is currently 24GB and running extremely slow.

Your best friend here might be the SQLite shell tool:



download from here:



> Is there a quick way to archive data and free-up some space?

Do you understand the data structure and know what rows you can delete ?  If 
so, just issue some DELETE commands and then do a VACUUM.

> Also, are
> there any risks to running the "vacuum" command...such as losing data?

It's no more or less safe than any other SQL command.  Obviously, you'd take a 
backup of your database file before you did anything at all to it -- that's 
your archive -- but there are no known corruption problems especially related 
to VACUUM.

> I would like to archive a year's worth of data.  Archiving the data via
> the application is taking roughly 30min for every 3-5MB.  Unfortunately
> our application cannot be down for such an extended time to Archive the
> years worth of data we need.


You can use the .backup command to backup the database while other applications 
have it open.  The command will continue trying to take a copy until it gets a 
consistent snapshot.  If you do use VACUUM then note that it does lock the 
database, possibly for a long time, so even if your normal apps are still 
running they may give 'cannot access database' problems.  It depends how the 
programmer wrote them.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Archive SQLite Database?

[Michael Black]

Tou haven't said what "really slow" means.
Could be multiple solutions to that problem.

30min for 3-5MB sounds a bit ridiculous.

What kind of select are you doing to archive and what indexes are on the
tables?


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jimmy Martin
Sent: Tuesday, January 08, 2013 8:05 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Archive SQLite Database?

Hello,

I have recently taken over a position that uses a SQLite
database.  The database is currently 24GB and running extremely slow.
Is there a quick way to archive data and free-up some space?  Also, are
there any risks to running the "vacuum" command...such as losing data?
I would like to archive a year's worth of data.  Archiving the data via
the application is taking roughly 30min for every 3-5MB.  Unfortunately
our application cannot be down for such an extended time to Archive the
years worth of data we need.

 

SQLite Version:  3.6.23.1

 

 

Thanks,

 

Jimmy Martin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Archive SQLite Database?

[Jimmy Martin]

Hello,

I have recently taken over a position that uses a SQLite
database.  The database is currently 24GB and running extremely slow.
Is there a quick way to archive data and free-up some space?  Also, are
there any risks to running the "vacuum" command...such as losing data?
I would like to archive a year's worth of data.  Archiving the data via
the application is taking roughly 30min for every 3-5MB.  Unfortunately
our application cannot be down for such an extended time to Archive the
years worth of data we need.

 

SQLite Version:  3.6.23.1

 

 

Thanks,

 

Jimmy Martin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users