Message-ID: <8619437df796903e64c8479e299d13ebacb5fbe0.camel@j-davis.com>
Subject: Re: Trouble with hashagg spill I/O pattern and costing
From: Jeff Davis <pgsql@j-davis.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org
Date: Thu, 21 May 2020 14:16:37 -0700
In-Reply-To: <20200521191318.gw3xjivrxracvzmg@development>
References: <20200519151202.u2p2gpiawoaznsv2@development>
	 <88dd39d782178539d7ee46f98b98579940e2a12e.camel@j-davis.com>
	 <20200519175320.tl4qm6le5dbg2pcf@development>
	 <acad6f2ba8d560b9dfa9a564c7b7d3162abf8347.camel@j-davis.com>
	 <20200521191318.gw3xjivrxracvzmg@development>
Content-Type: multipart/mixed; boundary="=-zT2YnXEqGs0RKXY9k5a/"
Mime-Version: 1.0
Precedence: bulk


--=-zT2YnXEqGs0RKXY9k5a/
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

On Thu, 2020-05-21 at 21:13 +0200, Tomas Vondra wrote:
> 2) We could make it self-tuning, by increasing the number of blocks
> we pre-allocate. So every time we exhaust the range, we double the
> number of blocks (with a reasonable maximum, like 1024 or so). Or we
> might just increment it by 32, or something.

Attached a new version that uses the doubling behavior, and cleans it
up a bit. It also returns the unused prealloc blocks back to lts-
>freeBlocks when the tape is rewound for reading.

> IIUC the danger of pre-allocating blocks is that we might not fill
> them,
> resulting in temp file much larger than necessary. It might be
> harmless
> on some (most?) current filesystems that don't actually allocate
> space
> for blocks that are never written, but it also confuses our
> accounting
> of temporary file sizes. So we should try to limit that, and growing
> the
> number of pre-allocated blocks over time seems reasonable.

There's another danger here: it doesn't matter how well the filesystem
deals with sparse writes, because ltsWriteBlock fills in the holes with
zeros anyway. That's potentially a significant amount of wasted IO
effort if we aren't careful.

Regards,
	Jeff Davis


--=-zT2YnXEqGs0RKXY9k5a/
Content-Disposition: attachment; filename="logtape-prealloc-v2.patch"
Content-Type: text/x-patch; name="logtape-prealloc-v2.patch"; charset="UTF-8"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL3NvcnQvbG9ndGFwZS5jIGIvc3JjL2JhY2tl
bmQvdXRpbHMvc29ydC9sb2d0YXBlLmMKaW5kZXggYmM4ZDU2ODA3ZTIuLjg0ZjlkNmQyMzU4IDEw
MDY0NAotLS0gYS9zcmMvYmFja2VuZC91dGlscy9zb3J0L2xvZ3RhcGUuYworKysgYi9zcmMvYmFj
a2VuZC91dGlscy9zb3J0L2xvZ3RhcGUuYwpAQCAtMTEwLDYgKzExMCw4IEBAIHR5cGVkZWYgc3Ry
dWN0IFRhcGVCbG9ja1RyYWlsZXIKICNkZWZpbmUgVGFwZUJsb2NrU2V0TkJ5dGVzKGJ1ZiwgbmJ5
dGVzKSBcCiAJKFRhcGVCbG9ja0dldFRyYWlsZXIoYnVmKS0+bmV4dCA9IC0obmJ5dGVzKSkKIAor
I2RlZmluZSBUQVBFX1dSSVRFX1BSRUFMTE9DX01JTiA4CisjZGVmaW5lIFRBUEVfV1JJVEVfUFJF
QUxMT0NfTUFYIDEyOAogCiAvKgogICogVGhpcyBkYXRhIHN0cnVjdHVyZSByZXByZXNlbnRzIGEg
c2luZ2xlICJsb2dpY2FsIHRhcGUiIHdpdGhpbiB0aGUgc2V0CkBAIC0xNTEsNiArMTUzLDIxIEBA
IHR5cGVkZWYgc3RydWN0IExvZ2ljYWxUYXBlCiAJaW50CQkJbWF4X3NpemU7CQkvKiBoaWdoZXN0
IHVzZWZ1bCwgc2FmZSBidWZmZXJfc2l6ZSAqLwogCWludAkJCXBvczsJCQkvKiBuZXh0IHJlYWQv
d3JpdGUgcG9zaXRpb24gaW4gYnVmZmVyICovCiAJaW50CQkJbmJ5dGVzOwkJCS8qIHRvdGFsICMg
b2YgdmFsaWQgYnl0ZXMgaW4gYnVmZmVyICovCisKKwkvKgorCSAqIFdoZW4gbXVsdGlwbGUgdGFw
ZXMgYXJlIGJlaW5nIHdyaXR0ZW4gdG8gY29uY3VycmVudGx5IChhcyBpbiBIYXNoQWdnKSwKKwkg
KiBhdm9pZCBleGNlc3NpdmUgZnJhZ21lbnRhdGlvbiBieSBwcmVhbGxvY2F0aW5nIGJsb2NrIG51
bWJlcnMgdG8KKwkgKiBpbmRpdmlkdWFsIHRhcGVzLiBUaGUgcHJlYWxsb2NhdGVkIGJsb2NrIG51
bWJlcnMgYXJlIGhlbGQgaW4gYW4gYXJyYXkKKwkgKiBzb3J0ZWQgaW4gZGVzY2VuZGluZyBvcmRl
cjsgYmxvY2tzIGFyZSBjb25zdW1lZCBmcm9tIHRoZSBlbmQgb2YgdGhlCisJICogYXJyYXkgKGxv
d2VzdCBibG9jayBudW1iZXJzIGZpcnN0KS4KKwkgKgorCSAqIE5vIGZpbGVzeXN0ZW0gb3BlcmF0
aW9ucyBhcmUgcGVyZm9ybWVkIGZvciBwcmVhbGxvY2F0aW9uOyBvbmx5IHRoZQorCSAqIGJsb2Nr
IG51bWJlcnMgYXJlIHJlc2VydmVkLiBUaGlzIG1heSBsZWFkIHRvIHNwYXJzZSB3cml0ZXMsIHdo
aWNoIHdpbGwKKwkgKiBjYXVzZSBsdHNXcml0ZUJsb2NrKCkgdG8gZmlsbCBpbiBob2xlcyB3aXRo
IHplcm9zLgorCSAqLworCWxvbmcJICAgKnByZWFsbG9jOworCWludAkJIAlucHJlYWxsb2M7IC8q
IG51bWJlciBvZiBlbGVtZW50cyBpbiBsaXN0ICovCisJaW50CQkJcHJlYWxsb2Nfc2l6ZTsgLyog
bnVtYmVyIG9mIGVsZW1lbnRzIGxpc3QgY2FuIGhvbGQgKi8KIH0gTG9naWNhbFRhcGU7CiAKIC8q
CkBAIC0xOTgsNiArMjE1LDcgQEAgc3RydWN0IExvZ2ljYWxUYXBlU2V0CiBzdGF0aWMgdm9pZCBs
dHNXcml0ZUJsb2NrKExvZ2ljYWxUYXBlU2V0ICpsdHMsIGxvbmcgYmxvY2tudW0sIHZvaWQgKmJ1
ZmZlcik7CiBzdGF0aWMgdm9pZCBsdHNSZWFkQmxvY2soTG9naWNhbFRhcGVTZXQgKmx0cywgbG9u
ZyBibG9ja251bSwgdm9pZCAqYnVmZmVyKTsKIHN0YXRpYyBsb25nIGx0c0dldEZyZWVCbG9jayhM
b2dpY2FsVGFwZVNldCAqbHRzKTsKK3N0YXRpYyBsb25nIGx0c0dldFByZWFsbG9jQmxvY2soTG9n
aWNhbFRhcGVTZXQgKmx0cywgTG9naWNhbFRhcGUgKmx0KTsKIHN0YXRpYyB2b2lkIGx0c1JlbGVh
c2VCbG9jayhMb2dpY2FsVGFwZVNldCAqbHRzLCBsb25nIGJsb2NrbnVtKTsKIHN0YXRpYyB2b2lk
IGx0c0NvbmNhdFdvcmtlclRhcGVzKExvZ2ljYWxUYXBlU2V0ICpsdHMsIFRhcGVTaGFyZSAqc2hh
cmVkLAogCQkJCQkJCQkgU2hhcmVkRmlsZVNldCAqZmlsZXNldCk7CkBAIC0zOTcsNiArNDE1LDUx
IEBAIGx0c0dldEZyZWVCbG9jayhMb2dpY2FsVGFwZVNldCAqbHRzKQogCXJldHVybiBibG9ja251
bTsKIH0KIAorLyoKKyAqIFJldHVybiB0aGUgbG93ZXN0IGZyZWUgYmxvY2sgbnVtYmVyIGZyb20g
dGhlIHRhcGUncyBwcmVhbGxvY2F0aW9uIGxpc3QuCisgKiBSZWZpbGwgdGhlIHByZWFsbG9jYXRp
b24gbGlzdCBpZiBuZWNlc3NhcnkuCisgKi8KK3N0YXRpYyBsb25nCitsdHNHZXRQcmVhbGxvY0Js
b2NrKExvZ2ljYWxUYXBlU2V0ICpsdHMsIExvZ2ljYWxUYXBlICpsdCkKK3sKKwkvKiBzb3J0ZWQg
aW4gZGVzY2VuZGluZyBvcmRlciwgc28gcmV0dXJuIHRoZSBsYXN0IGVsZW1lbnQgKi8KKwlpZiAo
bHQtPm5wcmVhbGxvYyA+IDApCisJCXJldHVybiBsdC0+cHJlYWxsb2NbLS1sdC0+bnByZWFsbG9j
XTsKKworCWlmIChsdC0+cHJlYWxsb2MgPT0gTlVMTCkKKwl7CisJCWx0LT5wcmVhbGxvY19zaXpl
ID0gVEFQRV9XUklURV9QUkVBTExPQ19NSU47CisJCWx0LT5wcmVhbGxvYyA9IChsb25nICopIHBh
bGxvYyhzaXplb2YobG9uZykgKiBsdC0+cHJlYWxsb2Nfc2l6ZSk7CisJfQorCWVsc2UKKwl7CisJ
CS8qIHdoZW4gdGhlIHByZWFsbG9jYXRpb24gbGlzdCBydW5zIG91dCwgZG91YmxlIHRoZSBzaXpl
ICovCisJCWlmIChsdC0+cHJlYWxsb2Nfc2l6ZSAqIDIgPD0gVEFQRV9XUklURV9QUkVBTExPQ19N
QVgpCisJCQlsdC0+cHJlYWxsb2Nfc2l6ZSAqPSAyOworCQlsdC0+cHJlYWxsb2MgPSAobG9uZyAq
KSByZXBhbGxvYyhsdC0+cHJlYWxsb2MsCisJCQkJCQkJCQkJIHNpemVvZihsb25nKSAqIGx0LT5w
cmVhbGxvY19zaXplKTsKKwl9CisKKwkvKiByZWZpbGwgcHJlYWxsb2NhdGlvbiBsaXN0ICovCisJ
bHQtPm5wcmVhbGxvYyA9IGx0LT5wcmVhbGxvY19zaXplOworCWZvciAoaW50IGkgPSBsdC0+bnBy
ZWFsbG9jOyBpID4gMDsgaS0tKQorCQlsdC0+cHJlYWxsb2NbaSAtIDFdID0gbHRzR2V0RnJlZUJs
b2NrKGx0cyk7CisKKyNpZmRlZiBVU0VfQVNTRVJUX0NIRUNLSU5HCisJeworCQkvKiB2ZXJpZnkg
dGhhdCBsaXN0IGlzIGluIHJldmVyc2Ugc29ydGVkIG9yZGVyICovCisJCWxvbmcgbGFzdF9ibG9j
a19udW1iZXIgPSAtMTsKKwkJZm9yIChpbnQgaSA9IGx0LT5ucHJlYWxsb2M7IGkgPiAwOyBpLS0p
CisJCXsKKwkJCUFzc2VydChsdC0+cHJlYWxsb2NbaSAtIDFdID4gbGFzdF9ibG9ja19udW1iZXIp
OworCQkJbGFzdF9ibG9ja19udW1iZXIgPSBsdC0+cHJlYWxsb2NbaSAtIDFdOworCQl9CisJfQor
I2VuZGlmCisKKwlyZXR1cm4gbHQtPnByZWFsbG9jWy0tbHQtPm5wcmVhbGxvY107Cit9CisKIC8q
CiAgKiBSZXR1cm4gYSBibG9jayMgdG8gdGhlIGZyZWVsaXN0LgogICovCkBAIC01NTcsNiArNjIw
LDkgQEAgbHRzSW5pdFRhcGUoTG9naWNhbFRhcGUgKmx0KQogCWx0LT5tYXhfc2l6ZSA9IE1heEFs
bG9jU2l6ZTsKIAlsdC0+cG9zID0gMDsKIAlsdC0+bmJ5dGVzID0gMDsKKwlsdC0+cHJlYWxsb2Mg
PSBOVUxMOworCWx0LT5ucHJlYWxsb2MgPSAwOworCWx0LT5wcmVhbGxvY19zaXplID0gMDsKIH0K
IAogLyoKQEAgLTcwOSw3ICs3NzUsNyBAQCBMb2dpY2FsVGFwZVdyaXRlKExvZ2ljYWxUYXBlU2V0
ICpsdHMsIGludCB0YXBlbnVtLAogCQlBc3NlcnQobHQtPmZpcnN0QmxvY2tOdW1iZXIgPT0gLTEp
OwogCQlBc3NlcnQobHQtPnBvcyA9PSAwKTsKIAotCQlsdC0+Y3VyQmxvY2tOdW1iZXIgPSBsdHNH
ZXRGcmVlQmxvY2sobHRzKTsKKwkJbHQtPmN1ckJsb2NrTnVtYmVyID0gbHRzR2V0UHJlYWxsb2NC
bG9jayhsdHMsIGx0KTsKIAkJbHQtPmZpcnN0QmxvY2tOdW1iZXIgPSBsdC0+Y3VyQmxvY2tOdW1i
ZXI7CiAKIAkJVGFwZUJsb2NrR2V0VHJhaWxlcihsdC0+YnVmZmVyKS0+cHJldiA9IC0xTDsKQEAg
LTczMyw3ICs3OTksNyBAQCBMb2dpY2FsVGFwZVdyaXRlKExvZ2ljYWxUYXBlU2V0ICpsdHMsIGlu
dCB0YXBlbnVtLAogCQkJICogRmlyc3QgYWxsb2NhdGUgdGhlIG5leHQgYmxvY2ssIHNvIHRoYXQg
d2UgY2FuIHN0b3JlIGl0IGluIHRoZQogCQkJICogJ25leHQnIHBvaW50ZXIgb2YgdGhpcyBibG9j
ay4KIAkJCSAqLwotCQkJbmV4dEJsb2NrTnVtYmVyID0gbHRzR2V0RnJlZUJsb2NrKGx0cyk7CisJ
CQluZXh0QmxvY2tOdW1iZXIgPSBsdHNHZXRQcmVhbGxvY0Jsb2NrKGx0cywgbHQpOwogCiAJCQkv
KiBzZXQgdGhlIG5leHQtcG9pbnRlciBhbmQgZHVtcCB0aGUgY3VycmVudCBibG9jay4gKi8KIAkJ
CVRhcGVCbG9ja0dldFRyYWlsZXIobHQtPmJ1ZmZlciktPm5leHQgPSBuZXh0QmxvY2tOdW1iZXI7
CkBAIC04MzUsMTMgKzkwMSwyMyBAQCBMb2dpY2FsVGFwZVJld2luZEZvclJlYWQoTG9naWNhbFRh
cGVTZXQgKmx0cywgaW50IHRhcGVudW0sIHNpemVfdCBidWZmZXJfc2l6ZSkKIAkJQXNzZXJ0KGx0
LT5mcm96ZW4pOwogCX0KIAotCS8qIEFsbG9jYXRlIGEgcmVhZCBidWZmZXIgKHVubGVzcyB0aGUg
dGFwZSBpcyBlbXB0eSkgKi8KIAlpZiAobHQtPmJ1ZmZlcikKIAkJcGZyZWUobHQtPmJ1ZmZlcik7
CiAKIAkvKiB0aGUgYnVmZmVyIGlzIGxhemlseSBhbGxvY2F0ZWQsIGJ1dCBzZXQgdGhlIHNpemUg
aGVyZSAqLwogCWx0LT5idWZmZXIgPSBOVUxMOwogCWx0LT5idWZmZXJfc2l6ZSA9IGJ1ZmZlcl9z
aXplOworCisJLyogZnJlZSB0aGUgcHJlYWxsb2NhdGlvbiBsaXN0LCBhbmQgcmV0dXJuIHVudXNl
ZCBibG9jayBudW1iZXJzICovCisJaWYgKGx0LT5wcmVhbGxvYyAhPSBOVUxMKQorCXsKKwkJZm9y
IChpbnQgaSA9IGx0LT5ucHJlYWxsb2M7IGkgPiAwOyBpLS0pCisJCQlsdHNSZWxlYXNlQmxvY2so
bHRzLCBsdC0+cHJlYWxsb2NbaSAtIDFdKTsKKwkJcGZyZWUobHQtPnByZWFsbG9jKTsKKwkJbHQt
PnByZWFsbG9jID0gTlVMTDsKKwkJbHQtPm5wcmVhbGxvYyA9IDA7CisJCWx0LT5wcmVhbGxvY19z
aXplID0gMDsKKwl9CiB9CiAKIC8qCg==


--=-zT2YnXEqGs0RKXY9k5a/--