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

Re: Connection.setReadOnly()

From: John R Pierce <pierce(at)hogranch(dot)com>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Connection.setReadOnly()
Date: 2009-12-11 01:54:09
Message-ID: 4B21A641.9030409@hogranch.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Oliver Jowett wrote:
> John R Pierce wrote:
>   
>> we have a fairly large database thats fed a
>> constant stream of realtime data 24/7 from a factory doing both inserts
>> and updates...    many different sorts of things have to query this
>> database, including mechanical/electrical engineering types doing adhoc
>> queries for oddball reports and such who are not SQL experts.   its way
>> to easy for them to accidentally create a transaction that they never
>> end, causing multiday blockage of VACUUM, massive table bloat, etc.
>>     
>
> Presumably they could also run a query that accidentally consumes all
> the CPU and I/O bandwidth on your server (hello missing WHERE clause!)
>   

on the typical server we use with 8 or 16 or more processors, and lots 
of fiberchannel bandwidth for storage, they can at most bung up a single 
core and some fraction of the total disk IO demand.   the administrators 
are fairly used to run away reporting queries in the oracle space, and 
will kill them off without hesitation if they are impacting operational 
transactions..

> It sounds like what you need is an intermediate layer that enforces
> transaction and query timeouts, rather than giving your mech/eng types
> direct access to the DB.
>   

thats kind of hard to do well when you need totally off the wall adhoc 
queries to answer complex engineering questions.    on our larger scale 
systems we have a seperate dedicated database for reporting queries that 
runs on its own hardware, but this postgres system is meant to be a 
lower-cost version for a smaller/simpler operation, so the array of 
several back end servers we have traditionally used have all been folded 
onto this one server (currently the production evaluation server is on 
an 8 core Sun v490 with 16GB ram etc).



In response to

pgsql-jdbc by date

Next:From: Greg StarkDate: 2009-12-11 12:09:04
Subject: Re: Connection.setReadOnly()
Previous:From: Oliver JowettDate: 2009-12-11 01:26:06
Subject: Re: Connection.setReadOnly()

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