search

powa-team / powa

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

Button "Optimize this database !" does nothing #62

Closed ribbles closed 5 years ago

ribbles commented 8 years ago

v3.0.0

Clicking "Optimize this database !" does nothing. I see that it makes an AJAX call to | /metrics/database/dfw_container_one/wizard/?from=2015-12-15+10%3A14%3A04-0800&to=2015-12-15+11%3A14%3A04-0800

which returns json (that has a few missing indexes reported) and the following error inthe browser console:

Uncaught TypeError: Cannot read property 'get' of undefined
o.extend.qualupdate @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
h @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
t.Events.trigger @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
n.extend._onModelEvent @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
h @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
t.Events.trigger @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
n.extend.set @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
o.extend.initialize @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
t.Model @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
n.has.i @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
n.has.i @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
(anonymous function) @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
(anonymous function) @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
d.each.d.forEach @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
e.Model.extend.update @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
h @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
t.Events.trigger @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
(anonymous function) @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
f @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
l.fireWith @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
S @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
(anonymous function) @ powa.min-all.js?v=04bd9c774fb3d323cae95ee9eafbedde:1
rjuju commented 8 years ago

Can you execute the following query on the powa database when the problem is occuring, and post the resulting results ?

You need to change the MY_DATABASE_NAME value. Also, you may need to change the now() - interval '1 hour' and now() parameters according to the time interval you chose when the problem happens.

SELECT array_agg(queryid) AS queryids, qualid, CAST(quals AS JSONB) AS quals, occurences, execution_count, array_agg(query) AS queries, avg_filter, filter_ratio 
FROM (SELECT qualid, powa_statements.queryid AS queryid, query, powa_statements.dbid AS dbid, to_json(quals) AS quals, sum(execution_count) AS execution_count, sum(occurences) AS occurences, sum(nbfiltered) / sum(occurences) AS avg_filter, CASE WHEN (sum(execution_count) = 0) THEN 0 ELSE (sum(nbfiltered) / CAST(sum(execution_count) AS NUMERIC)) *100 END AS filter_ratio 
FROM 
    (
    SELECT queryid, qualid, (unnested.records).*
    FROM (
        SELECT pqnh.qualid, pqnh.queryid, pqnh.dbid, pqnh.userid, pqnh.coalesce_range, unnest(records) as records
        FROM powa_qualstats_quals_history pqnh
        WHERE coalesce_range  && tstzrange(now() - interval '1 hour', now(), '[]')
    ) AS unnested
    WHERE tstzrange(now() - interval '1 hour', now(), '[]') @> (records).ts
    UNION ALL
    SELECT queryid, qualid, pqnc.ts, pqnc.occurences, pqnc.execution_count, pqnc.nbfiltered
    FROM powa_qualstats_quals_history_current pqnc
    WHERE tstzrange(now() - interval '1 hour', now(), '[]') @> pqnc.ts
    ) h
    JOIN powa_qualstats_quals pqnh USING (queryid, qualid)
     JOIN powa_statements ON powa_statements.queryid = pqnh.queryid GROUP BY qualid, powa_statements.queryid, powa_statements.dbid, powa_statements.query, quals) AS anon_1 JOIN pg_database ON pg_database.oid = dbid 
WHERE pg_database.datname = 'MY_DATABASE_NAME' AND avg_filter > 1000 AND filter_ratio > 0.3 GROUP BY qualid, execution_count, occurences, CAST(quals AS JSONB), avg_filter, filter_ratio ORDER BY occurences DESC 
 LIMIT 200
;
ribbles commented 8 years ago

It doesn't return any rows. If I remove the database WHERE clause, I see just one row for the powa database.

rdunklau commented 8 years ago

Just to be sure, do you have anything in powa_qualstats_quals and powa_qualstats_quals_history for your database ?

Can you check using the following queries:

select * from powa_qualstats_quals join pg_database on pg_database.oid = powa_qualstats_quals.dbid where datname = 'dfw_container_one' LIMIT 10;

select * from powa_qualstats_quals_history join pg_database on pg_database.oid = powa_qualstats_quals_history.dbid where datname = 'dfw_container_one' LIMIT 10;

What version of the extensions are you using ? Can you check in the config section (http://yourserver/config, also accessible from the top-left menu, "configuration overview" entry).

ribbles commented 8 years ago

query 1:

8321215 4052715015  68519850    68518785    {"(69067255,1,98,i)"}   dfw_container_one   68518785    6   en_US.UTF-8 en_US.UTF-8 false   true    -1  12170   714 1   1663    {=Tc/dfw_container_one_owner,dfw_container_one_owner=CTc/dfw_container_one_owner,dfw_container_one_login=c/dfw_container_one_owner}
42063431    2224877072  68519850    68518785    {"(69204574,1,98,i)"}   dfw_container_one   68518785    6   en_US.UTF-8 en_US.UTF-8 false   true    -1  12170   714 1   1663    {=Tc/dfw_container_one_owner,dfw_container_one_owner=CTc/dfw_container_one_owner,dfw_container_one_login=c/dfw_container_one_owner}
53428646    2778559591  68519850    68518785    {"(69247800,1,98,i)"}   dfw_container_one   68518785    6   en_US.UTF-8 en_US.UTF-8 false   true    -1  12170   714 1   1663    {=Tc/dfw_container_one_owner,dfw_container_one_owner=CTc/dfw_container_one_owner,dfw_container_one_login=c/dfw_container_one_owner}
59732238    2145094160  68519850    68518785    {"(69058596,1,98,i)"}   dfw_container_one   68518785    6   en_US.UTF-8 en_US.UTF-8 false   true    -1  12170   714 1   1663    {=Tc/dfw_container_one_owner,dfw_container_one_owner=CTc/dfw_container_one_owner,dfw_container_one_login=c/dfw_container_one_owner}
127762846   42592397    68519850    68518785    {"(69254631,1,98,i)"}   dfw_container_one   68518785    6   en_US.UTF-8 en_US.UTF-8 false   true    -1  12170   714 1   1663    {=Tc/dfw_container_one_owner,dfw_container_one_owner=CTc/dfw_container_one_owner,dfw_container_one_login=c/dfw_container_one_owner}
151383829   344254580   68519850    68518785    {"(69093158,1,98,i)"}   dfw_container_one   68518785    6   en_US.UTF-8 en_US.UTF-8 false   true    -1  12170   714 1   1663    {=Tc/dfw_container_one_owner,dfw_container_one_owner=CTc/dfw_container_one_owner,dfw_container_one_login=c/dfw_container_one_owner}
168235091   2462452619  68519850    68518785    {"(69229189,1,98,i)"}   dfw_container_one   68518785    6   en_US.UTF-8 en_US.UTF-8 false   true    -1  12170   714 1   1663    {=Tc/dfw_container_one_owner,dfw_container_one_owner=CTc/dfw_container_one_owner,dfw_container_one_login=c/dfw_container_one_owner}
169422922   968421040   68519850    68518785    {"(69218696,1,98,i)"}   dfw_container_one   68518785    6   en_US.UTF-8 en_US.UTF-8 false   true    -1  12170   714 1   1663    {=Tc/dfw_container_one_owner,dfw_container_one_owner=CTc/dfw_container_one_owner,dfw_container_one_login=c/dfw_container_one_owner}
178210374   826230220   68519850    68518785    {"(69162742,1,98,i)"}   dfw_container_one   68518785    6   en_US.UTF-8 en_US.UTF-8 false   true    -1  12170   714 1   1663    {=Tc/dfw_container_one_owner,dfw_container_one_owner=CTc/dfw_container_one_owner,dfw_container_one_login=c/dfw_container_one_owner}
194677505   1339630693  68519850    68518785    {"(69244625,1,98,i)"}   dfw_container_one   68518785    6   en_US.UTF-8 en_US.UTF-8 false   true    -1  12170   714 1   1663    {=Tc/dfw_container_one_owner,dfw_container_one_owner=CTc/dfw_container_one_owner,dfw_container_one_login=c/dfw_container_one_owner}

query 2: no rows

config:

rdunklau commented 8 years ago

The fact that there is nothing is a bit strange, this is something that should be investigated. Are you using the default settings for pg_qualstats-related settings ?

ribbles commented 8 years ago

Yes

rdunklau commented 8 years ago

By default, PoWA only keeps the last 7days worth of data. Has your database seen any trafic in the last 7 days ?

rdica commented 8 years ago

Same issue, except query 2 returns data. same version of everything.

SonicGD commented 7 years ago

Hello, have same issue. Both queries returns data. query to /metrics/database/cg2/wizard/ returns 40 objects in data. Exception throws on Wizard.js:52

this.set("relname", this.get("quals").models[0].get("relname"));

I set breakpoint and it hits several times without exception. When exception happens this.get("quals").models is empty array.

I can provide more info for debug if needed.

rjuju commented 7 years ago

Hello

That's really strange. Which browser and version are you using? Can you reliably reproduce this issue?

At this breakpointn, does this.get("quals") returns a Backbone.collection object? And does newquals.collection.models[0].get("relname") also empty?

SonicGD commented 7 years ago

Hello, @rjuju

Which browser and version are you using?

Opera 42. Chrome 54. Also i tried Firefox, but don't remember which version.

Can you reliably reproduce this issue?

Yes. On both "production" and "debug" version of powa-web. First i face this issue with production version, using powa-web docker container. Then i build my own, with debug version, directly from github.

At this breakpointn, does this.get("quals") returns a Backbone.collection object?

Yes. For both cases - when it works and when it throws exception.

And does newquals.collection.models[0].get("relname") also empty?

Yes, this code fails with TypeError: Cannot read property 'models' of undefined

rdunklau commented 7 years ago

Hello,

Could you provide us with the contents of this.get("quals") at the beginning of the method call ? I suspect the quals being considered don't support any access methods, thus they end up being removed from the collection.

Thanks !

SonicGD commented 7 years ago

I did this

 console.log(this.get("quals").clone());

before

this.trashQuals();

and receive this: image

json.stringify for this object:

[
    {
        "relid": "24477",
        "eval_type": "f",
        "nspname": "public",
        "most_common_values": "{...some_data...}",
        "amops": {},
        "attnum": 4,
        "example_values": [],
        "label": "Projects.site ~~* ?",
        "relname": "Projects",
        "null_frac": 0,
        "opname": "~~*",
        "attname": "site",
        "n_distinct": -0.969246
    }
]
rdunklau commented 7 years ago

Hello.

This commit should fix it; https://github.com/dalibo/powa-web/commit/0e7a9fb73d10b16c3e01499f408902948cd2ad3b

Can you test that ?

Thank you for the debugging !

SonicGD commented 7 years ago

Ok, the first error is gone. Now there is another:

GridView.js?[object Object]:93 Uncaught TypeError: Cannot read property 'replace' of undefined
    at child.render (GridView.js?[object Object]:93)
    at child.render (backgrid.js?[object Object]:1954)
    at child.insertRow (backgrid.js?[object Object]:2366)
    at triggerEvents (backbone.js?[object Object]:209)
    at child.trigger (backbone.js?[object Object]:148)
    at child._onModelEvent (backbone.js?[object Object]:946)
    at triggerEvents (backbone.js?[object Object]:210)
    at Backbone.Model.trigger (backbone.js?[object Object]:149)
    at child.set (backbone.js?[object Object]:749)
    at child.update (Wizard.js?[object Object]:389)

GridView.js?[object Object]:93:

 value = raw_value.replace(/^\s+/g,"").replace(/\n\s+/, "\n");
SonicGD commented 7 years ago

Any news on this?

rjuju commented 7 years ago

Sorry again for the delay. Can you put a breakpoint on wizard.js:389 and show what's in result[1]?

SonicGD commented 7 years ago

No problem. It's array with one undefined element:

image

Here is json - http://pastebin.com/NYy0kEMD

rjuju commented 7 years ago

Yes, this undefined element is the problem. I'd prefer to understand where it comes from rather than pushing a fix to blindly remove such elements.

Do you have a way to provide a self contained test case? Otherwise, can you do an export of "nodes" var, in wizard;js:388?

SonicGD commented 7 years ago

I clean it up a little... - http://pastebin.com/SS8mcb8Y

SonicGD commented 7 years ago

Hey guys, any work on this issue?)

SonicGD commented 7 years ago

Ok, i think this issue is fixes in 3.1.1

rjuju commented 5 years ago

Sorry, I'm terrible at keeping track of issues :( I assume this was indeed fixed in 3.1.1, feel free to reopen this issue if it wasn't.