Database Tipps Performance

From MPDLMediaWiki
Jump to: navigation, search

Some performance tips for settings on the escidoc-core database

Tablespaces

    • data shall be separated from indexes (migration scripts from 1.1 to 1.2 are doing it already)

Several tablespaces are created on following directory structure (under postgres data directory):

  • tables
    • escidoc-core -> tbl_escidoc_core_data
    • fedora -> tbl_fedora_data
    • triples ->tbl_triples_data
    • statistics -> tbl_statistics_data
  • indexes
    • escidoc-core-normal -> tbl_escidoc_core_normal_index
    • escidoc-core-large -> tbl_escidoc_core_large_index
    • fedora -> tbl_fedora_index
    • triples -> tbl_triples_index
    • statistics -> tbl_statistics_index

Indexes

  • see create-index statements in following scripts
   https://subversion.mpdl.mpg.de/repos/common/trunk/common_services/framework_access/src/test/resources/migration_1.1_1.2/postmigration-pre-recache.sql
   https://subversion.mpdl.mpg.de/repos/common/trunk/common_services/framework_access/src/test/resources/migration_1.1_1.2/set_list_property_indexes.sql

PostgreSQl.conf

Highly dependent on server configuration and available memory (here example from latest-coreservice.mpdl.mpg.de)

  • shared_buffers = 768 MB
  • work_mem = 16MB
    • in general shall not be too high, is used for sorting and is allocated for each sort-request (highly experimental)
  • seq_page_cost = 0.1
  • random_page_cost =0.1
  • effective_cache_size = 3GB
  • default_statistics_target = 500

After changes in postgresql.conf postgres has to be restarted. Note: the numbers above are run on a system that has 4GB memory.

     Checking free memory on Suse servers:
     free -m

Table list.property

  • ALTER TABLE list.property ALTER COLUMN resource_id SET STATISTICS 1000;
  • ALTER TABLE list.property ALTER COLUMN "value" SET STATISTICS 1000;
  • ALTER TABLE list.property ALTER COLUMN local_path SET STATISTICS 1000;
  • ALTER TABLE list.property ALTER COLUMN "position" SET STATISTICS 1000;


General info

  • affected are mostly the filter queries
  • after these settings are done:
    • analyze all database tables
    • the first filter executions may be slow, the rest are getting faster


Rest-test performance checks

                 ./rest-test.sh >& rest_test_results.txtFilename Extension for Text Files


to write the output in a file (on Suse Linux).