Re: [PERFORM] Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Am 02.11.2012 17:12, schrieb Petr Praus: Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. A process migration would be even more expensive there. Might be worth to - manually pin (with taskset) the session you test this in to a particular CPU (once on each socket) to see if the times change I tested this and it does not seem to have any effect (assuming I used taskset correctly but I think so: taskset 02 psql to pin down to CPU #1 and taskset 01 psql to pin to CPU #0). Well, that pinned your _client_ to the CPUs, not the server side session ;-) You'd have to spot for the PID of the new "IDLE" server process and pin that using "taskset -p". Also, 01 and 02 are probably cores in the same package/socket. Try "lscpu" first and spot for "NUMA node*" lines at the bottom. But anyway... let's try something else first: - try reducing work_mem in the session you're testing in (so you have large SHM, but small work mem) Did this and it indicates to me that shared_buffers setting actually does not have an effect on this behaviour as I previously thought it has. It really boils down to work_mem: when I set shared_buffers to something large (say 4GB) and just play with work_mem the problem persists. This only confirms what we've seen before. As soon as your work_mem permits an in-memory sort of the intermediate result set (which at that point in time is where? In the SHM, or in the private memory of the backend? I can't tell, tbth), the sort takes longer than when it's using a temp file. What if you reduce the shared_buffers to your original value and only increase/decrease the session's work_mem? Same behaviour? Cheers, -- Gunnar "Nick" Bluth RHCE/SCLA Mobil +49 172 8853339 Email: gunnar.bl...@pro-open.de __ In 1984 mainstream users were choosing VMS over UNIX. Ten years later they are choosing Windows over UNIX. What part of that message aren't you getting? - Tom Payne
Re: [PERFORM] Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Am 03.11.2012 16:20, schrieb Petr Praus: Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. A process migration would be even more expensive there. Ok, I've actually looked these up now... at the time these were current, I was in the lucky situation to only deal with Opterons. And actually, with these CPUs it is pretty possible that Scott Marlowe's hint (check vm.zone_reclaim_mode) was pointing in the right direction. Did you check that? Yes, same behaviour. I let the shared_buffers be the default (which is 8MB). With work_mem 1MB the query runs fast, with 96MB it runs slow (same times as before). It really seems that the culprit is work_mem. Well, I'm pretty sure that having more work_mem is a good thing (tm) normally ;-) -- Gunnar "Nick" Bluth RHCE/SCLA Mobil +49 172 8853339 Email: gunnar.bl...@pro-open.de __ In 1984 mainstream users were choosing VMS over UNIX. Ten years later they are choosing Windows over UNIX. What part of that message aren't you getting? - Tom Payne
Re: [PERFORM] Constraint exclusion in views
> Funny thing is, if I set constraint_exclusion=on, it works as > expected. But not with constraint_exclusion=partition. The difference between "on" and "partition" is how it treats UNION. This seems to be working as designed. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Constraint exclusion in views
Josh Berkus writes: >> Funny thing is, if I set constraint_exclusion=on, it works as >> expected. But not with constraint_exclusion=partition. > The difference between "on" and "partition" is how it treats UNION. > This seems to be working as designed. Well, what "partition" actually means is "only bother to try constraint exclusion proofs on appendrel members". UNION ALL trees will get flattened into appendrels in some cases. In a quick look at the code, it seems like in recent releases the restrictions are basically that the UNION ALL arms have to (1) each be a plain SELECT from a single table with no WHERE restriction; (2) all produce the same column datatypes; and (3) not have any volatile functions in the SELECT lists. I might be missing something relevant to the OP's case, but it's hard to tell without a concrete example. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How to keep queries low latency as concurrency increases
On Wed, Oct 31, 2012 at 11:39 AM, Merlin Moncure wrote: > On Tue, Oct 30, 2012 at 4:58 PM, Jeff Janes wrote: >> On Mon, Oct 29, 2012 at 5:11 PM, Catalin Iacob >> wrote: >> >>> pgbouncer 1.4.2 installed from Ubuntu's packages on the same machine >>> as Postgres. Django connects via TCP/IP to pgbouncer (it does one >>> connection and one transaction per request) and pgbouncer keeps >>> connections open to Postgres via Unix socket. >> >> Isn't pgbouncer single-threaded? >> >> If you hitting it with tiny queries as fast as possible from 20 >> connections, I would think that it would become the bottleneck. > > Single threaded asynchronous servers are known to scale better for > this type of workload than multi-threaded systems because you don't > have to do locking and context switching. How much locking would there be in what pgbouncer does? On a 4 CPU machine, if I run pgbench -c10 -j10 with dummy queries (like "select 1;" or "set timezone...") against 2 instances of pgbouncer, I get nearly twice the throughput as if I use only one instance. A rather odd workload, maybe, but it does seem to be similar to the one that started this thread. > pgbouncer is an absolute marvel and should be standard kit in any case > you're concerned about server scaling in terms of number of active > connections to the database. I'm in the camp that application side > connection pools are junk and should be avoided when possible. I have nothing against pgbouncer, but it is not without consequences. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
