Re: Some tuning suggestions on a Red Hat 6.7 - PG 9.5.3 production environment

Dear list,

I’m 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 “SELECT … INTO” and “INSERT”, no “UPDATE”), 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 = 30

shared_buffers = 32GB

work_mem = 256MB

maintenance_work_mem = 4GB

effective_io_concurrency = 30

checkpoint_completion_target = 0.9

random_page_cost = 2.0

effective_cache_size = 48GB

default_statistics_target = 1000

autovacuum is on and the collation is ‘C’.

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’t 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 … 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 “dmesg” 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’t 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