[rrs-users] Round Robin SQL question

Jim C. Nasby decibel at rrs.decibel.org
Sat May 12 11:36:03 CDT 2007


Adding rrs-users to the list... you should join the mailing list.

On Sun, Apr 29, 2007 at 04:14:25PM -0700, daveg wrote:
> 
> Thanks for the examples and clarification. I'll study that and try it out
> with the sample data I am going to be collecting. This is a great help.
> 
> On Sat, Apr 28, 2007 at 08:54:06PM -0500, Jim C. Nasby wrote:
> > Yes, though you need to be careful about how you aggregate things. I
> > think it might make sense to pol fairly frequently (like every 10
> > seconds), and convert that data into a per_minute rate... BUT, I think
> > we also need to 'toss out' the first value we see after a long period of
> > no data, because it's almost certainly going to be BS. The thought I had
> > was to just log stuff out of pg_stat* views fairly frequently, and then
> > have another process that would read that data and calculate per-minute
> > information. But it would be nice to make that second process part of
> > RRS, since this is another common use case. I've also been thinking that
> > there should be separate functions for pulling data from a raw table vs
> > from another RRS; if that was the case we should be able to handle this
> > with a different type of raw data insert function.
> 
> I have come up with the following code to convert from the raw pg_stat
> increasing counts to count per interval. No triggers needed, should run
> reasonably well. Also it handles the case of tables that come and go, dropping
> the first sample, and keeping track of tables in the log even though they
> may be departed from the catalogs.
> 
> My plan here is to use this to track io load and space by table or index
> for reporting for capacity planning, to understand which tables are io
> bottlenecks and to drive a script to move objects between tablespaces
> to balance space use and i/o load. For example, to move tables with more
> ios per page to faster drives. For this last, daily or weekly summaries are
> the interesting bucketsize. I don't want to move tables frequently, just to
> have the system slowly adapt to changes in workload.
> 
> Anyway, feel free to borrow if any of this is useful. 
 
Looks good except for one issue... it doesn't handle a roll-over.

Also, wouldn't it have been easier to use the pg_statio_* views?
 
> --
> -- to install
> --
> create table dba.io_raw_status (
>     new_stime   timestamp,
>     old_stime   timestamp
> ) ;
> 
> -- tracks timestamps of current and previous samples
> insert into dba.io_raw_status (new_stime, old_stime) values (null, null);
> 
> -- one new and one old row per relid with pg_stat... counts.
> create table dba.io_raw_samples (
>     stime       timestamp   not null,
>     relid       int         not null,
>     relpages    float       not null,
>     reltuples   float       not null,
>     blocks_fetched float    not null,
>     blocks_hit  float       not null,
>     numscans    float       not null,
>     tuples_fetched float    not null,
>     tuples_returned float   not null,
>     tuples_inserted float   not null,
>     tuples_updated float    not null,
>     tuples_deleted float    not null
> ) ;
> 
> -- one row per sample with endtime, interval, counts in interval
> create table dba.iosamples (
>     stime       timestamp   not null,
>     relid       int         not null,
>     nspname     text        not null,
>     relname     text        not null,
>     relkind     char        not null,
>     indrelid    int,
>     relpages    float       not null,
>     reltuples   float       not null,
>     duration    interval    not null,
>     delta_pages float       not null,
>     delta_tuples float      not null,
>     blocks_fetched float    not null,
>     blocks_hit  float       not null,
>     numscans    float       not null,
>     tuples_fetched float    not null,
>     tuples_returned float   not null,
>     tuples_inserted float   not null,
>     tuples_updated float    not null,
>     tuples_deleted float    not null
> ) ;
> 
> 
> 
> --
> -- to collect_stats
> --
> 
> begin;
> 
> -- update sample timestamps
> update dba.io_raw_status set old_stime = new_stime, new_stime = now();
> 
> -- collect new raw sample
> insert into dba.io_raw_samples (stime, relid, relpages, reltuples,
>             blocks_fetched, blocks_hit, numscans,
>             tuples_fetched, tuples_returned,
>             tuples_inserted, tuples_updated, tuples_deleted)
>   select new_stime, c.oid, relpages, reltuples,
>          pg_stat_get_blocks_fetched(c.oid),
>          pg_stat_get_blocks_hit(c.oid),
>          pg_stat_get_numscans(c.oid),
>          pg_stat_get_tuples_fetched(c.oid),
>          pg_stat_get_tuples_returned(c.oid),
>          pg_stat_get_tuples_inserted(c.oid),
>          pg_stat_get_tuples_deleted(c.oid),
>          pg_stat_get_tuples_updated(c.oid)
>     from dba.io_raw_status
>     cross join pg_class c
>     join pg_namespace n on n.oid = relnamespace
>     where relkind in ('r', 'i')
>       and nspname not like 'pg_temp%'
>       and nspname <> 'information_schema';
> 
> -- remove unwanted old raw samples
> delete from dba.io_raw_samples
>   where stime < (select old_stime from dba.io_raw_status);
> 
> -- get delta in counts between old and new samples for objects that have both.
> insert into dba.iosamples(stime, relid, nspname, relname, relkind, indrelid,
>             relpages, reltuples,
>             duration, delta_pages, delta_tuples,
>             blocks_fetched, blocks_hit, numscans,
>             tuples_fetched, tuples_returned,
>             tuples_inserted, tuples_updated, tuples_deleted)
>   select n.stime, relid, nspname, relname, relkind, indrelid,
>          n.relpages, n.reltuples,
>          n.stime-o.stime, n.relpages-o.relpages, n.reltuples-o.reltuples,
>          n.blocks_fetched-o.blocks_fetched,
>          n.blocks_hit-o.blocks_hit,
>          n.numscans-o.numscans,
>          n.tuples_fetched-o.tuples_fetched,
>          n.tuples_returned-o.tuples_returned,
>          n.tuples_inserted-o.tuples_inserted,
>          n.tuples_updated-o.tuples_updated,
>          n.tuples_deleted-o.tuples_deleted
>     from dba.io_raw_samples n
>     join dba.io_raw_samples o using (relid)
>     join dba.io_raw_status on new_stime = n.stime and old_stime = o.stime
>     join pg_class c on relid = c.oid
>     join pg_namespace nsp on nsp.oid = relnamespace
>     left join pg_index i on c.oid = indexrelid
>     order by relid;
> 
> end;
> 
> -dg
> 
> -- 
> David Gould                      daveg at sonic.net
> If simplicity worked, the world would be overrun with insects.
> 

-- 
Jim C. Nasby, Database Architect                decibel at decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"


More information about the rrs-users mailing list