Re: ZFS + mysql appears to be killing my SSD's

On 05/07/2021 16:09, Karl Denninger wrote:
> As noted elsewhere assuming ashift=12 the answer on write amplification 
> is no.
> 
> Geli should be initialized with -s 4096; I'm assuming you did that?

Yup, I have everything defaulting to 4k sectors now, and I was hoping 
there was no write amplification going on if I did that.

> I have a 5-unit geli-encrypted root pool, all Intel 240gb SSDs. They do 
> not report remaining lifetime via smart but do report indications of 
> trouble.  Here's one example snippet from one of the drives in that pool:

Thanks for this ... its inyeresting to have something to
compare against. Mine are Kingston 240G drives, though some
of the replacements will be Crucial (i.e. Micron)



> Device Statistics (GP Log 0x04)
> Page  Offset Size        Value Flags Description
> 0x01  =====  =               =  ===  == General Statistics (rev 2) ==
> 0x01  0x008  4             100  ---  Lifetime Power-On Resets
> 0x01  0x018  6    118722148102  ---  Logical Sectors Written
> 0x01  0x020  6        89033895  ---  Number of Write Commands
> 0x01  0x028  6     93271951909  ---  Logical Sectors Read
> 0x01  0x030  6         6797990  ---  Number of Read Commands

So this is interesting, because my vaues for the machine I was
describing arlier, where a drive was replaced are

ada0:0x01  0x018  6      3084505072  ---  Logical Sectors Written
ada0:0x01  0x020  6      1219372767  ---  Number of Write Commands
ada0:0x01  0x028  6      1168765724  ---  Logical Sectors Read
ada0:0x01  0x030  6        56588781  ---  Number of Read Commands

ada1:0x01  0x018  6      2014784574  ---  Logical Sectors Written
ada1:0x01  0x020  6        68557083  ---  Number of Write Commands
ada1:0x01  0x028  6        41230034  ---  Logical Sectors Read
ada1:0x01  0x030  6         2438618  ---  Number of Read Commands

The replacement has more written sectors than the one which wasnt 
replaced. yet these are in RAID-1, so how can that be? The
numbers for sectors written are way lower than yours, but the write
commands are much higher. For a pair of drives in RAID-1 where ada0 has
been replaced and resilvered from ada1, those looks very wrong to me.


> 6 years in-use, roughly, and no indication of anything going on in terms 
> of warnings about utilization or wear-out.  There is a MYSQL database on

yes, your figures are what I expect to see, but the ones above are what 
I actually have. The disparity between the two halves of the RAID are 
bothering me, I have to say.

Do you have any tuning applies to your ZFS filesystems ? I have reduced
the recrodsize on the data directories to 32k to better match innodb
and have turned off atime and sync. But those are things I would expect
to reduce the write load, not increase it.

-pete.