Thread: Limiting processor power on queries
Hey all,
I have 2 questions.
1) Is there some way to kill a query in 7.3?
I have seen that it was on the todo list back in the 6.x days, but wasnt
able to find anything on it lately.
2) Is there a way to limit the processing power for a query based on a) from
where the query originated (eg. IP address) or b) the type of query it is.
(eg. select vs. insert)
TIA
Chad
"Chad Thompson" <chad@weblinkservices.com> writes:
> 1) Is there some way to kill a query in 7.3?
That's been in there for a *long* time.
> 2) Is there a way to limit the processing power for a query based on a) from
> where the query originated (eg. IP address) or b) the type of query it is.
No. In 7.3 you can set a per-statement timeout, although it's not
something you could enforce against an uncooperative user.
regards, tom lane
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Chad Thompson" <chad@weblinkservices.com> Cc: "pgsql-novice" <pgsql-novice@postgresql.org> Sent: Tuesday, May 13, 2003 5:09 PM Subject: Re: [NOVICE] Limiting processor power on queries > "Chad Thompson" <chad@weblinkservices.com> writes: > > 1) Is there some way to kill a query in 7.3? > > That's been in there for a *long* time. Pray tell, what can I look for in the docs to find such a solution ;-) Thanks Chad
Chad, > 1) Is there some way to kill a query in 7.3? > I have seen that it was on the todo list back in the 6.x days, but > wasnt able to find anything on it lately. Two ways: 1) Better: terminate the query at the client end by terminating the requesting process. For example, in PSQL hit CTL-C. 2) Not So Good: "Kill" the connnection process on the client end."SIGHUP" kills are best if they work, but I have had to do KILL -9 on runaway procedures to get them to stop. > 2) Is there a way to limit the processing power for a query based on a) > from where the query originated (eg. IP address) or b) the type of query it > is. (eg. select vs. insert) You cannot cap CPU usage in *any* way unless you are using a "real time operating system", like QNX or Real Time Linux, or some of the radical patches for Linux kernel 2.5. And PostgreSQL has not been ported to any of those systems AFAIK, so you're on your own ... -- Josh Berkus Aglio Database Solutions San Francisco
"Chad Thompson" <chad@weblinkservices.com> writes:
> 1) Is there some way to kill a query in 7.3?
>>
>> That's been in there for a *long* time.
> Pray tell, what can I look for in the docs to find such a solution ;-)
In what context? In psql you type control-C (on most systems). A libpq
user would call PQrequestCancel(). I don't recall offhand what to do
when using other interface libraries.
regards, tom lane
> "Chad Thompson" <chad@weblinkservices.com> writes: > > 1) Is there some way to kill a query in 7.3? > >> > >> That's been in there for a *long* time. > > > Pray tell, what can I look for in the docs to find such a solution ;-) > > In what context? In psql you type control-C (on most systems). A libpq > user would call PQrequestCancel(). I don't recall offhand what to do > when using other interface libraries. > Oops, sorry. I didnt realize that it was context sensitive (hence the post to the novice list :-) I am sending queries through a .NET app via ODBC using the pgsqlodbc driver. I ssh into my dbase box running redhat 8. TIA Chad
Josh wrote:
>You cannot cap CPU usage in *any* way unless you are using a "real time
>operating system", like QNX or Real Time Linux, or some of the radical
>patches for Linux kernel 2.5. And PostgreSQL has not been ported to any of
>those systems AFAIK, so you're on your own ...
How about "man ulimit" whose man page hasn't changed since Linux 2.0?
The csh command "limit cputime 1" will happily limit child processes
to 1 second of CPU time, and any process exceeding this limit will
die with a SIGXCPU.
When I try this with postgresql, my log file then happily says something
like:
"server process (pid 18945) was terminated by signal 24"
from the SIGXCPU that kills the backend,
and my client happily chokes with:
"The connection to the server was lost. Attempting reset: Failed."
If I was worried about runaway queries (say, I exposed a reporting
system to non-technical users that had the ability for people to
easily shoot themselves in the foot by running absurd queries, would I get
myself in big trouble by "limit cputime 3600" before restarting the
postmaster?
Yeah, I know that the postmaster, stats collector, etc would eventually
get nailed by this limit. But can I assume WAL, etc, will protect against
data corruption?
Ron
PS: no, i don't recommend doing this on data that isn't backed up :-)
"Ron Mayer" <ron@intervideo.com> writes:
> Josh wrote:
>> You cannot cap CPU usage in *any* way unless you are using a "real time
>> operating system", like QNX or Real Time Linux, or some of the radical
>> patches for Linux kernel 2.5. And PostgreSQL has not been ported to any of
>> those systems AFAIK, so you're on your own ...
FWIW, we did have a working QNX port until recently. It's suffering bit
rot for lack of a maintainer (Andreas hasn't been seen for awhile :-()
but certainly it could be resurrected if someone wanted to put in some
effort.
> How about "man ulimit" whose man page hasn't changed since Linux 2.0?
> The csh command "limit cputime 1" will happily limit child processes
> to 1 second of CPU time, and any process exceeding this limit will
> die with a SIGXCPU.
Which the postmaster will (indeed must) take as a backend crash prompting
a database-wide restart. This is not gonna be a useful technique.
regards, tom lane