db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Peter Ondruška <peter.ondru...@kaibo.eu>
Subject Re: Avoid locking on DELETE
Date Sun, 06 Oct 2019 01:50:18 GMT
Peter, try this if it makes a difference:

1. Select entries to be deleted, note their primary keys.
2. Issue delete using keys to be deleted (1.) and use short transaction

On Sun, 6 Oct 2019, 01:33 Peter, <tableyourtime@gmail.com> wrote:

> Hi,
> I have a table "mytable" with columns "id", "created_at" and "json"
> (VARCHAR, BIGINT, LONG VARCHAR), where data is coming in like new 200k
> entries every hour and I would like to keep only entries of the last 1
> or 2 hours. It is expected behaviour for the user if too old entries
> gets lost as it is some kind of a LRU cache.
> The current solution is to delete entries older than 4 hours every 30
> minutes:
> DELETE FROM mytable WHERE created_at < ?
> I'm using this in a prepared statement where ? is "4 hours ago" in
> milliseconds (new DateTime().getMillis()).
> This works, but some (not all) INSERT statement get a bigger delay in
> the same order (2-5 seconds) that this DELETE takes, which is ugly.
> These INSERT statements are executed independently (using different
> threads) of the DELETE.
> Is there a better way? Can I somehow avoid locking the unrelated INSERT
> operations?
> What helps a bit is when I make those deletes more frequently than the
> delays will get smaller, but then the number of those delayed requests
> will increase.
> What also helps a bit (currently have not seen a negative impact) is
> increasing the page size for the Derby Network Server:
> -Dderby.storage.pageSize=32768
> Regards
> Peter

View raw message