Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t0PxP-00BJtb-Go for pgsql-general@arkaria.postgresql.org; Mon, 14 Oct 2024 18:38:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t0PxN-00FykM-24 for pgsql-general@arkaria.postgresql.org; Mon, 14 Oct 2024 18:38:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t0PxM-00FykD-LX for pgsql-general@lists.postgresql.org; Mon, 14 Oct 2024 18:38:13 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t0PxJ-0013CB-ST for pgsql-general@lists.postgresql.org; Mon, 14 Oct 2024 18:38:12 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-a991fedbd04so447445466b.3 for ; Mon, 14 Oct 2024 11:38:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1728931089; x=1729535889; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=NRPJgmDPXP7SDb6YqKuxXSTQFlNBm25oQKX4imSAIjA=; b=CGK9zPpFuuJyfxG0hqPbzSRCcuG/NfDinD4JvSD5EJCDAdDbTUq6m+lo5lc1Y88MMY mxTAbv3TAhKWD57R3QLWoBMhLhGd2XbsmW/JtJnaG8GEI6JqO9iwGC6jxTBsUHHE0xDw n9Cj8AL+VUznz1lpdVvMy7Fttn7qRH3gZFWxxrUrtxM8TpyPjf+7Vk7XPggJCykh0rSn NIug5TrIS7nWEIfVjVOb+unSTzMEWjKxMMhjSbNRG5rS96rBwWxH/2MpsKHo81vrF+72 TLNfo66Z5yWgV/TK/8NNJ0vvdXRyqIxDBmW4nXulvirB4Q94cqarwVzLmJFplkkaKB5b 22Rw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1728931089; x=1729535889; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=NRPJgmDPXP7SDb6YqKuxXSTQFlNBm25oQKX4imSAIjA=; b=tPg1scBWpKdTpBqeBB7d8WRgvx0/MEAR7rG+DppoIoyJW8x5TBifscbJhzSo/zRBR+ TPK2Fu887HliNVcqbzwdBeEhZe2DK7EuESjKiq0i7ut73yYEXymQHrFwjT7AABF/f/MJ L85cLNnBXPkBcm2bPZyugkdAwVsxEWcRsFhsHaKYy1in6lOq1N8phdaVJ6QAJ9+rIiXZ 0rNYTnOQBrXA3Tj7/FYh+0XIDQ+F5TOlKM5gIMjBvHhHkTPJMiRdz/8av8jqYaN3YMc/ y1VNTwfEl1rdfk2f6OYlr1I4F5t+mdZRFGGCU85c51y3v347Q5JjEUc8XXatcvPCOUvY lqbw== X-Gm-Message-State: AOJu0YxpT7PgOM+kDcVpWENWqSK2hWdKSFLtf/DXbrVU6NHkyTdsjtUD 6Me23FA2476iTBFiGHBAu+f8xBaUDrBllLINuSIm0WkpqA3/JFJa14Psc/c9gAmEV3t63HXIPL5 JmlRz53PHiJI+LN0uyN7WjtF4Da+8n0As X-Google-Smtp-Source: AGHT+IH8P99GjjD/snErWOBdIPry6Ze5rL9z1Q5oSWWAWvuECUJ5LHjjocokuevc0R8EOXI5TzsAEQtpUVholTBy4VY= X-Received: by 2002:a05:6402:248f:b0:5c9:465f:4c88 with SMTP id 4fb4d7f45d1cf-5c95ac1d91fmr13094541a12.18.1728931089137; Mon, 14 Oct 2024 11:38:09 -0700 (PDT) MIME-Version: 1.0 From: Siraj G Date: Tue, 15 Oct 2024 00:07:57 +0530 Message-ID: Subject: Help in dealing with OOM To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000068ccea062474238a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000068ccea062474238a Content-Type: text/plain; charset="UTF-8" Hello Experts! My secondary instance has been unstable today. The service is crashing with Out of Memory. Please see below error (/var/log/postgresql/postgresql-2024-10-14.log): 10.2.52.50,2024-10-14 15:28:11 IST,686671,finance_revamp,finance_b2b,1,LOG: duration: 1148.527 ms statement: SELECT "tripschedule"."id", "tripschedule"."name", "tripschedule"."branch_id", "tripschedule"."route_id", "tripschedule"."route_name", "tripschedule"."bus_id", "tripschedule"."path", "tripschedule"."path_timings", "tripschedule"."recurring_days", "tripschedule"."start_time", "tripschedule"."end_time", "tripschedule"."start_date", "tripschedule"."end_date", "tripschedule"."created_at", "tripschedule"."created_by", "tripschedule"."shift", "tripschedule"."is_deleted", "tripschedule"."is_active", "tripschedule"."is_cancelled", "tripschedule"."branch_latitude", "tripschedule"."branch_longitude", "tripschedule"."polygon_id", "tripschedule"."is_after_noon_shift" FROM "tripschedule" INNER JOIN "bus" ON ("tripschedule"."bus_id" = "bus"."id") WHERE ("bus"."vehicle_no" = 'KA51AH1922' AND "tripschedule"."end_date" >= '2024-10-14'::date AND "tripschedule"."is_active" AND "tripschedule"."recurring_days" && ARRAY[1]::integer[] AND "tripschedule"."start_date" <= '2024-10-14'::date AND ("tripschedule"."start_time" BETWEEN '14:57:57.654167'::time AND '15:57:57.654167'::time OR "tripschedule"."end_time" BETWEEN '14:57:57.654167'::time AND '15:57:57.654167'::time OR ("tripschedule"."start_time" <= '15:27:57.654167'::time AND "tripschedule"."end_time" >= '15:27:57.654167'::time))) ORDER BY "tripschedule"."id" DESC LIMIT 1 10.2.52.22,2024-10-14 15:28:11 IST,686748,orchids_letseduvate_db,autoscaling,1,LOG: duration: 468.028 ms statement: SELECT (1) AS "a" FROM "test" INNER JOIN "test_section_mapping" ON ("test"."id" = "test_section_mapping"."test_id") INNER JOIN "test_subjects" ON ("test"."id" = "test_subjects"."test_id") INNER JOIN "user_response" ON ("test"."id" = "user_response"."test_id") WHERE (("test"."test_date")::date >= '2024-10-14'::date AND ("test"."test_date")::date <= '2024-10-17'::date AND NOT "test"."is_delete" AND "test_section_mapping"."sectionmapping_id" IN (136364) AND "test_subjects"."subject_id" = 16 AND NOT "user_response"."is_delete" AND "user_response"."submitted_by_id" = 61725) LIMIT 1 ,2024-10-14 15:28:11 IST,2334064,,,8,LOG: checkpointer process (PID 2334587) was terminated by signal 9: Killed ,2024-10-14 15:28:11 IST,2334064,,,9,LOG: terminating any other active server processes 10.2.52.50,2024-10-14 15:28:11 IST,686752,mcollege_letseduvate_db,finance_b2b,1,WARNING: terminating connection because This is from the OS log (/var/log/kern.log): oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.sli ce/system-postgresql.slice/postgresql@12-main.service ,task=postgres,pid=2334587,uid=114 494 Oct 14 09:58:10 gce-k12-prod-as1-erp-pg-secondary kernel: [6905020.514569] Out of memory: Killed process 2334587 (postgres) total-vm:26349584kB, anon-rss:3464kB, file-rss:0kB, shmem-rs s:21813032kB, UID:114 pgtables:49024kB oom_score_adj:0 Regards Siraj --00000000000068ccea062474238a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello Experts!

My secondary instanc= e has been unstable today. The service is crashing with Out of Memory. Plea= se see below error (/var/log/postgresql/postgresql-2024-10-14.log):

10.2.52.50,2024-10-14 15:28:11 IST,686671,finance_revamp,= finance_b2b,1,LOG: =C2=A0duration: 1148.527 ms =C2=A0statement: SELECT &quo= t;tripschedule"."id", "tripschedule"."name&qu= ot;, "tripschedule"."branch_id", "tripschedule&quo= t;."route_id", "tripschedule"."route_name", &= quot;tripschedule"."bus_id", "tripschedule"."= path", "tripschedule"."path_timings", "tripsc= hedule"."recurring_days", "tripschedule"."sta= rt_time", "tripschedule"."end_time", "tripsch= edule"."start_date", "tripschedule"."end_date= ", "tripschedule"."created_at", "tripschedule= "."created_by", "tripschedule"."shift", = "tripschedule"."is_deleted", "tripschedule".&= quot;is_active", "tripschedule"."is_cancelled", &q= uot;tripschedule"."branch_latitude", "tripschedule"= ;."branch_longitude", "tripschedule"."polygon_id&q= uot;, "tripschedule"."is_after_noon_shift" FROM "t= ripschedule" INNER JOIN "bus" ON ("tripschedule".&= quot;bus_id" =3D "bus"."id") WHERE ("bus"= ;."vehicle_no" =3D 'KA51AH1922' AND "tripschedule&qu= ot;."end_date" >=3D '2024-10-14'::date AND "trips= chedule"."is_active" AND "tripschedule"."recu= rring_days" && ARRAY[1]::integer[] AND "tripschedule"= ;."start_date" <=3D '2024-10-14'::date AND ("trip= schedule"."start_time" BETWEEN '14:57:57.654167'::ti= me AND '15:57:57.654167'::time OR "tripschedule"."en= d_time" BETWEEN '14:57:57.654167'::time AND '15:57:57.6541= 67'::time OR ("tripschedule"."start_time" <=3D &= #39;15:27:57.654167'::time AND "tripschedule"."end_time&= quot; >=3D '15:27:57.654167'::time))) ORDER BY "tripschedul= e"."id" DESC LIMIT 1
10.2.52.22,2024-10-14 15:28:11 IST,6= 86748,orchids_letseduvate_db,autoscaling,1,LOG: =C2=A0duration: 468.028 ms = =C2=A0statement: SELECT (1) AS "a" FROM "test" INNER JO= IN "test_section_mapping" ON ("test"."id" =3D= "test_section_mapping"."test_id") INNER JOIN "tes= t_subjects" ON ("test"."id" =3D "test_subject= s"."test_id") INNER JOIN "user_response" ON ("= ;test"."id" =3D "user_response"."test_id"= ;) WHERE (("test"."test_date")::date >=3D '2024-= 10-14'::date AND ("test"."test_date")::date <=3D= '2024-10-17'::date AND NOT "test"."is_delete" = AND "test_section_mapping"."sectionmapping_id" IN (1363= 64) AND "test_subjects"."subject_id" =3D 16 AND NOT &qu= ot;user_response"."is_delete" AND "user_response".= "submitted_by_id" =3D 61725) LIMIT 1
,2024-10-14 15:28:11 IST,= 2334064,,,8,LOG: =C2=A0checkpointer process (PID 2334587) was terminated by= signal 9: Killed
,2024-10-14 15:28:11 IST,2334064,,,9,LOG: =C2=A0termin= ating any other active server processes
10.2.52.50,2024-10-14 15:28:11 I= ST,686752,mcollege_letseduvate_db,finance_b2b,1,WARNING: =C2=A0terminating = connection because

This is from the OS log (/var/l= og/kern.log):

oom-kill:constraint=3DCONSTRAINT_NONE,nod= emask=3D(null),cpuset=3D/,mems_allowed=3D0,global_oom,task_memcg=3D/system.= sli =C2=A0 =C2=A0 =C2=A0ce/system-postgresql.slice/postgresql@12-main.servi= ce,task=3Dpostgres,pid=3D2334587,uid=3D114
=C2=A0 494 Oct 14 09:58:= 10 gce-k12-prod-as1-erp-pg-secondary kernel: [6905020.514569] Out of memory= : Killed process 2334587 (postgres) total-vm:26349584kB, anon-rss:3464kB, f= ile-rss:0kB, shmem-rs =C2=A0 =C2=A0 =C2=A0s:21813032kB, UID:114 pgtables:49= 024kB oom_score_adj:0

Regards
Siraj=C2= =A0
--00000000000068ccea062474238a--