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 <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 <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
