eprints_tech messages

http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
*** EPrints community wiki - http://wiki.eprints.org/
Hi!

I am experiencing great performance problems with my EPrints
installation on Oracle, some requests take more than 15 seconds to
complete, even though there is only one eprint in the database!

I did some profiling research, added timers around the relevant sections
in session setup and found the culprit: it's EPrints::Database::has_table().

This method is used at least once in every request (see
$self->{database}->is_latest_version()). It uses DBI::table_info which
is _very_ expensive on Oracle. Each call takes more than one second on
our Oracle server which admittedly hosts many schemas and many many tables.

I have disabled the is_latest_version() check now, which speeds up
processing of static pages dramatically but unfortunately has_table() is
still used in many other places. (See my dependency graph below.)

Maybe the has_table check should be removed from many places, since it's
often much more efficient to try to use the table and catch a possible
error than to add another database query (which in this case is also
very slow). ("It is much easier to apologize than it is to get ↵
permission.")
In many cases, has_table() is only a sanity check, which succeeds nearly
everytime.

Additionally I have overridden has_table by the following code:
-------------------- 8< --------------------
sub has_table
{
	my( $self, $tablename ) =  AT _;
	my $rc;

	my $sql = "SELECT 1 FROM user_tables WHERE table_name=?";
	my $sth = $self->prepare($sql);
	$sth->execute( $tablename );
	$rc = $sth->fetch ? 1 : 0;
	$sth->finish();
	return $rc;
}
-------------------- 8< --------------------

(Should have looked at has_column() earlier, this method already states:
"Default method is really, really slow"... :-))

EPrints::Database::has_table() is used in:
  create_dataset_tables()
    <- create_archive_tables()
      <- bin/epadmin
    <- bin/epadmin
    <- bin/add_field
  create_dataset_index_tables()
    <- create_dataset_tables()
  create_dataset_ordervalues_tables()
    <- create_dataset_tables()
  has_dataset()
    <- bin/epadmin
  _has_field()
    <- has_field()
      <- cgi/paracite
      <- EPrints/DataSet.pm
      <- EPrints/DataObj/MetaField.pm
      <- EPrints/Plugin/Import.pm
      <- EPrints/Plugin/Screen/MetaField/View.pm
      <- EPrints/Plugin/Export/ContextObject.pm
      <- EPrints/Plugin/Export/Simple.pm
      <- EPrints/Plugin/Export/EAP.pm
      <- EPrints/Plugin/Export/MODS.pm
      <- EPrints/Plugin/Export/COinS.pm
      <- EPrints/Plugin/Export/ContextObject/Book.pm
      <- EPrints/Plugin/Export/ContextObject/Journal.pm
      <- EPrints/DataObj.pm
      <- bin/remove_field
      <- bin/epadmin
      <- bin/add_field
  _add_multiple_field()
      <- _add_field()
        <- add_field()
          <- perl_lib/EPrints/DataObj/MetaField.pm
          <- bin/epadmin
          <- bin/add_field
  install_table()
  drop_table()
    <- drop_dataset_tables()
      <- bin/remove_field
    <- dispose_buffer()
      <- garbage_collect()
        <- EPrints/Session.pm
    <- drop_cache()
      <- drop_old_caches()
        <- from_cache
          <- EPrints/List.pm
          <- EPrints/Search.pm
      <- EPrints/List.pm
    <- install_table()
    <- EPrints/DataObj/Cachemap.pm()
    <- bin/epadmin()

Cheers,
--leo
-- 
e-mail   ::: Leo.Bergolth (at) wu.ac.at
fax      ::: +43-1-31336-906050
location ::: IT-Services | Vienna University of Economics | Austria

eprints_tech messages

[EP-tech] EPrints::Database::has_table() is very expensive on Oracle