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))
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))
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))
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))
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))
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))