Thread: changing sort_mem on the fly?
Is there any way to increase sort_mem without having to perform a full restart of the DB? Will just doing a 'kill -HUP $PID" work as long as i've made the change in postgresql.conf first? As a side question, are the options that will take effect with a kill -HUP documented somewhere? thanks! -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand http://netllama.linux-sxs.org
On Thu, Jan 27, 2005 at 02:57:22PM -0800, Lonni J Friedman wrote: > Is there any way to increase sort_mem without having to perform a full > restart of the DB? Will just doing a 'kill -HUP $PID" work as long as > i've made the change in postgresql.conf first? > > As a side question, are the options that will take effect with a kill > -HUP documented somewhere? If you just want to change it for one connection, you can do 'set sort_mem=newvalue;'. You might need to be an admin to do it, though. -- Jim C. Nasby, Database Consultant decibel@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?"
On Thu, Jan 27, 2005 at 06:23:21PM -0600, Jim C. Nasby wrote: > > If you just want to change it for one connection, you can do 'set > sort_mem=newvalue;'. You might need to be an admin to do it, though. Ordinary users can set sort_mem/work_mem, at least in 8.0.0 and earlier. Does setting that variable per session actually have an effect, and if it does, does allowing ordinary users to set it present a risk? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
I usually use pg_ctl reload instead of kill On Thu, 27 Jan 2005 14:57:22 -0800, Lonni J Friedman wrote > Is there any way to increase sort_mem without having to perform a > full restart of the DB? Will just doing a 'kill -HUP $PID" work as > long as i've made the change in postgresql.conf first? > > As a side question, are the options that will take effect with a kill > -HUP documented somewhere? > > thanks! > > -- > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > L. Friedman netllama@gmail.com > LlamaLand http://netllama.linux-sxs.org > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Jonel Rienton http://blogs.road14.com Software Developer, *nix Advocate
OK, but does that actually work to change sort_mem for all future connections? On Thu, 27 Jan 2005 19:25:51 -0600, Jonel Rienton <jonel@road14.com> wrote: > I usually use pg_ctl reload instead of kill > > On Thu, 27 Jan 2005 14:57:22 -0800, Lonni J Friedman wrote > > Is there any way to increase sort_mem without having to perform a > > full restart of the DB? Will just doing a 'kill -HUP $PID" work as > > long as i've made the change in postgresql.conf first? > > > > As a side question, are the options that will take effect with a kill > > -HUP documented somewhere? -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand http://netllama.linux-sxs.org
On Thu, 27 Jan 2005 18:23:21 -0600, Jim C. Nasby <decibel@decibel.org> wrote: > On Thu, Jan 27, 2005 at 02:57:22PM -0800, Lonni J Friedman wrote: > > Is there any way to increase sort_mem without having to perform a full > > restart of the DB? Will just doing a 'kill -HUP $PID" work as long as > > i've made the change in postgresql.conf first? > > > > As a side question, are the options that will take effect with a kill > > -HUP documented somewhere? > > If you just want to change it for one connection, you can do 'set > sort_mem=newvalue;'. You might need to be an admin to do it, though. I need to do it for all future connections, not just one. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand http://netllama.linux-sxs.org
On Thu, Jan 27, 2005 at 06:23:05PM -0700, Michael Fuhr wrote: > On Thu, Jan 27, 2005 at 06:23:21PM -0600, Jim C. Nasby wrote: > > > > If you just want to change it for one connection, you can do 'set > > sort_mem=newvalue;'. You might need to be an admin to do it, though. > > Ordinary users can set sort_mem/work_mem, at least in 8.0.0 and > earlier. Does setting that variable per session actually have an > effect, and if it does, does allowing ordinary users to set it > present a risk? I ass-u-me that the setting actually takes effect. You could test it by setting it high enough to send a system into swapping and run a query that will use enough sort_mem. If it starts swapping, it worked. :) Yes, there is a risk allowing ad-hoc settings; you can starve the machine for memory. -- Jim C. Nasby, Database Consultant decibel@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?"
On Thu, Jan 27, 2005 at 05:52:41PM -0800, Lonni J Friedman wrote: > On Thu, 27 Jan 2005 18:23:21 -0600, Jim C. Nasby <decibel@decibel.org> wrote: > > On Thu, Jan 27, 2005 at 02:57:22PM -0800, Lonni J Friedman wrote: > > > Is there any way to increase sort_mem without having to perform a full > > > restart of the DB? Will just doing a 'kill -HUP $PID" work as long as > > > i've made the change in postgresql.conf first? > > > > > > As a side question, are the options that will take effect with a kill > > > -HUP documented somewhere? > > > > If you just want to change it for one connection, you can do 'set > > sort_mem=newvalue;'. You might need to be an admin to do it, though. > > I need to do it for all future connections, not just one. 'm guessing pg_ctl reload would take effect on all new connections. Try it, and see what psql -c 'show sort_mem' says. -- Jim C. Nasby, Database Consultant decibel@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?"
On Thu, 27 Jan 2005 20:05:19 -0600, Jim C. Nasby <decibel@decibel.org> wrote: > On Thu, Jan 27, 2005 at 05:52:41PM -0800, Lonni J Friedman wrote: > > On Thu, 27 Jan 2005 18:23:21 -0600, Jim C. Nasby <decibel@decibel.org> wrote: > > > On Thu, Jan 27, 2005 at 02:57:22PM -0800, Lonni J Friedman wrote: > > > > Is there any way to increase sort_mem without having to perform a full > > > > restart of the DB? Will just doing a 'kill -HUP $PID" work as long as > > > > i've made the change in postgresql.conf first? > > > > > > > > As a side question, are the options that will take effect with a kill > > > > -HUP documented somewhere? > > > > > > If you just want to change it for one connection, you can do 'set > > > sort_mem=newvalue;'. You might need to be an admin to do it, though. > > > > I need to do it for all future connections, not just one. > > 'm guessing pg_ctl reload would take effect on all new connections. Try > it, and see what psql -c 'show sort_mem' says. excellent, that worked! thanks! -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand http://netllama.linux-sxs.org
On Thu, 2005-01-27 at 20:03 -0600, Jim C. Nasby wrote: > Yes, there is a risk allowing ad-hoc settings; you can starve the > machine for memory. A malicious user who can execute SELECT queries can already consume an arbitrary amount of memory -- say, by disabling GEQO and self-joining pg_class to itself 50 times. I'm not sure that letting users modify sort_mem/work_mem actually increases the risk from malicious users. Restricting this parameter to superusers only would also be a hit to usability. -Neil
Neil Conway <neilc@samurai.com> writes:
> A malicious user who can execute SELECT queries can already consume an
> arbitrary amount of memory -- say, by disabling GEQO and self-joining
> pg_class to itself 50 times. I'm not sure that letting users modify
> sort_mem/work_mem actually increases the risk from malicious users.
The correct place for a sysadmin to limit memory usage would be in the
ulimit settings the postmaster starts under. Of course, Neil's argument
still holds in general: anyone who can write arbitrary queries is not
going to have any difficulty in soaking up unreasonable amounts of
resources. Trying to restrict that would probably make the system less
useful rather than more so.
regards, tom lane
On Fri, Jan 28, 2005 at 02:43:13AM -0500, Tom Lane wrote: > Neil Conway <neilc@samurai.com> writes: > > A malicious user who can execute SELECT queries can already consume an > > arbitrary amount of memory -- say, by disabling GEQO and self-joining > > pg_class to itself 50 times. I'm not sure that letting users modify > > sort_mem/work_mem actually increases the risk from malicious users. > > The correct place for a sysadmin to limit memory usage would be in the > ulimit settings the postmaster starts under. Of course, Neil's argument > still holds in general: anyone who can write arbitrary queries is not > going to have any difficulty in soaking up unreasonable amounts of > resources. Trying to restrict that would probably make the system less > useful rather than more so. Just to clarify, I wasn't suggesting limiting sort_mem to only admins, I was only pointing out that it's possible for a user to starve the box of memory with it. I'd also point out that IMHO it's far more likely that someone could accidentally hose the box with sort_mem than with a SELECT. In the year 2020 when there's a bunch of bored developers sitting around wondering what to do, it might be nice to have the ability to apply some security to GUC settings, possibly just being able to restrict them to super-user, and having a SECURITY DEFINER function users can call instead that could apply permissions. -- Jim C. Nasby, Database Consultant decibel@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?"
Tom Lane wrote: > The correct place for a sysadmin to limit memory usage would be in the > ulimit settings the postmaster starts under. Of course, Neil's argument > still holds in general: anyone who can write arbitrary queries is not > going to have any difficulty in soaking up unreasonable amounts of > resources. Trying to restrict that would probably make the system less > useful rather than more so. I'm not sure if I agree that there's no potential for implementing better resource limits/quotas in PG in the future, I was just pointing out that it would require a lot more work to prevent resource consumption by malicious users than merely limiting who is allowed to set sort_mem/work_mem. If you could implement per-user/per-connection limits on things like processor usage or disk space consumption, I think that would be useful to some users (e.g. people offering PG in a web hosting environment). -Neil
On Sat, Jan 29, 2005 at 09:23:06PM +1100, Neil Conway wrote: > Tom Lane wrote: > >The correct place for a sysadmin to limit memory usage would be in the > >ulimit settings the postmaster starts under. Of course, Neil's argument > >still holds in general: anyone who can write arbitrary queries is not > >going to have any difficulty in soaking up unreasonable amounts of > >resources. Trying to restrict that would probably make the system less > >useful rather than more so. > > I'm not sure if I agree that there's no potential for implementing > better resource limits/quotas in PG in the future, I was just pointing > out that it would require a lot more work to prevent resource > consumption by malicious users than merely limiting who is allowed to > set sort_mem/work_mem. If you could implement per-user/per-connection > limits on things like processor usage or disk space consumption, I think > that would be useful to some users (e.g. people offering PG in a web > hosting environment). Since you brought up the future... :) I'd really like to see an improvement in how sort_mem/work_mem is handled. It's currently impossible to set it in a way to support moderately large sorts (say, 5% of available memory) without either embedding 'set sort_mem = blah' in your code or running the risk that at some point your database server will start swapping. I wish there was some way to limit total sort size for the entire system, or at least per connection/query. -- Jim C. Nasby, Database Consultant decibel@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?"
Jim C. Nasby wrote: > I'd really like to see an improvement in how sort_mem/work_mem is > handled. So would I :) (I think it's well known that the current system is not optimal.) Do you have any thoughts on how to improve it? -Neil
On Sun, Jan 30, 2005 at 08:50:49PM +1100, Neil Conway wrote: > Jim C. Nasby wrote: > >I'd really like to see an improvement in how sort_mem/work_mem is > >handled. > > So would I :) (I think it's well known that the current system is not > optimal.) > > Do you have any thoughts on how to improve it? See http://archives.postgresql.org/pgsql-general/2003-06/msg01072.php and http://archives.postgresql.org/pgsql-general/2003-06/msg01089.php There could certainly be better ways than what I proposed, too. It might be worth investigating how the 'big 3' handle it, though some of what they do might be patented. -- Jim C. Nasby, Database Consultant decibel@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?"
"Jim C. Nasby" <decibel@decibel.org> writes: > On Sun, Jan 30, 2005 at 08:50:49PM +1100, Neil Conway wrote: >> Do you have any thoughts on how to improve it? > See http://archives.postgresql.org/pgsql-general/2003-06/msg01072.php > and http://archives.postgresql.org/pgsql-general/2003-06/msg01089.php All of this falls down on twin problems: (1) we have no portable way to find out how much RAM is really available, and (2) the planner has to make a cost estimate for the sort in advance of actual runtime --- which requires making some assumption about the value of sort_mem that will be used. regards, tom lane
On Sun, Jan 30, 2005 at 01:05:15PM -0500, Tom Lane wrote: > "Jim C. Nasby" <decibel@decibel.org> writes: > > On Sun, Jan 30, 2005 at 08:50:49PM +1100, Neil Conway wrote: > >> Do you have any thoughts on how to improve it? > > > See http://archives.postgresql.org/pgsql-general/2003-06/msg01072.php > > and http://archives.postgresql.org/pgsql-general/2003-06/msg01089.php > > All of this falls down on twin problems: (1) we have no portable way to > find out how much RAM is really available, and (2) the planner has to > make a cost estimate for the sort in advance of actual runtime --- which > requires making some assumption about the value of sort_mem that will be > used. As I said, I'm sure there's better minds who can come up with better ideas than I. :) I personally think this is important enough to warrant a TODO so those minds can think about it at some point, but that's just my opinion. As for your two points: if you mean how much RAM is available for sorting, I assumed there would be a GUC for that, although effective_cache_size might be used. I thought that the planner did estimate sort size when it called the sort code, but it's been a long time since I looked at it and it was somewhat over my head anyway. Since the planner knows how many rows will be going into the sort and how wide they are, ISTM it should be able to estimate how much memory will be needed. Even if it can't, I think you could still get by with just keeping track of how much memory a sort is using when it asks for more memory. At some point it would hit a threshold and would then spill to disk. BTW, if it would be useful, I could test sort speeds for sorts that spill to disk with different sort_mem settings. That would help answer the question of how much it would hurt for a sort that spills to disk to give back some of it's memory so that other sort operations wouldn't spill to disk. -- Jim C. Nasby, Database Consultant decibel@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?"
"Jim C. Nasby" <decibel@decibel.org> writes:
> Since the planner knows how many rows will
> be going into the sort and how wide they are, ISTM it should be able to
> estimate how much memory will be needed.
... which is different from how much will be available. See cost_sort():
* If the total volume of data to sort is less than work_mem, we will do
* an in-memory sort, which requires no I/O and about t*log2(t) tuple
* comparisons for t tuples.
*
* If the total volume exceeds work_mem, we switch to a tape-style merge
* algorithm. There will still be about t*log2(t) tuple comparisons in
* total, but we will also need to write and read each tuple once per
* merge pass. We expect about ceil(log6(r)) merge passes where r is the
* number of initial runs formed (log6 because tuplesort.c uses six-tape
* merging). Since the average initial run should be about twice work_mem,
* we have
* disk traffic = 2 * relsize * ceil(log6(p / (2*work_mem)))
* cpu = comparison_cost * t * log2(t)
The actual cost of a sort is therefore *highly* sensitive to how much
memory it is allowed to use. Blowing off any ability to estimate this
in advance is not going to improve matters...
regards, tom lane
On Sun, Jan 30, 2005 at 04:49:39PM -0500, Tom Lane wrote: > "Jim C. Nasby" <decibel@decibel.org> writes: > > Since the planner knows how many rows will > > be going into the sort and how wide they are, ISTM it should be able to > > estimate how much memory will be needed. > > ... which is different from how much will be available. See cost_sort(): Ok, I wasn't sure which you were refering to. As I mentioned in the earlier thread, there would have to be some means of accounting for how much memory active sorts in the system are using. One possibility is that a global counter is updated any time a sort allocates or frees memory. If allocations are done 8k at a time that would probably be too expensive, but I suspect it wouldn't be too bad with larger allocations that are done less frequently. And bear in mind that if these changes prevent even a few sorts an hour from spilling to disk then it's probably still a net gain. > * If the total volume of data to sort is less than work_mem, we will do > * an in-memory sort, which requires no I/O and about t*log2(t) tuple > * comparisons for t tuples. > * > * If the total volume exceeds work_mem, we switch to a tape-style merge > * algorithm. There will still be about t*log2(t) tuple comparisons in > * total, but we will also need to write and read each tuple once per > * merge pass. We expect about ceil(log6(r)) merge passes where r is the > * number of initial runs formed (log6 because tuplesort.c uses six-tape > * merging). Since the average initial run should be about twice work_mem, > * we have > * disk traffic = 2 * relsize * ceil(log6(p / (2*work_mem))) > * cpu = comparison_cost * t * log2(t) > > The actual cost of a sort is therefore *highly* sensitive to how much > memory it is allowed to use. Blowing off any ability to estimate this > in advance is not going to improve matters... Doesn't the sort code also have a provision for starting an in-memory sort, and then realizing that it's going to exceed work_mem and going to disk at that point? Yes, without something as simple as work_mem it is harder to determine how much memory you can use, though I don't think it's impossible. First, a global variable/counter could be used to keep track of how much memory all the active sorts estimate they'll need. That would be a first-order guess. This could also be compared to how much memory is actually being used, though I suspect that would require globally tracking how much memory each sort estimates it will need and how much it's already using. Of course there would also be some kind of policy limiting sorts from taking over all of sort_mem; possibly a GUC, possibly something more dynamic. Whatever it is, that gives you an upper-bounds for how much memory would be used at a given point in time. BTW, something that just occured to me... with the new cache management code (ARC and whatever's going to replace it), presumably it will now be better for PostgreSQL to primarily do caching instead of the OS. This means you'd want to give PostgreSQL the lion's share of memory, but with how sorts currently aquire memory doing so would be risky. Should there be an option in 8.0 for sorts to use shared memory instead of allocating their own from a (possibly small) pool of memory the OS has? -- Jim C. Nasby, Database Consultant decibel@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?"