Message-ID: <535E781A.5030708@denninger.net>
Date: Mon, 28 Apr 2014 10:47:38 -0500
From: Karl Denninger <karl@denninger.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.4.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Revisiting disk layout on ZFS systems
Content-Type: multipart/signed; protocol="application/pkcs7-signature";
 micalg=sha1; boundary="------------ms020803070405060708050503"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a cryptographically signed message in MIME format.

--------------ms020803070405060708050503
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

I've been doing a bit of benchmarking and real-world performance=20
testing, and have found some curious results.

The load in question is a fairly-busy machine hosting a web service that =

uses Postgresql as its back end.

"Conventional Wisdom" is that you want to run an 8k record size to match =

Postgresql's inherent write size for the database.

However, operational experience says this may no longer be the case now=20
that modern ZFS systems support LZ4 compression, because modern CPUs can =

compress fast enough that they overrun raw I/O capacity. This in turn=20
means that the recordsize is no longer the record size, basically, and=20
Postgresql's on-disk file format is rather compressible -- indeed, in=20
actual performance on my dataset it appears to be roughly 1.24x which is =

nothing to sneeze at.

The odd thing is that I am getting better performance with a 128k record =

size on this application than I get with an 8k one!  Not only is the=20
system faster to respond subjectively and can it sustain a higher TPS=20
load objectively but the I/O busy percentage as measured during=20
operation is MARKEDLY lower (by nearly an order of magnitude!)

This is not expected behavior!

What I am curious about, however, is the xlog -- that appears to suffer=20
pretty badly from 128k record size, although it compresses even=20
more-materially; 1.94x (!)

The files in the xlog directory are large (16MB each) and thus "first=20
blush" would be that having a larger record size for that storage area=20
would help.  It appears that instead it hurts.

Ideas?

--=20
-- Karl
karl@denninger.net


--------------ms020803070405060708050503
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: S/MIME Cryptographic Signature

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIFTzCC
BUswggQzoAMCAQICAQgwDQYJKoZIhvcNAQEFBQAwgZ0xCzAJBgNVBAYTAlVTMRAwDgYDVQQI
EwdGbG9yaWRhMRIwEAYDVQQHEwlOaWNldmlsbGUxGTAXBgNVBAoTEEN1ZGEgU3lzdGVtcyBM
TEMxHDAaBgNVBAMTE0N1ZGEgU3lzdGVtcyBMTEMgQ0ExLzAtBgkqhkiG9w0BCQEWIGN1c3Rv
bWVyLXNlcnZpY2VAY3VkYXN5c3RlbXMubmV0MB4XDTEzMDgyNDE5MDM0NFoXDTE4MDgyMzE5
MDM0NFowWzELMAkGA1UEBhMCVVMxEDAOBgNVBAgTB0Zsb3JpZGExFzAVBgNVBAMTDkthcmwg
RGVubmluZ2VyMSEwHwYJKoZIhvcNAQkBFhJrYXJsQGRlbm5pbmdlci5uZXQwggIiMA0GCSqG
SIb3DQEBAQUAA4ICDwAwggIKAoICAQC5n2KBrBmG22nVntVdvgKCB9UcnapNThrW1L+dq6th
d9l4mj+qYMUpJ+8I0rTbY1dn21IXQBoBQmy8t1doKwmTdQ59F0FwZEPt/fGbRgBKVt3Quf6W
6n7kRk9MG6gdD7V9vPpFV41e+5MWYtqGWY3ScDP8SyYLjL/Xgr+5KFKkDfuubK8DeNqdLniV
jHo/vqmIgO+6NgzPGPgmbutzFQXlxUqjiNAAKzF2+Tkddi+WKABrcc/EqnBb0X8GdqcIamO5
SyVmuM+7Zdns7D9pcV16zMMQ8LfNFQCDvbCuuQKMDg2F22x5ekYXpwjqTyfjcHBkWC8vFNoY
5aFMdyiN/Kkz0/kduP2ekYOgkRqcShfLEcG9SQ4LQZgqjMpTjSOGzBr3tOvVn5LkSJSHW2Z8
Q0dxSkvFG2/lsOWFbwQeeZSaBi5vRZCYCOf5tRd1+E93FyQfpt4vsrXshIAk7IK7f0qXvxP4
GDli5PKIEubD2Bn+gp3vB/DkfKySh5NBHVB+OPCoXRUWBkQxme65wBO02OZZt0k8Iq0i4Rci
WV6z+lQHqDKtaVGgMsHn6PoeYhjf5Al5SP+U3imTjF2aCca1iDB5JOccX04MNljvifXgcbJN
nkMgrzmm1ZgJ1PLur/ADWPlnz45quOhHg1TfUCLfI/DzgG7Z6u+oy4siQuFr9QT0MQIDAQAB
o4HWMIHTMAkGA1UdEwQCMAAwEQYJYIZIAYb4QgEBBAQDAgWgMAsGA1UdDwQEAwIF4DAsBglg
hkgBhvhCAQ0EHxYdT3BlblNTTCBHZW5lcmF0ZWQgQ2VydGlmaWNhdGUwHQYDVR0OBBYEFHw4
+LnuALyLA5Cgy7T5ZAX1WzKPMB8GA1UdIwQYMBaAFF3U3hpBZq40HB5VM7B44/gmXiI0MDgG
CWCGSAGG+EIBAwQrFilodHRwczovL2N1ZGFzeXN0ZW1zLm5ldDoxMTQ0My9yZXZva2VkLmNy
bDANBgkqhkiG9w0BAQUFAAOCAQEAZ0L4tQbBd0hd4wuw/YVqEBDDXJ54q2AoqQAmsOlnoxLO
31ehM/LvrTIP4yK2u1VmXtUumQ4Ao15JFM+xmwqtEGsh70RRrfVBAGd7KOZ3GB39FP2TgN/c
L5fJKVxOqvEnW6cL9QtvUlcM3hXg8kDv60OB+LIcSE/P3/s+0tEpWPjxm3LHVE7JmPbZIcJ1
YMoZvHh0NSjY5D0HZlwtbDO7pDz9sZf1QEOgjH828fhtborkaHaUI46pmrMjiBnY6ujXMcWD
pxtikki0zY22nrxfTs5xDWGxyrc/cmucjxClJF6+OYVUSaZhiiHfa9Pr+41okLgsRB0AmNwE
f6ItY3TI8DGCBQowggUGAgEBMIGjMIGdMQswCQYDVQQGEwJVUzEQMA4GA1UECBMHRmxvcmlk
YTESMBAGA1UEBxMJTmljZXZpbGxlMRkwFwYDVQQKExBDdWRhIFN5c3RlbXMgTExDMRwwGgYD
VQQDExNDdWRhIFN5c3RlbXMgTExDIENBMS8wLQYJKoZIhvcNAQkBFiBjdXN0b21lci1zZXJ2
aWNlQGN1ZGFzeXN0ZW1zLm5ldAIBCDAJBgUrDgMCGgUAoIICOzAYBgkqhkiG9w0BCQMxCwYJ
KoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0xNDA0MjgxNTQ3MzhaMCMGCSqGSIb3DQEJBDEW
BBRtTSfAK1sDdVf4U/oOkcq1PKmqXjBsBgkqhkiG9w0BCQ8xXzBdMAsGCWCGSAFlAwQBKjAL
BglghkgBZQMEAQIwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFA
MAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMIG0BgkrBgEEAYI3EAQxgaYwgaMwgZ0xCzAJBgNV
BAYTAlVTMRAwDgYDVQQIEwdGbG9yaWRhMRIwEAYDVQQHEwlOaWNldmlsbGUxGTAXBgNVBAoT
EEN1ZGEgU3lzdGVtcyBMTEMxHDAaBgNVBAMTE0N1ZGEgU3lzdGVtcyBMTEMgQ0ExLzAtBgkq
hkiG9w0BCQEWIGN1c3RvbWVyLXNlcnZpY2VAY3VkYXN5c3RlbXMubmV0AgEIMIG2BgsqhkiG
9w0BCRACCzGBpqCBozCBnTELMAkGA1UEBhMCVVMxEDAOBgNVBAgTB0Zsb3JpZGExEjAQBgNV
BAcTCU5pY2V2aWxsZTEZMBcGA1UEChMQQ3VkYSBTeXN0ZW1zIExMQzEcMBoGA1UEAxMTQ3Vk
YSBTeXN0ZW1zIExMQyBDQTEvMC0GCSqGSIb3DQEJARYgY3VzdG9tZXItc2VydmljZUBjdWRh
c3lzdGVtcy5uZXQCAQgwDQYJKoZIhvcNAQEBBQAEggIAaDcnvgY96IJzOB3WYp4grH+FTzc/
wTEnXst6LadEEIGwdAWyIQ+bEFTnMP0utVCj6C0UKWCaNuzSN2qK3yr3c6EmhL5bQyaYFnyO
+zNPsnpPbIlz+gKa5FFdoT2VjNhn4tsqVpzcgjan+ZIgpB3xFVGVlvbrpKLOm81TWgJCrwYh
/KqRM85BMxZsdYuP/9tK+yKLJArV9OLdp4hjUa1KG6mni3qgX+8DULvtbk1OcFqEHapE8X1Z
BgGmw8iEU6+VUvNrVLwL3YowkIyHw6fBl/wjTwh8b/UB1SxuWzlc5EH+8EQXpjFlZyF9Noqv
XLJ8XdLMB1WMII51MX1miRkJ83tcMGHwjrSZwhsXHe0vixKV8xzxZM9VXUT8nfiURJ7Tku8O
5cScNaBclQ0q9WHHvVsYbRbD143ouvX6JcVaaPy/I+53sdvlyhnyZQoZriocigSw5Snffmto
HmV7nyCqCbX4cjz46NE7KmrFai/d3U8CtirCDUECJ9Mpgopwn0LB8rVIgvqcc6Q7ZvmFZcFZ
E8PkogroOVFXExs4XGwitirhS7OkqfpU7yJQRqoeSYVloLJLG9MeP8m4Ng02DOnxULFbhxQe
yekuyl6LVs4mfpHEaDFMvxq/DrAjXXYAOOFwT4r1/Z/8D+gL9xSkwsw6mJTXiA2u87Fp0ZcV
BuUniu8AAAAAAAA=
--------------ms020803070405060708050503--