search

powa-team / powa-web

PoWA user interface
http://powa.readthedocs.io/
73 stars 31 forks source link

No applicative objects/queries shown #231

Open ptikf opened 1 week ago

ptikf commented 1 week ago

In my current configuration (remote server, postgresql 16),

but: no applicative object displayed (messages: "no data found" on web) no query on applicative object displayed (and no plans of course) In query using system view, all paramaters replaced by bind variables ( $1, $2 and so).

Where can I ibegin nvestigations ?

rjuju commented 1 week ago

Hi,

I'm sorry I don't really understand what your problems are. Could you send a screenshot with the problematic behavior, or the relevant part of the powa-web url (like /server/x/overview on the tab XXX)?

ptikf commented 1 week ago

Hello, this is a screenshot of my question :

powa_q1

rjuju commented 1 week ago

Thanks, I understand now!

no applicative object displayed (messages: "no data found" on web)

The support for the "Database objects" datasource is not available for postgres 16 and above in powa 4.x. It has been rewritten using a different approach and works with all versions with powa 5.0. The other tabs should work though.

no query on applicative object displayed (and no plans of course)

was there any activity on the chosen database for that remove server in the given interval? I do see some user tables (timescaledb_information.hypertables", and there seems to be queries done by pgadmin4 (the pga4dash query at least), so there is definitely activity going on on that database.

In query using system view, all paramaters replaced by bind variables ( $1, $2 and so).

That's pg_stat_statements behavior, which is the source of information for query activity in powa. It normalises all the queries so that the query text doesn't have any actual parameter, and all statistics are aggregated on that normalized query. You can refer to https://www.postgresql.org/docs/15/pgstatstatements.html for more details about it.

ptikf commented 1 week ago

Hello, screen capture for queries: (db under test, some queries run, one table 4 billion rows): powa_q2

rjuju commented 1 week ago

pg_qualstats could be used to put some values on the parameters in the join and where clauses (but not in the select part of the statement), but it doesn't mean that we could associate specific metrics to those values. pg_stat_statements accumulates planning time, execution time and the rest of the counters to a single normalised entry, so the detail per specific parameter is never stored anywhere.

do you have a specific performance problem you're trying to solve?

ptikf commented 1 week ago

Yes during that period, I ran a query (select count(*) from 4 billion rows table) The peak appears on the screen capture above (io read peak), but nt the query itself. It ran for 2m30s but doesnt appears in the list of queries ? why ?

pgiraud commented 1 week ago

If I'm not wrong, a query will not show in the table if it ran only once. Queries must have happened at least twice (and in different powa snapshot) to be taken into account.

rjuju commented 1 week ago

indeed, this is documented at https://powa.readthedocs.io/en/latest/FAQ.html#some-queries-don-t-show-up-in-the-ui