Changeset 24190

Show
Ignore:
Timestamp:
22.06.2011 15:17:23 (9 years ago)
Author:
max
Message:

Improve set_infodb_entry to now use optimisations such as transactions, this is very useful when we only update one metadata entry, it is now much faster.
Add a new set of optional optimisations when writing to the database in order to get even faster I/O by using PRAGMA journal_mode (MEMORY instead of DELETE) and synchronous (OFF instead of FULL).

Files:
1 modified

Legend:

Unmodified
Added
Removed
  • main/trunk/greenstone2/perllib/dbutil/sqlite.pm

    r23683 r24190  
    2929use strict; 
    3030 
     31# Please set $db_fast to 1 if you wish to enable faster I/O to the database by using 
     32# optimisations such as PRAGMA journal_mode (MEMORY instead of DELETE) and synchronous (OFF instead of FULL) 
     33# Please be aware that in this case it will be less secure and the database file 
     34# may become corrupted if the if the operating system crashes or the computer loses 
     35# power before that data has been written to the disk surface. 
     36# But the speed gain is about 50x 
     37my $db_fast = 0; 
     38 
    3139 
    3240# ----------------------------------------------------------------------------- 
     
    4856  }  
    4957 
    50   if(!open($infodb_handle, "| \"$sqlite3_exe\" \"$infodb_file_path\"")) 
     58  # running sqlite3 with the pragma journal_mode=memory, causes sqlite to print out the  
     59  # word "memory". While this is not a problem usually, in our case, this ends up going out 
     60  # to the web page first, as part of the web page's headers, thus ruining the web page 
     61  # which causes an Internal Server Error (500). Therefore, we redirect sqlite's output to 
     62  # the nul device instead.  
     63  my $nul_device=""; 
     64  if(defined $db_fast && $db_fast == 1) { 
     65    if($ENV{'GSDLOS'} =~ m/windows/) { 
     66        $nul_device=">NUL"; 
     67    } else { 
     68        $nul_device=">/dev/null"; # linux, mac 
     69    } 
     70  }   
     71   
     72  if(!open($infodb_handle, "| \"$sqlite3_exe\" \"$infodb_file_path\"$nul_device")) 
    5173  { 
    5274      print STDERR "Error: Failed to open pipe to \"$sqlite3_exe\" \"$infodb_file_path\"\n"; 
     
    5678 
    5779  binmode($infodb_handle,":utf8"); 
     80   
     81   # Add extra optimisations, less secure but with a massive gain in performance with large databases which are often uptaded 
     82   # They should be set before the transaction begins 
     83  if (defined $db_fast && $db_fast == 1) { 
     84    print $infodb_handle "PRAGMA synchronous=OFF;\n"; 
     85    print $infodb_handle "PRAGMA journal_mode=MEMORY;\n"; 
     86  } 
     87   
     88  # This is very important for efficiency, otherwise each command will be actioned one at a time 
     89  print $infodb_handle "BEGIN TRANSACTION;\n"; 
     90   
     91 
    5892 
    5993  if (!(defined $opt_append) || ($opt_append ne "append")) { 
     
    68102  print $infodb_handle "CREATE INDEX IF NOT EXISTS dmd ON document_metadata(docOID);\n"; 
    69103 
    70   # This is very important for efficiency, otherwise each command will be actioned one at a time 
    71   print $infodb_handle "BEGIN TRANSACTION;\n"; 
    72  
    73104  return $infodb_handle; 
    74105} 
     
    79110  my $infodb_handle = shift(@_); 
    80111 
     112  # This is crucial for efficient queries on the database! 
     113  print $infodb_handle "CREATE INDEX IF NOT EXISTS dme ON document_metadata(element);\n"; 
     114   
    81115  # Close the transaction we began after opening the file 
    82116  print $infodb_handle "END TRANSACTION;\n"; 
    83  
    84   # This is crucial for efficient queries on the database! 
    85   print $infodb_handle "CREATE INDEX IF NOT EXISTS dme ON document_metadata(element);\n"; 
    86117 
    87118  close($infodb_handle); 
     
    201232  my $infodb_key = shift(@_); 
    202233  my $infodb_map = shift(@_); 
     234   
    203235 
    204236  # Add the key -> value mapping into the "data" table 
     
    211243    } 
    212244  } 
    213  
     245   
    214246  my $safe_infodb_key = &sqlite_safe($infodb_key); 
    215247  print $infodb_handle "INSERT OR REPLACE INTO data (key, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_entry_value) . "');\n"; 
     
    219251  if ($infodb_key !~ /\./ && $infodb_entry_value =~ /\<doctype\>doc\n/) 
    220252  { 
     253     
    221254    print $infodb_handle "DELETE FROM document_metadata WHERE docOID='" . $safe_infodb_key . "';\n"; 
    222  
     255     
    223256    foreach my $infodb_value_key (keys(%$infodb_map)) 
    224257    { 
     
    242275      foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}}) 
    243276      { 
    244         print $infodb_handle "INSERT INTO document_metadata (docOID, element, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_value_key) . "', '" . &sqlite_safe($infodb_value) . "');\n"; 
     277        print $infodb_handle "INSERT INTO document_metadata (docOID, element, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_value_key) . "', '" . &sqlite_safe($infodb_value) . "');\n"; 
    245278      } 
    246279    } 
    247280  } 
     281 
     282     #### DEBUGGING 
     283    #my $new_file = "D:\\sql.txt"; 
     284    #open(FOUT, ">>$new_file") or die "Unable to open $new_file for writing out sql statements...ERROR: $!\n"; 
     285    #print FOUT "BEGIN;\n".$insertStatementsBuffer."\nEND;\n"; 
     286    #close(FOUT); 
     287    #print STDERR $insertStatementsBuffer;  
     288     #### END DEBUGGING 
    248289} 
    249290 
     
    261302sub set_infodb_entry 
    262303{ 
    263     my $infodb_file_path = shift(@_); 
    264     my $infodb_key = shift(@_); 
    265     my $infodb_map = shift(@_); 
    266      
    267   my $sqlite3_exe = &util::filename_cat($ENV{'GSDLHOME'},"bin",$ENV{'GSDLOS'}, "sqlite3" . &util::get_os_exe()); 
    268   my $infodb_handle = undef; 
    269   my $cmd = "\"$sqlite3_exe\" \"$infodb_file_path\""; 
    270  
    271   if (!-e "$sqlite3_exe" || !open($infodb_handle, "|$cmd")) 
    272   { 
    273       print STDERR "Unable to execute: $cmd\n"; 
    274       print STDERR "$!\n"; 
     304  my $infodb_file_path = shift(@_); 
     305  my $infodb_key = shift(@_); 
     306  my $infodb_map = shift(@_); 
     307  my $infodb_handle = open_infodb_write_handle($infodb_file_path, "append"); 
     308 
     309  if (!defined $infodb_handle) { 
     310      print STDERR "Error: Failed to open infodb write handle\n"; 
    275311      return -1; 
    276312  } 
    277313  else { 
    278  
    279       binmode($infodb_handle, ":utf8"); 
    280314      write_infodb_entry($infodb_handle,$infodb_key,$infodb_map); 
    281       close($infodb_handle); 
    282   } 
    283  
     315      close_infodb_write_handle($infodb_handle); 
     316  } 
    284317  # Not currently checking for errors on write to DB 
    285318  return 0; 
    286    
    287    
    288     } 
    289      
     319 
     320} 
    290321 
    291322