search

powa-team / powa

PostgreSQL Workload Analyzer
http://powa.readthedocs.io/
PostgreSQL License
768 stars 57 forks source link

'No Data' showing for all databases #74

Closed ashishdchauhan closed 8 years ago

ashishdchauhan commented 8 years ago

POWA tool showing ‘No Data’ for all our databases, I tried to Drop extension and recreate it but same ‘No Data’ issue. Could you pls help?

rjuju commented 8 years ago

Hello,

We need some more details:

ashishdchauhan commented 8 years ago

Hi,

Thanks, Ashish

From: Julien Rouhaud [mailto:notifications@github.com] Sent: Tuesday, May 03, 2016 12:50 AM, 0:50 To: dalibo/powa Cc: Ashish Chauhan; Author Subject: Re: [dalibo/powa] 'No Data' showing for all databases (#74)

Hello,

We need some more details:

— You are receiving this because you authored the thread. Reply to this email directly or view it on GitHubhttps://github.com/dalibo/powa/issues/74#issuecomment-216461033

rjuju commented 8 years ago

Unfortunately, it looks like the screenshots were lost during transfer. Could you copy paste the results instead?

Also, could you provide the results of the following queries (on the powa database):

ashishdchauhan commented 8 years ago

Attached excel sheet for query results.

Thanks, Ashish

From: Julien Rouhaud [mailto:notifications@github.com] Sent: Tuesday, May 03, 2016 10:18 AM, 10:18 To: dalibo/powa Cc: Ashish Chauhan; Author Subject: Re: [dalibo/powa] 'No Data' showing for all databases (#74)

Unfortunately, it look like the screenshots were lost during transfer. Could you copy paste the results instead?

Also, could you provide the results of the following queries (on the powa database):

— You are receiving this because you authored the thread. Reply to this email directly or view it on GitHubhttps://github.com/dalibo/powa/issues/74#issuecomment-216601048

rjuju commented 8 years ago

Same issue, attachments are lost when replying on github.

Can you forward the two previous mails to powa (AT) dalibo.com ?

ashishdchauhan commented 8 years ago

POWA_Result.xlsx POWA_Result.xlsx

see sheet1 and sheet2 for results

rjuju commented 8 years ago

Ok, so it looks like all the powa tables are empty.

Can you provide the result of following queries:

ashishdchauhan commented 8 years ago

select current_setting('shared_preload_libraries'); Result : "pg_stat_statements,pg_stat_kcache,pg_qualstats,powa"

select name, setting from pg_settings where name ilike '%powa%'; Result: already provided in excel sheet, sheet1

rjuju commented 8 years ago

Ok. So powa is loaded, and default settings are used, but no data is present.

Do you see any error in the postgresql log files? Do you see a process on the server called something like "postgres: bgworker: powa"?

ashishdchauhan commented 8 years ago

i don't see any error in postgres logs neither process "postgres: bgworker: powa" running on server.

rjuju commented 8 years ago

If the bgworker isn't running, that explains why you don't see any data.

With powa 3.0.1, if you kill the bgworker with a SIGTERM, it won't relaunch automatically and won't display any log about exiting. This behavior has been fixed and some other diagnostics enhancement have been committed since, and will be available in the release 3.0.2.

In the mean time, I'm afraid your only choice is to restart postgres so the powa bgworker can restart. You should also try to find if the bgworker have been sigterm killed or not.

ashishdchauhan commented 8 years ago

its our production server, i cannot restart Postgres. Is there way i can start bgworker without restarting postgres?

rjuju commented 8 years ago

Unfortunately no, it's not possible.

If you can't plan a restart of postgres soon, what you can do is call the snapshot function from a cron job, which will do exactly the same thing as the bgworker. You just need to remember to remove this cron when postgres will have been restarted.

As postgres user, you can add a cron like this:

*/5 *   * * *   psql -p PORT -h HOST -c "SELECT powa_take_snapshot()" powa

modifying PORT and HOST so psql can connect to your production server, and you'll have a snapshot every 5 minutes.

ashishdchauhan commented 8 years ago

i executed query directly on powa database but same "no data" issue on powa tool.

postgres=# \c powa You are now connected to database "powa" as user "postgres". powa=# SELECT powa_take_snapshot();

powa_take_snapshot

(1 row)

rjuju commented 8 years ago

powa works by comparing snapshots, so the ui will start showing data after 2 snapshots.

ashishdchauhan commented 8 years ago

cron running fine, still i don't see data.

rjuju commented 8 years ago

What does the previous queries report now that some snapshots have been taken?

ashishdchauhan commented 8 years ago

i don't think cronjob is working correctly, i dont see any changes in data.

powa=# SELECT count(*) FROM pg_stat_statements ;

count

4264 (1 row)

powa=# SELECT count(*) FROM powa_statements;

count

 0

(1 row)

powa=# select date_trunc('day', upper(coalesce_range)), count(*) from powa_statements_history group by 1 order by 1; date_trunc | count ------------+------- (0 rows)

powa=# select date_trunc('day', (record).ts) from powa_statements_history_current group by 1 order by 1;

date_trunc

(0 rows)

powa=#

rjuju commented 8 years ago

What's the result of this query, again on powa database:

SELECT * FROM powa_functions;
ashishdchauhan commented 8 years ago

powa=# SELECT * FROM powa_functions; module | operation | function_name | added_manually | enabled --------------------------+------------+-------------------------------+----------------+--------- pg_stat_statements | snapshot | powa_statements_snapshot | f | t powa_stat_user_functions | snapshot | powa_user_functions_snapshot | f | t powa_stat_all_relations | snapshot | powa_all_relations_snapshot | f | t pg_stat_statements | aggregate | powa_statements_aggregate | f | t powa_stat_user_functions | aggregate | powa_user_functions_aggregate | f | t powa_stat_all_relations | aggregate | powa_all_relations_aggregate | f | t pg_stat_statements | purge | powa_statements_purge | f | t powa_stat_user_functions | purge | powa_user_functions_purge | f | t powa_stat_all_relations | purge | powa_all_relations_purge | f | t pg_stat_statements | reset | powa_statements_reset | f | t powa_stat_user_functions | reset | powa_user_functions_reset | f | t powa_stat_all_relations | reset | powa_all_relations_reset | f | t pg_qualstats | snapshot | powa_qualstats_snapshot | f | t pg_qualstats | aggregate | powa_qualstats_aggregate | f | t pg_qualstats | unregister | powa_qualstats_unregister | f | t pg_qualstats | purge | powa_qualstats_purge | f | t pg_qualstats | reset | powa_qualstats_reset | f | t pg_stat_kcache | snapshot | powa_kcache_snapshot | f | t pg_stat_kcache | aggregate | powa_kcache_aggregate | f | t pg_stat_kcache | unregister | powa_kcache_unregister | f | t pg_stat_kcache | purge | powa_kcache_purge | f | t pg_stat_kcache | reset | powa_kcache_reset | f | t (22 rows)

rjuju commented 8 years ago

That's really weird.

Can you give the return of these queries, on powa database:

SELECT count(*) FROM pg_stat_statements;
WITH capture AS(
        SELECT pgss.*
        FROM pg_stat_statements pgss
        JOIN pg_roles r ON pgss.userid = r.oid
    )
SELECT queryid, dbid, userid, query
FROM capture c
WHERE NOT EXISTS (SELECT 1
    FROM powa_statements ps
    WHERE ps.queryid = c.queryid
    AND ps.dbid = c.dbid
    AND ps.userid = c.userid
);
ashishdchauhan commented 8 years ago

SELECT count(*) FROM pg_stat_statements; Result: 4298 -- i see this number is increasing gradually.

Query 2 Result: 4243 rows --i see data in queryid, dbid, userid but query column is empty, i dont see any query text

rjuju commented 8 years ago

Query field is empty?

What does this query returns?

select case when query is null then 'empty' else 'not empty' end, count(*) from pg_stat_statements group by 1;

Can you also check the size of the file $PGDATA/pg_stat_tmp/pgss_query_texts.stat?

ashishdchauhan commented 8 years ago

i meant query column not showing any queries, empty column.

powa=# select case when query is null then 'empty' else 'not empty' end, count(*) from pg_stat_statements group by 1; case | count -------+------- empty | 4268 (1 row)

pg_query_texts.stat file size - 3,9 GB

rjuju commented 8 years ago

Ok. I don't know what happened on your server, but this file is most certainly corrupted.

Did you have any crash or problem on your server? You should also check the logs, system wide to make sure you don't have any hardware issue.

Anyway, reseting pg_stat_statements should solve your issue:

SELECT pg_stat_statements_reset();
ashishdchauhan commented 8 years ago

FINALLY!!! its working, thanks a ton for your help!

do you think still i need to run cron job every 5 mins or should i disable it?

rjuju commented 8 years ago

Good news :)

About the cron job, unfortunately you have to keep it until the bgworker is present, which mean next database restart.

ashishdchauhan commented 8 years ago

Awesome! Again thanks a lot for all your help!

minh10huy commented 7 years ago

@ashishdchauhan I am not fortunate like you, I still catch that error. Anyway to solve that problem

rjuju commented 7 years ago

Hello @minh10huy

Since the root cause of your issue is most likely different from this issue, could you open a new one?

Also, if you can provide results of the first commands I asked at the beginning of this thread that'd help a lot. Thanks!