1

Context:

I have a process that runs daily, with different amounts of data, if the process is on the weekday (process last 30 days), for weekends (process last year). The input is of 10 tables of approximately 50 GB each and runs about 50 functions (we only use this PostgreSQL to run this process). The main problem is that the last weekend we got an error for a specific function (I don't know if this is related but CLoud SQL updated my version from 9.6.xx to 9.6.19)

ERROR: Out of shared memory.

Details:

Version: PostgreSQL 9.6.19 on x86_64-pc-linux-gnu, compiled by Debian clang version 10.0.1 , 64-bit

vCPUs: 8

Memory: 24 GB

SSD Storage: 1.27 TB

Flags: Used as a reference PGTune

work_mem:51200

temp_file_limit: 309556884

maintenance_work_mem: 2097152

checkpoint_completion_target: 0.9

default_statistics_target: 500

random_page_cost: 1.1

max_wal_size: 16777216

max_connections: 20

Research:

Looking for the error I found that it's associated with max_locks_per_transaction, which depends on max_connections so my plan is to increase our max_connections to 50 and decrease work_mem to 15 MB (PostgreSQL complaining about shared memory, but shared memory seems to be OK)

Question:

Taking into consideration that not all the flags that PG Tune tells me to modify are available in Cloud SQL to change, So I don't know if this is the correct setup for the flags. Can somebody give me some advice to tunning it correctly, and if I can solve this problem making that modification to the flags or I should investigate another thing?

Explain Analyze Of the function that crashed:

Insert on cita_step1  (cost=197217712.76..202239735.56 rows=346346 width=604) (actual time=6175091.981..6175091.981 rows=0 loops=1)
  ->  Subquery Scan on i  (cost=197217712.76..202239735.56 rows=346346 width=604) (actual time=5778467.883..5916796.283 rows=17298114 loops=1)
        Filter: (i.seq = 1)
        Rows Removed by Filter: 48623005
        ->  WindowAgg  (cost=197217712.76..201373869.56 rows=69269280 width=651) (actual time=5778467.827..5904858.709 rows=65921119 loops=1)
              ->  Sort  (cost=197217712.76..197390885.96 rows=69269280 width=643) (actual time=5778466.235..5850126.963 rows=65921119 loops=1)
                    Sort Key: apo.id, apo.modifiedon DESC
                    Sort Method: external sort  Disk: 36489752kB
                    ->  Group  (cost=120913670.25..128533291.05 rows=69269280 width=643) (actual time=4375124.867..5043473.108 rows=65921119 loops=1)
                          Group Key: apo.id, apo.serviceid, apo.centerid, apo.patientld, apo.resourceld, ((apo.datatimefrom)::date), ((apo.datatimefrom)::time without time zone), ((apo.datatimeto)::time without time zone), apo.duration, apo.isoverapacity, apo.numberofslots, apo.cancelled, (upper((pati.fullname)::text)), ("substring"(pati.documentnumber, 0, length(pati.documentnumber))), pati.documentnumber, (upper((reso.name)::text)), ("substring"(reso.documentnumber, 0, length(reso.documentnumber))), reso.documentnumber, (upper((cent.name)::text)), serv.areaid, serv.specialityid, (upper((serv.name)::text)), (upper((spec.name)::text)), (upper((area."Name")::text)), pati.birthdate, pati.gender, apo.modifiedon
                          ->  Sort  (cost=120913670.25..121086843.45 rows=69269280 width=643) (actual time=4375124.814..5002165.728 rows=67907365 loops=1)
                                Sort Key: apo.id, apo.serviceid, apo.centerid, apo.patientld, apo.resourceld, ((apo.datatimefrom)::date), ((apo.datatimefrom)::time without time zone), ((apo.datatimeto)::time without time zone), apo.duration, apo.isoverapacity, apo.numberofslots, apo.cancelled, (upper((pati.fullname)::text)), ("substring"(pati.documentnumber, 0, length(pati.documentnumber))), pati.documentnumber, (upper((reso.name)::text)), ("substring"(reso.documentnumber, 0, length(reso.documentnumber))), reso.documentnumber, (upper((cent.name)::text)), serv.areaid, serv.specialityid, (upper((serv.name)::text)), (upper((spec.name)::text)), (upper((area."Name")::text)), pati.birthdate, pati.gender, apo.modifiedon
                                Sort Method: external merge  Disk: 37594160kB
                                ->  Hash Left Join  (cost=45096896.73..52229248.55 rows=69269280 width=643) (actual time=1231645.730..2246291.363 rows=67907365 loops=1)
                                      Hash Cond: ((apo.serviceid)::text = (serv.id)::text)
                                      ->  Hash Left Join  (cost=45096644.60..48419390.35 rows=69228414 width=446) (actual time=1231628.606..1679733.083 rows=67689274 loops=1)
                                            Hash Cond: ((apo.resourceld)::text = (reso.id)::text)
                                            ->  Hash Left Join  (cost=45096214.19..47380550.70 rows=69225020 width=410) (actual time=1231614.631..1648543.126 rows=67649410 loops=1)
                                                  Hash Cond: ((apo.centerid)::text = (cent.id)::text)
                                                  ->  Merge Right Join  (cost=45096210.03..46342194.32 rows=69225020 width=382) (actual time=1231613.700..1621200.386 rows=67649410 loops=1)
                                                        Merge Cond: ((pati.id)::text = (apo.patientld)::text)
                                                        ->  Sort  (cost=2201286.92..2221567.36 rows=8112174 width=113) (actual time=75191.673..79525.023 rows=8112158 loops=1)
                                                              Sort Key: pati.id
                                                              Sort Method: external sort  Disk: 999664kB
                                                              ->  Seq Scan on patientdto_test_clean pati  (cost=0.00..272168.74 rows=8112174 width=113) (actual time=1.630..7123.143 rows=8112158 loops=1)
                                                        ->  Materialize  (cost=42894919.06..43229031.52 rows=66822492 width=306) (actual time=1156422.000..1508256.407 rows=67649410 loops=1)
                                                              ->  Sort  (cost=42894919.06..43061975.29 rows=66822492 width=306) (actual time=1156421.963..1496273.748 rows=66822491 loops=1)
                                                                    Sort Key: apo.patientld
                                                                    Sort Method: external merge  Disk: 20659496kB
                                                                    ->  Seq Scan on citas_historico apo  (cost=0.00..5432002.92 rows=66822492 width=306) (actual time=1.253..78238.586 rows=66822491 loops=1)
                                                  ->  Hash  (cost=2.96..2.96 rows=96 width=65) (actual time=0.889..0.889 rows=96 loops=1)
                                                        Buckets: 1024  Batches: 1  Memory Usage: 18kB
                                                        ->  Seq Scan on centerdto_test_clean cent  (cost=0.00..2.96 rows=96 width=65) (actual time=0.821..0.848 rows=96 loops=1)
                                            ->  Hash  (cost=299.07..299.07 rows=10507 width=73) (actual time=13.931..13.931 rows=10507 loops=1)
                                                  Buckets: 16384  Batches: 1  Memory Usage: 1219kB
                                                  ->  Seq Scan on resourcedto_physiciandto_test_clean reso  (cost=0.00..299.07 rows=10507 width=73) (actual time=0.846..11.407 rows=10507 loops=1)
                                      ->  Hash  (cost=222.11..222.11 rows=2401 width=385) (actual time=13.357..13.357 rows=2401 loops=1)
                                            Buckets: 4096  Batches: 1  Memory Usage: 519kB
                                            ->  Hash Left Join  (cost=67.08..222.11 rows=2401 width=385) (actual time=7.439..12.322 rows=2401 loops=1)
                                                  Hash Cond: ((serv.specialityid)::text = (spec.id)::text)
                                                  ->  Hash Left Join  (cost=12.47..134.50 rows=2401 width=358) (actual time=1.776..5.637 rows=2401 loops=1)
                                                        Hash Cond: ((serv.areaid)::text = (area.id)::text)
                                                        ->  Seq Scan on servicedto_test_clean serv  (cost=0.00..89.01 rows=2401 width=140) (actual time=1.039..4.103 rows=2401 loops=1)
                                                        ->  Hash  (cost=11.10..11.10 rows=110 width=436) (actual time=0.715..0.716 rows=18 loops=1)
                                                              Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                                              ->  Seq Scan on areadto_test_clean area  (cost=0.00..11.10 rows=110 width=436) (actual time=0.705..0.707 rows=18 loops=1)
                                                  ->  Hash  (cost=37.60..37.60 rows=1360 width=64) (actual time=5.538..5.538 rows=1360 loops=1)
                                                        Buckets: 2048  Batches: 1  Memory Usage: 145kB
                                                        ->  Seq Scan on specialitydto_test_clean spec  (cost=0.00..37.60 rows=1360 width=64) (actual time=0.678..5.286 rows=1360 loops=1)
Planning time: 65.976 ms
Execution time: 6179107.200 ms
Improve this question
2
  • Welcome Diego! I edited your question to remove your biographical content, which should go into your profile and not questions, along with a few other improvements to your question. Be sure to check out the help center to learn some more of the DBA.SE specific etiquette that will help you be a good contributor to the community!
    – LowlyDBA - John M
    Dec 22, 2020 at 19:26
  • You should add your SQL statement and its execution plan. This message typically means that your statement is using a parallel plan that tries to allocate a large shared memory segment for inter-process communication.
    – Laurenz Albe
    Dec 23, 2020 at 3:18

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.