[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: REINDEXdb performance degrading gradually PG13.4
From:       Jeff Janes <jeff.janes () gmail ! com>
Date:       2022-06-01 17:41:07
Message-ID: CAMkU=1wR9Er2me8u=TL0vGNpMbig90bGryzzEXCpZs2y-apW3Q () mail ! gmail ! com
[Download RAW message or body]

On Tue, May 31, 2022 at 11:14 AM Praneel Devisetty <
devisettypraneel@gmail.com> wrote:

>
> Hi,
>>
>> We are trying to reindex 600k tables in a single database  of size 2.7TB
>> using reindexdb utility in a shell script
>> reindexdb -v -d $dbname -h $hostname -U tkcsowner --concurrently -j
>> $parallel -S $schema
>>
>>
What is the value of $parallel?  Are all the tables in the same schema?


> Initially it was processing 1000 tables per minute. Performance is
>> gradually dropping and now after 24 hr it was processing 90 tables per
>> minute.
>>
>
I can't even get remotely close to 1000 per minute with those options, even
with only 100000 single-index tables with all of them being empty.  Are you
sure that isn't 1000 per hour?

Using --concurrently really hits the stats system hard (I'm not sure why).
 Could you just omit that?  If it is running at 1000 per minute or even per
hour, does it really matter if the table is locked for as long as it takes
to reindex?

Cheers,

Jeff

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Tue, May 31, 2022 at 11:14 AM Praneel Devisetty &lt;<a \
href="mailto:devisettypraneel@gmail.com">devisettypraneel@gmail.com</a>&gt; \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div \
dir="auto"><div><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr"><br></div><blockquote class="gmail_quote" style="margin:0px 0px \
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div \
dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div \
dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div \
dir="ltr"><div dir="ltr"><div dir="ltr">Hi,<div><br></div><div>We are trying to \
reindex 600k tables in a single database   of size 2.7TB</div><div>using reindexdb \
utility in a shell script</div><div><span \
style="color:rgb(36,36,36);font-family:-apple-system,BlinkMacSystemFont,&quot;Segoe \
UI&quot;,&quot;Apple Color Emoji&quot;,&quot;Segoe UI Emoji&quot;,&quot;Segoe UI \
Web&quot;,sans-serif;font-size:14px">reindexdb -v -d $dbname -h $hostname -U \
tkcsowner --concurrently -j $parallel -S $schema</span><br></div><div><span \
style="color:rgb(36,36,36);font-family:-apple-system,BlinkMacSystemFont,&quot;Segoe \
UI&quot;,&quot;Apple Color Emoji&quot;,&quot;Segoe UI Emoji&quot;,&quot;Segoe UI \
Web&quot;,sans-serif;font-size:14px"><br></span></div></div></div></div></div></div></ \
div></div></div></div></div></div></div></div></blockquote></div></div></div></blockquote><div><br></div><div>What \
is the value of  <span \
style="color:rgb(36,36,36);font-family:-apple-system,BlinkMacSystemFont,&quot;Segoe \
UI&quot;,&quot;Apple Color Emoji&quot;,&quot;Segoe UI Emoji&quot;,&quot;Segoe UI \
Web&quot;,sans-serif;font-size:14px">$parallel?   Are all the tables in the same \
schema?</span></div><div>  </div><blockquote class="gmail_quote" style="margin:0px \
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div \
dir="auto"><div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div \
dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div \
dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div><font \
color="#242424" face="-apple-system, BlinkMacSystemFont, Segoe UI, Apple Color Emoji, \
Segoe UI Emoji, Segoe UI Web, sans-serif"><span style="font-size:14px">Initially it \
was processing 1000 tables per minute. Performance  is gradually dropping and now \
after 24 hr it was processing 90 tables per \
minute.</span></font></div></div></div></div></div></div></div></div></div></div></div \
></div></div></div></blockquote></div></div></div></blockquote><div><br></div><div>I \
> can&#39;t even get remotely close to 1000 per minute with those options, even with \
> only 100000 single-index tables with all of them being empty.   Are you sure that \
> isn&#39;t 1000 per hour?</div><div><br></div><div>Using --concurrently really hits \
> the stats system hard (I&#39;m not sure why).     Could you just omit that?   If it \
> is running at 1000 per minute or even per hour, does it really matter  if the table \
> is locked for as long as it takes to \
> reindex?</div><div><br></div><div>Cheers,</div><div><br></div><div>Jeff</div></div></div>
> 



[prev in list] [next in list] [prev in thread] [next in thread]