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 1tfMpu-001qTh-Ao for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 17:35:46 +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 1tfMpt-006M8r-59 for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 17:35:45 +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 1tfMps-006M8j-PD for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 17:35:44 +0000 Received: from mail-io1-xd2e.google.com ([2607:f8b0:4864:20::d2e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tfMpp-003Gxx-3D for pgsql-general@postgresql.org; Tue, 04 Feb 2025 17:35:44 +0000 Received: by mail-io1-xd2e.google.com with SMTP id ca18e2360f4ac-844bff5ba1dso400599239f.1 for ; Tue, 04 Feb 2025 09:35:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738690540; x=1739295340; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=18kJ+VZk5+q7U8OJROCd0jVHnnZnObMi9pgTPhbIW38=; b=hxiQWr9Uu5hFYqlyXR8ZCiWbAcX1WYQ+hm9kf8wRQCu0zKyw3+Ap8/aKqHi346QHcp fsAci8LhNXniR6P+zhPT8D4B/x9VC4PCk7B8DFoKJj7Y36MB7XX06D5AIQcjovrf+ExF KmLBlnTD+V3ANeSvEbFpNHlYyKdGrmmbObDn0FJmqj81xIPCw1UUcF7xPB3sCLKPpe60 yh8w/yCRvD0C6zD6Gq7zyyyjv3ZM2KKv51g6Ur17gIKthiY4Z37f5EKIURXqx5z1XHxN KcCvMQrRBfmfM8gZO50mDk/MqKqXKE4JUPh9viPRUx6/Drcw9khvnj44WBiFmXFTDrYB RC0g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738690540; x=1739295340; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=18kJ+VZk5+q7U8OJROCd0jVHnnZnObMi9pgTPhbIW38=; b=uJFmGMATfArStN7nuqWVewS3OyiKsIyk4p/I+ECvq+VL57186Bu6toKfmES25xWEEV Z/LiKc4V4xyujugJuRjqqBGbL7WrLjSwkIY+RNHSzSHgQr0N3jJwNENPNk/7YRpPVfdr U+teWx3FiPuQmGzTxpA9sE2L/JnN1B+poPBN07bJqaHgNwmhrOVusvyznn6dsIRaMvck XnTe3BQHT+PzeV7fC+xQ3emW4J44ovbuTQQRB8e0lS+UnbOp9LwB8lKP1upGT37UJ1aU jWgtzaQP4KwvCJU8tMv9aoPXbJYzNFw8STThMfKQPZcu4/Jo0TD025m5VpoqBATrDjjJ 91lA== X-Forwarded-Encrypted: i=1; AJvYcCVGIJt/dKGs2r6Fx4UXucGVoPM4vpiF9/wJmoXsJbW7HPyVUGC6nsshJQ8nyvklsCDdaGqdb82yEPOxp+0q@postgresql.org X-Gm-Message-State: AOJu0Yzk00ARtKt7Iv42nr1PFLfwXraVukrNmaxfE5/qQH+cI84rrllY BEZB2/izZRMWgJOY/dNPj9aPFOcQ8TMPcZuIw+5hWTFHb05sZGs9M30PqZqxVVzrl6AehI5bpnb /LyWdcAwyTuwcIybKDW1xiKKdDpM= X-Gm-Gg: ASbGncufRYsvIZHhO+5Mquwhk+UVu2eIULoMHIiUjbSs4MoQpUstZqVrhsQE2yzrC6a ws1VNItnGRPiP5OSb2wcweYOK44uABaCy6fql4GdtlxBEXNxyaS+gdcBlabhkVDIZcDXTqNt6Gw == X-Google-Smtp-Source: AGHT+IG5zzP6s9eNckmNWWktJN/gIFs6Ncv33WPin3aJzxLhWxzMb++oqL/Od32crg4ILMdBLk4BJZ9nfQr543MJgxU= X-Received: by 2002:a05:6e02:4813:b0:3d0:2280:ce30 with SMTP id e9e14a558f8ab-3d02280d0abmr119917225ab.8.1738690540537; Tue, 04 Feb 2025 09:35:40 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Rakesh Nashine Date: Tue, 4 Feb 2025 23:05:29 +0530 X-Gm-Features: AWEUYZl9fKpxuE7AyLHoalbC7hg-4Qi85WwOlMirBAxCC9-Z92iFjiIHLlh9qVU Message-ID: Subject: Re: Postgres performance issue with High CPU usage To: Adrian Klaver Cc: KK CHN , pgsql-general Content-Type: multipart/alternative; boundary="0000000000000b0ed3062d5470d3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000b0ed3062d5470d3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Just validate your environment once via the below states ... -top -p $(pgrep -d',' postgres) -Review the PostgreSQL logs (typically located in /var/log/postgresql/ or /var/lib/pgsql/data/pg_log/) -- check if any long running active query running via below query SELECT pid, query, state, start_time, age(now(), start_time) AS duration FROM pg_stat_activity WHERE state =3D 'active' ORDER BY duration DESC; -- check if any locks persist via below query SELECT pid, relation::regclass, mode, granted, query FROM pg_locks l JOIN pg_stat_activity a ON l.pid =3D a.pid WHERE l.granted =3D 'f'; -- Analyze index if require via below qeury EXPLAIN ANALYZE ; --check if any autovacuum is in progress, that is also causes high CPU SELECT * FROM pg_stat_autovacuum; --Any background programs SELECT * FROM pg_stat_bgwriter; Thanks Rakesh On Tue, Feb 4, 2025 at 10:44=E2=80=AFPM Adrian Klaver wrote: > On 2/4/25 09:11, KK CHN wrote: > > List, > > > > Could someone point out how can I trace what causes the edb-postgres > > process with %CPU usage reaching 73 to 80.1 percentage in this box. > > What's wrong with the server VM.. > What version of EDB database are you using? > > If it is not their install of the community version available here: > > https://www.postgresql.org/download/windows/ > > then this is probably a question for their tech support. > > > > > The clients connecting the databases experience slow responses .. > > > > The top out put of the DB server is pasted below.. > > > > > > Any hints much appreciated to trouble shoot this unusual load on the > > server instance. > > > > Thank you, > > Krishane. > > > > > > > > > > I > > > > top - 22:34:13 up 61 days, 7:43, 3 users, load average: 8.76, 9.92, > 19.12 > > Tasks: 697 total, 9 running, 687 sleeping, 1 stopped, 0 zombie > > %Cpu(s): 34.3 us, 18.7 sy, 0.0 ni, 46.2 id, 0.0 wa, 0.5 hi, 0.3 si, > > 0.0 st > > MiB Mem : 31837.6 total, 1296.9 free, 11447.0 used, 27969.1 > buff/cache > > MiB Swap: 8060.0 total, 7400.0 free, 660.0 used. 20390.5 avail > Mem > > > > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ > > COMMAND > > 4007576 enterpr+ 20 0 8944520 951764 941212 R 80.1 2.9 0:57.24 > > edb-postgres > > 4035945 enterpr+ 20 0 8943056 472108 463068 R 79.7 1.4 0:11.48 > > edb-postgres > > 4020721 enterpr+ 20 0 8925752 702836 689644 S 73.1 2.2 0:27.99 > > edb-postgres > > 4036409 enterpr+ 20 0 8916540 368084 363772 S 43.5 1.1 0:01.31 > > edb-postgres > > 4036410 enterpr+ 20 0 8928512 481808 469852 S 32.2 1.5 0:03.52 > > edb-postgres > > 442070 enterpr+ 20 0 8908184 181248 180096 S 13.0 0.6 1391:16 > > edb-postgres > > 4041239 enterpr+ 20 0 8921696 995.0m 986.1m S 9.0 3.1 0:00.75 > > edb-postgres > > 4041225 enterpr+ 20 0 8921856 > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > > > --=20 Thanks & Regards Rakesh Nashine --0000000000000b0ed3062d5470d3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Just validate your environment=C2=A0once via the belo= w states ...=C2=A0

-top -p $(pgrep -d',' postgr= es)

-Review the PostgreSQL logs (typically located in /var/log/postg= resql/ or /var/lib/pgsql/data/pg_log/)

-- check if any long running = active query running via below query
SELECT pid, query, state, start_ti= me, age(now(), start_time) AS duration
FROM pg_stat_activity
WHERE st= ate =3D 'active'
ORDER BY duration DESC;

-- check if any = locks persist via below query
SELECT pid, relation::regclass, mode, gra= nted, query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid =3D a.pi= d
WHERE l.granted =3D 'f';

-- Analyze index if require vi= a below qeury

EXPLAIN ANALYZE <your_query_here>;

--chec= k if any autovacuum is in progress, that is also causes high CPU

SEL= ECT * FROM pg_stat_autovacuum;

--Any background programs

SEL= ECT * FROM pg_stat_bgwriter;

Thanks=C2=A0
Rakesh=C2= =A0

On Tue, Feb 4, 2025 at 10:44=E2=80=AFPM Adri= an Klaver <adrian.klaver@ak= laver.com> wrote:
On 2/4/25 09:11, KK CHN wrote:
> List,
>
> Could someone point out how can I trace what causes the edb-postgres= =C2=A0
> process with=C2=A0 %CPU usage reaching 73 to 80.1 percentage=C2=A0 in = this box.=C2=A0
>=C2=A0 =C2=A0What's wrong with the server VM..
What version of EDB database are you using?

If it is not their install of the community version available here:

https://www.postgresql.org/download/windows/

then this is probably a question for their tech support.

>
> The clients connecting=C2=A0the databases experience=C2=A0 slow respon= ses ..
>
> The top out put=C2=A0of the DB server is=C2=A0 pasted below..
>
>
> Any hints=C2=A0 much appreciated to trouble shoot=C2=A0this unusual=C2= =A0load on the
> server instance.
>
> Thank you,
> Krishane.
>
>
>
>
> I
>
> top - 22:34:13 up 61 days, =C2=A07:43, =C2=A03 users, =C2=A0load avera= ge: 8.76, 9.92, 19.12
> Tasks: 697 total, =C2=A0 9 running, 687 sleeping, =C2=A0 1 stopped, = =C2=A0 0 zombie
> %Cpu(s): 34.3 us, 18.7 sy, =C2=A00.0 ni, 46.2 id, =C2=A00.0 wa, =C2=A0= 0.5 hi, =C2=A00.3 si,
>=C2=A0 =C2=A00.0 st
> MiB Mem : =C2=A031837.6 total, =C2=A0 1296.9 free, =C2=A011447.0 used,= =C2=A027969.1 buff/cache
> MiB Swap: =C2=A0 8060.0 total, =C2=A0 7400.0 free, =C2=A0 =C2=A0660.0 = used. =C2=A020390.5 avail Mem
>
>=C2=A0 =C2=A0 =C2=A0 PID USER =C2=A0 =C2=A0 =C2=A0PR =C2=A0NI =C2=A0 = =C2=A0VIRT =C2=A0 =C2=A0RES =C2=A0 =C2=A0SHR S =C2=A0%CPU =C2=A0%MEM =C2=A0= =C2=A0 TIME+
> COMMAND
> 4007576 enterpr+ =C2=A020 =C2=A0 0 8944520 951764 941212 R =C2=A080.1 = =C2=A0 2.9 =C2=A0 0:57.24
> edb-postgres
> 4035945 enterpr+ =C2=A020 =C2=A0 0 8943056 472108 463068 R =C2=A079.7 = =C2=A0 1.4 =C2=A0 0:11.48
> edb-postgres
> 4020721 enterpr+ =C2=A020 =C2=A0 0 8925752 702836 689644 S =C2=A073.1 = =C2=A0 2.2 =C2=A0 0:27.99
> edb-postgres
> 4036409 enterpr+ =C2=A020 =C2=A0 0 8916540 368084 363772 S =C2=A043.5 = =C2=A0 1.1 =C2=A0 0:01.31
> edb-postgres
> 4036410 enterpr+ =C2=A020 =C2=A0 0 8928512 481808 469852 S =C2=A032.2 = =C2=A0 1.5 =C2=A0 0:03.52
> edb-postgres
>=C2=A0 =C2=A0442070 enterpr+ =C2=A020 =C2=A0 0 8908184 181248 180096 S = =C2=A013.0 =C2=A0 0.6 =C2=A0 1391:16
> edb-postgres
> 4041239 enterpr+ =C2=A020 =C2=A0 0 8921696 995.0m 986.1m S =C2=A0 9.0 = =C2=A0 3.1 =C2=A0 0:00.75
> edb-postgres
> 4041225 enterpr+ =C2=A020 =C2=A0 0 8921856

--
Adrian Klaver
adrian.klave= r@aklaver.com





--
Thanks & Regards
Rakesh Nashine
--0000000000000b0ed3062d5470d3--