1

I have a very large database (at least for me) - above 1 000 000 records and I need to delete all records that are with a timestamp lower than something. Like for example:

DELETE FROM table WHERE TS < 2020-01-01;

The problem I'm facing is that after the transaction finishes, if it finishes at all, is that the database is unresponsive and unusable. How can I delete so much records without the above said problem?

I'm new to this, as of now I've only worked with databases that had 1000-10000 rows and the command I used to delete records hasn't caused problems.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Tony
  • 618
  • 12
  • 27
  • 2
    What do you mean with unresponsive and unusable? Do you by any chance issue a `select * from table` or something else that scans a large part of the table? That could indicate you're triggering the cooperative garbage collection, which means the transaction that does the table scan incurs the cost of cleaning up the garbage from the delete. – Mark Rotteveel Feb 18 '21 at 18:25
  • maybe you need an index? – OldProgrammer Feb 18 '21 at 18:25
  • @Mark Rotterveel Yes that is exactly what I have issues with, or with next deleting I try to do. How can I get past that? Should I do the first `DELETE` then backup and restore and after that next delete and again backup and restore? – Tony Feb 18 '21 at 18:37
  • @Tony1234 . . . It is often better to store the data you want to keep in a new table. Then truncate the existing table and re-insert the data. – Gordon Linoff Feb 18 '21 at 18:59
  • @GordonLinoff Firebird has no truncate table statement, so you would need to delete all records, which will cause an even bigger version of the problem, as it would generate more garbage to collect. – Mark Rotteveel Feb 18 '21 at 19:34
  • @MarkRotteveel . . . Would `RECREATE` be more efficient? – Gordon Linoff Feb 18 '21 at 21:08
  • @GordonLinoff Yes, but with a lot of caveats: `RECREATE` does a `DROP` and `CREATE`, which means any dependencies (e.g. foreign keys to the table) will block the drop, you'll lose any indexes on the table, and any privileges on the table assigned to other objects (users, etc). In addition, if a table is in use (e.g. by a prepared statement), it can also not be dropped. – Mark Rotteveel Feb 19 '21 at 09:16
  • Would switching to Classic or SuperClassic make GC better or worse ? – Arioch 'The Feb 19 '21 at 10:22
  • @Arioch'The It would usually make it worse, because then the only option is cooperative. Switching to (or remaining on) SuperServer, and setting the `GCPolicy` to `background` is the simplest solution (but there are some tradeoffs, because I believe it will not collect garbage as soon as `combined` or `cooperative`, meaning there might be some performance degradation due to longer version chains. – Mark Rotteveel Feb 19 '21 at 12:30

2 Answers2

1

Based on your description in the question, and your comments, the problem has to do with how garbage collection works in Firebird. Firebird is a so-called Multi-Version Concurrency Control (MVCC) database, each change you make to a row (record), including deletions, will create new versions of that record, and keep previous versions available for other transactions that were started before the transaction that made the change is committed.

If there are no more transactions 'interested' in a previous version of record, that previous version becomes eligible for garbage collection. Firebird has two options for garbage collection: cooperative (supported by all server modes) and background (supported by SuperServer), and a third combined mode which does both (this is the default for SuperServer).

The background mode is a dedicated thread which cleans up garbage, it's signaled by active statements if they see garbage.

In the cooperative mode, a statement that sees garbage is also the one that has to clean it up. This can be especially costly when the statement performs a full table scan just after a large update or delete. Instead of just finding and returning rows, that statement will also rewrite database pages to get rid of that garbage.

See also the slides Garbage collection mechanism and sweep in details.

There are some possible solutions:

  1. If you're using SuperServer, change the policy, by setting the setting GCPolicy in firebird.conf to background.

    The downside of this solution is that it might take longer before all garbage is collected, but the big benefit is that transactions are not slowed down by doing garbage collection work.

  2. After committing a transaction that produced a lot garbage, execute a statement that performs a full table scan (e.g. select count(*) from table) to trigger garbage collection, using a separate worker thread to not block the rest of your process.

    This option only really works if there are no active transactions that are still interested in those old record versions.

  3. Create a backup of the database (there is no need to restore, except to verify if the backup worked correctly).

    By default (unless you specify the -g option to disable garbage collection), the gbak tool will perform garbage collection during a backup. This has the same restriction as option 2, as this works because gbak does the equivalent of a select * from table

  4. Perform a 'sweep' of the database using gfix -sweep.

    This has similar restrictions as the previous two options

  5. For connections that cannot incur the slowdown of a garbage collection, specify the connection option isc_dpb_no_garbage_collect (details vary between drivers and connection libraries).

    If you specify this for all connections, and your policy is cooperative (either because it is configured, or you're using Classic or SuperClassic server mode), then no garbage collection will take place, which can cause an eventual slowdown as well, because the engine will have to scan longer chains of record versions. This can be mitigated by using the previous two options to perform a garbage collection.

  6. Instead of really deleting records, introduce a soft-delete in your application to mark records as deleted instead of really deleting them.

    Either keep those records permanently, or really delete them at a later time, for example by a scheduled job running at a time the database is not under load, and include one of the previous options to trigger a garbage collection.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Thank you for the complete answer. Another question that came to mind is - when the garbage is cleaned by the `select * from table` of whatever I choose to use to sweep it , will the database shrink in size? – Tony Feb 20 '21 at 08:43
  • 1
    @T0ny1234 No, the database will not shrink. The space will remain allocated, but becomes available for reuse to store new records (or new record-versions of existing records). In some cases, if a data page becomes entirely empty, the data page allocated for a specific table might be freed, but that just means it will be added to the list of free pages that will be reused. The only option to shrink a database is backing up with _gbak_ and restoring it. – Mark Rotteveel Feb 20 '21 at 08:47
  • @ Mark Rotterveel Thank you again. – Tony Feb 20 '21 at 08:53
1

Actually background garbage collection is exactly what can cause "unresponsive database" behavior because of high tension between garbage collector and working threads. Cooperative GC may slow down operations but keep the database "responsive". At least for version 2.5.

Another reason is bad indexes which have a lot of duplicates. Such indexes are often useless for queries and should be simply dropped. If it is not an option, they could be deactivated before delete and reactivated after in separate transactions (as a side effect the activation will cause full garbage collection).

But of course the best option is to keep all data. 1kk records is not that much for well-designed database on decent hardware.

user13964273
  • 1,012
  • 1
  • 4
  • 7
  • Background garbage collection should not cause an unresponsive database. The cooperative mechanism causes it, because the statement that touches the table will do the additional work of garbage collection, which could slow it down considerably if there is a lot of garbage to collect. – Mark Rotteveel Feb 19 '21 at 17:23
  • larger RAM caches can help too, as Interbase was designed many decades ago and is very conservative by default. But i wonder if this "subthread" belongs to general FB engine optimizaitions topic more than to this specific mass delete one. – Arioch 'The Feb 20 '21 at 07:18
  • Yes, it should not cause an unresponsive database but unfortunately it does in some cases. That's why for Firebird 2.5 Classic Server is often a better option. Things are better in 3.0 and much better in 4.0. – user13964273 Feb 20 '21 at 13:48