Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Backup restore is slow in FB3 when the database contains many small tables with indices [CORE5101] #5385

Closed
firebird-automations opened this issue Feb 3, 2016 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: michalk1 (michalk1)

Attachments:
Fb25Gbk.zip
Fb30Gbk.zip
restore-benchmark_fb25-vs-fb30_-_tables_1e3__indices_1e4.zip
create-table-with-NN-indices-benchmark-FB-25_vs_30__fw_OFF_maxunflushed_minus_1.rar

Let's have a database with 1000 simple empty tables
CREATE TABLE TABn (ID INTEGER PRIMARY KEY)

The gbk restore time (without verbose switch) of the database is 15 seconds in FB2.5 vs 2 minutes in FB3 RC1. When run in verbose mode, it shows that FB3 gbak spends too much time in "activating and creating deferred index" lines phase.

Commits: 0719958 FirebirdSQL/fbt-repository@d26c94d

@firebird-automations
Copy link
Collaborator Author

Modified by: michalk1 (michalk1)

Attachment: Fb25Gbk.zip [ 12895 ]

Attachment: Fb30Gbk.zip [ 12896 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

The issue is that fb3 fixed very old bug : when database is created its file was opened in FW=OFF mode while internal flags set as FW=ON.
It leads that fb 2.5 doesn't flush file on commit while fb3 do flush (as fb3 have internal flags set to FW=OFF).
After activation of every index gbak call commit retain, so fb3 flush file after every index activation while fb 2.5 - doesn't flush.

It is easy to check - just set in firebird.conf
MaxUnflushedWrites = -1
MaxUnflushedWriteTime = -1

and fb3 will have same behavior as fb 2.5 and will restore attached backup much faster.

Looking for solution for it.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Now flushing is disabled while database is restored.
More exactly - flush is disabled when database is just created, is in single-user shutdown mode (as gbak does at restore) and flush is run by creating attachment.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC2 [ 10048 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

I've prepared two databases both with 1'000 tables and 10'000 indices (each table has 10 indexes).
Both databases have page_size 8192 and were backed up.
Then they were restored with switch "-st tdrw" and redirecting output to log.

Final part of log for 2.5:

gbak: 165.429 0.007 116 16 committing metadata
gbak: 166.243 0.813 511 2752 finishing, closing, and going home
gbak: 166.244 0.000 24142 113715 total statistics

Similar for 3.0:

gbak: 220.830 0.010 119 13 committing metadata
gbak: 220.832 0.001 2 2 fixing views dbkey length
gbak: 220.839 0.006 21 0 updating ownership of packages, procedures and tables
gbak: 221.215 0.376 180 1135 adding missing privileges
gbak: 221.216 0.000 1 0 fixing system generators
gbak: 221.908 0.692 597 1075 finishing, closing, and going home
gbak: 221.909 0.000 25092 105198 total statistics
gbak:adjusting the ONLINE and FORCED WRITES flags

May be this difference is not significant, but let's look inside logs.
For 2.5 we'll see:
---
gbak: 4.832 0.000 0 0 restoring privilege for user SYSDBA
gbak: 4.832 0.000 0 0 restoring privilege for user SYSDBA
gbak: 5.200 0.368 49 148 creating indexes
gbak: 158.660 153.460 506 596 activating and creating deferred index RDB$9991
gbak: 158.661 0.000 7 11 activating and creating deferred index RDB$9992
---

And for 3.0:
---
gbak: 7.825 0.000 0 0 restoring privilege for user SYSDBA
gbak: 7.825 0.000 0 0 restoring privilege for user SYSDBA
gbak: 8.712 0.886 109 263 creating indexes
gbak: 209.297 200.585 788 613 activating and creating deferred index RDB$9991
gbak: 209.299 0.001 21 9 activating and creating deferred index RDB$9992
---

So, again time differs because of indexes creation ?

DDL script (with creation 1'000 tables and 10'000 indices) plus .fbk plus reports of restoring (on Linux server with good IO) see in attached zip.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Attachment: restore-benchmark_fb25-vs-fb30_-_tables_1e3__indices_1e4.zip [ 12901 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

It seems that number of indices that are created has drastic affect on total time of creation DB objects in FB 3.0.
This is so at least implicitly defined in a field declaration, i.e.: create table test(fld varchar(36) UNIQUE).
I've done benchmark when new database (with page_size = 8192 and fw = OFF) should contain 50, 100, 150, 200, 250, 300, 350, 400, 450 and 500 tables and 0, 5, 10 and 15 indices per each of tables.

Result for maximal number of tables (500) and different count of indices:

1) for FB 2.5.6 it took about 10" for creating 5 * 500 and 25" for 15 * 500 indices:

1.1) 0 indices per table:
DTS_START 2016-02-07 08:17:05.3110
DTS_FINISH 2016-02-07 08:17:07.1240

1.2) 5 indices per table:
DTS_START 2016-02-07 08:22:43.9210
DTS_FINISH 2016-02-07 08:22:53.3740

1.3) 10 indices per table:

DTS_START 2016-02-07 08:35:43.8270
DTS_FINISH 2016-02-07 08:36:00.7490

1.4) 15 indices per table:

DTS_START 2016-02-07 08:54:51.0460
DTS_FINISH 2016-02-07 08:55:16.6080

2) for FB 3.0 elapsed time for reating 5*500 indices is almost as in FB 2.5.6 - about 10", but for 15*500 it is 4'30", i.e. 270 / 25 ==> 10x more than in FB 2.5.6:

2.1) 0 indices per table:
DTS_START 2016-02-07 08:17:14.9210
DTS_FINISH 2016-02-07 08:17:17.6080

2.2) 5 indices per table:
DTS_START 2016-02-07 08:22:54.8110
DTS_FINISH 2016-02-07 08:23:28.8270

2.3) 10 indices per table:
DTS_START 2016-02-07 08:36:02.7330
DTS_FINISH 2016-02-07 08:38:19.1710

3.4) 15 indices per table:
DTS_START 2016-02-07 08:55:18.7640
DTS_FINISH 2016-02-07 08:59:48.2330

Both in 2.5 and 3.0 configs have:
MaxUnflushedWrites = -1
MaxUnflushedWriteTime = -1

Checked on WI-V3.0.0.32328 (SS, cache = 2048), WI-V2.5.6.26970 (SC, cache = 256).

Full logs, used FB configs and batch see in attached zip (open "c5101-ddl.bat" and correct env. settings; then run "c5101-run.bat" )

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Attachment: create-table-with-NN-indices-benchmark-FB-25_vs_30__fw_OFF_maxunflushed_minus_1.rar [ 12902 ]

ilya071294 added a commit to red-soft-ru/firebird that referenced this issue Jul 14, 2022
…hen Classic server mode is used

CCH_flush has dbb->dbb_ast_flags & DBB_shutdown_single check to avoid PIO_flush call when a database is restoring. But a restore attachment didn't update dbb->dbb_ast_flags while setting a shutdown mode in a database header. This optimization worked fine for Super server mode because it has Garbage Collector and Cache Writer attachments which read the header and update flags in a shared dbb.
ilya071294 added a commit to red-soft-ru/firebird that referenced this issue Jul 14, 2022
…hen Classic server mode is used

CCH_flush has dbb->dbb_ast_flags & DBB_shutdown_single check to avoid PIO_flush call when a database is restoring. But a restore attachment didn't update dbb->dbb_ast_flags while setting a shutdown mode in a database header. This optimization worked fine for Super server mode because it has Garbage Collector and Cache Writer attachments which read the header and update flags in a shared dbb.
ilya071294 added a commit that referenced this issue Jul 20, 2023
Postfix for #5385 (CORE-5101): Fix slow database restore when Classic server mode is used
ilya071294 added a commit that referenced this issue Jul 20, 2023
ilya071294 added a commit that referenced this issue Jul 20, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants