search

powa-team / powa-web

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

Index suggestion button still not "visilbe" #145

Closed jcujhc closed 2 years ago

jcujhc commented 2 years ago

Hi,

I have problem with Index suggestion button, similar issue I found here here

I installed powa-web on separate server and powa extension locally for every postgres cluster here is example: every postgres cluster have this shared_preload_libraries shared_preload_libraries = 'pg_stat_statements,auto_explain,passwordcheck,auth_delay,powa,pg_stat_kcache,pg_qualstats,repmgr'

we have about 80 virtual servers and on each there is one postgres cluster running on default port, Every server I registered in /etc/powa-web.conf file but for none I see "Index suggestion" button for database, but pg_qualstats extension seems collecting data. here are my checks for one postges cluster

` powa=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+----------------------------------------------------------- btree_gist | 1.5 | public | support for indexing common datatypes in GiST hypopg | 1.1.4 | public | Hypothetical indexes for PostgreSQL pg_qualstats | 2.0.2 | public | An extension collecting statistics about quals pg_stat_kcache | 2.2.0 | public | Kernel statistics gathering pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language powa | 4.1.2 | public | PostgreSQL Workload Analyser-core (7 rows)

bear_prod_data=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+----------------------------------------------------------- pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows)

powa=# \dx+ public.powa* showed me 163 rows

SELECT DISTINCT d.datname, q.queryid, s.query FROM powa_qualstats_quals_history_current q JOIN pg_database d on d.oid = q.dbid JOIN pg_stat_statements s on s.queryid = q.queryid and s.dbid = q.dbid and s.userid = q.userid WHERE ts >= now() - interval '1 hour';

bear_proddata | -6864120122665887324 | select osversionc0.id as id113, osversionc0_.client_id as client_i213, osversionc0_.headerline as headerli313, osversion c0_.min_client_version as min_clie413, osversionc0_.min_os_version as min_os_v513, osversionc0_.textbody as textbody613 from osversioncheck osversionc0 where os versionc0_.client_id=$1 bear_proddata | -5835213759761989196 | select ssotargete0.target_id as target_i1_180, ssotargete0_.display_options as display_2_180, ssotargete0_.target_url as t arget_u3_180 from ssotarget ssotargete0 where ssotargete0_.target_id=$1 bear_proddata | -3786320278718656516 | select businesspr0.id as id17, businesspr0_.business_process as business27, businesspr0_.display as display37, businessp r0_.order_name as order_na47 from ecare_businessprocessmapping businesspr0 where businesspr0_.business_process=$1 bear_proddata | -3466650395608050309 | select limitation0.rule_id as rule_id111, limitation0_.anonymous as anonymou211, limitation0_.client_id as client_i311, limitation0_.method as method411, limitation0_.request_limit_count as request_511, limitation0_.request_limit_timespan as request_611, limitation0_.service as ser vice711 from limitationrule limitation0 where (limitation0_.clientid=$1 or limitation0.clientid=$5) and (limitation0.service=$2 or limitation0.service=$6) and (limitation0.method=$3 or limitation0.method=$7) and limitation0.anonymous=$4 bear_proddata | 800526856053629543 | select maintenanc0.id as id112, maintenanc0_.blocked_area_id as blocked_212, maintenanc0_.client_id as client_i312, main tenanc0_.client_version as client_v412, maintenanc0_.enabled as enabled512, maintenanc0_.end_time as end_time612, maintenanc0_.heading as heading712, maintenanc 0_.start_time as start_ti812, maintenanc0_.text as text912 from maintenance maintenanc0 where (maintenanc0.clientid=$6 or maintenanc0.clientid=$1 and (maintena nc0.clientversion=$2 or maintenanc0.clientversion=$7)) and maintenanc0.enabled=$8 and ((maintenanc0_.starttime is null) and (maintenanc0.endtime is null) or (ma intenanc0.endtime is null) and $3>maintenanc0.starttime or (maintenanc0.starttime is null) and $4<maintenanc0.endtime or $5 between maintenanc0.starttime and maintenanc0.endtime) order by maintenanc0.end_time desc nulls first bear_proddata | 3936196832880420086 | select dwhporders0.id as id16, dwhporders0_.orders_version as orders_v26, dwhporders0_.state as state36, dwhporders0_.st ate_category as state_ca46, dwhporders0_.status as status56 from dwhp_orderstatusmapping dwhporders0 where dwhporders0_.ordersversion=$1 and dwhporders0.status= $2 bear_proddata | 5474394877855405675 | select clientappl0.client_id as client_i1_10, clientappl0_.client_versions as client_v2_10, clientappl0_.enabled as enable d3_10, clientappl0_.login_required as login_re4_10, clientappl0_.permitted_services as permitte5_10 from clientapplications clientappl0 where clientappl0_.clien t_id=$1 bear_proddata | 9013984491707605861 | select versionche0.id as id122, versionche0_.client_id as client_i222, versionche0_.client_version as client_v322, versi onche0_.client_version_upto as client_v422, versionche0_.headline as headline522, versionche0_.information_mode as informat622, versionche0_.os_version as os_vers i722, versionche0_.os_version_from as os_versi822, versionche0_.textbody as textbody922 from versioncheck versionche0 where versionche0_.client_id=$1 (8 rows)

select * from pg_qualstats_all limit 10;

powa=# select * from pg_qualstats_all limit 10; dbid | relid | userid | queryid | attnums | opno | qualid | occurences | execution_count | nbfiltered | qualnodeid -------+-----------+--------+---------------------+---------+------+------------+------------+-----------------+------------+------------ 16479 | 278223691 | 16481 | 5474394877855405675 | {1} | 98 | | 1 | 1 | 0 | 2789835144 16479 | 278223691 | 16481 | 5474394877855405675 | {1} | 98 | | 14 | 14 | 0 | 3487122411 16479 | 278223703 | 16481 | 800526856053629543 | {2} | 98 | | 79 | 3397 | 2054 | 254178150 16479 | 278223703 | 16481 | 800526856053629543 | {3} | 98 | | 79 | 3397 | 2054 | 1132030798 16479 | 278223703 | 16481 | 800526856053629543 | {2} | 98 | 1921737337 | 79 | 3397 | 2054 | 1921737337 16479 | 278223703 | 16481 | 800526856053629543 | {3} | 98 | | 79 | 3397 | 2054 | 3549450963 16479 | 278223703 | 16481 | 800526856053629543 | {9} | 2062 | 3069703144 | 79 | 3397 | 2054 | 3069703144 16479 | 278223703 | 16481 | 800526856053629543 | {9} | 2063 | 181609157 | 79 | 3397 | 2054 | 181609157 16479 | 278223703 | 16481 | 800526856053629543 | {8} | 2064 | 1444511762 | 79 | 3397 | 2054 | 1444511762 16479 | 278223703 | 16481 | 800526856053629543 | {8} | 2065 | 181609157 | 79 | 3397 | 2054 | 181609157 (10 rows)

powa=# select srvid,module,operation,function_name,query_source from powa_functions; srvid | module | operation | function_name | query_source -------+--------------------------+------------+-------------------------------+------------------------- 0 | pg_stat_statements | snapshot | powa_databases_snapshot | powa_databases_src 0 | pg_stat_statements | snapshot | powa_statements_snapshot | powa_statements_src 0 | powa_stat_user_functions | snapshot | powa_user_functions_snapshot | powa_user_functions_src 0 | powa_stat_all_relations | snapshot | powa_all_relations_snapshot | powa_all_relations_src 0 | pg_stat_bgwriter | snapshot | powa_stat_bgwriter_snapshot | powa_stat_bgwriter_src 0 | pg_stat_statements | aggregate | powa_statements_aggregate | 0 | powa_stat_user_functions | aggregate | powa_user_functions_aggregate | 0 | powa_stat_all_relations | aggregate | powa_all_relations_aggregate | 0 | pg_stat_bgwriter | aggregate | powa_stat_bgwriter_aggregate | 0 | pg_stat_statements | purge | powa_statements_purge | 0 | pg_stat_statements | purge | powa_databases_purge | 0 | powa_stat_user_functions | purge | powa_user_functions_purge | 0 | powa_stat_all_relations | purge | powa_all_relations_purge | 0 | pg_stat_bgwriter | purge | powa_stat_bgwriter_purge | 0 | pg_stat_statements | reset | powa_statements_reset | 0 | powa_stat_user_functions | reset | powa_user_functions_reset | 0 | powa_stat_all_relations | reset | powa_all_relations_reset | 0 | pg_stat_bgwriter | reset | powa_stat_bgwriter_reset | 0 | pg_qualstats | snapshot | powa_qualstats_snapshot | powa_qualstats_src 0 | pg_qualstats | aggregate | powa_qualstats_aggregate | 0 | pg_qualstats | unregister | powa_qualstats_unregister | 0 | pg_qualstats | purge | powa_qualstats_purge | 0 | pg_qualstats | reset | powa_qualstats_reset | 0 | pg_stat_kcache | snapshot | powa_kcache_snapshot | powa_kcache_src 0 | pg_stat_kcache | aggregate | powa_kcache_aggregate | 0 | pg_stat_kcache | unregister | powa_kcache_unregister | 0 | pg_stat_kcache | purge | powa_kcache_purge | 0 | pg_stat_kcache | reset | powa_kcache_reset | (28 rows)

powa=# SET powa.debug = on; SET powa=# SELECT powa_take_snapshot(0); WARNING: start of powa_take_snapshot(0) WARNING: coalesce_seq(0): 29214 WARNING: calling snapshot function: powa_databases_snapshot WARNING: running powa_databases_snapshot WARNING: Maintaining database list... WARNING: missing db: 0 WARNING: renamed db: 0 WARNING: dropped db: 0 WARNING: calling snapshot function: powa_statements_snapshot WARNING: running powa_statements_snapshot WARNING: powa_statements_snapshot - rowcount: 962 WARNING: calling snapshot function: powa_kcache_snapshot WARNING: running powa_kcache_snapshot WARNING: powa_kcache_snapshot - rowcount: 428 WARNING: calling snapshot function: powa_qualstats_snapshot WARNING: running powa_qualstats_snapshot WARNING: powa_qualstats_snapshot - rowcount: 14 WARNING: calling snapshot function: powa_stat_bgwriter_snapshot WARNING: running powa_stat_bgwriter_snapshot WARNING: powa_stat_bgwriter_snapshot - rowcount: 1 WARNING: calling snapshot function: powa_all_relations_snapshot WARNING: running powa_all_relations_snapshot WARNING: powa_all_relations_snapshot - rowcount: 1824 WARNING: calling snapshot function: powa_user_functions_snapshot WARNING: running powa_user_functions_snapshot WARNING: powa_user_functions_snapshot - rowcount: 64 WARNING: end of powa_take_snapshot(0) powa_take_snapshot

              0

(1 row)

powa=# SELECT * FROM powa_qualstats_src(0); ts | uniquequalnodeid | dbid | userid | qualnodeid | occurences | execution_count | nbfiltered | mean_err_estimate_ratio | mean_err_estimat e_num | queryid | constvalues | quals -------------------------------+------------------+-------+--------+------------+------------+-----------------+------------+-------------------------+----------------- ------+---------------------+---------------+------------------------------------------------- 2021-08-18 10:45:10.770226+02 | 3069703144 | 16479 | 16481 | 3069703144 | 9 | 387 | 234 | 1.7 | 7 | 800526856053629543 | {NULL} | {"(278223703,9,2062,f)"} 2021-08-18 10:45:10.770226+02 | 1444511762 | 16479 | 16481 | 1444511762 | 9 | 387 | 234 | 1.7 | 7 | 800526856053629543 | {NULL} | {"(278223703,8,2064,f)"} 2021-08-18 10:45:10.770226+02 | 1196756784 | 16479 | 16481 | 3549450963 | 9 | 387 | 234 | 1.7 | 7 | 800526856053629543 | {'ANY'::text} | {"(278223703,3,98,f)"} 2021-08-18 10:45:10.770226+02 | 1132030798 | 16479 | 16481 | 1132030798 | 9 | 387 | 234 | 1.7 | 7 | 800526856053629543 | {NULL} | {"(278223703,3,98,f)"} 2021-08-18 10:45:10.770226+02 | 266730487 | 16479 | 16481 | 1921737337 | 9 | 387 | 234 | 1.7 | 7 | 800526856053629543 | {NULL} | {"(278223703,2,98,f)"} 2021-08-18 10:45:10.770226+02 | 181609157 | 16479 | 16481 | 181609157 | 9 | 387 | 234 | 1.7 | 7 | 800526856053629543 | {NULL,NULL} | {"(278223703,9,2063,f)","(278223703,8,2065,f)"} 2021-08-18 10:45:10.770226+02 | 120733548 | 16479 | 16481 | 254178150 | 9 | 387 | 234 | 1.7 | 7 | 800526856053629543 | {'ANY'::text} | {"(278223703,2,98,f)"} 2021-08-18 10:45:10.770226+02 | 3487122411 | 16479 | 16481 | 3487122411 | 1 | 1 | 0 | 0 | 0 | 5474394877855405675 | {NULL} | {"(278223691,1,98,i)"} (8 rows)

powa=# SELECT * powa-# FROM powa_qualstats_src(0) q powa-# WHERE EXISTS (SELECT 1 powa(# FROM powa_statements s powa(# WHERE s.srvid = 0 powa(# AND q.queryid = s.queryid powa(# AND q.dbid = s.dbid powa(# AND q.userid = s.userid); ts | uniquequalnodeid | dbid | userid | qualnodeid | occurences | execution_count | nbfiltered | mean_err_estimate_ratio | mean_err_estimat e_num | queryid | constvalues | quals -------------------------------+------------------+-------+--------+------------+------------+-----------------+------------+-------------------------+----------------- ------+----------------------+-------------------------------------------+------------------------------------------------- 2021-08-18 10:46:48.498234+02 | 3069703144 | 16479 | 16481 | 3069703144 | 4 | 172 | 104 | 1.7 | 7 | 800526856053629543 | {NULL} | {"(278223703,9,2062,f)"} 2021-08-18 10:46:48.498234+02 | 1444511762 | 16479 | 16481 | 1444511762 | 4 | 172 | 104 | 1.7 | 7 | 800526856053629543 | {NULL} | {"(278223703,8,2064,f)"} 2021-08-18 10:46:48.498234+02 | 1196756784 | 16479 | 16481 | 3549450963 | 4 | 172 | 104 | 1.7 | 7 | 800526856053629543 | {'ANY'::text} | {"(278223703,3,98,f)"} 2021-08-18 10:46:48.498234+02 | 1132030798 | 16479 | 16481 | 1132030798 | 4 | 172 | 104 | 1.7 | 7 | 800526856053629543 | {NULL} | {"(278223703,3,98,f)"} 2021-08-18 10:46:48.498234+02 | 266730487 | 16479 | 16481 | 1921737337 | 4 | 172 | 104 | 1.7 | 7 | 800526856053629543 | {NULL} | {"(278223703,2,98,f)"} 2021-08-18 10:46:48.498234+02 | 181609157 | 16479 | 16481 | 181609157 | 4 | 172 | 104 | 1.7 | 7 | 800526856053629543 | {NULL,NULL} | {"(278223703,8,2065,f)","(278223703,9,2063,f)"} 2021-08-18 10:46:48.498234+02 | 120733548 | 16479 | 16481 | 254178150 | 4 | 172 | 104 | 1.7 | 7 | 800526856053629543 | {'ANY'::text} | {"(278223703,2,98,f)"} 2021-08-18 10:46:48.498234+02 | 3506710520 | 16479 | 16481 | 1210766864 | 1 | 133 | 132 | 0 | 0 | -5835213759761989196 | {'mf.post.phx.contract.renew.post'::text} | {"(278223734,1,98,f)"} (8 rows)

powa=# SELECT * FROM pg_stat_statements WHERE dbid = 16479 AND userid = 16481 AND queryid = 800526856053629543;

userid | dbid | queryid |

                                                                                                                                   query

                                                                 |   calls    |    total_time     | min_time | max_time |     mean_time     |     stddev_time      |
rows     | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_writt

en | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time --------+-------+--------------------+----------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------+------------+-------------------+----------+----------+-------------------+----------------------+ -------------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+----------------- ---+----------------+-------------------+---------------+---------------- 16481 | 16479 | 800526856053629543 | select maintenanc0_.id as id112, maintenanc0_.blocked_area_id as blocked_212, maintenanc0_.client_id as client_i312, mainte nanc0_.client_version as client_v412, maintenanc0_.enabled as enabled512, maintenanc0_.end_time as end_time612, maintenanc0_.heading as heading712, maintenanc0_ .start_time as start_ti812, maintenanc0_.text as text912 from maintenance maintenanc0 where (maintenanc0.clientid=$6 or maintenanc0.clientid=$1 and (maintenanc 0.clientversion=$2 or maintenanc0.clientversion=$7)) and maintenanc0.enabled=$8 and ((maintenanc0_.starttime is null) and (maintenanc0.endtime is null) or (main tenanc0.endtime is null) and $3>maintenanc0.starttime or (maintenanc0.starttime is null) and $4<maintenanc0.endtime or $5 between maintenanc0.starttime and ma intenanc0.endtime) order by maintenanc0.end_time desc nulls first | 4011102911 | 178983983.8848132 | 0.027586 | 6.522913 | 0.044622137066703 | 0.009361030179416401 | 51329749617 | 4024921879 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.007569 | 0 (1 row)

powa=# SELECT * FROM powa_statements WHERE dbid = 16479 AND userid = 16481 AND queryid = 800526856053629543 and srvid = 0; srvid | queryid | dbid | userid |

                                                                                                                                           query

                                                                         |        last_present_ts

-------+--------------------+-------+--------+--------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------+------------------------------- 0 | 800526856053629543 | 16479 | 16481 | select maintenanc0_.id as id112, maintenanc0_.blocked_area_id as blocked_212, maintenanc0_.client_id as client_i312 , maintenanc0_.client_version as client_v412, maintenanc0_.enabled as enabled512, maintenanc0_.end_time as end_time612, maintenanc0_.heading as heading712, main tenanc0_.start_time as start_ti812, maintenanc0_.text as text912 from maintenance maintenanc0 where (maintenanc0.clientid=$6 or maintenanc0.clientid=$1 and (ma intenanc0.clientversion=$2 or maintenanc0.clientversion=$7)) and maintenanc0.enabled=$8 and ((maintenanc0_.starttime is null) and (maintenanc0.endtime is null) or (maintenanc0.endtime is null) and $3>maintenanc0.starttime or (maintenanc0.starttime is null) and $4<maintenanc0.endtime or $5 between maintenanc0.starttim e and maintenanc0.endtime) order by maintenanc0.end_time desc nulls first | 2021-08-18 10:46:30.237041+02 (1 row) `

Should I change something here ? (srvid != "0" and database is None)

/usr/lib/python3.6/site-packages/powa/framework.py

    # For remote server, check first if powa-collector reported a version
    # for that extension, but only for default database.
    if (srvid != 0 and database is None):
        try:
            remver = self.execute(text(
                """

powa=# show pg_qualstats.sample_rate powa-# ; pg_qualstats.sample_rate

0.000621504 (1 row)

I also changed pg_qualstats.sample_rate but didn't help.

powa=# set pg_qualstats.sample_rate to 1; SET powa=# show pg_qualstats.sample_rate powa-# ; pg_qualstats.sample_rate

1 (1 row)

powa_index_missing_button.docx

Could you please help, Am I doing something wrong ? regards Jozef

rjuju commented 2 years ago

Hi,

So looking at the .docx document, the UI is reporting "please enable support for pg_qualstats..."

Looking at the other screenshots, it seems that you're using powa-web version 4.1.0. I'm not sure if the server is srvid = 0 or another one, but both versions 4.1.1 and 4.1.2 fixed issues with extension version detection. Given that there seems to be all the required data sampled by powa, I suspect that updating powa-web to version 4.1.2 would fix the problem.

Can you try to update the UI, or did you face any problem that prevented you from using a version after 4.1.0?

jcujhc commented 2 years ago

Hi rjuju, thanks for reply, "please enable support for pg_qualstats..." - I was thinking that this is already enabled on monitored postgres cluster in powa database, see my installed extensions. there is version 2.0.2 on another server i upgraded it to 2.0.3 but still not working. Should I enable support but how (?), because in docu I see just only create extensions in powa database is enough. (except hypopg and pg_stat_statements there should be created in "every" db)

powa=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+----------------------------------------------------------- btree_gist | 1.5 | public | support for indexing common datatypes in GiST hypopg | 1.1.4 | public | Hypothetical indexes for PostgreSQL pg_qualstats | 2.0.2 | public | An extension collecting statistics about quals pg_stat_kcache | 2.2.0 | public | Kernel statistics gathering pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language powa | 4.1.2 | public | PostgreSQL Workload Analyser-core (7 rows)

"but both versions 4.1.1 and 4.1.2 fixed issues with extension version detection" "Can you try to update the UI" yes. If you can help me, because when I checked powa_12-eb package there is installed version 4.1.2-1

[root@XXXX ~]# yum --showduplicates list powa* | expand | grep 12 powa_12.x86_64 4.1.2-1.rhel7 @pg_12 powa_12-web.x86_64 4.1.2-1.rhel7 @pg_12 powa_12.x86_64 3.2.0-2.rhel7.1 pg_12 powa_12.x86_64 4.0.1-1.rhel7 pg_12 powa_12.x86_64 4.0.1-2.rhel7 pg_12 powa_12.x86_64 4.1.2-1.rhel7 pg_12 powa_12-web.x86_64 3.2.0-2.rhel7.1 pg_12 powa_12-web.x86_64 4.0.1-1.rhel7 pg_12 powa_12-web.x86_64 4.0.1-2.rhel7 pg_12 powa_12-web.x86_64 4.1.2-1.rhel7 pg_12 [root@XXXX ~]# yum list installed | grep powa powa_12.x86_64 4.1.2-1.rhel7 @pg_12 powa_12-web.x86_64 4.1.2-1.rhel7 @pg_12

my powa was installed from default postgeres 12 repo. running powa as nohup command, but don't know why in GUI I see "Version 4.1.0" nohup powa-web --config=/etc/powa-web_NONPROD.conf >> /var/log/powa/powa-web_NONPROD.log &

Should I download some new "fixed" version from github ? (if yes please paste the link) sorry for my stupid questions. :( Thnaks Jozef

rjuju commented 2 years ago

"please enable support for pg_qualstats..." - I was thinking that this is already enabled on monitored postgres cluster in powa database, see my installed extensions. there is version 2.0.2 on another server i upgraded it to 2.0.3 but still not working.

Yes, as I mentioned it all looks properly configured. But there were bugs in older powa-web version that prevented to properly find the extension version, so the UI thought that pg_qualstats was not installed.

running powa as nohup command, but don't know why in GUI I see "Version 4.1.0"

There was a packaging problem in older versions where the powa-web version was the same as the powa-archivist version, even though they should be different. Devrim fixed it recently, see https://github.com/powa-team/powa-web/issues/138#issuecomment-870038385

I'm not sure why you don't see this 4.1.2-3 version of the RPM. Can you try to update your repo?

jcujhc commented 2 years ago

repo updated, new version of powa_12-web (4.1.2-3.rhel7) installed now, working Powa_update.docx ! you are doing awesome work! Many, many Thanks! jozef

rjuju commented 2 years ago

Excellent news, and thanks a lot!