Database Tipps Performance

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

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
• 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