On 8 Prosinec 2011, 14:17, Craig Ringer wrote:
> You can.
>
> ALTER USER username SET work_mem = '100MB';
>
> It's not a hard cap - the user can raise/lower it however they like. The
> initial value can be set globally, per-user, per-database, or globally.
Oh, shame on me! I wasn't aware of this. Too bad it's not possible to
restrict this (changing certain config values). A simple 'before SET' hook
might do the trick.
>> I wonder if the prioritisation could be done using nice - each backend
>> is a separate process, so why not to do 'nice(10)' for low priority
>> processes or something like that.
>
> Yes, to a limited degree you can prioritise queries using nice and
> ionice, but it's awkward because:
>
> - All queries run as `postgres' so you can't do per-user limiting very
> easily
>
> - The postmaster doesn't have a way to set the nice level and ionice
> level when it forks a backend, nor does the backend have any way to do
> it later. You can use your own user-defined C functions for this, though.
Yes, that's what I meant.
> - Most importantly, even if you nice and ionice using C functions or
> manually with the cmdline utilities, you can't affect the bgwriter, nor
> can you affect how much data a low-priority query pushes out of cache.
IMHO bgwriter may be reasonably tuned by bgwriter_* GUC variables. The
user backends are probably more interesting here.
Tomas