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 1sg3hY-00F0Hl-OK for pgsql-sql@arkaria.postgresql.org; Mon, 19 Aug 2024 14:49:44 +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 1sg3hW-004hi2-42 for pgsql-sql@arkaria.postgresql.org; Mon, 19 Aug 2024 14:49:42 +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 1sg3hV-004hhk-Oz for pgsql-sql@lists.postgresql.org; Mon, 19 Aug 2024 14:49:42 +0000 Received: from mail-ed1-f48.google.com ([209.85.208.48]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sg3hS-000Sgs-Nj for pgsql-sql@lists.postgresql.org; Mon, 19 Aug 2024 14:49:41 +0000 Received: by mail-ed1-f48.google.com with SMTP id 4fb4d7f45d1cf-5bed05c0a2fso3434542a12.3 for ; Mon, 19 Aug 2024 07:49:39 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724078978; x=1724683778; 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=m0eJTCbEzqKQLdGN671N5a83d4Wwdn3yU10LsfrpMgI=; b=sE7ZOinnsl2jBFt3qSPOJwIA0RTbXeSod91mF+vn4v1IlFGdATCgUeqQq/Tkjazu3r u/7yzxEynSppZv33XycwsH6/EHDGflO9PfJP5DJkA8FHs8kfHP2vG+7gV1lX9b/XdUCY /13iL7wizM3ad3EV3EpU8LwHbGWiLN99ektqWO2I2GsVtRZ0h+WODkw4MmmsNUisQJZf BnWU/7AQaQZ6h6uP6p+9ieYnQvMq2K6BaxFC8gCzlGiQU27UtBlp4GDm2WGtqrRStJOb 7Zy5wWPwmpevc6z3w283c9AsKAarJ805J2loccBBJjyFte+PhL//B2E2pMEG/ZVtkkRh Uzog== X-Gm-Message-State: AOJu0YyJV0L4QVjqcBzvBxQ5uuN6eJeXz8H0YdeLu/wOXfJEhGP3gmQ9 8mj2gVVweGvi60MSbQy1DgB29uWzrLh28GJbk1Wmk1MGsH9bgsv1VfvtA2Ko X-Google-Smtp-Source: AGHT+IEHeNQHyrx6Lo/ih3ARzwydIKJPk1rOX2Cwcb9LSvXDgvrp276qk/alLeR1lwAPq9BdIbqGbA== X-Received: by 2002:a17:907:c893:b0:a77:c043:5b5a with SMTP id a640c23a62f3a-a8392951b74mr807365866b.39.1724078977757; Mon, 19 Aug 2024 07:49:37 -0700 (PDT) Received: from mail-wm1-f49.google.com (mail-wm1-f49.google.com. [209.85.128.49]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-a838394730dsm649410466b.176.2024.08.19.07.49.37 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 19 Aug 2024 07:49:37 -0700 (PDT) Received: by mail-wm1-f49.google.com with SMTP id 5b1f17b1804b1-428119da952so35327845e9.0 for ; Mon, 19 Aug 2024 07:49:37 -0700 (PDT) X-Received: by 2002:a5d:4f0f:0:b0:362:8201:fa3 with SMTP id ffacd0b85a97d-3719465e8a6mr9180301f8f.34.1724078977429; Mon, 19 Aug 2024 07:49:37 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Steve Midgley Date: Mon, 19 Aug 2024 07:49:24 -0700 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Powerfailure caused a reduction in INSERT performance of version 12 database. To: Frank Komsic Cc: pgsql-sql Content-Type: multipart/alternative; boundary="00000000000003beca06200a6b2f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000003beca06200a6b2f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Aug 19, 2024, 7:34=E2=80=AFAM Frank Komsic wrote: > HI, > > > > Not sure if this is the place to write to. > > > > We have a database running on version 12.2 of postgresql. We have > experienced an extended power failure and our backup systems did not work > as intended resulting in a sudden shutdown of the database server (Window= s > 2012). After restarting the system it had performed some automated > recovery and the database was functional. Unfortunately, the database > speed had slowed down significantly, particularly in the INSERT commands = in > an UploadData application we have on our production machines that uploads > 139 columns of data. > > > > The data is inserted into partitioned tables and there is a trigger > function updating additional tables. I have noticed that this additional > table although small in records (2500 records with 18 columns) it take > about 4-6 seconds to visualize the table using PGADMIN 8.10. The data in > this table is being updated by multiple UlpadData applications. So the > last 10 records the date columns are constantly being updated. > > > > Visualizing the data before the latest 15 records it appears withing 1 > second or less. The latest 15 records can take 2-4 seconds to visualize. > Then visualizing the entire table take 5-7 seconds. This is too long if > this occurs for every record that is inserted. If every record is 2 > seconds of data, the application can never keep up with the updates. > > > > Performed a full vacuum on this table with a little improvement. > > > > Is there anything I can do to see what is going on? > > > > Thank you > > > > Frank Komsic > > Shoei Canada Coproration > > 514-949-6319 > > > > I'm far from an expert in this area but running explain it explain analyz= e seems like a useful thing to share with the group. Then I wonder if running vacuum analyze would be useful? Maybe the planner is doing something weird. I'd also check if you lost any indexes you need during the bad day? Also being sure your system performance stats are correct - are you using all the cores and ram that you expect? Is the disk temporary space and swap performing normally? Are other tables unaffected, somewhat affected or in the same situation as this table. I'd recommend, if your environment can tolerate debugging, with reuploading this table's data into an identical table and see if the problem exists there too. Also if you dump the entire database can you reload in a new server and replicate there? I hope this helps, Steve > > --00000000000003beca06200a6b2f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Mon, Aug 19, 2024, 7:34=E2=80=AFAM Frank K= omsic <komsicf@shoeicanada.co= m> wrote:

HI,

=C2=A0

Not sure if this is the place to write to.=

=C2=A0

We have a database running on version 12.2 of postgr= esql.=C2=A0 We have experienced an extended power failure and our backup sy= stems did not work as intended resulting in a sudden shutdown of the databa= se server (Windows 2012).=C2=A0 After restarting the system it had performed some automated recovery and the database was f= unctional.=C2=A0 Unfortunately, the database speed had slowed down signific= antly, particularly in the INSERT commands in an UploadData application we = have on our production machines that uploads 139 columns of data.

=C2=A0

The data is inserted into partitioned tables and the= re is a trigger function updating additional tables.=C2=A0 I have noticed t= hat this additional table although small in records (2500 records with 18 c= olumns) it take about 4-6 seconds to visualize the table using PGADMIN 8.10.=C2=A0 The data in this table is being update= d by multiple UlpadData applications.=C2=A0 So the last 10 records the date= columns are constantly being updated.

=C2=A0

Visualizing the data before the latest 15 records it= appears withing 1 second or less.=C2=A0 The latest 15 records can take 2-4= seconds to visualize.=C2=A0 Then visualizing the entire table take 5-7 sec= onds.=C2=A0 This is too long if this occurs for every record that is inserted.=C2=A0 If every record is 2 seconds of data, the a= pplication can never keep up with the updates.

=C2=A0

Performed a full vacuum on this table with a little = improvement.=C2=A0

=C2=A0

Is there anything I can do to see what is going on?<= u>

=C2=A0

Thank you

=C2=A0

Frank Komsic

Shoei Canada Coproration

514-949-6319

=C2=A0

I'm far from an expert in this area but running explain it ex= plain analyze seems like a useful thing to share with the group. Then I won= der if running vacuum analyze would be useful? Maybe the planner is doing s= omething weird.=C2=A0

I&= #39;d also check if you lost any indexes you need during the bad day?=C2=A0=

Also being sure your sy= stem performance stats are correct - are you using all the cores and ram th= at you expect? Is the disk temporary space and swap performing normally?=C2= =A0

Are other tables una= ffected, somewhat affected or in the same situation as this table.=C2=A0

I'd recommend, if your= environment can tolerate debugging, with reuploading this table's data= into an identical table and see if the problem exists there too.=C2=A0

Also if you dump the entire= database can you reload in a new server and replicate there?=C2=A0

I hope this helps,=C2=A0
<= div dir=3D"auto">Steve=C2=A0
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">


--00000000000003beca06200a6b2f--