Example #1
0
 def query(self):
     bs = block_size.c.block_size
     query = powa_getstatdata_sample("db")
     query = query.alias()
     c = query.c
     return (select([
         extract("epoch", c.ts).label("ts"),
         (sum(c.runtime) / greatest(sum(c.calls), 1)).label("avg_runtime"),
         total_read(c),
         total_hit(c)
     ]).where(c.calls != None).group_by(c.ts, bs).order_by(
         c.ts).params(samples=100))
Example #2
0
 def query(self):
     bs = block_size.c.block_size
     query = powa_getstatdata_sample("db")
     query = query.alias()
     c = query.c
     return (select([
             extract("epoch", c.ts).label("ts"),
             (sum(c.runtime) / greatest(sum(c.calls), 1)).label("avg_runtime"),
             total_read(c),
             total_hit(c)])
         .where(c.calls != None)
         .group_by(c.ts, bs)
         .order_by(c.ts)
         .params(samples=100))
Example #3
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")
     # Put the where clause inside the subquery
     subquery = subquery.where(column("datname") == bindparam("database"))
     query = subquery.alias()
     c = query.c
     return (select([
         to_epoch(c.ts),
         (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)
     ]).where(c.calls != None).group_by(
         c.ts, bs, c.mesure_interval).order_by(c.ts).params(samples=100))
Example #4
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")
     # Put the where clause inside the subquery
     subquery = subquery.where(column("datname") == bindparam("database"))
     query = subquery.alias()
     c = query.c
     return (select([
         to_epoch(c.ts),
         (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)])
             .where(c.calls != None)
             .group_by(c.ts, bs, c.mesure_interval)
             .order_by(c.ts)
             .params(samples=100))
Example #5
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))
Example #6
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))