Exemple #1
0
 def get(self, database, query, qual):
     stmt = qualstat_getstatdata()
     c = inner_cc(stmt)
     stmt = stmt.alias()
     stmt = (stmt.select().where((c.qualid == bindparam("qualid")) & (
         c.queryid == bindparam("query"))).where(stmt.c.count > 0).column(
             (c.queryid == bindparam("query")).label("is_my_query")))
     quals = list(
         self.execute(stmt,
                      params={
                          "query": query,
                          "from": self.get_argument("from"),
                          "to": self.get_argument("to"),
                          "qualid": qual
                      }))
     quals = resolve_quals(self.connect(database=database), quals)
     my_qual = None
     other_queries = {}
     for qual in quals:
         if qual['is_my_query']:
             my_qual = qual
         else:
             other_queries[qual['queryid']] = qual['query']
     if my_qual is None:
         self.render("xhr.html", content="nodata")
         return
     self.render("database/query/qualdetail.html",
                 qual=my_qual,
                 database=database,
                 other_queries=other_queries)
Exemple #2
0
 def get(self, database, query, qual):
     stmt = qualstat_getstatdata()
     c = inner_cc(stmt)
     stmt = stmt.alias()
     stmt = (stmt.select()
         .where((c.qualid == bindparam("qualid")))
         .where(stmt.c.occurences > 0)
         .column((c.queryid == bindparam("query")).label("is_my_query")))
     quals = list(self.execute(
         stmt,
         params={"query": query,
                 "from": self.get_argument("from"),
                 "to": self.get_argument("to"),
                 "qualid": qual}))
     my_qual = None
     other_queries = {}
     for qual in quals:
         if qual['is_my_query']:
             my_qual = resolve_quals(self.connect(database=database),
                                     [qual])[0]
         else:
             other_queries[qual['queryid']] = qual['query']
     if my_qual is None:
         self.render("xhr.html", content="nodata")
         return
     self.render("database/query/qualdetail.html",
                 qual=my_qual,
                 database=database,
                 other_queries=other_queries)
Exemple #3
0
 def query(self):
     query = (qual_constants("most_used",
                             text("""
         datname = :database AND
         s.queryid = :query AND
         qn.qualid = :qual AND
         coalesce_range && tstzrange(:from, :to)"""), top=10))
     base = qualstat_getstatdata()
     c = inner_cc(base)
     base = base.where(c.queryid == bindparam("query")).alias()
     totals = (base.select()
               .where((c.qualid == bindparam("qual")) &
                      (c.queryid == bindparam("query")))).alias()
     return (query.alias().select()
             .column(totals.c.occurences.label('total_occurences'))
             .correlate(query))
Exemple #4
0
 def query(self):
     query = (qual_constants("most_used",
                             text("""
         datname = :database AND
         s.queryid = :query AND
         qn.qualid = :qual AND
         coalesce_range && tstzrange(:from, :to)"""),
                             top=10))
     base = qualstat_getstatdata()
     c = inner_cc(base)
     base = base.where(c.queryid == bindparam("query")).alias()
     totals = (
         base.select().where((c.qualid == bindparam("qual"))
                             & (c.queryid == bindparam("query")))).alias()
     return (query.alias().select().column(
         totals.c.occurences.label('total_occurences')).correlate(query))
Exemple #5
0
    def get(self, database, query):
        if not self.has_extension("pg_qualstats"):
            raise HTTPError(501, "PG qualstats is not installed")
        base_query = qualstat_getstatdata()
        c = inner_cc(base_query)
        base_query.append_from(text("""LATERAL unnest(quals) as qual"""))
        base_query = (base_query.where(c.queryid == query).having(
            func.bool_or(column('eval_type') == 'f')).having(
                c.execution_count > 1000).having(c.occurences > 0).having(
                    c.filter_ratio > 0.5).params(**{
                        'from': '-infinity',
                        'to': 'infinity'
                    }))
        optimizable = list(self.execute(base_query, params={'query': query}))
        optimizable = resolve_quals(self.connect(database=database),
                                    optimizable, 'quals')
        hypoplan = None
        indexes = {}
        for qual in optimizable:
            indexes[qual.where_clause] = possible_indexes(qual)
        hypo_version = self.has_extension("hypopg", database=database)
        if indexes and hypo_version and hypo_version >= "0.0.3":
            # identify indexes
            # create them
            allindexes = [
                ind for indcollection in indexes.values()
                for ind in indcollection
            ]
            for ind in allindexes:
                ddl = ind.hypo_ddl
                if ddl is not None:
                    ind.name = self.execute(ddl, database=database).scalar()
            # Build the query and fetch the plans
            querystr = get_any_sample_query(self, database, query,
                                            self.get_argument("from"),
                                            self.get_argument("to"))
            try:
                hypoplan = get_hypoplans(self.connect(database=database),
                                         querystr, allindexes)
            except:
                # TODO: offer the possibility to fill in parameters from the UI
                self.flash("We couldn't get plans for this query, presumably "
                           "because some parameters are missing ")

        self.render("database/query/indexes.html",
                    indexes=indexes,
                    hypoplan=hypoplan)
Exemple #6
0
    def get(self, database, query):
        if not self.has_extension("pg_qualstats"):
            raise HTTPError(501, "PG qualstats is not installed")
        base_query = qualstat_getstatdata()
        c = inner_cc(base_query)
        base_query.append_from(text("""LATERAL unnest(quals) as qual"""))
        base_query = (base_query
                      .where(c.queryid == query)
                      .having(func.bool_or(column('eval_type') == 'f'))
                      .having(c.execution_count > 1000)
                      .having(c.occurences > 0)
                      .having(c.filter_ratio > 0.5)
                      .params(**{'from': '-infinity',
                                 'to': 'infinity'}))
        optimizable = list(self.execute(base_query, params={'query': query}))
        optimizable = resolve_quals(self.connect(database=database),
                                    optimizable,
                                    'quals')
        hypoplan = None
        indexes = {}
        for qual in optimizable:
            indexes[qual.where_clause] = possible_indexes(qual)
        hypo_version = self.has_extension("hypopg", database=database)
        if hypo_version and hypo_version >= "0.0.3":
            # identify indexes
            # create them
            allindexes = [ind for indcollection in indexes.values()
                          for ind in indcollection]
            for ind in allindexes:
                ddl = ind.hypo_ddl
                if ddl is not None:
                    ind.name = self.execute(ddl, database=database).scalar()
            # Build the query and fetch the plans
            querystr = get_any_sample_query(self, database, query,
                                        self.get_argument("from"),
                                        self.get_argument("to"))
            try:
                hypoplan = get_hypoplans(self.connect(database=database), querystr,
                                         allindexes)
            except:
                # TODO: offer the possibility to fill in parameters from the UI
                self.flash("We couldn't get plans for this query, presumably "
                           "because some parameters are missing ")

        self.render("database/query/indexes.html", indexes=indexes,
                    hypoplan=hypoplan)
Exemple #7
0
    def get(self, server, database, query, qual):
        try:
            # Check remote access first
            remote_conn = self.connect(server,
                                       database=database,
                                       remote_access=True)
        except Exception as e:
            raise HTTPError(501,
                            "Could not connect to remote server: %s" % str(e))
        stmt = qualstat_getstatdata(server)
        c = inner_cc(stmt)
        stmt = stmt.alias()
        stmt = (stmt.select().where((c.qualid == bindparam("qualid"))).where(
            stmt.c.occurences > 0).column(
                (c.queryid == bindparam("query")).label("is_my_query")))
        quals = list(
            self.execute(stmt,
                         params={
                             "server": server,
                             "query": query,
                             "from": self.get_argument("from"),
                             "to": self.get_argument("to"),
                             "queryids": [query],
                             "qualid": qual
                         }))

        my_qual = None
        other_queries = {}

        for qual in quals:
            if qual['is_my_query']:
                my_qual = resolve_quals(remote_conn, [qual])[0]

        if my_qual is None:
            self.render("xhr.html", content="No data")
            return

        self.render("database/query/qualdetail.html",
                    qual=my_qual,
                    database=database,
                    server=server)
Exemple #8
0
 def get(self, database, query):
     if not self.has_extension("pg_qualstats"):
         raise HTTPError(501, "PG qualstats is not installed")
     base_query = qualstat_getstatdata()
     c = inner_cc(base_query)
     base_query.append_from(text("""LATERAL unnest(quals) as qual"""))
     base_query = (base_query
                   .where(c.queryid == query)
                   .having(func.bool_or(column('eval_type') == 'f'))
                   .having(c.count > 1000)
                   .having(c.filter_ratio > 0.5)
                   .params(**{'from': '-infinity',
                              'to': 'infinity'}))
     optimizable = list(self.execute(base_query, params={'query': query}))
     optimizable = resolve_quals(self.connect(database=database),
                                 optimizable,
                                 'quals')
     qual_indexes = {}
     for line in optimizable:
         qual_indexes[line['where_clause']] = possible_indexes(
             line['quals'])
     self.render("database/query/indexes.html", indexes=qual_indexes)
Exemple #9
0
 def get(self, database, query):
     if not self.has_extension("pg_qualstats"):
         raise HTTPError(501, "PG qualstats is not installed")
     base_query = qualstat_getstatdata()
     c = inner_cc(base_query)
     base_query.append_from(text("""LATERAL unnest(quals) as qual"""))
     base_query = (base_query
                   .where(c.queryid == query)
                   .having(func.bool_or(column('eval_type') == 'f'))
                   .having(c.count > 1000)
                   .having(c.filter_ratio > 0.5)
                   .params(**{'from': '-infinity',
                              'to': 'infinity'}))
     optimizable = list(self.execute(base_query, params={'query': query}))
     optimizable = resolve_quals(self.connect(database=database),
                                 optimizable,
                                 'quals')
     qual_indexes = {}
     for line in optimizable:
         qual_indexes[line['where_clause']] = possible_indexes(
             line['quals'])
     self.render("database/query/indexes.html", indexes=qual_indexes)
Exemple #10
0
    def query(self):
        # Fetch the base query for sample, and filter them on the database
        bs = block_size.c.block_size
        subquery = powa_getstatdata_sample("db", bindparam("server"))
        # Put the where clause inside the subquery
        subquery = subquery.where(column("datname") == bindparam("database"))
        query = subquery.alias()
        c = query.c

        cols = [
            c.srvid,
            to_epoch(c.ts),
            (sum(c.calls) /
             greatest(extract("epoch", c.mesure_interval), 1)).label("calls"),
            (sum(c.runtime) / greatest(sum(c.calls), 1.)).label("avg_runtime"),
            (sum(c.runtime) /
             greatest(extract("epoch", c.mesure_interval), 1)).label("load"),
            total_read(c),
            total_hit(c)
        ]

        from_clause = query
        if self.has_extension(self.path_args[0], "pg_stat_kcache"):
            # Add system metrics from pg_stat_kcache,
            kcache_query = kcache_getstatdata_sample("db")
            kc = inner_cc(kcache_query)
            kcache_query = (kcache_query.where(
                (kc.srvid == bindparam("server"))
                & (kc.datname == bindparam("database"))).alias())
            kc = kcache_query.c

            def sum_per_sec(col):
                ts = extract("epoch", greatest(c.mesure_interval, '1 second'))
                return (sum(col) / ts).label(col.name)

            total_sys_hit = (total_read(c) - sum(kc.reads) /
                             greatest(extract("epoch", c.mesure_interval), 1.)
                             ).label("total_sys_hit")
            total_disk_read = (sum(kc.reads) /
                               greatest(extract("epoch", c.mesure_interval),
                                        1.)).label("total_disk_read")
            minflts = sum_per_sec(kc.minflts)
            majflts = sum_per_sec(kc.majflts)
            # nswaps = sum_per_sec(kc.nswaps)
            # msgsnds = sum_per_sec(kc.msgsnds)
            # msgrcvs = sum_per_sec(kc.msgrcvs)
            # nsignals = sum_per_sec(kc.nsignals)
            nvcsws = sum_per_sec(kc.nvcsws)
            nivcsws = sum_per_sec(kc.nivcsws)

            cols.extend([
                total_sys_hit,
                total_disk_read,
                minflts,
                majflts,
                # nswaps, msgsnds, msgrcvs, nsignals,
                nvcsws,
                nivcsws
            ])
            from_clause = from_clause.join(kcache_query,
                                           kcache_query.c.ts == c.ts)

        return (select(cols).select_from(from_clause).where(
            c.calls is not None).group_by(c.srvid, c.ts, bs,
                                          c.mesure_interval).order_by(
                                              c.ts).params(samples=100))
Exemple #11
0
    def query(self):
        query = powa_getstatdata_sample("query")
        query = query.where(
            (column("datname") == bindparam("database")) &
            (column("queryid") == bindparam("query")))
        query = query.alias()
        c = query.c
        total_blocks = ((c.shared_blks_read + c.shared_blks_hit)
                        .label("total_blocks"))

        def bps(col):
            ts = extract("epoch", greatest(c.mesure_interval, '1 second'))
            return (mulblock(col) / ts).label(col.name)
        cols = [to_epoch(c.ts),
                c.rows,
                c.calls,
                case([(total_blocks == 0, 0)],
                     else_=cast(c.shared_blks_hit, Numeric) * 100 /
                     total_blocks).label("hit_ratio"),
                bps(c.shared_blks_read),
                bps(c.shared_blks_hit),
                bps(c.shared_blks_dirtied),
                bps(c.shared_blks_written),
                bps(c.local_blks_read),
                bps(c.local_blks_hit),
                bps(c.local_blks_dirtied),
                bps(c.local_blks_written),
                bps(c.temp_blks_read),
                bps(c.temp_blks_written),
                c.blk_read_time,
                c.blk_write_time,
                (c.runtime / greatest(c.calls, 1)).label("avg_runtime")]

        from_clause = query
        if self.has_extension("pg_stat_kcache"):
            # Add system metrics from pg_stat_kcache,
            # and detailed hit ratio.
            kcache_query = kcache_getstatdata_sample()
            kc = inner_cc(kcache_query)
            kcache_query = (
                kcache_query
                .where(kc.queryid == bindparam("query"))
                .alias())
            kc = kcache_query.c
            sys_hits = (greatest(mulblock(c.shared_blks_read) -
                                 kc.reads, 0)
                        .label("kcache_hitblocks"))
            sys_hitratio = (cast(sys_hits, Numeric) * 100 /
                            mulblock(total_blocks))
            disk_hit_ratio = (kc.reads /
                              mulblock(total_blocks))
            total_time = greatest(c.runtime, 1);
            # Rusage can return values > real time due to sampling bias
            # aligned to kernel ticks. As such, we have to clamp values to 100%
            total_time_percent = lambda x: least(100, (x * 100) /
                                                 total_time)
            cols.extend([
                kc.reads,
                kc.writes,
                total_time_percent(kc.user_time * 1000).label("user_time"),
                total_time_percent(kc.system_time * 1000).label("system_time"),
                greatest(total_time_percent(
                    c.runtime - (kc.user_time + kc.system_time) *
                    1000), 0).label("other_time"),
                case([(total_blocks == 0, 0)],
                     else_=disk_hit_ratio).label("disk_hit_ratio"),
                case([(total_blocks == 0, 0)],
                     else_=sys_hitratio).label("sys_hit_ratio")])
            from_clause = from_clause.join(
                kcache_query,
                kcache_query.c.ts == c.ts)
        else:
            cols.extend([
                case([(total_blocks == 0, 0)],
                     else_=cast(c.shared_blks_read, Numeric) * 100 /
                     total_blocks).label("miss_ratio")
            ])

        return (select(cols)
                .select_from(from_clause)
                .where(c.calls != None)
                .order_by(c.ts)
                .params(samples=100))
Exemple #12
0
 def query(self):
     base = qualstat_getstatdata()
     c = inner_cc(base)
     return (base.where(c.queryid == bindparam("query")))
Exemple #13
0
    def query(self):
        query = powa_getstatdata_sample("query", bindparam("server"))
        query = query.where((column("datname") == bindparam("database"))
                            & (column("queryid") == bindparam("query")))
        query = query.alias()
        c = query.c
        total_blocks = ((sum(c.shared_blks_read) +
                         sum(c.shared_blks_hit)).label("total_blocks"))

        def get_ts():
            return extract("epoch", greatest(c.mesure_interval, '1 second'))

        def sumps(col):
            return (sum(col) / get_ts()).label(col.name)

        def bps(col):
            return (mulblock(sum(col)) / get_ts()).label(col.name)

        cols = [
            to_epoch(c.ts),
            sumps(c.rows),
            sumps(c.calls),
            case([(total_blocks == 0, 0)],
                 else_=cast(sum(c.shared_blks_hit), Numeric) * 100 /
                 total_blocks).label("hit_ratio"),
            bps(c.shared_blks_read),
            bps(c.shared_blks_hit),
            bps(c.shared_blks_dirtied),
            bps(c.shared_blks_written),
            bps(c.local_blks_read),
            bps(c.local_blks_hit),
            bps(c.local_blks_dirtied),
            bps(c.local_blks_written),
            bps(c.temp_blks_read),
            bps(c.temp_blks_written),
            sumps(c.blk_read_time),
            sumps(c.blk_write_time),
            (sum(c.runtime) / greatest(sum(c.calls), 1)).label("avg_runtime")
        ]

        from_clause = query
        if self.has_extension(self.path_args[0], "pg_stat_kcache"):
            # Add system metrics from pg_stat_kcache,
            # and detailed hit ratio.
            kcache_query = kcache_getstatdata_sample("query")
            kc = inner_cc(kcache_query)
            kcache_query = (kcache_query.where(
                (kc.srvid == bindparam("server"))
                & (kc.datname == bindparam("database"))
                & (kc.queryid == bindparam("query"))).alias())
            kc = kcache_query.c
            sys_hits = (greatest(
                mulblock(sum(c.shared_blks_read)) - sum(kc.reads),
                0).label("kcache_hitblocks"))
            sys_hitratio = (cast(sys_hits, Numeric) * 100 /
                            mulblock(total_blocks))
            disk_hit_ratio = (sum(kc.reads) * 100 / mulblock(total_blocks))
            total_time = greatest(sum(c.runtime), 1)
            # Rusage can return values > real time due to sampling bias
            # aligned to kernel ticks. As such, we have to clamp values to 100%
            total_time_percent = lambda x: least(100, (x * 100) / total_time)

            def per_sec(col):
                ts = extract("epoch", greatest(c.mesure_interval, '1 second'))
                return (sum(col) / ts).label(col.name)

            cols.extend([
                per_sec(kc.reads),
                per_sec(kc.writes),
                per_sec(kc.minflts),
                per_sec(kc.majflts),
                # per_sec(kc.nswaps),
                # per_sec(kc.msgsnds),
                # per_sec(kc.msgrcvs),
                # per_sec(kc.nsignals),
                per_sec(kc.nvcsws),
                per_sec(kc.nivcsws),
                total_time_percent(sum(kc.user_time) *
                                   1000).label("user_time"),
                total_time_percent(sum(kc.system_time) *
                                   1000).label("system_time"),
                greatest(
                    total_time_percent(
                        sum(c.runtime) -
                        ((sum(kc.user_time) + sum(kc.system_time)) * 1000)),
                    0).label("other_time"),
                case([(total_blocks == 0, 0)],
                     else_=disk_hit_ratio).label("disk_hit_ratio"),
                case([(total_blocks == 0, 0)],
                     else_=sys_hitratio).label("sys_hit_ratio")
            ])
            from_clause = from_clause.join(
                kcache_query,
                and_(kcache_query.c.ts == c.ts,
                     kcache_query.c.queryid == c.queryid,
                     kcache_query.c.userid == c.userid,
                     kcache_query.c.dbid == c.dbid))
        else:
            cols.extend([
                case([(total_blocks == 0, 0)],
                     else_=cast(sum(c.shared_blks_read), Numeric) * 100 /
                     total_blocks).label("miss_ratio")
            ])

        return (select(cols).select_from(from_clause).where(
            c.calls != '0').group_by(c.ts, block_size.c.block_size,
                                     c.mesure_interval).order_by(
                                         c.ts).params(samples=100))
Exemple #14
0
    def query(self):
        bs = block_size.c.block_size
        query = powa_getstatdata_sample("db", bindparam("server"))
        query = query.alias()
        c = query.c

        cols = [c.srvid,
                extract("epoch", c.ts).label("ts"),
                (sum(c.calls) / greatest(extract("epoch", c.mesure_interval),
                                         1)).label("calls"),
                (sum(c.runtime) / greatest(sum(c.calls),
                                           1)).label("avg_runtime"),
                (sum(c.runtime) / greatest(extract("epoch", c.mesure_interval),
                                           1)).label("load"),
                total_read(c),
                total_hit(c)
                ]

        if self.has_extension_version(self.path_args[0],
                                      'pg_stat_statements', '1.8'):
            cols.extend([
                (sum(c.plantime) / greatest(extract("epoch", c.mesure_interval),
                                            1)).label("planload"),
                (sum(c.wal_records) / greatest(extract("epoch",
                                                       c.mesure_interval),
                                               1)).label("wal_records"),
                (sum(c.wal_fpi) / greatest(extract("epoch",
                                                   c.mesure_interval),
                                           1)).label("wal_fpi"),
                (sum(c.wal_bytes) / greatest(extract("epoch",
                                                     c.mesure_interval),
                                             1)).label("wal_bytes")
                ])

        from_clause = query
        if self.has_extension(self.path_args[0], "pg_stat_kcache"):
            # Add system metrics from pg_stat_kcache,
            kcache_query = kcache_getstatdata_sample("db")
            kc = inner_cc(kcache_query)
            kcache_query = (
                kcache_query
                .where(
                    (kc.srvid == bindparam("server"))
                    )
                .alias())
            kc = kcache_query.c

            def sum_per_sec(col):
                ts = extract("epoch", greatest(c.mesure_interval, '1 second'))
                return (sum(col) / ts).label(col.name)

            total_sys_hit = (total_read(c) - sum(kc.reads) /
                             greatest(extract("epoch", c.mesure_interval), 1.)
                             ).label("total_sys_hit")
            total_disk_read = (sum(kc.reads) /
                               greatest(extract("epoch", c.mesure_interval), 1.)
                               ).label("total_disk_read")
            minflts = sum_per_sec(kc.minflts)
            majflts = sum_per_sec(kc.majflts)
            # nswaps = sum_per_sec(kc.nswaps)
            # msgsnds = sum_per_sec(kc.msgsnds)
            # msgrcvs = sum_per_sec(kc.msgrcvs)
            # nsignals = sum_per_sec(kc.nsignals)
            nvcsws = sum_per_sec(kc.nvcsws)
            nivcsws = sum_per_sec(kc.nivcsws)

            cols.extend([total_sys_hit, total_disk_read, minflts, majflts,
                         # nswaps, msgsnds, msgrcvs, nsignals,
                         nvcsws, nivcsws])
            from_clause = from_clause.join(
                kcache_query,
                and_(kcache_query.c.dbid == c.dbid,
                     kcache_query.c.ts == c.ts))

        return (select(cols)
                .select_from(from_clause)
                .where(c.calls != '0')
                .group_by(c.srvid, c.ts, bs, c.mesure_interval)
                .order_by(c.ts)
                .params(samples=100))