Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1c6ODI-0007Zg-Ul for pgsql-performance@arkaria.postgresql.org; Mon, 14 Nov 2016 20:50:49 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1c6ODI-0001Cd-8h for pgsql-performance@arkaria.postgresql.org; Mon, 14 Nov 2016 20:50:48 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1c6ODH-0001CF-7O for pgsql-performance@postgresql.org; Mon, 14 Nov 2016 20:50:47 +0000 Received: from mail-wm0-x233.google.com ([2a00:1450:400c:c09::233]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1c6ODD-0008Rh-JV for pgsql-performance@postgresql.org; Mon, 14 Nov 2016 20:50:45 +0000 Received: by mail-wm0-x233.google.com with SMTP id f82so123042096wmf.1 for ; Mon, 14 Nov 2016 12:50:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:subject:from:in-reply-to:date:cc:message-id:references :to; bh=y57s4M+6I4cwl4mwLuSv+W9rkiUvtDNeeqb6RhRJwLw=; b=DBH0D6/JHXdm2f9tZgUxufFKmrhrdOjcJDE1h9owDZuRujdxxd9KxbxF3+Er6bpR5x Pe5WGKqCZIrpfvrZe+zK61mncljLGGcCGRdp22aiBwcGgzJbNWGnpmpWt68fW4LOqvdY PaZLCw9piCOK3qcgBDWZxxXzkEHBHfStwGKooolBBdDQ4eHWoPxuaEN5ZhSHGD/8T5y6 DiJh3lc+gBj1nZo7zdC4TQ8yp6/vZgej+pSWG5L+S0WsT9Oa2cDG8K+sJN2syEcVjd1I VI6DZYla/yeMJLapKhkOVkhzbgezlC4Jh1QXX0As7iZcK5oGt2214ZYZv5qtvkRmCdB1 DNsA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to; bh=y57s4M+6I4cwl4mwLuSv+W9rkiUvtDNeeqb6RhRJwLw=; b=jcy93VYcllxLLuudHbuhBkOQjKoevJHr8lzLZgEMwmzfgdnyVX46OdTPts3UDyJKqQ Rlv1/5NMUfYqJrgdLDkGg+oR5c3iINQEnsJkUkMkWt7SYfstEv3OkXp++vncJOqzWalm OJEDlJoRewQjnexyBhVfHUeTNukBeOKVPIzGQrNMG/7c6wy3pgLja1ys6IMVpXxE7dtY tiuQloesIywoUjeYEml7AoaZMF9jb3Hrx89rJFE5jeO+fFmd7Gn5myOcdmFfD+k50HV7 EwE3Cj990hijmcmwwLsmxg9LoYwm5OIaEgBeoubdGRfR1myGcBGiENwYVGTbs1UKVHwd uoUg== X-Gm-Message-State: ABUngvcdk0M4uGocTYbTK/gJQsMd06G69IUufUy+9QZ1W5SIlSpAiFuY75hgg8PYYfCfLA== X-Received: by 10.28.227.9 with SMTP id a9mr259866wmh.85.1479156640457; Mon, 14 Nov 2016 12:50:40 -0800 (PST) Received: from [192.168.2.5] (2-230-140-245.ip202.fastwebnet.it. [2.230.140.245]) by smtp.gmail.com with ESMTPSA id u17sm268866wma.24.2016.11.14.12.50.39 (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Mon, 14 Nov 2016 12:50:39 -0800 (PST) Content-Type: multipart/alternative; boundary="Apple-Mail=_9DE739AC-7DC5-47CE-9571-3ED1FCA5DD21" Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\)) Subject: Re: Some tuning suggestions on a Red Hat 6.7 - PG 9.5.3 production environment From: Pietro Pugni In-Reply-To: Date: Mon, 14 Nov 2016 21:50:38 +0100 Cc: "pgsql-performance@postgresql.org" Message-Id: <00A79BA0-84E8-4F13-A46F-942B4046FB4D@gmail.com> References: To: domenico febbo X-Mailer: Apple Mail (2.3124) X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_9DE739AC-7DC5-47CE-9571-3ED1FCA5DD21 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Dear Domenico, I pushed a little hard on that because the virtualizer runs on a = distributed system composed by 7 clusters with more than 100 cores and = an enterprise storage. I know that usually effective_io_concurrency is = set based on the number of disks available in a RAID configuration = (minus the stripe disks), so I decided to push hard on this due to the = nature of the host machine. I have no much information about that = machine but I can investigate. Anyway, the OOM configuration described in the official documentation = prevents from killing the postmaster. I=E2=80=99m still wondering about the other configuration parameters, if = they are reasonable or can be tweaked to get more performance. Thank you for your support Pietro PS I made a typo in the configuration. max_connections is 20, not 30. > Il giorno 14 nov 2016, alle ore 18:36, domenico febbo = ha scritto: >=20 > dear Pietro,=20 > are you sure about >=20 > effective_io_concurrency =3D 30 >=20 > could you please explain the type of disk storage? >=20 >=20 > Il 14/Nov/2016 12:46, "Pietro Pugni" > ha scritto: > Dear list, > I=E2=80=99m looking for some guidelines on how to optimize the = configuration of a production database dedicated to a DWH application. > I run the application on different machines and have solved several = issues since now but am struggling on a production environment running = Red Hat 6.7 and PostgreSQL 9.5.3. > My application does a lot of reads and many writes (plain =E2=80=9CSELEC= T =E2=80=A6 INTO=E2=80=9D and =E2=80=9CINSERT=E2=80=9D, no = =E2=80=9CUPDATE=E2=80=9D), but on a order of magnitude lower than the = reads. > The work flow consists of two big blocks: an ETL phase and the = workloads on the data imported during the ETL phase. >=20 > The biggest schema has about 1.2 billions of rows distributed over a = ten of tables; many of those tables are partitioned and have indexes. At = the moment the database stores two schemas but I plan to add other three = schemas of similar size. >=20 > The machine is virtualized and has 8 CPUs at about 3GHz, 64GB of RAM = and 5TB of storage. It runs on Red Hat 6.7, kernel 2.6.x >=20 > The configuration changes I made so far are: > max_connections =3D 30 > shared_buffers =3D 32GB > work_mem =3D 256MB > maintenance_work_mem =3D 4GB > effective_io_concurrency =3D 30 > checkpoint_completion_target =3D 0.9 > random_page_cost =3D 2.0 > effective_cache_size =3D 48GB > default_statistics_target =3D 1000 >=20 > autovacuum is on and the collation is =E2=80=98C=E2=80=99. >=20 >=20 > The first issue I faced was about maintenance_work_mem because I set = it to 16GB and the server silently crashed during a VACUUM because I = didn=E2=80=99t consider that it could take up to autovacuum_max_workers = * maintenance_work_mem (roughly 48GB). So I lowered maintenance_work_mem = to 4GB and it did work. Should I set maintenance_work_mem to a smaller = value (1GB) after the ETL terminates or can I leave it at 4GB without = degrading the overall performance? >=20 > The second issue emerged during a intensive parallel query. I = implemented a splitter that parallelize certain kind of queries. There = were 8 similar queries running that was working on 8 overall disjoined = subsets of the same table; this table has roughly 4.5 millions of rows. = These queries uses SELECT DISTINCT, ORDER BY, OVER (PARTITION BY =E2=80=A6= ORDER BY) and COALESCE(). At a certain point the server crashed and I = found the following error in the logs:=20 >=20 > postgres server process was terminated by signal 9 killed >=20 > After some research, I found that probably it was the OOM killer. = Running =E2=80=9Cdmesg=E2=80=9D tells that effectively it was. Reading = the documentation and this answer on SO ( = http://stackoverflow.com/questions/16418173/psql-seems-to-timeout-with-lon= g-queries = ), I realized that probably the issue is due to a = misconfiguration. The value I set for this pg instance don=E2=80=99t = seem to be so wrong, except maybe from maintenance_work_mem. I will = certainly disable OOM as suggested by the official docs ( = https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX= -MEMORY-OVERCOMMIT = ) but was wondering if I could tune the = configuration a little better. Can someone give me some more advices? >=20 > I run the same application with different data (and workload) on other = machines, but they have different configurations (Ubuntu 16.0.4). On one = of them I previously disabled the virtual memory overcommit and never = experienced that issue, but the machine has 128GB of RAM. >=20 > I hope to have been clear enough. > Thank you everyone > Pietro --Apple-Mail=_9DE739AC-7DC5-47CE-9571-3ED1FCA5DD21 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
Dear Domenico,
I pushed = a little hard on that because the virtualizer runs on a distributed = system composed by 7 clusters with more than 100 cores and an enterprise = storage. I know that usually effective_io_concurrency is set based on = the number of disks available in a RAID configuration (minus the stripe = disks), so I decided to push hard on this due to the nature of the host = machine. I have no much information about that machine but I can = investigate.

Anyway, the OOM configuration described in the official = documentation prevents from killing the postmaster.

I=E2=80=99m still = wondering about the other configuration parameters, if they are = reasonable or can be tweaked to get more performance.

Thank you for your = support
 Pietro

PS I made a typo in the configuration. = max_connections is 20, not 30.


Il giorno 14 nov 2016, alle ore 18:36, = domenico febbo <mimmopasticcio@gmail.com> ha scritto:

dear Pietro,
are you sure about

effective_io_concurrency = =3D 30

could you please explain the type of = disk storage?


Il = 14/Nov/2016 12:46, "Pietro Pugni" <pietro.pugni@gmail.com> ha scritto:
Dear list,
I=E2=80=99m looking for some = guidelines on how to optimize the configuration of a production database = dedicated to a DWH application.
I run the = application on different machines and have solved several issues since = now but am struggling on a production environment running Red Hat 6.7 = and PostgreSQL 9.5.3.
My application does a lot of = reads and many writes (plain =E2=80=9CSELECT =E2=80=A6 INTO=E2=80=9D and = =E2=80=9CINSERT=E2=80=9D, no =E2=80=9CUPDATE=E2=80=9D), but on a order = of magnitude lower than the reads.
The work flow = consists of two big blocks: an ETL phase and the workloads on the data = imported during the ETL phase.

The biggest schema has = about 1.2 billions of rows distributed over a ten of tables; many of = those tables are partitioned and have indexes. At the moment the = database stores two schemas but I plan to add other three schemas of = similar size.

The machine is virtualized and has 8 CPUs at about 3GHz, 64GB = of RAM and 5TB of storage. It runs on Red Hat 6.7, kernel = 2.6.x

The = configuration changes I made so far are:
max_connections =3D 30
shared_buffers =3D = 32GB
work_mem =3D 256MB
maintenance_work_mem =3D 4GB
effective_io_concurrency =3D 30
checkpoint_completion_target =3D 0.9
random_page_cost =3D 2.0
effective_cache_size =3D 48GB
default_statistics_target =3D 1000

autovacuum is on and the collation is = =E2=80=98C=E2=80=99.


The first issue I faced = was about maintenance_work_mem because I set it to 16GB and the server = silently crashed during a VACUUM because I didn=E2=80=99t consider that = it could take up to autovacuum_max_workers * maintenance_work_mem = (roughly 48GB). So I lowered maintenance_work_mem to 4GB and it did = work. Should I set maintenance_work_mem to a smaller value = (1GB) after the ETL terminates or can I leave it at 4GB without = degrading the overall performance?

The second issue emerged during a = intensive parallel query. I implemented a splitter that parallelize = certain kind of queries. There were 8 similar queries running that was = working on 8 overall disjoined subsets of the same table; this table has = roughly 4.5 millions of rows. These queries uses SELECT DISTINCT, ORDER = BY, OVER (PARTITION BY =E2=80=A6 ORDER BY) and COALESCE(). At a certain = point the server crashed and I found the following error in the = logs: 

postgres server process was terminated by signal 9 = killed

After= some research, I found that probably it was the OOM killer. Running = =E2=80=9Cdmesg=E2=80=9D tells that effectively it was. Reading the = documentation and this answer on SO ( http://stackoverflow.com/questions/16418173/psql-seems-to-timeout-with-long-queries ), I = realized that probably the issue is due to a misconfiguration. The value = I set for this pg instance don=E2=80=99t seem to be so wrong, except = maybe from maintenance_work_mem. I will certainly disable OOM as = suggested by the official docs ( https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT ) but was wondering if I = could tune the configuration a little better. Can someone give me = some more advices?

I run the same application with different data (and workload) = on other machines, but they have different configurations (Ubuntu = 16.0.4). On one of them I previously disabled the virtual memory = overcommit and never experienced that issue, but the machine has 128GB = of RAM.

I hope = to have been clear enough.
Thank you = everyone
 Pietro

= --Apple-Mail=_9DE739AC-7DC5-47CE-9571-3ED1FCA5DD21--