From: Frank Komsic <komsicf@shoeicanada.com>
To: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: Powerfailure caused a reduction in INSERT performance of version 12
 database.
Thread-Topic: Powerfailure caused a reduction in INSERT performance of version
 12 database.
Thread-Index: AQHa8BmrA3Z6t2X3jkqdRcLXpvH5zw==
Date: Mon, 19 Aug 2024 14:33:04 +0000
Message-ID: 
 <BL3PR05MB9236F587AD7551AD63B2C65AB3812@BL3PR05MB9236.namprd05.prod.outlook.com>
Accept-Language: en-CA, en-US
Content-Language: en-CA
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?VWG5JqtUI2po31KLqz+/jdrV5Cg3j6/t+bMuRwtcLYXDYHNF4p1rJi5S1OyF?=
 =?us-ascii?Q?6lktrizckcg3WFVuOCSsedRP2Q6jd3mxcy6Kk+MiKzM92NeHswlvNrYrjn3s?=
 =?us-ascii?Q?CuE7D1G65glCihAxthtzkHozaE1bBGzAJifdQGlT3eNs15+BwhSwyTarCGuj?=
 =?us-ascii?Q?4gYUrFIDe1xsJ1NVdMQtZFJG36QPHVLKrSg1r5oCa+5ahrivtrrLWgxVEZFt?=
 =?us-ascii?Q?kJ4W4OS2W4+5rxhsnt9lRX4KJdKxV+Vkb707tBjFVjjYM/ZCdiwNjiCPi7FC?=
 =?us-ascii?Q?scArecBqU+xcVnCO2Kb8FgGTcptANVCUQCQ0/MGzJo8BLsFTw4ZARxDtvCcS?=
 =?us-ascii?Q?0F3dw1b9udby8X1begf1A8jPVuFrKF7PNkF9YI8eL0Fy6hdE6e54U0JSQtnf?=
 =?us-ascii?Q?ic/uAMd4GggartSZ/5MZEsfulI903Y0VlGaheODN1dXcGbQMI6Xrlj/1xMAH?=
 =?us-ascii?Q?bZWuCBuzDtqaJqAsJX1iXHDc1+4HHmYXW+SbK5ppqsmPnImyJc8tmE7V77kJ?=
 =?us-ascii?Q?HYvuapf9mzYB96In2jHZzq9NgTrr3vlpzcjZN01JLPW0tYmNoirJCw8LutB5?=
 =?us-ascii?Q?d0iiZJqWtfvVjhHvMsGhaCBHodlf5amTKwj+Cg7i1GoR2fEG8MKH0bsFYawy?=
 =?us-ascii?Q?9ZJkf2nmVGNiVU4qhCsexYBvN9TdZxtVWQBUJKTUnn92TQXTwllRVnqUg/dp?=
 =?us-ascii?Q?UEq3unO/KeQ6ELQq0+Y51XFoRVyvPncMAjXizPlTbOtNkG0bfJe8zOQFTU/e?=
 =?us-ascii?Q?7tTm1MLTfYpw9o0ugDR7yODHOLr8GDyFroLtjj2yPt6pev2Hg5OvfODIqv0X?=
 =?us-ascii?Q?28ZOK9U0sJzDK6twty2xw4CWZGi6LJvvAbbs+JCqGbxrc74T7wIQcGAMJXOO?=
 =?us-ascii?Q?q757EA+CqOGbHGdwR8EZDPQGzOcKeiSpuPwlThBpJSFVd0OSx7cfr3DmI/wr?=
 =?us-ascii?Q?7z5MrBtagu3IBVkiQzaaTsl1H4wODPozUEv0YItY3Mz3L6UCsT6epGuWJ3QL?=
 =?us-ascii?Q?5MyshoZPZzmSdTHjPMgM8bFoXmQqd7hAPujFL5i4vLzED2F3lTuez8P9kTY3?=
 =?us-ascii?Q?qNELAS2UgdmywFxehemuyD40Y72ABAyzo4QXso9Bxi9zZj/ehAtn28SHfwd/?=
 =?us-ascii?Q?R5CcCyTMK5lsSHEiIygoXAdikJuoKjh5bFWT+hpmbLJdVYQXLfaiy1lSga6r?=
 =?us-ascii?Q?czhQzvLEuA87+bZxElDFbqR69shixaaeV7uafRGM2WmxhjMOlJ8H2SVyecTr?=
 =?us-ascii?Q?93vTV3rTNkGWDs5AnWXqGWFO+KsioyyMDm5OkkgT+/t3ZopUAITMsoZGBcWx?=
 =?us-ascii?Q?hA6RRlOGsS6TKs115w+0OL7qcnF7f3FKdEkr0uSbDJy9kyBfjfXWO/2lCOkF?=
 =?us-ascii?Q?5qpijHvP1sRiVTikay7v/OiLNYfesVLb0SJYcy0BsbMFJaGoBmu5L5qY8LzT?=
 =?us-ascii?Q?kdLADIpLRHXGDZtZo0YpHNhPTzmCBKaGm6liVHIEHJJ2XP1e7zDa7ZwNiW5A?=
 =?us-ascii?Q?1n9c+uL6FMs+v0FTTvq1kcKYVsHzXCQJyH1xwHVkVfdplXPPGbKaDeF/0VpB?=
 =?us-ascii?Q?N4JmW9b++um4MEvFFh1gNaOoKH6FAjluj+qXu0OEiA9nqKvw7mq7WF7REi0h?=
 =?us-ascii?Q?Aw=3D=3D?=
Content-Type: multipart/alternative;
	boundary="_000_BL3PR05MB9236F587AD7551AD63B2C65AB3812BL3PR05MB9236namp_"
MIME-Version: 1.0
X-OriginatorOrg: shoeicanada.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: BL3PR05MB9236.namprd05.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 9d11236b-c86d-4e26-108e-08dcc05bee71
X-MS-Exchange-CrossTenant-originalarrivaltime: 19 Aug 2024 14:33:21.2984
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 01347b42-4202-49b1-8efe-675406351eed
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 l4/0mMBob5O3djhCCpIUC7bjoqvdWsNd/MpacOb/vmN43hh27NBoDozTRxUnxw+wVcDYNc6mr4MjAWyD+8lyzQ==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CO1PR05MB8021
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/BL3PR05MB9236F587AD7551AD63B2C65AB3812%40BL3PR05MB9236.namprd05.prod.outlook.com>
Precedence: bulk

--_000_BL3PR05MB9236F587AD7551AD63B2C65AB3812BL3PR05MB9236namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

HI,

Not sure if this is the place to write to.

We have a database running on version 12.2 of postgresql.  We have experien=
ced an extended power failure and our backup systems did not work as intend=
ed resulting in a sudden shutdown of the database server (Windows 2012).  A=
fter restarting the system it had performed some automated recovery and the=
 database was functional.  Unfortunately, the database speed had slowed dow=
n significantly, particularly in the INSERT commands in an UploadData appli=
cation we have on our production machines that uploads 139 columns of data.

The data is inserted into partitioned tables and there is a trigger functio=
n updating additional tables.  I have noticed that this additional table al=
though small in records (2500 records with 18 columns) it take about 4-6 se=
conds 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 seco=
nd 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 o=
ccurs for every record that is inserted.  If every record is 2 seconds of d=
ata, 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


--_000_BL3PR05MB9236F587AD7551AD63B2C65AB3812BL3PR05MB9236namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" xmlns:w=3D"urn:sc=
hemas-microsoft-com:office:word" xmlns:m=3D"http://schemas.microsoft.com/of=
fice/2004/12/omml" xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Aptos;
	panose-1:2 11 0 4 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	font-size:11.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:11.0pt;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style>
</head>
<body lang=3D"EN-CA" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">HI,<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Not sure if this is the place to write to.<o:p></o:p=
></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">We have a database running on version 12.2 of postgr=
esql.&nbsp; 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).&nbsp; After restarting
 the system it had performed some automated recovery and the database was f=
unctional. &nbsp;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.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">The data is inserted into partitioned tables and the=
re is a trigger function updating additional tables.&nbsp; 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.&nbsp; The data in this table is being update=
d by multiple UlpadData applications.&nbsp; So the last 10 records the date=
 columns are constantly being updated.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Visualizing the data before the latest 15 records it=
 appears withing 1 second or less.&nbsp; The latest 15 records can take 2-4=
 seconds to visualize.&nbsp; Then visualizing the entire table take 5-7 sec=
onds.&nbsp; This is too long if this occurs for every
 record that is inserted.&nbsp; If every record is 2 seconds of data, the a=
pplication can never keep up with the updates.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Performed a full vacuum on this table with a little =
improvement.&nbsp;
<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Is there anything I can do to see what is going on?<=
o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Thank you<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Frank Komsic<o:p></o:p></p>
<p class=3D"MsoNormal">Shoei Canada Coproration<o:p></o:p></p>
<p class=3D"MsoNormal">514-949-6319<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
</div>
</body>
</html>

--_000_BL3PR05MB9236F587AD7551AD63B2C65AB3812BL3PR05MB9236namp_--