Database Tipps Performance

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

• https://subversion.mpdl.mpg.de/repos/common/trunk/common_services/framework_access/src/test/resources/test/rest-test/
  • note: script has to be adjusted to take core-service URL as parameter (no time to do it now)
  • todo: extend with more tests
• to run this script you may use

                 ./rest-test.sh >& rest_test_results.txt

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

Uniform Resource Locator

Filename Extension for Text Files