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
https://subversion.mpdl.mpg.de/repos/common/trunk/common_services/framework_access/src/test/resources/migration_1.1_1.2/postmigration-pre-recache.sql
 * 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/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).