On Tue, Jun 30, 2015 at 8:52 AM, eudald_v <reaven.galaeindael@xxxxxxxxx> wrote: > Hello all, > This is my very first message to the Postgresql community, and I really hope > you can help me solve the trouble I'm facing. > > I've an 80 core server (multithread) with close to 500GB RAM. > > My configuration is: > MaxConn: 1500 (was 850) Drop this to 80 to 160, and use pgbouncer for pooling. pg_pooler is nice, but it's a lot harder to configure. pgbouncer takes literally a couple of minutes and you're up and running. > Shared buffers: 188Gb I have yet to see shared_buffers this big be a big help. I'd drop it down to 1 to 16G or so but that's just me. > work_mem: 110Mb (was 220Mb) 110MB*1500connections*1, 2, or 3 sorts per query == disaster. Drop this down as well. If you have ONE query that needs a lot, create a user for that query, alter that user for bigger work_mem and then use it only for that big query. > maintenance_work_mem: 256Mb > effective_cache_size: 340Gb > > The database is running under postgresql 9.3.9 on an Ubuntu Server 14.04 LTS > (build 3.13.0-55-generic) Good kernel. OK so you've got 80 cores. Have you checked zone_reclaim_mode? Under no circumstances should that ever be turned on on a database server. if "sysctl -a|grep zone" returns: vm.zone_reclaim_mode = 1 then use /etc/sysctl.conf to set it to 0. It's a silent killer and it will make your machine run REALLY slow for periods of 10 to 30 minutes for no apparent reason. But first and foremost GET A POOLER in the loop NOW. Not having one is making your machine extremely vulnerable to overload, and making it impossible for you to manage it. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
