-
-
Notifications
You must be signed in to change notification settings - Fork 212
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
Comments
Modified by: michalk1 (michalk1)Attachment: Fb25Gbk.zip [ 12895 ] Attachment: Fb30Gbk.zip [ 12896 ] |
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 is easy to check - just set in firebird.conf and fb3 will have same behavior as fb 2.5 and will restore attached backup much faster. Looking for solution for it. |
Modified by: @hvladassignee: Vlad Khorsun [ hvlad ] |
Commented by: @hvlad Now flushing is disabled while database is restored. |
Modified by: @hvladstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 RC2 [ 10048 ] |
Commented by: @pavel-zotov I've prepared two databases both with 1'000 tables and 10'000 indices (each table has 10 indexes). Final part of log for 2.5: gbak: 165.429 0.007 116 16 committing metadata Similar for 3.0: gbak: 220.830 0.010 119 13 committing metadata May be this difference is not significant, but let's look inside logs. And for 3.0: 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. |
Modified by: @pavel-zotovAttachment: restore-benchmark_fb25-vs-fb30_-_tables_1e3__indices_1e4.zip [ 12901 ] |
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. 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: 1.2) 5 indices per table: 1.3) 10 indices per table: DTS_START 2016-02-07 08:35:43.8270 1.4) 15 indices per table: DTS_START 2016-02-07 08:54:51.0460 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: 2.2) 5 indices per table: 2.3) 10 indices per table: 3.4) 15 indices per table: Both in 2.5 and 3.0 configs have: 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" ) |
Modified by: @pavel-zotovAttachment: create-table-with-NN-indices-benchmark-FB-25_vs_30__fw_OFF_maxunflushed_minus_1.rar [ 12902 ] |
…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.
…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.
Postfix for #5385 (CORE-5101): Fix slow database restore when Classic server mode is used
…ore when Classic server mode is used
…ore when Classic server mode is used
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
The text was updated successfully, but these errors were encountered: