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.
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