Date: Wed, 16 Oct 2024 16:56:55 +0200
From: Bzzzz <lazyvirus@gmx.com>
To: pgsql-novice@lists.postgresql.org
Subject: Re: Recommendations on how to combine SSD and HDD drives in bare
 metal PostgreSQL server
Message-ID: <20241016165655.29c2d2a8@msi>
In-Reply-To: <89E7D559-F734-4739-9730-7EDDF787910D@flaky.build>
References: <89E7D559-F734-4739-9730-7EDDF787910D@flaky.build>
Organization: Anyone, anywhere but in banana demokratik republik of france
 or ueRSS.
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:g+WsBRr9xmI=;GCcVLOdBruFcldWfjxxDKK/uVXG
 oW6h7fgKj5Qmxguiw/Ou6W4Yhpoc4QyrTCldDj36EkRuJsNuNS5h1Wa9S+qU1blL6EWb88kUZ
 P3iiggT3nbQapjmmNdnPNxf/C/HV7RwtkoEB11QWoH7+SHmPrR8OiA8kZr1AtPHPXoo3WVXvi
 i494ogwjbfbOT4xu+VBLu46U1LrBlA7jxnOeM3WFgE/Q/cK/qDpCZr2C+q8QPvA5x78wTHLHg
 +G+9Lm9QTrZeoRd3nS4K1GygwhVbAgo7lxuNsxx/s/dTd8P27ouB2JiVI4gJLVc0/2xw0cftp
 ueQdyNKhG8GdIjqLlvDYTmN8OnXB4KuLQBuvwYRcX5GdRA+PXwwf3O+fgXq0hxoYJLZYk4i3b
 TpFtTYRrDfgj4Z46P4qgEh9z5BsuAKtg0GIn4Fwxq2xnCEPEgSR7/58jIFMXIm2PNARv1bjMU
 XgPRwEyPEXdkTrIzOJi9gsjPXCKNlBxaJgL/TFt28CmmXg3kUJU2eWVNwcNBIEhKWutDk3Hyz
 9l8p64VZDk5FUqxk+0z7H0E3k+Q5MwkmFbi+orwoIElCJwn3lRY/z1xUoMMSnfBiE21KyVf7f
 eZRVgSOcwxd8ZUXrH5rvm2JL/2QmOClVWGsKoC2EAJeAFqHRk6mTu9wdSGhtxZt3xF+pW4Mdk
 wFc19Uj3pyn9KhzmT/StwRyhvfDCmFEi8CZ38N1NAFrX7JOgH/Imao1dcQxPyj/7bc2gIR+kr
 eNGQBAv9TFYae59KjQZJGRtxYwN1ZREVJB9LAQNUEHJhkmjdbzYZwYFnkokAiCq/uSxgYDZ36
 m3WnOrGcBXx2vtcHPFHMZFLe7fntluYNudNHmBzP+WqsNWfoH+mMnIqsgZBYT3dneiihW08yk
 rNDBdDoLxh3CW/A==
Archived-At: 
 <https://www.postgresql.org/message-id/20241016165655.29c2d2a8%40msi>
Precedence: bulk

On Wed, 16 Oct 2024 17:06:24 +0300
Onni Hakala <onni@fyff.ee> wrote:

> Hey,

Hi,

> I have a large dataset of > 100TB which would be very expensive to
> store solely into SSD drives.
>
> I have access to a server which has 2x 3.84TB NVME SSD disks and
> large array of HDD drives 8 x 22TB.
>
> Most of the data that I have in my dataset is very rarely accessed
> and is stored only for archival purposes.

I can't answer to the rest but with the configuration you describe,
you might meet some slow down because HDD speed is not what you think
it is when you reach a barrier in disks filling.


A test on a 2TB/7200RPM returns that, at the beginning of the disk it is
quite fast :
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
hdparm --offset 1g --direct -t /dev/sdb1
/dev/sdb1:
 geometry      =3D 243201/255/63, sectors =3D 3907027087, start =3D 2048
 Timing O_DIRECT disk reads (offset 1 GB): 504 MB in  3.00 seconds =3D
 167.97 MB/sec


@ 50%, there's even a small gain (NB : this is a PITA, hdparm displays
GB where it uses in fact GiB:( :
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
hdparm --offset 931g --direct -t /dev/sdb1
/dev/sdb1:
 geometry      =3D 243201/255/63, sectors =3D 3907027087, start =3D 2048
 Timing O_DIRECT disk reads (offset 931 GB): 520 MB in  3.01 seconds =3D
 172.73 MB/sec


@ 70% of the disk, the loss is only 11% :
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
hdparm --offset 1304g --direct -t /dev/sdb1
/dev/sdb1:
 geometry      =3D 243201/255/63, sectors =3D 3907027087, start =3D 2048
 Timing O_DIRECT disk reads (offset 1304 GB): 440 MB in  3.01 seconds =3D
 146.38 MB/sec


@ 80% of the disk the read speed has already lost 21% :
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
hdparm --offset 1490g --direct -t /dev/sdb1
/dev/sdb1:
 geometry      =3D 243201/255/63, sectors =3D 3907027087, start =3D 2048
 Timing O_DIRECT disk reads (offset 1490 GB): 390 MB in  3.01 seconds =3D
129.68 MB/sec


but @ 90% of the disk, the loss climbs @ 30.5% :
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
hdparm --offset 1676g --direct -t /dev/sdb1
/dev/sdb1:
 geometry      =3D 243201/255/63, sectors =3D 3907027087, start =3D 2048
 Timing O_DIRECT disk reads (offset 1676 GB): 344 MB in  3.01 seconds =3D
 114.45 MB/sec


So, for a good response time, it is best to never fill your disks
further than 75% and it is also always a good practice to avoid
some inconvenience, especially when using a COW FS, such as ZFS.

Jean-Yves

=2D-