Database Performance Issues

[p-a-s-c-a-l]

Queries on the View _pycswview created for pyCSW are very slow on some machines. A select * from pycsw_view (> 7.000 rows) takes

• ~ 60 (!!!) seconds on a local (VM Network Connection) Ubuntu Server VirtualBox VM (4x Core i7, 2.8 GHZ, 8GB RAM) with Postgres 9.3/PostGis 2.1
• ~ 20 seconds on the remote (WAN Connection) deltares server with Postgres 8.4/PostGis 1.5
• ~ 26 seconds on another remote (WAN Connection) deltares server with Postgres 9.3/PostGis 2.0
• ~ 6 (!) seconds on a remote (in LAN) Windows 7 server (8x Core i7, 2.8 GHZ, 16GB RAM) with Postgres 9.3/PostGis 2.0

The Postgres 9.3/PostGis 2.0 view creation statements are sightly different than the Postgres 8.4/PostGis 1.5 statements since they use new Postgres 9 functions and populate an an additional pyCSW column (type) with data. This could explain the difference between the two deltares servers.

Possible Causes

• Missing Indexes
• Complex Joins: many relations need to be resolved
• Poorly designed SQL Statements
• "Big" data sets: query result is > 40 MB
• General I/O limitations of the VM

Implemented Workarounds

• Defined Indexes on cids object tables for all ids: This should speed up view creation statements
• Defined a Postgres Materialized View _pycsw_meterializedview : This should considerably speed up queries on the view since the result of creation statements is cached as a table
• Defined Indexes on the materialized view (geom, anytext, keywords): This should speed up queries made by pyCSW.

The differences after and before the optimisations are negligible. Querying the materialized view on the VM is ~ 5 sec faster than querying the ordinary view!

Other observations Postgres seems to utilize only one CPU, so the # of CPU Cores of the VM is not the limiting factor. The VM is also configured to consume 100% of the assigned cores. There could be an issue with network bandwidth:

• deltares -> client via WAN: ~ 2 MB/sec
• VM -> client on VM host: ~ 1MB/sec
• server -> client via LAN: ~ > 10 MB/sec

But also queries executed within the VM OS are very slow. Another observation is, that not only queries on views, but also queries on object tables are very slow. A select * from resource (~7.000 results, ~20MB) takes > 5 sec within the VM!

Conclusions:

• Queries on the pyCSW view on the deltares servers are not fast but acceptable. Probably the time-outs of pyCSW have to be adjusted. Further optimisations of the view statements should be made.
• Queries on VM are not acceptable. Currently, no solution is available. The Postgres DBMS has to be installed on the host machine.

[p-a-s-c-a-l]

The _csw_create_view_forpycsw.sql script for creating the view is available here: https://github.com/switchonproject/cids-custom-switchon-server/tree/feature/5-meta-data-import/src/model/scripts

The materialized view is currently not in use, since a) the performance improvements are negligible and b) update triggers for the materialized view do not yet exist (see also https://github.com/switchonproject/cids-custom-switchon-server/issues/9)!

[p-a-s-c-a-l]

• title and description of resource should be put into a common index and the fulltext search for title and description should use this index.
• as mentioned here, geo intersects query on multi-polygons is nearly unusable and has to be optimized.

[ci-cismet-de]

Integrated branch 'bug/7-database-performance-issues (9340795c952e08f171c0728425c2081e06c44d65)' into 'dev (a59903aece4df502016ad3b0fef70b64517214ec)' -- Build 37

[esafa]

great! this was one of the thing I thought would be very useful.

On Wed, Mar 4, 2015 at 5:44 PM, ci-cismet-de notifications@github.com wrote:

Integrated branch 'bug/7-database-performance-issues (9340795 https://github.com/switchonproject/cids-custom-switchon-server/commit/9340795c952e08f171c0728425c2081e06c44d65)' into 'dev (a59903a https://github.com/switchonproject/cids-custom-switchon-server/commit/a59903aece4df502016ad3b0fef70b64517214ec)' -- Build 37 https://ci.cismet.de/job/cids-custom-switchon-server/37/

— Reply to this email directly or view it on GitHub https://github.com/switchonproject/cids-custom-switchon-server/issues/7#issuecomment-77194431 .

Esa Falkenroth mobile: 070-666-14-14 (intl +46706661414) work: 011-4958298 (intl +46114958298) skype: Esa.Falkenroth email: nospam@falkenroth.se (for business mail) email: esa@falkenroth.se (for private mail)

[p-a-s-c-a-l]

• changed contents of column anytext to contain only keywords, abstract and title of the resource.
• fulltext indexes created.