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
Gradual slowdown compilation (create, recreate or drop) of views [CORE5612] #5878
Comments
Commented by: @abzalov Script to generate test views |
Modified by: @abzalovAttachment: BUILD.sql [ 13179 ] |
Commented by: @abzalov Do I need to provide any other information? |
Commented by: @abzalov During debugging it turned out that dependencies are removed (MET_delete_dependencies) in MET_get_dependencies and then created (store_dependencies). If the check is removed, then the view re-creation time (from the example) is reduced up to 3 times (was 24 sec. became 8 sec. - checked in the debug build). |
Commented by: @abzalov In the above method, which does not verify the existence of dependencies, there is a bug, which creates duplicate dependency records. There was also one more optimization method, which completely removes the slowdown depending from the number of existed views - creating a unique index in RDB$DEPENDENCIES across all its fields (in 2.5 the field RDB$PACKAGE_NAME does not exist). After the creation of such an index in 2.5 - creating view does not depend on the number of existing views and always takes ~ 0.2s - 0.3s (tested with 500 existing views created in BUILD.sql). In my opinion, such an index should be in the standard installation, because it will significantly speed up the create/alter/delete of views and additionally solve the task of controlling the availability of records. Of course, we would create this index ourselves, but since 3.0 it is forbidden to create an index for system tables: Why is this prohibited? And what about the solution to the original problem in 3.0? |
Commented by: @abzalov In total, to solve our problem now we do the following: Proceeding from the fact that it is necessary to apply adaptive mechanics, and from the fact that in the source code is not used anywhere specifying the plan (perhaps there are reasons for it) - there is hardly a chance that you implement the use of "correct" system indexes in the right places. In the end, what can be a bad from modification of system indexes, given that DB developers understand what they are doing? Of course, you can find a way around this restriction (check_gbak_cheating_insupd, check_gbak_cheating_delete), but why can not you provide a regular way? --- Итого, чтобы сейчас разрешить нашу проблему мы делаем следующее: Исходя из того, что нужно применять адаптивную механику, и из того, что в исходниках нигде не используется указание плана (возможно на это есть свои причины) - вряд ли есть шансы, что вы реализуете использование "правильных" системных индексов в нужных местах. В конце концов, что может быть плохого модификации системных индексов, учитывая, что разработчики БД понимают, что они делают? Конечно можно найти способ обойти это ограничение (check_gbak_cheating_insupd, check_gbak_cheating_delete), но почему нельзя предоставить штатный способ? |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Commented by: @dyemanov System indices are adjusted in v4 (ODS13) to cover these cases. In prior versions, additional indices may be created manually. |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 4.0 Beta 1 [ 10750 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: No test => Done successfully |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: @abzalov
Jira_subtask_outward CORE5746
Attachments:
BUILD.sql
With a large number of existing views, the compilation of new views (or drop existing) is very slow and the time increases as the number of views increases.
Time for create 1 view:
- on empty database: 0.2s
- 50 view already exist: 1s
- 100 view already exist: 1.7s
- 200 view already exist: 3.3s
The CPU load (1 core) is 100%, and the time is almost independent of the computer configuration, i.e. that on the server with 2 Xeon processors, 64Gb of memory and hardware RAID, that on the usual desktop.
When creating real databases ~ 1000-1500 view, creating 1 view gradually slows down ~ from 30 sec. up to 1 min.
On real databases ~1000 view, creating 1 view takes ~ from 30 seconds up to 1 min.
Approximate number of records in real databases:
RDB$DEPENDENCIES ~ 1 000 000
RDB$RELATION_FIELDS ~ 300 000
RDB$RELATIONS ~ 3 500
Commits: 2cb9e64
The text was updated successfully, but these errors were encountered: