Bugzilla – Attachment 101071 Details for
Bug 24904
New YAML files for installer are slow to insert
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 24904: Speed up installer process - 1 query per table
Bug-24904-Speed-up-installer-process---1-query-per.patch (text/plain), 4.21 KB, created by
Kyle M Hall (khall)
on 2020-03-20 10:58:23 UTC
(
hide
)
Description:
Bug 24904: Speed up installer process - 1 query per table
Filename:
MIME Type:
Creator:
Kyle M Hall (khall)
Created:
2020-03-20 10:58:23 UTC
Size:
4.21 KB
patch
obsolete
>From a67cd79a26f521222be0c6651b17e71a3c77330f Mon Sep 17 00:00:00 2001 >From: Jonathan Druart <jonathan.druart@bugs.koha-community.org> >Date: Thu, 19 Mar 2020 11:57:16 +0100 >Subject: [PATCH] Bug 24904: Speed up installer process - 1 query per table >MIME-Version: 1.0 >Content-Type: text/plain; charset=UTF-8 >Content-Transfer-Encoding: 8bit > >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> >--- > C4/Installer.pm | 12 +++++++----- > 1 file changed, 7 insertions(+), 5 deletions(-) > >diff --git a/C4/Installer.pm b/C4/Installer.pm >index a1c6f5f6a6..5f343f5ffe 100644 >--- a/C4/Installer.pm >+++ b/C4/Installer.pm >@@ -487,19 +487,21 @@ sub load_sql { > my @rows = @{ $table->{$table_name}->{rows} }; # > my @columns = ( sort keys %{$rows[0]} ); # column names > my $fields = join ",", map{sprintf("`%s`", $_)} @columns; # idem, joined >- my $placeholders = join ",", map { "?" } @columns; # '?,..,?' string >- my $query = "INSERT INTO $table_name ( $fields ) VALUES ( $placeholders )"; >- my $sth = $dbh->prepare($query); >+ my $query = "INSERT INTO $table_name ( $fields ) VALUES "; > my @multiline = @{ $table->{$table_name}->{'multiline'} }; # to check multiline values; >+ my $placeholders = '(' . join ( ",", map { "?" } @columns ) . ')'; # '(?,..,?)' string >+ my @values; > foreach my $row ( @rows ) { >- my @values = map { >+ push @values, map { > my $col = $_; > ( @multiline and grep { $_ eq $col } @multiline ) > ? join "\r\n", @{$row->{$col}} # join multiline values > : $row->{$col}; > } @columns; >- $sth->execute( @values ); > } >+ # Doing only 1 INSERT query for the whole table >+ $query .= join ', ', ( $placeholders ) x scalar @rows; >+ $dbh->do( $query, undef, @values ); > } > for my $statement ( @{ $yaml->{'sql_statements'} } ) { # extra SQL statements > $dbh->do($statement); >-- >2.21.1 (Apple Git-122.3)
<b>You cannot view the attachment while viewing its details because your browser does not support IFRAMEs. <a href="attachment.cgi?id=101071">View the attachment on a separate page</a>.</b>
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 24904
:
101005
|
101006
|
101030
|
101031
| 101071