| Summary: | New YAML files for installer are slow to insert | ||
|---|---|---|---|
| Product: | Koha | Reporter: | Jonathan Druart <jonathan.druart> |
| Component: | Installation and upgrade (command-line installer) | Assignee: | Jonathan Druart <jonathan.druart> |
| Status: | CLOSED FIXED | QA Contact: | Testopia <testopia> |
| Severity: | major | ||
| Priority: | P5 - low | CC: | 1joynelson, bgkriegel, martin.renvoize |
| Version: | unspecified | ||
| Hardware: | All | ||
| OS: | All | ||
| Change sponsored?: | --- | Patch complexity: | --- |
| Documentation contact: | Documentation submission: | ||
| Text to go in the release notes: | Version(s) released in: |
20.05.00
|
|
| Circulation function: | |||
| Bug Depends on: | 13897 | ||
| Bug Blocks: | 24883 | ||
| Attachments: |
Bug 24904: DO NOT PUSH - benchmark script
Bug 24904: Speed up installer process - 1 query per table Bug 24904: DO NOT PUSH - benchmark script Bug 24904: Speed up installer process - 1 query per table Bug 24904: Speed up installer process - 1 query per table |
||
|
Description
Jonathan Druart
2020-03-19 11:51:22 UTC
Created attachment 101005 [details] [review] Bug 24904: DO NOT PUSH - benchmark script Created attachment 101006 [details] [review] Bug 24904: Speed up installer process - 1 query per table Since bug 13897 we are using YAML file to store the installer data. We are generating/executing 1 query per row, which is much more slower than before. We should generate 1 query per table to get back to the previous execution time. With this patch we are going to generate a single one big SQL query per table. Test plan: 1/ Checkout a commit before bug 13897: git checkout -b before_13897 0706922221dcdf9fa8ed7fcf86245c5622f33a3c 2/ Execute the following commands (several times, to get a median!): mysql -h db -u koha_kohadev -ppassword -e"DROP DATABASE koha_kohadev"; mysql -h db -u koha_kohadev -ppassword -e"CREATE DATABASE koha_kohadev"; koha-shell -p -c "perl benchmark_installer.pl sql" kohadev; Note the different times displayed in the output, especially the first 2 installer/data/mysql/en/marcflavour/marc21/mandatory/authorities_normal_marc21.yml installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.yml They are the number of seconds took to insert them 3/ checkout master git checkout -B master origin/master # Will reset your master branch! 4/ Execute the previous commands but without the 'sql' parameter passed to the benchmark script mysql -h db -u koha_kohadev -ppassword -e"DROP DATABASE koha_kohadev"; mysql -h db -u koha_kohadev -ppassword -e"CREATE DATABASE koha_kohadev"; koha-shell -p -c "perl benchmark_installer.pl" kohadev; Note the different times. 5/ Apply this patch and retry 4 Please post the execution times when you signoff on this patch. Spoiler: total time goes from 21s to 4s. marc21_framework_DEFAULT.yml from 12s to .5s Created attachment 101030 [details] [review] Bug 24904: DO NOT PUSH - benchmark script Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> Created attachment 101031 [details] [review] Bug 24904: Speed up installer process - 1 query per table Since bug 13897 we are using YAML file to store the installer data. We are generating/executing 1 query per row, which is much more slower than before. We should generate 1 query per table to get back to the previous execution time. With this patch we are going to generate a single one big SQL query per table. Test plan: 1/ Checkout a commit before bug 13897: git checkout -b before_13897 0706922221dcdf9fa8ed7fcf86245c5622f33a3c 2/ Execute the following commands (several times, to get a median!): mysql -h db -u koha_kohadev -ppassword -e"DROP DATABASE koha_kohadev"; mysql -h db -u koha_kohadev -ppassword -e"CREATE DATABASE koha_kohadev"; koha-shell -p -c "perl benchmark_installer.pl sql" kohadev; Note the different times displayed in the output, especially the first 2 installer/data/mysql/en/marcflavour/marc21/mandatory/authorities_normal_marc21.yml installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.yml They are the number of seconds took to insert them 3/ checkout master git checkout -B master origin/master # Will reset your master branch! 4/ Execute the previous commands but without the 'sql' parameter passed to the benchmark script mysql -h db -u koha_kohadev -ppassword -e"DROP DATABASE koha_kohadev"; mysql -h db -u koha_kohadev -ppassword -e"CREATE DATABASE koha_kohadev"; koha-shell -p -c "perl benchmark_installer.pl" kohadev; Note the different times. 5/ Apply this patch and retry 4 Please post the execution times when you signoff on this patch. Spoiler: total time goes from 21s to 4s. marc21_framework_DEFAULT.yml from 12s to .5s Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> My numbers, 99.99% confidence 3.906 ±0.252 SQL files 15.516 ±0.772 YAML wo/patch 3.324 ±0.193 YAML w/patch Very good! Created attachment 101071 [details] [review] Bug 24904: Speed up installer process - 1 query per table Since bug 13897 we are using YAML file to store the installer data. We are generating/executing 1 query per row, which is much more slower than before. We should generate 1 query per table to get back to the previous execution time. With this patch we are going to generate a single one big SQL query per table. Test plan: 1/ Checkout a commit before bug 13897: git checkout -b before_13897 0706922221dcdf9fa8ed7fcf86245c5622f33a3c 2/ Execute the following commands (several times, to get a median!): mysql -h db -u koha_kohadev -ppassword -e"DROP DATABASE koha_kohadev"; mysql -h db -u koha_kohadev -ppassword -e"CREATE DATABASE koha_kohadev"; koha-shell -p -c "perl benchmark_installer.pl sql" kohadev; Note the different times displayed in the output, especially the first 2 installer/data/mysql/en/marcflavour/marc21/mandatory/authorities_normal_marc21.yml installer/data/mysql/en/marcflavour/marc21/mandatory/marc21_framework_DEFAULT.yml They are the number of seconds took to insert them 3/ checkout master git checkout -B master origin/master # Will reset your master branch! 4/ Execute the previous commands but without the 'sql' parameter passed to the benchmark script mysql -h db -u koha_kohadev -ppassword -e"DROP DATABASE koha_kohadev"; mysql -h db -u koha_kohadev -ppassword -e"CREATE DATABASE koha_kohadev"; koha-shell -p -c "perl benchmark_installer.pl" kohadev; Note the different times. 5/ Apply this patch and retry 4 Please post the execution times when you signoff on this patch. Spoiler: total time goes from 21s to 4s. marc21_framework_DEFAULT.yml from 12s to .5s Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> My numbers, 99.99% confidence 3.906 ±0.252 SQL files 15.516 ±0.772 YAML wo/patch 3.324 ±0.193 YAML w/patch Very good! Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> Nice work everyone! Pushed to master for 20.05 does not apply to 19.11.x branch. not backported |