https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24904 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24904 --- Comment #2 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 101006 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=101006&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24904 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |24883 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24883 [Bug 24883] Command line utility to load yaml files -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24904 Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #101005|0 |1 is obsolete| | --- Comment #3 from Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> --- Created attachment 101030 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=101030&action=edit Bug 24904: DO NOT PUSH - benchmark script Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24904 Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24904 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #101031|0 |1 is obsolete| | --- Comment #5 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 101071 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=101071&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24904 --- Comment #6 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Nice work everyone! Pushed to master for 20.05 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24904 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|rel_20_05_candidate | -- You are receiving this mail because: You are watching all bug changes.