Quantcast

DELETE when DB is full

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

DELETE when DB is full

Ward Willats
Hello.

We have an embedded system out in the wild with a DB in WALL mode that we set a max_pages value on to keep its size constrained. The system is more or less a data logger. We run a "purger" thread at intervals to DELETE records when it discovers free space is running below a threshold.

The purger starts a transaction, does a query to figure out the time range to delete, and then does a delete with that time range. (I realize now I could combine the DELETE and SELECT into one statement) and then ends the transaction.

Thing is, the delete sometimes (very rarely) fails with a 13 "disk or database full" error. I assume because the purger is late to the party and it needs pages in the WAL to be able to rollback if necessary.

I figure I can run the purger more often, or raise my "must keep free" threshold, but it is hard to know the rate at which data will be generated and it can be really "bursty" -- so such fixes are a bit hand-wavy and unsatisfying.

So, I thought I'd check with you folks and see if:

1. The operational assumption (delete need pages) I've asserted here is correct, and
2. There is some trick I can use to force a delete of records when the DB pages are already maxed out -- esp. if I don't need to roll this back.

This is sqlite 3.10.1.

Thanks

-- Ward

 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DELETE when DB is full

Richard Hipp-3
On 1/31/17, Ward WIllats <[hidden email]> wrote:
> 1. The operational assumption (delete need pages) I've asserted here is
> correct, and

That is correct.  While the delete is underway, you need to have both
the old and new content of every modified database page stored on
disk, in case a rollback is required.

Actually, that is not 100% true.  The "new" value of pages added to
the freelist leaf pages is not saved since it does not matter if the
content of a freelist leaf page changes due to a power failure
followed by a recovery.  But even on a full table delete, not every
page becomes a freelist leaf page, so you still need some space.

That said, if you are in WAL mode, the new page content might be
stored in WAL pages that have already been allocated, if space is
available in the WAL file.  You can increase the chance that space
will be available in the WAL file if you run "PRAGMA wal_checkpoint"
just prior to running your big DELETE.

>
> This is sqlite 3.10.1.

Version 3.16.2 is faster, fully compatible, and contains new features.  :-)
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DELETE when DB is full

Warren Young
In reply to this post by Ward Willats
On Jan 31, 2017, at 2:03 PM, Ward WIllats <[hidden email]> wrote:
>
> the delete sometimes (very rarely) fails with a 13 "disk or database full" error. I assume because the purger is late to the party and it needs pages in the WAL to be able to rollback if necessary.

Is there an especially good reason you have to do this in a single shot?

If you get this error, shrink the date range or pages-to-free value by half and try again.  Repeat until it works, then repeat at that size until you’ve deleted as much as you need to.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DELETE when DB is full

Simon Slavin-3

On 31 Jan 2017, at 10:40pm, Warren Young <[hidden email]> wrote:

> On Jan 31, 2017, at 2:03 PM, Ward WIllats <[hidden email]> wrote:
>
>> the delete sometimes (very rarely) fails with a 13 "disk or database full" error. I assume because the purger is late to the party and it needs pages in the WAL to be able to rollback if necessary.
>
> Is there an especially good reason you have to do this in a single shot?
>
> If you get this error, shrink the date range or pages-to-free value by half and try again.  Repeat until it works, then repeat at that size until you’ve deleted as much as you need to.

It’s possible to do a DELETE in chunks, like you would use LIMIT on a SELECT.  Start by doing

        SELECT rowid FROM MyTable
                WHERE [whatever condition picks deletable rows]
                OFFSET 1000 LIMIT 1

This gives you the 1000th row you would DELETE.  Then you DELETE all the rows up to that one:

        DELETE FROM MyTable WHERE [whatever condition picks deletable rows] AND rowid <= [whatever was returned]

then you do the SELECT again.  Eventually you get nothing returned from the SELECT and then you do the DELETE you would have done in the first place to delete any surviving rows.

This keeps any transaction down to a maximum of 1000 rows, which puts an upper limit on the amount of space the journal should take up.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DELETE when DB is full

Ward Willats

> On Jan 31, 2017, at 3:54 PM, Simon Slavin <[hidden email]> wrote:
>
> On 31 Jan 2017, at 10:40pm, Warren Young <[hidden email]> wrote:
>
>> On Jan 31, 2017, at 2:03 PM, Ward WIllats <[hidden email]> wrote:
>>
>>> the delete sometimes (very rarely) fails with a 13 "disk or database full" error. I assume because the purger is late to the party and it needs pages in the WAL to be able to rollback if necessary.
>>
>> If you get this error, shrink the date range or pages-to-free value by half and try again.  Repeat until it works, then repeat at that size until you’ve deleted as much as you need to.
>
> It’s possible to do a DELETE in chunks, like you would use LIMIT on a SELECT.  

Thanks for taking the time to provide the great tips and technical explanation by DRH. Our architecture is well suited to some kind of chunked and/or recursing delete so I will do some experiments today to find some reasonable bounding parameters and push ahead.

(I have another simple question related to this, but I'll start a new thread so it is not buried here.)

-- Ward



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...