Database Tipps Performance

Some performance tips for settings on the escidoc-core database

Tablespaces[edit]

- 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[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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).