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 1sRddQ-00HHf0-2O for pgsql-hackers@arkaria.postgresql.org; Wed, 10 Jul 2024 20:09:52 +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 1sRddO-008bPe-Nx for pgsql-hackers@arkaria.postgresql.org; Wed, 10 Jul 2024 20:09:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRddN-008bMX-HU for pgsql-hackers@lists.postgresql.org; Wed, 10 Jul 2024 20:09:50 +0000 Received: from fhigh8-smtp.messagingengine.com ([103.168.172.159]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRddJ-001QPI-76 for pgsql-hackers@postgresql.org; Wed, 10 Jul 2024 20:09:47 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailfhigh.nyi.internal (Postfix) with ESMTP id D5A2D1140C3F; Wed, 10 Jul 2024 16:09:43 -0400 (EDT) Received: from imap49 ([10.202.2.99]) by compute2.internal (MEProxy); Wed, 10 Jul 2024 16:09:43 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eulerto.com; h= cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm3; t=1720642183; x=1720728583; bh=WySj/q2hXF KrPQOHGm2RZw+M9ifZ4BlhIB1FqcLvATI=; b=vzMALCPHxb6coocTYXKb+fr9o3 eevdPdZ01bWw2y9ffZpS4RDOfvYMV9tN7lyKV8YEsM8S6TB66mqlYMAzJMy9dCEj 4pApncwL7flMFI4xXZUqeL61JNaacOR5lhl5WhTz40kGNjEZak0maxybvuZc2L4a c259t9Mqib9aDR4dj351nM6GeNYpxm8Q4zCX6IZnXjzI2LXlNUw1q2tz/FN2a/E1 zIdfZN7gYoSF/R1bpZONwqmo4m+BjoF61IPZhV7u+KURdXYsM4UU93lSxb+1u+Pt EL5uMjxMmzkwNU6EB0WGfyzgv/WSNFa9SaCnAsb3b9uBRL/ElKm/GKCmdFtw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm2; t=1720642183; x=1720728583; bh=WySj/q2hXFKrPQOHGm2RZw+M9ifZ 4BlhIB1FqcLvATI=; b=g9CkMgnZrIVMxBYqHViUwNCud/k7rw6R5VrLU4wIg4eK oco5NzRQ9Ia6U64RjajA04uG/TXTojne8kAAbqqISno5cYE534g21fWHlD6XxCFz EsDKZmU6klm7Rtvpxes383NpANzymKKRbkRMZy/RqBJIqnF8ZmplMZVgx1nWe5+i +UCOC8M+JKswBigiJ4Mc1OuNvjC1G1ScBo/9e5aQI5NkFI5yfc3urt2GKMLiGS3Z WBnxNTE/3p3RODjsCb3aGvnKs15QdEX57OOPqwrWl8F6n3KVj/jH/xEKCv3Fog1P ZZmgN5vNHs0fqKK3cAjOKKpApzk+K98aj937kGbSxg== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeeftddrfedvgdekhecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefofgggkfgjfhffhffvufgtsegrtderreerredtnecuhfhrohhmpedfgfhulhgv rhcuvfgrvhgvihhrrgdfuceovghulhgvrhesvghulhgvrhhtohdrtghomheqnecuggftrf grthhtvghrnhepjeethfeuvdfgffdtteevudeftdejueffkeelveekhfevkeegleefhfej hedtjeegnecuffhomhgrihhnpegvnhhtvghrphhrihhsvggusgdrtghomhenucevlhhush htvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegvuhhlvghrsegvuhhl vghrthhordgtohhm X-ME-Proxy: Feedback-ID: i0c21471d:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id 945E615A0092; Wed, 10 Jul 2024 16:09:42 -0400 (EDT) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.11.0-alpha0-568-g843fbadbe-fm-20240701.003-g843fbadb MIME-Version: 1.0 Message-Id: <0a0f4d52-ab75-4d69-abe8-5713bf70eee6@app.fastmail.com> In-Reply-To: References: <20240503025140.GA1227404@nathanxps13> Date: Wed, 10 Jul 2024 17:08:56 -0300 From: "Euler Taveira" To: "Nathan Bossart" , pgsql-hackers Subject: Re: improve performance of pg_dump with many sequences Content-Type: multipart/alternative; boundary=16f1c11385b44519a24358e395d27f68 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --16f1c11385b44519a24358e395d27f68 Content-Type: text/plain On Tue, Jul 9, 2024, at 4:11 PM, Nathan Bossart wrote: > rebased Nice improvement. The numbers for a realistic scenario (10k sequences) are for i in `seq 1 10000`; do echo "CREATE SEQUENCE s$i;"; done > /tmp/s.sql master: real 0m1,141s user 0m0,056s sys 0m0,147s patched: real 0m0,410s user 0m0,045s sys 0m0,103s You are changing internal representation from char to int64. Is the main goal to validate catalog data? What if there is a new sequence data type whose representation is not an integer? This code path is adding zero byte to the last position of the fixed string. I suggest that the zero byte is added to the position after the string length. Assert(strlen(PQgetvalue(res, 0, 0)) < sizeof(seqtype)); strncpy(seqtype, PQgetvalue(res, 0, 0), sizeof(seqtype)); seqtype[sizeof(seqtype) - 1] = '\0'; Something like l = strlen(PQgetvalue(res, 0, 0)); Assert(l < sizeof(seqtype)); strncpy(seqtype, PQgetvalue(res, 0, 0), l); seqtype[l] = '\0'; Another suggestion is to use a constant for seqtype char seqtype[MAX_SEQNAME_LEN]; and simplify the expression: size_t seqtype_sz = sizeof(((SequenceItem *) 0)->seqtype); If you are not planning to apply 0003, make sure you fix collectSequences() to avoid versions less than 10. Move this part to 0002. @@ -17233,11 +17235,24 @@ collectSequences(Archive *fout) PGresult *res; const char *query; + if (fout->remoteVersion < 100000) + return; + Since you apply a fix for pg_sequence_last_value function, you can simplify the query in 0003. CASE is not required. I repeated the same test but not applying 0003. patched (0001 and 0002): real 0m0,290s user 0m0,038s sys 0m0,104s I'm not sure if 0003 is worth. Maybe if you have another table like you suggested. -- Euler Taveira EDB https://www.enterprisedb.com/ --16f1c11385b44519a24358e395d27f68 Content-Type: text/html Content-Transfer-Encoding: quoted-printable
On Tue, Jul 9, = 2024, at 4:11 PM, Nathan Bossart wrote:
rebased

Nice improvement. The numbers for a realistic scenario (10k seque= nces) are

for i in `seq 1 10000`; do echo "= CREATE SEQUENCE s$i;"; done > /tmp/s.sql

master:
real 0m1,141s
user 0m0,056s
sys 0m0,147s

patched:
real 0m0,410s
user 0m0,045s
sys 0m0,103s

You are changing internal representation fro= m char to int64. Is the main goal to
validate catalog data= ? What if there is a new sequence data type whose
represen= tation is not an integer?

This code path is= adding zero byte to the last position of the fixed string. I
<= div>suggest that the zero byte is added to the position after the string= length.

Assert(strlen(PQgetvalue(res, 0, 0= )) < sizeof(seqtype));
strncpy(seqtype, PQgetvalue(res,= 0, 0), sizeof(seqtype));
seqtype[sizeof(seqtype) - 1] =3D= '\0';

Something like

l =3D strlen(PQgetvalue(res, 0, 0));
Assert(l <= sizeof(seqtype));
strncpy(seqtype, PQgetvalue(res, 0, 0),= l);
seqtype[l] =3D '\0';

Ano= ther suggestion is to use a constant for seqtype

char seqtype[MAX_SEQNAME_LEN];

and si= mplify the expression:

size_t  &n= bsp;   seqtype_sz =3D sizeof(((SequenceItem *) 0)->seqtype)= ;

If you are not planning to apply 0003, ma= ke sure you fix collectSequences() to
avoid versions less = than 10. Move this part to 0002.

@@ -17233,= 11 +17235,24 @@ collectSequences(Archive *fout)    &= nbsp;           &= nbsp;      
  &nbs= p; PGresult   *res;       &= nbsp;           &= nbsp;           &= nbsp;           &= nbsp;           &= nbsp;    
    const cha= r *query;          &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           
          =             =             =             =             =             =           
+   if (fout->remoteVersion < 100000)  &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;    
+     =   return;         &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;       
+ 

Since you apply a fix for pg_sequence_last_value= function, you can simplify the
query in 0003. CASE is not= required.

I repeated the same test but not= applying 0003.

patched (0001 and 0002):
real 0m0,290s
user 0m0,038s
sys = 0m0,104s

I'm not sure if 0003 is worth. Ma= ybe if you have another table like you
suggested.


--
Euler Taveira
EDB   https://www.enterprisedb.com/

= --16f1c11385b44519a24358e395d27f68--