Skip site navigation (1) Skip section navigation (2)

RE: Access statistics

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Jan Wieck <JanWieck(at)Yahoo(dot)com>
Cc: PostgreSQL HACKERS <pgsql-hackers(at)postgresql(dot)org>
Subject: RE: Access statistics
Date: 2001-06-01 14:45:03
Message-ID: 01C0EA87.EA8CBB70.mascarm@mascari.com (view raw or flat)
Thread:
Lists: pgsql-hackers
For what its worth,

Oracle has a nice feature for resource management called PROFILEs:

CREATE PROFILE profile LIMIT
[ SESSION_PER_USER [ session_limit | UNLIMITED | DEFAULT ] ]
[ CPU_PER_SESSION cpu_session_limit | UNLIMITED | DEFAULT ] ]
[ CPU_PER_CALL cpu_call_limit | UNLIMITED | DEFAULT ] ]
[ CONNECT_TIME connect_limit | UNLIMITED | DEFAULT ] ]
[ IDLE_TIME idle_limit | UNLIMITED | DEFAULT ] ]		
[ LOGICAL READS_PER_SESSION read_session_limit | UNLIMITED | DEFAULT 
] ]		
[ LOGICAL READS_PER_CALL read_call_limit | UNLIMITED | DEFAULT ] ]		
[ PRIVATE_SGA memory_limit | UNLIMITED | DEFAULT ] ]		
[ COMPOSITE_LIMIT resource_cost_limit | UNLIMITED | DEFAULT ] ]

which limits things like CPU_PER_CALL and LOGICAL_READS_PER_SESSION 
to a profile. The ALTER USER command then allows you to assign a 
PROFILE to a user. This is really nice, since you can prevent 
run-away queries from denying service by the database to other users. 
It also can prevent a user from soaking up all of the available 
connections. You must set a flag in your initSID.ora configuration 
profile for ORACLE to support profiles. Since Jan is collecting these 
statistics any way (if the appropriate configuration flag is set), it 
would be pretty trivial to implement PROFILEs in PostgreSQL.

Mike Mascari
mascarm(at)mascari(dot)com

-----Original Message-----
From:	Tom Lane [SMTP:tgl(at)sss(dot)pgh(dot)pa(dot)us]

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
>     So outing myself not beeing a *real programmer*, this is what
>     I have so far:

Hmm ... what is the performance of all this like?  Seems like a lot
of overhead.  Can it be turned off?

>     * Backends call some collector functions  at  various  places
>       now  (these  will  finally  be macros), that count up table
>       scans, tuples returned by scans,  buffer  fetches/hits  and
>       the like.

Have you removed the existing stats-gathering support
(backend/access/heap/stats.c and so on)?   That would buy back
at least a few of the cycles involved ...

			regards, tom lane


pgsql-hackers by date

Next:From: Peter EisentrautDate: 2001-06-01 15:04:10
Subject: Re: Fw: Isn't pg_statistic a security hole - Solution Proposal
Previous:From: Tom LaneDate: 2001-06-01 14:42:30
Subject: Re: Access statistics

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group