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 1qKgcE-001I7j-R3 for pgsql-general@arkaria.postgresql.org; Sat, 15 Jul 2023 14:51:22 +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 1qKgcD-000vu5-1S for pgsql-general@arkaria.postgresql.org; Sat, 15 Jul 2023 14:51:21 +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 1qKgcC-000vtx-NM for pgsql-general@lists.postgresql.org; Sat, 15 Jul 2023 14:51:20 +0000 Received: from mail-qk1-x72f.google.com ([2607:f8b0:4864:20::72f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qKgc8-000ZJg-S4 for pgsql-general@lists.postgresql.org; Sat, 15 Jul 2023 14:51:19 +0000 Received: by mail-qk1-x72f.google.com with SMTP id af79cd13be357-7658752ce2fso216079785a.1 for ; Sat, 15 Jul 2023 07:51:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689432675; x=1692024675; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=XT/Mz1sERKdzOwu/9S+dUwY88p+9iB/r8jeJiSuPeKg=; b=XxSv2C3ecikPaOAx8LvetFrJXJc2v0BZUfTLxRcymSJ33GBDLJJVbqgOQ9TXFQQR6T 4vK/NxiKPFzMI3Vc/ht1XcGUx+0+6M1zTalePsay4tKc9wmFNJYFfhdoGNWRvqPD1JHX doZSVBLcLpptPlCIH0zk2sknTUW5jaz2+vNONGlh5Jjb7TAbo42WYYfpyf0mU9QeXlyU 5qfDN2iswcio9Z/TrneiZtSMqjf6oG803X++jx+0zrRPH1zVcq1KERAXF8xIlfNV/chK 2k8K6xRXpeG7kABr8gF8C3opaT5kLvnVRppmPJszg9WDWB8D3UUjioHYXCO9RpiNXHHY dp9g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689432675; x=1692024675; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=XT/Mz1sERKdzOwu/9S+dUwY88p+9iB/r8jeJiSuPeKg=; b=UbNrM9e/RHTdm4JtElL1Ycc/N17EFfYRUMwzEAiHGfIAf7okl6yjBmTu6xMRnlJS08 zw3IVnmB52pYTikJkON6u7NGqxOMVJdVRFsUtT1H45VHZMY/c13mDqU4TulyUQ86T1zx nB8V9G9KOu3OqojMZB6lE9seC3BITq1taySAeDWvslZ5wsi2tg5bQX3TUawP4YnubKFi Rt0vlTDO0qf1WPRXHHNwVkcPTbQVj5tiUyLBWcg73n+8jd85cOVKGIyuO0g4ZyueLF72 akZayGiEt/Um9Y5CuwZbMSgkPFgO+37F1Y4I+miMwv7R6NG5/mQDWs/rNnJjgig8UD7J 2Meg== X-Gm-Message-State: ABy/qLbT4FTVEkVO5SIg4rJ0oX8Ow3nUwYj4RiK08033bf3qdKQuVbI0 8pMFxrdBgX3DV+ipOkGrg5g/HA9eFJE= X-Google-Smtp-Source: APBJJlEZol0GtEIX+Kp0C5xTutfKHafZZvJg4tlGQmwGO2UN9h4xrru1oWTJb8y9H922B5ZKdX6nuA== X-Received: by 2002:a37:5ac5:0:b0:768:1476:63c8 with SMTP id o188-20020a375ac5000000b00768147663c8mr863224qkb.67.1689432675604; Sat, 15 Jul 2023 07:51:15 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:6c67:eb19:9631:2feb? ([2600:1700:bd2c:b810:6c67:eb19:9631:2feb]) by smtp.googlemail.com with ESMTPSA id b8-20020a816708000000b0056d31a089e9sm2850229ywc.126.2023.07.15.07.51.15 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 15 Jul 2023 07:51:15 -0700 (PDT) Message-ID: <61c5cff7-4c28-4d40-4404-27159a05e6cc@gmail.com> Date: Sat, 15 Jul 2023 09:51:15 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: pg_restore mostly idle on restoring a large number of tables Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 7/13/23 02:41, Boris Sagadin wrote: > Hi, > > restoring a 1.5TB database with about 800k tables on i3.4xlarge AWS > instace, PgSQL V12.15 on Ubuntu. > > Running pg_restore with -j 16, I noticed the pg_restore is busy for an > hour or so with IO at 80%+ and then most of processes start idling and > only a few doing some work, disk IO at 1-2%, pg_stat_activity is mostly > idle, same goes for CPU, and this state proceeds for further 6 hours, disk > space increases very slowly. > > I thought because of a lot of small tables, number of workers should be > increased to increase parallel efficiency, so I tried with -j 128. The > situation was somewhat better, but most of the workers start idling, again > disk IO lowers to about 4% util, CPU util goes to about 4%, too. > > Stracing workers produces the perpetual read call on most pg_restore workers: > > # strace -p 59567 > strace: Process 59567 attached > read(3, > > With only about 10 or so (out of 128) workers doing some actual work: > > strace -p 59367 -e sendto > strace: Process 59367 attached > sendto(4, "Q\0\0\0\263ALTER TABLE ONLY raw.space0"..., 180, MSG_NOSIGNAL, > NULL, 0) = 180 > sendto(4, "Q\0\0\0.TRUNCATE TABLE ONLY raw.spa"..., 47, MSG_NOSIGNAL, > NULL, 0) = 47 > sendto(4, "Q\0\0\0\205COPY raw.space000448117 (da"..., 134, MSG_NOSIGNAL, > NULL, 0) = sendto(4, "Q\0\0\0\vCOMMIT\0", 12, MSG_NOSIGNAL, NULL, 0) = 12 > sendto(4, "Q\0\0\0pCREATE INDEX idx_space00044"..., 113, MSG_NOSIGNAL, > NULL, 0) = 113 > sendto(4, "Q\0\0\0\263ALTER TABLE ONLY raw.space0"..., 180, MSG_NOSIGNAL, > NULL, 0) = 180 > ... > .. some lines omitted > > I would think that all workers would proceed with creating indexes and > doing some useful work until the restore is finished completely? > > Most of the tables are very small, 2 indexes per table and without any > foreign references etc., we have a multi tenant environment. I always run pg_dump and pg_restore with "--verbose", and redirect stdout & stderr to a log file.  Then I "tail -f" that log file. It'll tell you what's happening. Also, iotop is quite useful. -- Born in Arizona, moved to Babylonia.