Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gS1FS-0006EP-U8 for pgsql-admin@arkaria.postgresql.org; Wed, 28 Nov 2018 14:55:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gS1FQ-0002Wt-Ah for pgsql-admin@arkaria.postgresql.org; Wed, 28 Nov 2018 14:55:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gS1FQ-0002WN-0v for pgsql-admin@lists.postgresql.org; Wed, 28 Nov 2018 14:55:28 +0000 Received: from mail-ot1-x333.google.com ([2607:f8b0:4864:20::333]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gS1FL-0008GI-3l for pgsql-admin@lists.postgresql.org; Wed, 28 Nov 2018 14:55:27 +0000 Received: by mail-ot1-x333.google.com with SMTP id u16so23753839otk.8 for ; Wed, 28 Nov 2018 06:55:22 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=Ww8udOCkB96v64BjWTpGjmK4YKRBTRm2jQlNjAC+npA=; b=cwzmZg136D/RFKK3rOwx1fx6zh9/v5kd3A2Y8B9C1InN6pIQtC+9i7SZFxLgObT6r5 Jb9NFwRf/d3hjn1NPGuaKrWALH65XBNTncY/yH16MMN33eIkIOymM3v/IxVfmvlalP44 q4MY64dkL0gX6/+wHULs3JLoY8GFrTJJNb1ykRd1MEgykY8lQ3ueadTzfR8usFMmIAsv bSGplw0+BJoeIso3czIh+j4DscF4EEl2KXVRxxzQWjEWVcwTWbueB9GX4UxiyhkMDHhX ZKzUOposBODPz85AAV4VwBoYLDzuvqN4843NuweGu0wZ9SBjCZtf5LCTlopMyQwnJ7Mi +emw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=Ww8udOCkB96v64BjWTpGjmK4YKRBTRm2jQlNjAC+npA=; b=B7a8xRm+PmgS5PNsXVwNEeEIYOQBvyDzdn/199toXOUiSS75ZpGUnBbsI68QKwfoyR datxM3qI/dvdUian2cQlAC1t6FToy/YUkb/NRhkqkOzjyi0OyKYqVTlm07n0YFmYVYLf ZipNO7DItrRC6t+ryqJxE4cjqwhzkI/sZFQUmiQw7wZKt0sd/+u7ZtKZ2aa81BAmvm1g VwEdMlUQ7nO/Y+mtin5AY7YIbL20TdRKE6axSkLC+HtKKCA4JdDUwQ60Jw5sqHLZbeJi pYEBePkbV+pXaTV/BrZ8Ca3gXGVXkc225CrsuN2T4udbZ+Na0iSXJRL+vw7iEhZo6tPD AadQ== X-Gm-Message-State: AA+aEWadoVEBHPW+/3lolHtImqKmYpgIJgf2LyVQWb0b6EoGgzNfrig3 7k0NaAPz8rSPXKG+KZQ+EGbs4wC0 X-Google-Smtp-Source: AFSGD/X6h9bV3yi5oH0AyvQOdhcYQUcKEVlB0KPW8xktOuhM2bvAONhFPJmJLkt0Rc/PZFlXV+Cy/Q== X-Received: by 2002:a9d:775a:: with SMTP id t26mr19627001otl.32.1543416919251; Wed, 28 Nov 2018 06:55:19 -0800 (PST) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id e42sm5034973oth.36.2018.11.28.06.55.18 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 28 Nov 2018 06:55:18 -0800 (PST) Subject: Re: Manual Vacuum Analyze Take More Time To: pgsql-admin@lists.postgresql.org References: <5387ACC6-52FD-42B3-A404-0FB63329CEE1@hotmail.com> From: Ron Message-ID: Date: Wed, 28 Nov 2018 08:55:17 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: <5387ACC6-52FD-42B3-A404-0FB63329CEE1@hotmail.com> Content-Type: multipart/alternative; boundary="------------90D2CA7F91BC042B0C5696C0" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------90D2CA7F91BC042B0C5696C0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 11/28/2018 08:22 AM, Moin Akther wrote: > Dear All, > > While running Manual “VACUUM ANALYZE” on table size of 1.2TB, its running > more than 1 Hour and generated almost 15K WAL files and due to this we > have faced huge replication lag on our standby db. > > I have maitenance_work_mem of 6GB and RAM Size of 128GB. > > /There is no lock and blocking queries at that time and CPU and > memory utilization also normal./ > / > / > How we can fix this issue ? Not an answer to your question, but you should really think about partitioning that table. -- Angular momentum makes the world go 'round. --------------90D2CA7F91BC042B0C5696C0 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 11/28/2018 08:22 AM, Moin Akther wrote:
Dear All,

While running Manual “VACUUM ANALYZE” on table size of 1.2TB, its running more than 1 Hour and generated almost 15K WAL files and due to this we have faced huge replication lag on our standby db.

I have maitenance_work_mem of 6GB and RAM Size of 128GB.

There is no lock and blocking queries at that time and CPU and memory utilization also normal.

How we can fix this issue ?

Not an answer to your question, but you should really think about partitioning that table.

--
Angular momentum makes the world go 'round.
--------------90D2CA7F91BC042B0C5696C0--