Re: [PERFORM] Query kills machine.

[Magnus Naeslund(t)]

Stef wrote:
Christopher Kings-Lynne mentioned :
=> sort_mem = 4096
Reducing sort_mem to 4096 seems to make it run in a reasonable time
again. Any idea why? The database does a whole lot of huge sorts
every day, so I thought upping this parameter would help.
A couple of queries do seem to run slower now that I reduced
the sort_mem. 

The shared buffers still makes a significant difference when I increase it.
Well you have to take in account that sort_mem is not the total memory 
allocated for sorting but per connection and in complex expressions 
serveral times that too.

So if you sort a lot it can push your operating system off the cliff and 
it might start reaping things that shouldn't be reaped and start swapping.

If that happens _everything_ on that box will get slow...
Shared buffers on the other hand is only allocated once.
Regards,
Magnus
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Wierd context-switching issue on Xeon

[Magnus Naeslund(t)]

Tom Lane wrote:
Hmmm ... I've been able to reproduce the CS storm on a dual Athlon,
which seems to pretty much let the Xeon per se off the hook.  Anybody
got a multiple Opteron to try?  Totally non-Intel CPUs?
It would be interesting to see results with non-Linux kernels, too.
			regards, tom lane
I also tested on an dual Athlon MP Tyan Thunder motherboard (2xMP2800+, 
2.5GB memory), and got the same high numbers.
I then ran with kernel 2.6.5, it lowered them a little, but it's still 
some ping pong effect here. I wonder if this is some effect of the 
scheduler, maybe the shed frequency alone (100HZ vs 1000HZ).

It would be interesting to see what a locking implementation ala FUTEX 
style would give on an 2.6 kernel, as i understood it that would work 
cross process with some work.

The first file attached is kernel 2.4 running one process then starting 
up the other one.
Same with second file, but with kernel 2.6...

Regards
Magnus
procs ---memory-- ---swap-- -io --system-- cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us sy id wa
 1  0  0 1828408  27852 52885200 0 0  317   557 50  0 50  0
 1  0  0 1828408  27852 52885200 0 0  293   491 50  0 49  0
 1  0  0 1828400  27860 52885200 016  399   709 50  0 50  0
 1  0  0 1828400  27860 52885200 0 0  350   593 50  0 49  0
 2  0  0 1828400  27860 52885200 0 0  349   608 50  0 50  0
 1  0  0 1828400  27860 52885200 0 0  109   412 50  0 50  0
 1  0  0 1828400  27860 52885200 0 0  10192 50  0 50  0
 1  0  0 1828392  27868 52885200 016  10496 50  0 50  0
 1  0  0 1828392  27868 52885200 0 0  101   103 50  0 50  0
 2  0  0 1827408  27892 52885200 848  113 61197 45  9 46  0
 2  0  0 1827408  27892 52885200 0 0  101 167237 41 27 32  0
procs ---memory-- ---swap-- -io --system-- cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us sy id wa
 4  0  0 1827408  27892 52885200 0 0  101 166145 39 25 36  0
 2  0  0 1827400  27900 52885200 048  105 149406 42 19 40  0
 3  0  0 1827400  27900 52885200 0 0  101 157559 43 26 32  0
 2  0  0 1827400  27900 52885200 0 0  101 163813 46 24 30  0
 2  0  0 1827400  27900 52885200 0 0  101 156872 44 26 30  0
 2  0  0 1827400  27900 52885200 0 0  103 160722 45 28 28  0
 2  0  0 1827392  27908 52885200 016  104 158644 41 23 37  0
 3  0  0 1827392  27908 52885200 0 0  101 157534 42 25 33  0
 2  0  0 1827392  27908 52885200 0 0  101 160007 37 28 35  0
 3  0  0 1827392  27908 52885200 0 0  101 161852 45 24 31  0
 3  0  0 1827392  27908 52885200 0 0  101 161616 42 25 33  0
 2  0  0 1827392  27916 52885200 068  114 152144 44 25 31  0
 2  0  0 1827384  27916 52885200 0 0  101 156485 35 28 37  0
procs ---memory-- ---swap-- -io --system-- cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us sy id wa
 1  0  0 2436044   8844  9002800 016 1010   235 50  0 50  0
 1  0  0 2436108   8844  9002800 0 0 1024   404 50  0 50  0
 1  0  0 2436108   8844  9002800 0 0 1008   199 50  0 50  0
 1  0  0 2436108   8844  9002800 0 0 1017   272 50  0 50  0
 1  0  0 2436108   8844  9002800 0 0 1013   253 50  0 50  0
 1  1  0 2436108   8852  9002000 016 1019   282 51  0 49  1
 2  0  0 2435068   8852  9002000 0 0 1005 23929 45  4 50  0
 2  0  0 2435068   8852  9002000 020 1008 95501 33 14 53  0
procs ---memory-- ---swap-- -io --system-- cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us sy id wa
 3  0  0 2435068   8852  9002000 0 0 1002 103940 35 15 50  0
 0  0  0 2435068   8852  9002000 0 0 1003 104343 32 16 51  0
 2  0  0 2435068   8860  9008000 052 1006 102477 34 16 51  1
 2  0  0 2435068   8860  9008000 0 0 1002 92809 31 14 54  0
 2  0  0 2435068   8860  9008000 0 0 1002 100498 37 14 49  0
 1  0  0 2435068   8860  9008000 0 0 1002 108130 35 16 49  0
 0  0  0 2435068   8860  9008000 0 0 1002 94045 33 14 54  0
 0  0  0 2435004   8868  9007200 016 1005 104380 34 15 52  0
 2  0  0 2435004   8868  9007200 0 0 1002 100696 36 14 50  0
 2  0  0 2435068   8868  9007200 0 0 1002 98289 31 14 54  0
 0  0  0 2435068   8868  900720

Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.

[Magnus Naeslund(t)]

Gary Doades wrote:

Has anyone else done any comparative testing with the 2.6 kernel?

I know for a fact that certain stuff is recognized differently between 
2.2, 2.4 and 2.6 kernels.
For example i have one box that i installed debian stable on that used a 
2.2 kernel which automatically tuned on DMA on the harddrive, didn't do 
it on a 2.4 kernel, but on 2.6 one it saw it as DMA able.
Such things can dramatically affect performance, so make sure to compare 
what capabilities the kernel thinks your hardware has between the 
kernels first...

But i'll grant that the 2.6 kernel is a great deal faster on some of our 
test servers.

Regards
Magnus
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [PERFORM] [ADMIN] syslog slowing the database?

[Magnus Naeslund(t)]

Tom Lane wrote:
Greg Spiegelberg <[EMAIL PROTECTED]> writes:

I turned syslog back on and the restore slowed down again.  Turned
it off and it sped right back up.


We have heard reports before of syslog being quite slow.  What platform
are you on exactly?  Does Richard's suggestion of turning off syslog's
fsync help?
Another tip is to use a better (well atleast more optimized) syslog 
implementation, like metalog. It optimizes log writes to a blocksize 
that is better for disk throughput.
You can also use "per line" mode with those if you want, i think.

I use another logger that is called multilog (see at http://cr.yp.to), 
that's a pipe logger thing, like one per postmaster.
It also gives very exact timestamps to every line, has built in log 
rotation and works nice with all programs i use it for.

One thing is for sure, if you log much, standard syslog (atleast on 
linux) sucks big time.
I gained back approx 30% CPU on a mailserver over here by changing to 
another logger.

Cheers
Magnus


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match