Обсуждение: Pointers towards identifying bulk import bottleneck (walwritertuning?)
Dear all,
I hope that this is the right place to ask.
I have trouble identifying the bottleneck in a bulk import. I'm suspecting the bottleneck to be the walwriter. I'm using PostgreSQL 11, my configuration is at [5]. I'm running on ZFS raidz3 that can write write 300MB/s+. However, I'm only seeing ~35MB/s from walwriter.
What would be a strategy to improve bulk insertion performance or find out more about potential ways forward/understanding this better?
Thanks,
Manuel
The full description is as follows:
I have the following use case. My table is relatively complex (see [2] for the definition, [1] contains all scripts and commands while [3] has the data that I'm using for testing; original data from [4]).
To summarise:
- My table has has 47 fields, most are integers but there are also two VARCHAR(64) arrays and one JSONB field.
- The data can nicely be partitioned by "case_id" as I will only query one case at a time.
- I want to import TSV files into the table, the example data has 370k lines which is realistic.
- Ideally I would be able to import multiple TSV files in parallel to saturate the I/O of the system.
The import strategy is that I'm first importing the TSV file into a table with the same number of fields as my actual table but is text only and then insert from this table into the actual table (I've cut out the SQL from my software which uses a library that goes this way for bulk import). I tried to directly \copy into the original table but that did not improve performance much.
For the benchmarking, I have created four separate of these "text tables" and pre-filled them (fill[1-4].sql). Then I'm filling my main table from them (query[1-4].sql), with 1, 2, and 4 queryX.sql executions in parallel. Wall-clock running time are about 14s for one import process, 21s for two import processes and 44s for four import processes.
As the main table is partitioned and each TSV import process only inserts into one partition entry, I was expecting the speedup to be almost linear. I also tried to insert into the partition entry tables directly from TSV in parallel but that did not help either.
My Postgres configuration is at [5]. I'm running PostgreSQL 11.5 on a recent CentOS 7 (running inside a LXC container). The data is stored on a ZFS raidz3 pool with 16 spinning disks (and an SSD each for each ZFS logs and cache). The file system is capable of writing 300MB/s+ but I'm seeing 80MB/sec or less by postgres. With one import process the CPU usage is at 80%, dropping to ~60% for two import processes, and to about 30% for four import processes.
iotop tells me that walwriter does not go beyond ~35MB/s so maybe this is the culprit? Is there a way to tune walwriter I/O performance?
On Tue, Aug 27, 2019 at 12:06 PM Holtgrewe, Manuel <manuel.holtgrewe@bihealth.de> wrote: > iotop tells me that walwriter does not go beyond ~35MB/s so maybe this is the culprit? Is there a way to tune walwriterI/O performance? As far as I know, walwriter is there to help background processes, so in the case it cannot keep up with WALs the backends will write on their own. If my understanding is correct, I don't think that could be the bootleneck. I've seen you have checkpoints at 4h, that's quite huge to me. Do you have any hint that checkpoints are happening too frequently? Any chance you can turn fsync off (only if this is a testbed)? Also this <https://www.postgresql.org/list/pgsql-performance/> could be a better place to ask for help. Luca
RE: [ext] Re: Pointers towards identifying bulk import bottleneck(walwriter tuning?)
От
"Holtgrewe, Manuel"
Дата:
Hi, I also tried creating the table as "UNLOGGED" which led to walwriter I/O to drop drastically and I now get no wall-clocktime increase with two import processes but it gets slower with four. Switching off fsync leads to a drastic time improvement but still higher wall-clock time for four threads. Thanks for the pointer to the other mailing list. I'll try my luck there. Cheers, Manuel
On Tue, Aug 27, 2019 at 10:43 AM Holtgrewe, Manuel <manuel.holtgrewe@bihealth.de> wrote:
Hi,
I also tried creating the table as "UNLOGGED" which led to walwriter I/O to drop drastically and I now get no wall-clock time increase with two import processes but it gets slower with four.
Switching off fsync leads to a drastic time improvement but still higher wall-clock time for four threads.
Does switching fsync off make it faster even when the table are unlogged (that would be surprising) or were the two changes made one at a time? When you say still higher for four threads, do you mean the four threads for fsync=off are much faster than 4 threads for fsync=on but still doesn't scale linearly within the fsync=off set? Or is the nonlinearity so bad that you fsync=off doesn't even improve the 4 thread situation?
PostgreSQL fsyncs each wal segment once it is full. Under heavy load, this is effectively done in the foreground (even when done by WALwriter), because other processes inserting WAL records will soon be blocked by locks taken out by the fsyncing process. So if you can't run your production database with fsync=off, one thing you can try is setting up a new database with a larger wal segment size (--wal-segsize argument to initdb).
Cheers,
Jeff
Re: [ext] Re: Pointers towards identifying bulk import bottleneck(walwriter tuning?)
От
Laurenz Albe
Дата:
Holtgrewe, Manuel wrote: > Switching off fsync leads to a drastic time improvement but still > higher wall-clock time for four threads. Don't do that unless you are ready to start from scratch with a new "initdb" in the case of a crash. You can do almost as good by setting "synchronous_commit = off", and that is crash-safe. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
RE: [ext] Re: Pointers towards identifying bulk import bottleneck(walwriter tuning?)
От
"Holtgrewe, Manuel"
Дата:
I guess this cannot be pointed out too often ;) I'm not intending to do this but the behaviour of the system with fsync=offled me to further understand that the bottleneck had to be CPU-related rather than I/O-related. Of course, I neverswitched off fsync on the production system. Best wishes, ________________________________________ From: Laurenz Albe [laurenz.albe@cybertec.at] Sent: Wednesday, August 28, 2019 05:45 To: Holtgrewe, Manuel; Luca Ferrari Cc: pgsql-general Subject: Re: [ext] Re: Pointers towards identifying bulk import bottleneck (walwriter tuning?) Holtgrewe, Manuel wrote: > Switching off fsync leads to a drastic time improvement but still > higher wall-clock time for four threads. Don't do that unless you are ready to start from scratch with a new "initdb" in the case of a crash. You can do almost as good by setting "synchronous_commit = off", and that is crash-safe. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: [ext] Re: Pointers towards identifying bulk import bottleneck(walwriter tuning?)
От
Michael Lewis
Дата:
On Tue, Aug 27, 2019 at 9:45 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
Holtgrewe, Manuel wrote:
> Switching off fsync leads to a drastic time improvement but still
> higher wall-clock time for four threads.
Don't do that unless you are ready to start from scratch with a new
"initdb" in the case of a crash.
You can do almost as good by setting "synchronous_commit = off",
and that is crash-safe.
It seems like it depends on your definition of crash-safe. Data loss can occur but not data corruption, right? Do you know any ballpark for how much difference in performance it makes to turn off synchronous_commit or what type of hardware or usage it would make the biggest (or least) difference?
Re: [ext] Re: Pointers towards identifying bulk import bottleneck(walwriter tuning?)
От
Laurenz Albe
Дата:
On Wed, 2019-08-28 at 12:27 -0600, Michael Lewis wrote: > > You can do almost as good by setting "synchronous_commit = off", > > and that is crash-safe. > > It seems like it depends on your definition of crash-safe. Data loss > can occur but not data corruption, right? Right. > Do you know any ballpark for how much difference in performance it > makes to turn off synchronous_commit or what type of hardware or > usage it would make the biggest (or least) difference? In my experience, the performance will be almost as good as with fsync=off, which is as good as it gets. For an exact answer for your specific system, run a simple benchmark. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com