Slow importObservation sos 4.0

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Slow importObservation sos 4.0

sanguinela
Hi Carsten,
I have sos v4.0 with huge quantity of data : at now 3882606 observations.
It seems that duration of import function is proportionally with number of data in db.
I think I understand from this forum that every import starts a reload of cache. This is correct?
Now with this data every import duration is about 40s. This is a huge limit!

I have already removed (http://sensorweb.forum.52north.org/Offering-Cache-Update-takes-long-td4027506.html#a4027511) spatial filtering profile.

Can I disable caching reload by a setup of configuration? How?
What is used for the cache?
Maybe in new version of SOS this problem is fixed?

The problem is not due to hardware, I have 8GB of RAM, 4CPU (2GHz) and free space in disk.

have you any hints?
Kind Regards.
S.
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

Carsten Hollmann
Hi,

> I have sos v4.0 with huge quantity of data : at now 3882606 observations.
> It seems that duration of import function is proportionally with number of
> data in db.
> I think I understand from this forum that every import starts a reload of
> cache. This is correct?

No, the SOS 4.x does not reload the cache from the database after data
insertion. The SOS updates the local cache directly with the information
from the request.

> Now with this data every import duration is about 40s. This is a huge limit!
>
> I have already removed
> (http://sensorweb.forum.52north.org/Offering-Cache-Update-takes-long-td4027506.html#a4027511)
> spatial filtering profile.
>
> Can I disable caching reload by a setup of configuration? How?

Cache update can be disabled via the Settings (admin interface ->
Settings ->  Service tab). If you set the value of "Content cache update
interval"" to "0" the periodically cache update is disabled.

> What is used for the cache?

The cache consists of Java classes.

How did you insert your data into the SOS?

Best,
Carsten
_______________________________________________
SWE mailing list
[hidden email]
http://list.52north.org/mailman/listinfo/swe
http://sensorweb.forum.52north.org
Please respect our mailing list guidelines:
http://52north.org/resources/mailing-lists-and-forums/guidelines
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

sanguinela
Hi Carsten,
thx for your answers.
I use SOS API for import my data.

I have another import in sos via slq, but this one is fast.
I have some problem with SOS API due to time duration of this operations

Regards.
S.
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

Carsten Hollmann
Hi,

which setup do you use?
SOS, DBMS, Tomcat, JDK versions?

Is it possible for you to provide us your insertion requests?

Best,
Carsten
_______________________________________________
SWE mailing list
[hidden email]
http://list.52north.org/mailman/listinfo/swe
http://sensorweb.forum.52north.org
Please respect our mailing list guidelines:
http://52north.org/resources/mailing-lists-and-forums/guidelines
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

sanguinela
So, this is my environment:

SOS : 4.0
DBMS : Postgres 9.1 + Postgis 2.0
Tomcat : 7.0.26
JDK : jdk1.7.0_60

example_import_data_comet.xml

Regards.
S.
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

Carsten Hollmann
Hi,

in PostgreSQL versions prior 9.3 the default "shared_buffers" value
(memory to use for caching) is low. For large datasets this can cause a
slower performance.

But the you can increase the "shared_buffers" value and possibly your
performance issue is solved by this adjustment.

Here you can find some more information about tuning PostgreSQL and the
shared_buffers:

https://wiki.52north.org/bin/view/SensorWeb/SosFAQ#Increase_the_shared_buffers

Best,
Carsten
_______________________________________________
SWE mailing list
[hidden email]
http://list.52north.org/mailman/listinfo/swe
http://sensorweb.forum.52north.org
Please respect our mailing list guidelines:
http://52north.org/resources/mailing-lists-and-forums/guidelines
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

sanguinela
Hi Carsten,
really thank for your support.
I have already tried to increase shared_buffer but I have got this:


This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 572907520 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections. If the request size is already small, it's possible that it is less than your kernel's SHMMAX parameter, in which case raising the request size or reconfiguring SHMMIN is called for. The PostgreSQL documentation contains more information about shared memory configuration

It seems there is a vm limit.
Have you any other hints? Actually : shared_buffers = 24MB
Regards.
S.
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

sanguinela
Carsten,
this morning,  duration of import was 92s for each observation.

Now, I have new environment:

SOS : 4.0
DBMS : Postgres 9.3 + Postgis 2.1
Tomcat : 7.0.26
JDK : jdk1.7.0_60

RAM : 8GB

postgres conf:
shared_buffer 1GB

At now, one import it's done in 50s.

Regards.
S.
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

sanguinela
Hi Carsten,
I have increased log details of postgres.

Below there is query that sos has done during import phase.

LOGQUERYIMPORT.sql

This transaction it's done in more less 2seconds.

In the other 50second what 53n_sos does??
I'm sorry for my insistence

Regards.
S.
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

Carsten Hollmann
Hi,

in the 52°North SOS version 4.0 the local cache has been updated and
persisted in a file during the processing of the transactional
operations. Perhaps this leads to longer processing time, depending on
the complexity of the metadata (number of procdures, offerings, ...).

Starting with version 4.1, the writing into the file is executed
asynchronously in an own thread. This has considerably improved the
performance of transactional operations.

Therefore, I recommend to use a newer version of SOS.

Best,
Carsten
_______________________________________________
SWE mailing list
[hidden email]
http://list.52north.org/mailman/listinfo/swe
http://sensorweb.forum.52north.org
Please respect our mailing list guidelines:
http://52north.org/resources/mailing-lists-and-forums/guidelines
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

sanguinela
Hi Carsten,
I have upgraded my sos to version 4.1
Now the import procedure has good performances!
The numbers are similar, 120 like before, but 120ms now! not seconds!
Thank for the support.
Regards.
S.
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

and.vianello
Hi,
I have similar problem with my SOS 4.3.7 and postgres 9.3: to query the observation table with 1.5 milion records I need more than 2 minutes...too much.

The DB is on a server with 8 GB of memory and I set to 2 GB the shared memory in postgresql.conf file.
I restarted also the services but....no differences.

I also made vacuum and analyze but still no differences.

What may be the problem?

Thank you a lot,
Andrea.
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

Carsten Hollmann
Hi Andrea,

it is hard to say because it might be due to the modeling of the data or
due to the query or ... .

Do you use the SOS Test Client to execute the query?
Because the SOS Test Client uses a pretty print library for the
responses which is not so fast.

Is the Tomcat running on the same server as the DB?

How many procedures, featureOfInterest, observedProperties and offerings
are contained in your SOS instance?

Which GetObservation request do you execute?
Do you use any filters?

It would be good if you can provide the request you have executed

Is your SOS publicly accessible?

Best,
Carsten

Am 29.08.2016 um 17:04 schrieb and.vianello:

> Hi,
> I have similar problem with my SOS 4.3.7 and postgres 9.3: to query the
> observation table with 1.5 milion records I need more than 2 minutes...too
> much.
>
> The DB is on a server with 8 GB of memory and I set to 2 GB the shared
> memory in postgresql.conf file.
> I restarted also the services but....no differences.
>
> I also made vacuum and analyze but still no differences.
>
> What may be the problem?
>
> Thank you a lot,
> Andrea.
>
>
>
> --
> View this message in context: http://sensorweb.forum.52north.org/Slow-importObservation-sos-4-0-tp4028033p4028632.html
> Sent from the 52° North - Sensor Web Community Forum mailing list archive at Nabble.com.
> _______________________________________________
> SWE mailing list
> [hidden email]
> http://list.52north.org/mailman/listinfo/swe
> http://sensorweb.forum.52north.org
> Please respect our mailing list guidelines:
> http://52north.org/resources/mailing-lists-and-forums/guidelines
>

--
Carsten Hollmann

52°North Initiative for Geospatial Open Source Software GmbH
Martin-Luther-King-Weg 24
48155 Münster, Germany

E-Mail: [hidden email]
Fon: +49 (0)-251-396371-35
Fax: +49 (0)-251-396371-11
http://52north.org/
Twitter: @FiveTwoN

General Managers: Dr. Albert Remke, Dr. Andreas Wytzisk
Local Court Muenster HRB 10849
_______________________________________________
SWE mailing list
[hidden email]
http://list.52north.org/mailman/listinfo/swe
http://sensorweb.forum.52north.org
Please respect our mailing list guidelines:
http://52north.org/resources/mailing-lists-and-forums/guidelines
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

and.vianello
Hi Carsten,
thank you for your reply.

Normally I try to read the data by PgAdmin by the "view table" or a "select * from observation;" I mean a complete table view. It's very slow even if a use the "where" clause to filter data.

PgAdmin is in my computer and the SOS database is in a server on our local network that is fast.

52N webb-app is in a different server than database. The DB server has 32 GB of RAM and 4GB for shared_buffer parameter in postgresql.conf file, I think it's enough.

At the moment we are filling the database and we have:

11 procedures
5 FOIs
39 observable properties
19 offerings
1,5 milion observations

But they are going to incrase a lot if we improve the performance of get obesrvations.

If I execute the get observation by the test-client I get this message:
" A script on this page may be busy, or it may have stopped responding. You can stop the script now, open the script in the debugger, or let the script continue.

Script: <a href="http://sdcgbe01.eurac.edu:8083…app/static/lib/prettify.min.js:">http://sdcgbe01.eurac.edu:8083…app/static/lib/prettify.min.js:"



The request is:
<?xml version="1.0" encoding="UTF-8"?>
<sos:GetObservation service="SOS" version="2.0.0"
    xmlns:sos="http://www.opengis.net/sos/2.0"
    xmlns:fes="http://www.opengis.net/fes/2.0"
    xmlns:gml="http://www.opengis.net/gml/3.2"
    xmlns:swe="http://www.opengis.net/swe/2.0"
    xmlns:xlink="http://www.w3.org/1999/xlink"
    xmlns:swes="http://www.opengis.net/swes/2.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.opengis.net/sos/2.0 http://schemas.opengis.net/sos/2.0/sos.xsd">
   
    <sos:procedure>Laimburg_station_Pfatten_Vadena</sos:procedure>
   
    <sos:responseFormat>http://www.opengis.net/om/2.0</sos:responseFormat>
</sos:GetObservation>

Same result if I define a specific offering or observable property.
So I think it's strange we need 2-3 minutes to get one table, isn't it?
Thank you for your help,
Andrea.

 
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

Carsten Hollmann
Hi Andrea,

as I wrote in my previous mail, the SOS Test Client uses a pretty print
library whose performance is not so good, especially not with large
responses.

If your query for all observations of one procedure the response might
contain thousands observations that have to be encoded as single
OM_Observations.

Have you tried to execute a KVP GetObservation request in the browser?

You can also try the MergeObservationsIntoDataArray [0] which reduces
the size of the response by merging all observations that have the same
procedure, observedProperty and featureOfInterest.

Best,
Carsten

[0]
https://wiki.52north.org/SensorWeb/SensorObservationServiceIVDocumentation#MergeObservationsIntoDataArray
_______________________________________________
SWE mailing list
[hidden email]
http://list.52north.org/mailman/listinfo/swe
http://sensorweb.forum.52north.org
Please respect our mailing list guidelines:
http://52north.org/resources/mailing-lists-and-forums/guidelines
Reply | Threaded
Open this post in threaded view
|

Re: Slow importObservation sos 4.0

and.vianello
Hi Carsten,
I used the method you suggested and with this "query":
http://sdcgbe01.eurac.edu:8083/52n-sos-webapp/service?service=SOS&version=2.0.0&request=GetObservation&MergeObservationsIntoDataArray=true&procedure=Laimburg_station_Pfatten_Vadena

I obtained that the browser doesn't crash but it's still slow i think, it need more than 2 minutes for one procedure with 3 offering and 8 observable properties. Is it normal? Or may be we have some problem in your opinion?

A select query on the DB by pgadmin for complete observation table (now about 3 milion of records) needs 5 minutes.

What do you think about?
Thank you very much.
Andrea.