On Wed, Nov 13, 2002 at 10:18:19AM -0700, scott.marlowe wrote:
> Generally a sort mem of 8 meg or less is pretty safe, as the allocation is
> only made WHILE the sort is running and is released right after. The
> danger is that if it is set higher, like say 32 or 64 meg, and a dozen or
> so sql statements just happen to all sort at the same time, you can run
> out of memory and have a "swap storm" where the machine is swapping out
> processes one after the other to give each the amount of swap space it
> needs. Note also that a SQL query with more than one sort in it will use
> up to sort_mem for each sort independently, so a dozen SQL queries that
> each require say three sorts all running at once can theoretically use
> 36*sort_mem amount of memory. Once the machine starts swapping for
> sort_mem, things get slow VERY fast. It's one of those knees you don't
> want to hit.
Something I havn't seen mentioned yet is the very useful program "vmstat".
It gives you a quick summary of how many blocks are being copied to and from
disk, whether and how much you are swapping, how many processes are actually
running and sleeping at any one time. You can tell it you give you an
average over any period of time (like a minute or an hour). Example:
# vmstat 1
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
1 0 0 29728 47584 1128 63048 3 2 17 10 21 38 9 2 35
1 0 0 29728 47584 1128 63048 0 0 0 0 1539 356 6 0 94
1 0 0 29728 47552 1128 63080 0 0 32 0 1542 354 5 1 94
1 0 0 29728 47552 1128 63080 0 0 0 0 1551 355 6 0 94
1 0 0 29728 47520 1128 63112 0 0 32 0 1542 361 5 2 93
As you can see, not a terribly loaded machine :)
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> We place no reliance On Virgin or Pigeon;
> Our method is Science, Our aim is Religion. - Aleister Crowley