From nobody Wed Jul 7 17:05:12 2021 X-Original-To: freebsd-stable@mlmmj.nyi.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2610:1c1:1:606c::19:1]) by mlmmj.nyi.freebsd.org (Postfix) with ESMTP id E3CB811E75D2 for ; Wed, 7 Jul 2021 17:05:23 +0000 (UTC) (envelope-from wlosh@bsdimp.com) Received: from mail-qv1-xf33.google.com (mail-qv1-xf33.google.com [IPv6:2607:f8b0:4864:20::f33]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (4096 bits) server-digest SHA256 client-signature RSA-PSS (2048 bits) client-digest SHA256) (Client CN "smtp.gmail.com", Issuer "GTS CA 1O1" (verified OK)) by mx1.freebsd.org (Postfix) with ESMTPS id 4GKm5H5trFz4S32 for ; Wed, 7 Jul 2021 17:05:23 +0000 (UTC) (envelope-from wlosh@bsdimp.com) Received: by mail-qv1-xf33.google.com with SMTP id d2so1392078qvh.2 for ; Wed, 07 Jul 2021 10:05:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=bsdimp-com.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=q0+fWFJ3gabFhV3v+s2DuIw+4fwOQF+xOJkcOE+Rt+I=; b=s9y5x8rYN0ytvARe63Ya8aTb7Mh8L/DT6WrHsUEPJzpRCvqs5njwcB0O07M/p9fseI DcfANSwEd74+VT1ony95P1IEIkiuYfX7Q929I56HuOPlGiMkjfO9f9JjueVwroLYEsj/ PiaLDcQtWWbpnsW3q/yXDJVXb70MxJniDrvFs8V+GlTWjoql8CIzBUrPkAY3ter+98m2 yHzgYNOj+50Jv+N6q9xzrZSaR3baVHX51jsj4eOVe0ZqbDBgpXGDIsV+HMRpkKz/IhgJ cQONiDpMEt6NVjxT4KRPvNhniOo018vbJe6+IB7KqWL+xdIB74iuNFpFqq8hcUUT36WN vyIA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=q0+fWFJ3gabFhV3v+s2DuIw+4fwOQF+xOJkcOE+Rt+I=; b=TtmFRkAQTJCAvwGkh6NhhRIoLND11pDXlv5mxIIpdAesZMKkLZFKolo3B1dBiGcuLx BXq2MPZD7laHBAfbFvBgpRJMEUkvcgdUQZV77ZqLH6B3MkscfJ2/+dr0Ke7kNHWoLKQc 4RQhuw2e+24AroKftbRWxKwZB+GGCDeq9r7usbG5HlB6X1iRK+CP7VYskKmN2Nug2nOQ LYXe8u0h9NqI6DMajMs+b5HigNCagcZhgk117BF92ShJgJ92Pw5Buud23E6Hksg+i3ms 3MiP0Kvz0v7WKeIIRrwkYulcQ3QyeGt490YmU6gwxYH2TwQL5wVNlAjWrAJqFF7EKj/K ++YQ== X-Gm-Message-State: AOAM5336wUe8W2kGdyUv1hfoXIPMKXbDGc7CGuDC9e8J93J/mGWbP5EC DrDBm/t83f0TIn9VrPDEENIp3fuERw1gSBihQbMO7w== X-Google-Smtp-Source: ABdhPJwR473gU8QfebxOgHcom/uQJzuJ5lrD70FOl1nj+Gz3EN7F3wfZfuRdU2nkIWTXI+lTJFaM/9xyGLMqDcTA2YE= X-Received: by 2002:a0c:d7c4:: with SMTP id g4mr24412867qvj.23.1625677522892; Wed, 07 Jul 2021 10:05:22 -0700 (PDT) List-Id: Production branch of FreeBSD source code List-Archive: https://lists.freebsd.org/archives/freebsd-stable List-Help: List-Post: List-Subscribe: List-Unsubscribe: Sender: owner-freebsd-stable@freebsd.org X-BeenThere: freebsd-stable@freebsd.org MIME-Version: 1.0 References: <89c37c3e-22e8-006e-5826-33bd7db7739e@ingresso.co.uk> <2fd9b7e4-dc75-fedc-28d7-b98191167e6b@freebsd.org> <9c71d627-55b8-2464-6cc9-489e4ce98049@ingresso.co.uk> <2e6dbe13-e08f-1dab-1f5b-76a90c3a2ea7@nomadlogic.org> In-Reply-To: From: Warner Losh Date: Wed, 7 Jul 2021 11:05:12 -0600 Message-ID: Subject: Re: ZFS + mysql appears to be killing my SSD's To: Pete French Cc: Pete Wright , Alan Somers , Stefan Esser , FreeBSD Stable Mailing List Content-Type: multipart/alternative; boundary="000000000000458fc005c68b8bc9" X-Rspamd-Queue-Id: 4GKm5H5trFz4S32 X-Spamd-Bar: ---- Authentication-Results: mx1.freebsd.org; none X-Spamd-Result: default: False [-4.00 / 15.00]; REPLY(-4.00)[] X-ThisMailContainsUnwantedMimeParts: Y --000000000000458fc005c68b8bc9 Content-Type: text/plain; charset="UTF-8" On Wed, Jul 7, 2021 at 4:06 AM Pete French wrote: > > > On 07/07/2021 00:01, Pete Wright wrote: > > > i also wonder if this could be a TRIM related issue. according to > > zpoolprops(8) TRIM is not enabled by default on pools - but it also has > > this note (see autotrim): > > > > "Be aware that automatic trimming of recently freed data blocks > > can put significant stress on the underlying storage devices. > > This will vary depending of how well the specific device handles > > these commands. For lower end devices it is often possible to > > achieve most of the benefits of automatic trimming by running an > > on-demand (manual) TRIM periodically using the zpool trim > > command." > > > > > > I wonder if adjusting the autotrim feature will address these issues - i > > manually enable autotrim for my pools and have seen no bad effects under > > quite of bit of bursty i/o. if it is enabled i wonder if the ssd you > > are using doesn't play nice with autotrim and should stay disabled? > > I was thinking this too - the autotrim stuff came in with OpenZFS, but I > had trim enabled previously (I believe we had our own implementation on > FreeBSD ZFS, is that right?). But I am wondering if a schduled trim > might be a better option. Though the question arises as to 'how often' > in that case. > Two observations about this whole thread. First: the cheapest SSDs have a much lower DWPD (drive writes per day) rating now than they used to have. A few years ago, 3DWPD was normal, now it's closer to 0.3DWPD. If your drives are wearing out faster now than before, this may well be why. If you have a large write workload, you'll almost certainly need to buy more expensive drives with higher DWPD ratings. In general, the cost difference between the drives is small enough that over-provisioning this by a factor of about 10-20 (meaning if you think you only need 0.3DWPD, buy a 3DWPD drive), especially for smaller deployments where your time to replace the bad drives will easily exceed the delta in cost up front. Second, TRIM should help reduce write amplification that happens inside the drive. Ideally, it should be done inline. However, that's not always performant due to the quality of TRIM implementations on some drives. That's why the automated periodic TRIM features were added. TRIMs are most effective when you have lots of data that's written and shortly after becomes idle for a long time. If it's written and then is rewritten quickly after the blocks are released, then TRIMs have much less effect on the write amp than if there's a period of time that elapses. Which brings us to how often. That's tricky. A lot of that depends on the performance impact the TRIMs have on the on-going operations and expected lifetime of the recently written "cold" data (data that will stick around for a while). Daily is likely a good place to start, ideally at an off time. I don't really know your workload that well, so I can't say for sure, but that's a good place to start. TRIMs can only help the internal writeamp of the drive's FTL, but won't help the raw write rate. You'll need to change your application to reduce writes if you must write in excess of your drive's DWPD. FreeBSD's old ZFS had its own implementation, but that was never ported to OpenZFS. It was replaced by a better implementation with which I'm not too familiar. Warner --000000000000458fc005c68b8bc9--