Stored procedure data not collected about pgobserver HOT 18 CLOSED

Are the stored procedures in a seperate schema? Are they plpgsql functions?

In theory as far as your config for that database is set to monitor sproc metrics at a given interval the data should be gathered.

from pgobserver.

the problem is most probably that currently "by default" only data from schemas named '%_api%' or '%_data' is gathered...so most probably you need to tailor the query from https://github.com/zalando/PGObserver/blob/master/gatherer/src/main/java/de/zalando/pgobserver/gatherer/SprocGatherer.java to meet your needs

...pg_namespace WHERE nspname LIKE '%_api%' ) apis ( name, rank ) where rank <= 2 ) OR schemaname LIKE '%_data'...

from pgobserver.

@Jan-M Yes the stored procedures are spread across different schemas on the database I am monitoring. They are plpgsql functions, yes.

from pgobserver.

As Kaarel mentioned the public gatherer contained our filter, I was somehow under the impression this is not the case. I have moved this filter to the config file where you can change/amend/remove it with the latest commit.

Can you give this a try?

from pgobserver.

I have set the deafult filter to this in the config file in my home folder. Compiled the files again and ./run.sh.
"default_schema_filter":""

I tried this and I thought this would be generic enough to catch all the sprocs in the database I am monitoring. However, it is still not populating any sprocs. Does it take time to populate? Even the 'Show all sprocs' does not show anything.

from pgobserver.

That is not good news, we will take a look into why this is happening.

from pgobserver.

Maybe you can do one more thing, check whether querying the view "pg_stat_user_functions" with the specified user does indeed yield any functions.

from pgobserver.

@Jan-M Yep! Using the pgobserver_gatherer user, I cannot view all the queries in pg_stat_statements. It shows .
Where is pg_stat_user_functions view located?

from pgobserver.

With my user I am able to run basically:

select * from pg_stat_user_functions;

What PG version are you running?

from pgobserver.

I am using PostgreSQL 9.3.5. I ran that query across many different users. I tried on the command line as postgres using psql too and all of them returned 0 rows. The table is there but it seems to be empty.

from pgobserver.

My first guess is, that you need to enable track_functions

http://www.postgresql.org/docs/9.3/static/monitoring-stats.html

track_functions (enum)
Enables tracking of function call counts and time used. Specify pl to track only procedural-language functions, all to also track SQL and C language functions. The default is none, which disables function statistics tracking. Only superusers can change this setting.

from pgobserver.

Ah ok! Yep they all seem to be commented out. Are there any other options that need to be turned out to get the most out of PGObserver?

from pgobserver.

Similarly you need to enable the pg_stat_statement module if you in the future want to track statements. I will update our readme with a link to this page.

from pgobserver.

Yep, this module has been enabled. Like I said I queried this table but the gatherer user was not able to view all the fields. Some fields needed extra permissions it seems.

from pgobserver.

The new version that covers pg stat statements contains a function to query this table that uses the security definer setting, so the gatherer role itself does not need super users privs ( which is needed to see the query column ), only during deployment super user privs are needed.

from pgobserver.

I am closing this for now, feel free to give more feedback if you run into troubles.

from pgobserver.

@Jan-M Could you show me how I would go about filtering by the name of sproc? For e.g. exclude sproc with the substring 'pldbg'.

from pgobserver.

https://github.com/zalando/PGObserver/blob/master/gatherer/src/main/java/de/zalando/pgobserver/gatherer/SprocGatherer.java

As you can see in the above file we just append the filter string to the query, thus I would try to set the filter to:

AND NOT f.funcname like '%pldbg%'

from pgobserver.