configuration problems

[oemergenc]

Hey there,

i tried this tool today and unfortunately i am having some problems. I am able to start the frontend and also the gatherer. The frontend also display some information about my configured database. So i guess that the basic configuration is okay. However on several pages of the front end, no data is displayed. For example, the tables Top 10 Sprocs last 1 hour by total run time and Top 10 Sprocs last 3 hours by total run time are not displayed, while on the same page, Load Average 15min Sprocs only shows a graph. Furthermore the following tables dont show anything:

• Perf. Issues - Tables
• Perf. Issues - SProcs
• Perf. Issues - Indexes
• Pg_Stat_Statements report

Especially Pg_Stat_Statements report is very important for me, but does not display any data. I have already configured the pg_stat_statement module in my cluster like this (added in postgresql.conf):

shared_preload_libraries = 'pg_stat_statements'         # (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Restarted everything, but still no luck.

With this enabled, i am able to do a
select * from pg_stat_statement
when connected to my database in psql and get the expected result, but not in PGObserver web frontend.

Furthermore i get the following error message when trying to access the "Top Tables" page:

Traceback (most recent call last):
File "/usr/local/lib/python2.7/dist-packages/cherrypy/_cprequest.py", line 656, in respond
response.body = self.handler()
File "/usr/local/lib/python2.7/dist-packages/cherrypy/lib/encoding.py", line 188, in __call__
self.body = self.oldhandler(*args, **kwargs)
File "/usr/local/lib/python2.7/dist-packages/cherrypy/_cpdispatch.py", line 34, in __call__
return self.callable(*self.args, **self.kwargs)
File "/home/ilexx/Downloads/PGObserver/frontend/src/tablesfrontend.py", line 132, in index
top_tables = tabledata.getTopTables(None, date_from, date_to, order, limit)
File "/home/ilexx/Downloads/PGObserver/frontend/src/tabledata.py", line 388, in getTopTables
pattern = '%' + pattern + '%' 
TypeError: cannot concatenate 'str' and 'NoneType' objects

Are there some configurations of postgresql that i am missing?
Could you please give me some more advice.
So far, thanks...

[kmoppel]

Hi,

Thanks for the feedback! The Python error was indeed a bug, I fixed it now in master (b0140d4).

About the other issues - all those "Perf. *" screens not showing data is probably OK, as they're only meant to show possible problems as sudden rise in sequential scans etc, which might not be the case.

For sprocs info not being shown - you should 1st check the "sproc_schemas_monitoring_configuration" table and adjust it if needed. Here is decided from which schemas information is actually gathered. By default for example the "public" schema is left out (as using it is not a good practice).

For Pg_Stat_Statements report - there the gatherer probably is by default not instructed to collect this data (you could check the monitor_data.stat_statements_data table) as out of box Postgres doesn't have this information. You can fix this from "/hosts" screen by updating the Setting column key "statStatementsGatherInterval" to some > 0 value.

[oemergenc]

Hey,

thanks for you quick reply. You are right, i missed the configuration of statStatementsGatherInterval for the Pg_Stat_Statements report and already configured that accordingly later on. It works now as expected.

Regarding the "Top Tables" page bug, i checked out your update. And this also works now. Thanks for that.

Another problem i had when using you vagrant configuration is with the Dockerfile of the gatherer. The Dockerfile of the gatherer tries to add pgobserver-gatherer-1.0.4-jar-with-dependencies.jar, while it mus be pgobserver-gatherer-1.0.5-jar-with-dependencies.jar, note the different version number.

Nice work that you put into this project.

[ErikvdVen]

Having a similar problem over here. SELECT * FROM pg_stat_statements; does return rows, but the monitor_data.stat_database_data table stays empty, and so is the /perfstatstatements page. I already set the "statStatementsGatherInterval": 5, setting on the /hosts table.

@kmoppel Any ideas?

These are my postgresql.conf settings:

shared_preload_libraries = 'pg_stat_statements'         # (change requires restart)

pg_stat_statements.max = 10000
pg_stat_statements.track = all

[slitsche]

Hi Erik,
If I understand you right you expect data in stat_database_data? Then you should set statDatabaseGatherInterval to a value bigger than 0. statStatementsGatherInterval indeed gathers only pg_stat_statements. For this it is required to have zz_utils.get_stat_statements installed on the monitored cluster..

[ErikvdVen]

My bad, I meant stat_statements_data, I copy/pasted the wrong table name.

Anyways, thank you for your response. I double checked the get_stat_statements.sql and I figured out it didn't do anything after executing it, even though it displayed a success message.
The problem was, the extension still wasn't enabled on the database, so after executing the query CREATE EXTENSION pg_stat_statements; and executing the .sql file again, it worked 👍 Thanks again.

[kmoppel]

Thanks for the feedback again @ErikvdVen ! Btw, if you see that something could be improved in the "setup" section of the readme file, feel free to create a PR :) Closing the issue for now.