On Tue, Apr 21, 2020 at 11:36 AM Andres Freund <and...@anarazel.de> wrote:
> It's all CRC overhead. I don't see a difference with
> --manifest-checksums=none anymore. We really should look for a better
> "fast" checksum.
Hmm, OK. I'm wondering exactly what you tested here. Was this over
your 20GiB/s connection between laptop and workstation, or was this
local TCP? Also, was the database being read from persistent storage,
or was it RAM-cached? How do you expect to take advantage of I/O
parallelism without multiple processes/connections?
Meanwhile, I did some local-only testing on my new 16GB MacBook Pro
laptop with all combinations of:
- UNIX socket, local TCP socket, local TCP socket with SSL
- Plain format, tar format, tar format with gzip
- No manifest ("omit"), manifest with no checksums, manifest with
CRC-32C checksums, manifest with SHA256 checksums.
The database is a fresh scale-factor 1000 pgbench database. No
concurrent database load. Observations:
- UNIX socket was slower than a local TCP socket, and about the same
speed as a TCP socket with SSL.
- CRC-32C is about 10% slower than no manifest and/or no checksums in
the manifest. SHA256 is 1.5-2x slower, but less when compression is
also used (see below).
- Plain format is a little slower than tar format; tar with gzip is
typically >~5x slower, but less when the checksum algorithm is SHA256
(again, see below).
- SHA256 + tar format with gzip is the slowest combination, but it's
"only" about 15% slower than no manifest, and about 3.3x slower than
no compression, presumably because the checksumming is slowing down
the server and the compression is slowing down the client.
- Fastest speeds I see in any test are ~650MB/s, and slowest are
~65MB/s, obviously benefiting greatly from the fact that this is a
local-only test.
- The time for a raw cp -R of the backup directory is about 10s, and
the fastest time to take a backup (tcp+tar+m:omit) is about 22s.
- In all cases I've checked so far both pg_basebackup and the server
backend are pegged at 98-100% CPU usage. I haven't looked into where
that time is going yet.
Full results and test script attached. I and/or my colleagues will try
to test out some other environments, but I'm not sure we have easy
access to anything as high-powered as a 20GiB/s interconnect.
It seems to me that the interesting cases may involve having lots of
available CPUs and lots of disk spindles, but a comparatively slow
pipe between the machines. I mean, if it takes 36 hours to read the
data from disk, you can't realistically expect to complete a full
backup in less than 36 hours. Incremental backup might help, but
otherwise you're just dead. On the other hand, if you can read the
data from the disk in 2 hours but it takes 36 hours to complete a
backup, it seems like you have more justification for thinking that
the backup software could perhaps do better. In such cases efficient
server-side compression may help a lot, but even then, I wonder
whether you can you read the data at maximum speed with only a single
process? I tend to doubt it, but I guess you only have to be fast
enough to saturate the network. Hmm.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
unix+plain+m:omit 45.330231 39.744768 42.264874
unix+tar+m:omit 32.767868 36.227577 35.742676
unix+targzip+m:omit 189.888759 188.50978 195.031579
tcp+plain+m:omit 29.758328 29.752194 30.242722
tcp+tar+m:omit 22.723285 24.207906 25.760343
tcp+targzip+m:omit 169.138959 169.258639 175.542641
tcpssl+plain+m:omit 39.846619 39.846117 39.813978
tcpssl+tar+m:omit 33.796067 33.26529 36.273268
tcpssl+targzip+m:omit 176.152361 188.631385 188.13747
unix+plain+m:none 39.791435 41.266778 41.276581
unix+tar+m:none 34.730879 35.743352 35.228618
unix+targzip+m:none 187.237583 196.353209 191.903126
tcp+plain+m:none 29.486182 31.252909 30.75195
tcp+tar+m:none 23.328765 24.725305 24.726108
tcp+targzip+m:none 165.605943 174.549782 176.558254
tcpssl+plain+m:none 39.403247 40.286618 40.27026
tcpssl+tar+m:none 34.288039 35.298867 32.776008
tcpssl+targzip+m:none 176.625429 186.062193 188.132955
unix+plain+m:crc32c 44.262171 47.808442 46.307146
unix+tar+m:crc32c 38.256322 41.773116 39.2641
unix+targzip+m:crc32c 191.790014 200.942023 197.461197
tcp+plain+m:crc32c 29.945689 30.731167 30.757778
tcp+tar+m:crc32c 23.712764 24.246618 25.229781
tcp+targzip+m:crc32c 167.55035 174.614658 172.695148
tcpssl+plain+m:crc32c 39.334947 39.818292 40.317529
tcpssl+tar+m:crc32c 34.786403 33.27777 35.283009
tcpssl+targzip+m:crc32c 175.731637 188.610379 186.590181
unix+plain+m:sha256 70.567034 74.90739 72.873767
unix+tar+m:sha256 67.808388 68.342512 70.912017
unix+targzip+m:sha256 219.002669 229.611438 230.585041
tcp+plain+m:sha256 46.369327 46.8214 47.857381
tcp+tar+m:sha256 46.843969 46.356686 46.847939
tcp+targzip+m:sha256 172.02733 174.575485 177.553958
tcpssl+plain+m:sha256 54.344198 54.362139 55.845257
tcpssl+tar+m:sha256 53.341627 54.377519 56.382529
tcpssl+targzip+m:sha256 180.57967 185.719082 185.634747
#!/opt/local/bin/perl
use strict;
use warnings;
use File::Path qw(remove_tree);
use IPC::Run;
use Time::HiRes qw(gettimeofday tv_interval);
my $backup_dir = 'pgbackup';
my @result_order;
my %results;
my @base_cmd = ('pg_basebackup', '-D', $backup_dir);
my @manifest_options = (
{ 'name' => 'omit', 'options' => ['--no-manifest' ] },
{ 'name' => 'none', 'options' => ['--manifest-checksums=none' ] },
{ 'name' => 'crc32c', 'options' => ['--manifest-checksums=crc32c' ] },
{ 'name' => 'sha256', 'options' => ['--manifest-checksums=sha256' ] },
);
my @connection_options = (
{ 'name' => 'unix', 'options' => [] },
{ 'name' => 'tcp',
'options' => [ '-d', 'host=127.0.0.1 sslmode=disable' ] },
{ 'name' => 'tcpssl',
'options' => [ '-d', 'host=127.0.0.1 sslmode=require' ] },
);
my @output_options = (
{ 'name' => 'plain', 'options' => [ '-Fp' ] },
{ 'name' => 'tar', 'options' => [ '-Ft' ] },
{ 'name' => 'targzip', 'options' => [ '-Ft', '-z' ] },
);
remove_tree($backup_dir);
for my $n (1, 2, 3)
{
for my $mopt (@manifest_options)
{
for my $copt (@connection_options)
{
for my $oopt (@output_options)
{
my @cmd = @base_cmd;
push @cmd, @{$mopt->{'options'}};
push @cmd, @{$copt->{'options'}};
push @cmd, @{$oopt->{'options'}};
my @tag;
push @tag, $copt->{'name'};
push @tag, $oopt->{'name'};
push @tag, 'm:' . $mopt->{'name'};
my $tag = join '+', @tag;
doit($tag, @cmd);
}
}
}
}
print "\n=== result summary ===\n";
for my $tag (@result_order)
{
print $tag, ' ', join(' ', @{$results{$tag}}), "\n";
}
sub doit
{
my ($tag, @cmd) = @_;
my $t0 = [ gettimeofday() ];
IPC::Run::run(@cmd);
my $elapsed = tv_interval( $t0, [ gettimeofday() ]);
print $tag, " ", $elapsed, "\n";
push @result_order, $tag if !exists $results{$tag};
push @{$results{$tag}}, $elapsed;
remove_tree($backup_dir);
}
