From: Jerry Sievers <gsievers19@comcast.net>
To: Ogden Brash <info@litika.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Modification of data in base folder and very large tables
References: 
 <CAFCR_K3KraRUTKxZeYEGVyNd_6VMa+cgdW_WYn6k4JZz_m3prA@mail.gmail.com>
Date: Wed, 09 Oct 2019 18:05:20 -0500
In-Reply-To: 
 <CAFCR_K3KraRUTKxZeYEGVyNd_6VMa+cgdW_WYn6k4JZz_m3prA@mail.gmail.com>
	(Ogden Brash's message of "Tue, 8 Oct 2019 13:20:43 -0700")
Message-ID: <87zhi9pljj.fsf@jsievers.enova.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

Ogden Brash <info@litika.com> writes:

> I have a question about the files in .../data/postgresql/11/main/
> base, specifically in relation to very large tables and how they are
> written.
>
> I have been attempting to restore a relatively large database with
> pg_restore and it has been running for more than a week. (I also=C2=A0
> have another thread about the same restore related to network vs.
> local disk I/O)
>
> I ran the pg_restore in verbose mode using multiple jobs so I can
> tell what has finished and what has not. The database had 66 tables
> and most of them have been restored. Two of the tables were quite
> large (billions of rows translating to between 1 and 2TB of data on
> disk for those two tables) and those two tables are pretty much the
> only things remaining that has not been reported as finished by
> pg_restore.
>
> As the process has been going for a week, I have been tracking the
> machine (a dedicated piece of hardware, non-virtualized) and have
> been noticing a progressive slowdown (as tracked by iostat). There is

Do the tables that are being loaded have any indexes on them?

> nothing running on the machine besides postgresql and the server is
> only doing this restore, nothing else. It is now, on average, running
> at less than 25% of the speed that it was running four days ago (as
> measured by rate of I/O).=C2=A0
>
> I started to dig into what was happening on the machine and I noticed
> the following:
>
> iotop reports that two postgres=C2=A0processes (I assume each processing
> one of the two tables that needs to be processed) are doing all the I
> /O. That makes sense
>
> Total DISK READ :=C2=A0 =C2=A0 1473.81 K/s | Total DISK WRITE :=C2=A0 =C2=
=A0 =C2=A0617.30 K/s
> Actual DISK READ:=C2=A0 =C2=A0 1473.81 K/s | Actual DISK WRITE:=C2=A0 =C2=
=A0 =C2=A0 =C2=A00.00 B/s
> =C2=A0 TID=C2=A0 PRIO=C2=A0 USER=C2=A0 =C2=A0 =C2=A0DISK READ=C2=A0 DISK =
WRITE=C2=A0 SWAPIN=C2=A0 =C2=A0 =C2=A0IO>=C2=A0 =C2=A0
> COMMAND
> =C2=A06601 be/4 postgres=C2=A0 586.44 K/s=C2=A0 =C2=A0 7.72 K/s=C2=A0 0.0=
0 % 97.39 % postgres:
> 11/main: postg~s thebruteff [local] COPY
> =C2=A06600 be/4 postgres=C2=A0 887.37 K/s=C2=A0 601.87 K/s=C2=A0 0.00 % 9=
3.42 % postgres:
> 11/main: postg~s thebruteff [local] COPY
> =C2=A0 666 be/3 root=C2=A0 =C2=A0 =C2=A0 =C2=A0 0.00 B/s=C2=A0 =C2=A0 7.7=
2 K/s=C2=A0 0.00 %=C2=A0 5.73 % [jbd2/
> sda1-8]
> =C2=A0 =C2=A0 1 be/4 root=C2=A0 =C2=A0 =C2=A0 =C2=A0 0.00 B/s=C2=A0 =C2=
=A0 0.00 B/s=C2=A0 0.00 %=C2=A0 0.00 % init
> =C2=A0 =C2=A0 2 be/4 root=C2=A0 =C2=A0 =C2=A0 =C2=A0 0.00 B/s=C2=A0 =C2=
=A0 0.00 B/s=C2=A0 0.00 %=C2=A0 0.00 %
> [kthreadd]
> =C2=A0 =C2=A0 4 be/0 root=C2=A0 =C2=A0 =C2=A0 =C2=A0 0.00 B/s=C2=A0 =C2=
=A0 0.00 B/s=C2=A0 0.00 %=C2=A0 0.00 % [kworker/
> 0:0H]
>
> So, the next thing I though I would do is an "lsof" command for each
> of those two processes to see what they were writing. That was a bit
> of a surpise:
>
> # lsof -p 6600 | wc -l;
> 840
>
> # lsof -p 6601 | wc -l;
> 906
>
> Is that normal? That there be so many open file pointers? ~900 open
> file pointers for each of the processes?
>
> The next I did was go to see the actual data files, to see how many
> there are. In my case they are in postgresql/11/main/base/24576 and
> there are 2076 files there. That made sense. However, I found that
> when I list them by modification date I see something interesting:
>
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.7
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.8
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.9
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.10
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.11
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.12
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.13
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.14
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.16
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.15
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.17
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.18
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.19
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.21
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.22
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.23
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.24
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.25
> -rw------- 1 postgres postgres 1073741824 Oct=C2=A0 8 13:05 27083.26
> -rw------- 1 postgres postgres=C2=A0 =C2=A019062784 Oct=C2=A0 8 13:05 270=
82_fsm
> -rw------- 1 postgres postgres=C2=A0 544489472 Oct=C2=A0 8 13:05 27077.34
> -rw------- 1 postgres postgres=C2=A0 169705472 Oct=C2=A0 8 13:05 27082.72
> -rw------- 1 postgres postgres=C2=A0 978321408 Oct=C2=A0 8 13:05 27083.27
> -rw------- 1 postgres postgres=C2=A0 342925312 Oct=C2=A0 8 13:05 27076.88
>
> If you notice, the file size is capped at 1 GB and as the giant table
> has grown it has added more files in this directory. However, the
> mysterious thing to me is that it keeps modifying those files
> constantly - even the ones that are completely full. So for the two
> large tables it has been restoring all week, the modification time
> for the ever growing list of files is being updating constantly.
>
> Could it be that thats why I am seeing a slowdown over the course of
> the week - that for some reason as the number of files for the table
> has grown, the system is spending more and more time seeking around
> the disk to touch all those files for some reason?
>
> Does anyone who understands the details of postgresql's interaction
> with the file system have an explanation for why all those files
> which are full are being touched constantly?
>
>
>
>

--=20
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net