Bug 24904

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
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
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.
Comment 1 Jonathan Druart 2020-03-19 12:02:08 UTC
Created attachment 101005 [details] [review]
Bug 24904: DO NOT PUSH - benchmark script
Comment 2 Jonathan Druart 2020-03-19 12:02:11 UTC
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
Comment 3 Bernardo Gonzalez Kriegel 2020-03-19 15:27:33 UTC
Created attachment 101030 [details] [review]
Bug 24904: DO NOT PUSH - benchmark script

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
Comment 4 Bernardo Gonzalez Kriegel 2020-03-19 15:27:36 UTC
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!
Comment 5 Kyle M Hall 2020-03-20 10:58:23 UTC
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>
Comment 6 Martin Renvoize 2020-03-20 15:44:00 UTC
Nice work everyone!

Pushed to master for 20.05
Comment 7 Joy Nelson 2020-04-03 20:51:43 UTC
does not apply to 19.11.x  branch.  not backported