1""" 2Global optimization widget 3""" 4from __future__ import absolute_import 5from powa.framework import AuthHandler 6from powa.dashboards import ( 7 Widget, MetricGroupDef) 8 9from powa.sql import (resolve_quals, get_any_sample_query, 10 get_hypoplans, HypoIndex) 11import json 12from powa.sql.compat import JSONB 13from powa.sql.views import qualstat_getstatdata 14from powa.sql.tables import powa_databases 15from sqlalchemy.sql import (bindparam, literal_column, join, select, 16 alias, text, func, column, cast) 17from sqlalchemy.types import TEXT 18from sqlalchemy.exc import DBAPIError 19from tornado.web import HTTPError 20 21 22class IndexSuggestionHandler(AuthHandler): 23 24 def post(self, srvid, database): 25 try: 26 # Check remote access first 27 remote_conn = self.connect(srvid, database=database, 28 remote_access=True) 29 except Exception as e: 30 raise HTTPError(501, "Could not connect to remote server: %s" % 31 str(e)) 32 33 payload = json.loads(self.request.body.decode("utf8")) 34 from_date = payload['from_date'] 35 to_date = payload['to_date'] 36 indexes = [] 37 for ind in payload['indexes']: 38 hypoind = HypoIndex(ind['nspname'], 39 ind['relname'], 40 ind['ams']) 41 hypoind._ddl = ind['ddl'] 42 indexes.append(hypoind) 43 queryids = payload['queryids'] 44 powa_conn = self.connect(database="powa") 45 queries = list(powa_conn.execute(text(""" 46 SELECT DISTINCT query, ps.queryid 47 FROM powa_statements ps 48 WHERE srvid = :srvid 49 AND queryid IN :queryids 50 """), srvid=srvid, queryids=tuple(queryids))) 51 # Create all possible indexes for this qual 52 hypo_version = self.has_extension_version(srvid, "hypopg", 53 database=database) 54 hypoplans = {} 55 indbyname = {} 56 inderrors = {} 57 if hypo_version and hypo_version >= "0.0.3": 58 # identify indexes 59 # create them 60 for ind in indexes: 61 try: 62 indname = remote_conn.execute( 63 select(["*"]) 64 .select_from(func.hypopg_create_index(ind.ddl)) 65 ).first()[1] 66 indbyname[indname] = ind 67 except DBAPIError as e: 68 inderrors[ind.ddl] = str(e.orig) 69 continue 70 except Exception: 71 # TODO handle other errors? 72 continue 73 # Build the query and fetch the plans 74 for query in queries: 75 querystr = get_any_sample_query(self, srvid, database, 76 query.queryid, 77 from_date, 78 to_date) 79 if querystr: 80 try: 81 hypoplans[query.queryid] = get_hypoplans( 82 remote_conn, querystr, indbyname.values()) 83 except Exception: 84 # TODO: stop ignoring the error 85 continue 86 # To value of a link is the the reduction in cost 87 result = {} 88 result["plans"] = hypoplans 89 result["inderrors"] = inderrors 90 self.render_json(result) 91 92 93class WizardMetricGroup(MetricGroupDef): 94 """Metric group for the wizard.""" 95 name = "wizard" 96 xaxis = "quals" 97 axis_type = "category" 98 data_url = r"/server/(\d+)/metrics/database/([^\/]+)/wizard/" 99 enabled = False 100 101 @property 102 def query(self): 103 pq = qualstat_getstatdata(bindparam("server"), 104 column("eval_type") == "f") 105 base = alias(pq) 106 query = (select([ 107 # queryid in pg11+ is int64, so the value can exceed javascript's 108 # Number.MAX_SAFE_INTEGER, which mean that the value can get 109 # truncated by the browser, leading to looking for unexisting 110 # queryid when processing this data. To avoid that, simply cast 111 # the value to text. 112 func.array_agg(cast(column("queryid"), TEXT)).label("queryids"), 113 column("qualid"), 114 cast(column("quals"), JSONB).label('quals'), 115 column("occurences"), 116 column("execution_count"), 117 func.array_agg(column("query")).label("queries"), 118 column("avg_filter"), 119 column("filter_ratio") 120 ]).select_from( 121 join(base, powa_databases, 122 onclause=( 123 powa_databases.c.oid == literal_column("dbid") and 124 powa_databases.c.srvid == literal_column("srvid") 125 ))) 126 .where(powa_databases.c.datname == bindparam("database")) 127 .where(powa_databases.c.srvid == bindparam("server")) 128 .where(column("avg_filter") > 1000) 129 .where(column("filter_ratio") > 0.3) 130 .group_by(column("qualid"), column("execution_count"), 131 column("occurences"), 132 cast(column("quals"), JSONB), 133 column("avg_filter"), column("filter_ratio")) 134 .order_by(column("occurences").desc()) 135 .limit(200)) 136 return query 137 138 def post_process(self, data, server, database, **kwargs): 139 conn = self.connect(server, database=database, remote_access=True) 140 data["data"] = resolve_quals(conn, data["data"]) 141 return data 142 143 144class Wizard(Widget): 145 146 def __init__(self, title): 147 self.title = title 148 149 def parameterized_json(self, handler, **parms): 150 values = self.__dict__.copy() 151 values['metrics'] = [] 152 values['type'] = 'wizard' 153 values['datasource'] = 'wizard' 154 155 # First check that we can connect on the remote server, otherwise we 156 # won't be able to do anything 157 try: 158 remote_conn = handler.connect(parms["server"], 159 database=parms["database"], 160 remote_access=True) 161 except Exception as e: 162 values['has_remote_conn'] = False 163 values['conn_error'] = str(e) 164 return values 165 166 values['has_remote_conn'] = True 167 168 hypover = handler.has_extension_version(parms["server"], 169 "hypopg", 170 database=parms["database"]) 171 qsver = handler.has_extension_version(parms["server"], "pg_qualstats") 172 values['has_hypopg'] = hypover and hypover >= '0.0.3' 173 values['has_qualstats'] = qsver and qsver >= '0.0.7' 174 values['server'] = parms["server"] 175 values['database'] = parms["database"] 176 return values 177