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
