Difference between revisions of "Database Tipps Performance"
Jump to navigation
Jump to search
m |
|||
| Line 26: | Line 26: | ||
* effective_cache_size = 3GB | * effective_cache_size = 3GB | ||
* default_statistics_target = 500 | * default_statistics_target = 500 | ||
After changes in postgresql.conf postgres has to be restarted | |||
==Table list.property== | ==Table list.property== | ||
| Line 33: | Line 35: | ||
* ALTER TABLE list.property ALTER COLUMN local_path SET STATISTICS 1000; | * ALTER TABLE list.property ALTER COLUMN local_path SET STATISTICS 1000; | ||
* ALTER TABLE list.property ALTER COLUMN "position" 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 | |||
Revision as of 11:15, 6 August 2010
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
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