From owner-freebsd-hackers@freebsd.org Wed Oct 17 03:31:56 2018 Return-Path: Delivered-To: freebsd-hackers@mailman.ysv.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2610:1c1:1:606c::19:1]) by mailman.ysv.freebsd.org (Postfix) with ESMTP id 56CBE10C19D1 for ; Wed, 17 Oct 2018 03:31:56 +0000 (UTC) (envelope-from allanjude@freebsd.org) Received: from mx1.scaleengine.net (mx1.scaleengine.net [209.51.186.6]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by mx1.freebsd.org (Postfix) with ESMTPS id E1A158DB3C for ; Wed, 17 Oct 2018 03:31:55 +0000 (UTC) (envelope-from allanjude@freebsd.org) Received: from [10.1.1.2] (Seawolf.HML3.ScaleEngine.net [209.51.186.28]) (Authenticated sender: allanjude.freebsd@scaleengine.com) by mx1.scaleengine.net (Postfix) with ESMTPSA id 15D661F01F for ; Wed, 17 Oct 2018 03:31:49 +0000 (UTC) Subject: Re: High load and MySQL slow without apparent reason To: freebsd-hackers@freebsd.org References: From: Allan Jude Openpgp: preference=signencrypt Autocrypt: addr=allanjude@freebsd.org; prefer-encrypt=mutual; keydata= xsFNBFVwZcYBEADwrZDH0xe0ZVjc9ORCc6PcBLwS/RTXA6NkvpD6ea02pZ8lPOVgteuuugFc D34LdDbiWr+479vfrKBh+Y38GL0oZ0/13j10tIlDMHSa5BU0y6ACtnhupFvVlQ57+XaJAb/q 7qkfSiuxVwQ3FY3PL3cl1RrIP5eGHLA9hu4eVbu+FOX/q/XVKz49HaeIaxzo2Q54572VzIo6 C28McX9m65UL5fXMUGJDDLCItLmehZlHsQQ+uBxvODLFpVV2lUgDR/0rDa0B9zHZX8jY8qQ7 ZdCSy7CwClXI054CkXZCaBzgxYh/CotdI8ezmaw7NLs5vWNTxaDEFXaFMQtMVhvqQBpHkfOD 7rjjOmFw00nJL4FuPE5Yut0CPyx8vLjVmNJSt/Y8WxxmhutsqJYFgYfWl/vaWkrFLur/Zcmz IklwLw35HLsCZytCN5A3rGKdRbQjD6QPXOTJu0JPrJF6t2xFkWAT7oxnSV0ELhl2g+JfMMz2 Z1PDmS3NRnyEdqEm7NoRGXJJ7bgxDbN+9SXTyOletqGNXj/bSrBvhvZ0RQrzdHAPwQUfVSU2 qBhQEi2apSZstgVNMan0GUPqCdbE2zpysg+zT7Yhvf9EUQbzPL4LpdK1llT9fZbrdMzEXvEF oSvwJFdV3sqKmZc7b+E3PuxK6GTsKqaukd/3Cj8aLHG1T1im1QARAQABzSJBbGxhbiBKdWRl IDxhbGxhbmp1ZGVAZnJlZWJzZC5vcmc+wsF/BBMBAgApBQJVcGXGAhsjBQkSzAMABwsJCAcD AgEGFQgCCQoLBBYCAwECHgECF4AACgkQGZU1PhKYC34Muw/+JOKpSfhhysWFYiRXynGRDe07 Z6pVsn7DzrPUMRNZfHu8Uujmmy3p2nx9FelIY9yjd2UKHhug+whM54MiIFs90eCRVa4XEsPR 4FFAm0DAWrrb7qhZFcE/GhHdRWpZ341WAElWf6Puj2devtRjfYbikvj5+1V1QmDbju7cEw5D mEET44pTuD2VMRJpu2yZZzkM0i+wKFuPxlhqreufA1VNkZXI/rIfkYWK+nkXd9Efw3YdCyCQ zUgTUCb88ttSqcyhik/li1CDbXBpkzDCKI6I/8fAb7jjOC9LAtrZJrdgONywcVFoyK9ZN7EN AVA+xvYCmuYhR/3zHWH1g4hAm1v1+gIsufhajhfo8/wY1SetlzPaYkSkVQLqD8T6zZyhf+AN bC7ci44UsiKGAplB3phAXrtSPUEqM86kbnHg3fSx37kWKUiYNOnx4AC2VXvEiKsOBlpyt3dw WQbOtOYM+vkfbBwDtoGOOPYAKxc4LOIt9r+J8aD+gTooi9Eo5tvphATf9WkCpl9+aaGbSixB tUpvQMRnSMqTqq4Z7DeiG6VMRQIjsXDSLJEUqcfhnLFo0Ko/RiaHd5xyAQ4DhQ9QpkyQjjNf /3f/dYG7JAtoD30txaQ5V8uHrz210/77DRRX+HJjEj6xCxWUGvQgvEZf5XXyxeePvqZ+zQyT DX61bYw6w6bOwU0EVXBlxgEQAMy7YVnCCLN4oAOBVLZ5nUbVPvpUhsdA94/0/P+uqCIh28Cz ar56OCX0X19N/nAWecxL4H32zFbIRyDB2V/MEh4p9Qvyu/j4i1r3Ex5GhOT2hnit43Ng46z5 29Es4TijrHJP4/l/rB2VOqMKBS7Cq8zk1cWqaI9XZ59imxDNjtLLPPM+zQ1yE3OAMb475QwN UgWxTMw8rkA7CEaqeIn4sqpTSD5C7kT1Bh26+rbgJDZ77D6Uv1LaCZZOaW52okW3bFbdozV8 yM2u+xz2Qs8bHz67p+s+BlygryiOyYytpkiK6Iy4N7FTolyj5EIwCuqzfk0SaRHeOKX2ZRjC qatkgoD/t13PNT38V9tw3qZVOJDS0W6WM8VSg+F+bkM9LgJ8CmKV+Hj0k3pfGfYPOZJ/v18i +SmZmL/Uw2RghnwDWGAsPCKu4uZR777iw7n9Io6Vfxndw2dcS0e9klvFYoaGS6H2F13Asygr WBzFNGFQscN4mUW+ZYBzpTOcHkdT7w8WS55BmXYLna+dYer9/HaAuUrONjujukN4SPS1fMJ2 /CS/idAUKyyVVX5vozoNK2JVC1h1zUAVsdnmhEzNPsvBoqcVNfyqBFROEVLIPwq+lQMGNVjH ekLTKRWf59MEhUC2ztjSKkGmwdg73d6xSXMuq45EgIJV2wPvOgWQonoHH/kxABEBAAHCwWUE GAECAA8FAlVwZcYCGwwFCRLMAwAACgkQGZU1PhKYC34w5A//YViBtZyDV5O+SJT9FFO3lb9x Zdxf0trA3ooCt7gdBkdnBM6T5EmjgVZ3KYYyFfwXZVkteuCCycMF/zVw5eE9FL1+zz9gg663 nY9q2F77TZTKXVWOLlOV2bY+xaK94U4ytogOGhh9b4UnQ/Ct3+6aviCF78Go608BXbmF/GVT 7uhddemk7ItxM1gE5Hscx3saxGKlayaOsdPKeGTVJCDEtHDuOc7/+jGh5Zxpk/Hpi+DUt1ot 8e6hPYLIQa4uVx4f1xxxV858PQ7QysSLr9pTV7FAQ18JclCaMc7JWIa3homZQL/MNKOfST0S 2e+msuRwQo7AnnfFKBUtb02KwpA4GhWryhkjUh/kbVc1wmGxaU3DgXYQ5GV5+Zf4kk/wqr/7 KG0dkTz6NLCVLyDlmAzuFhf66DJ3zzz4yIo3pbDYi3HB/BwJXVSKB3Ko0oUo+6/qMrOIS02L s++QE/z7K12CCcs7WwOjfCYHK7VtE0Sr/PfybBdTbuDncOuAyAIeIKxdI2nmQHzl035hhvQX s4CSghsP319jAOQiIolCeSbTMD4QWMK8RL/Pe1FI1jC3Nw9s+jq8Dudtbcj2UwAP/STUEbJ9 5rznzuuhPjE0e++EU/RpWmcaIMK/z1zZDMN+ce2v1qzgV936ZhJ3iaVzyqbEE81gDxg3P+IM kiYh4ZtPB4Q= Message-ID: <248cd85b-f36e-58ea-873d-8d89846f1c93@freebsd.org> Date: Tue, 16 Oct 2018 23:31:44 -0400 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="NztwEwbgfJvkJ04HVBJP9GJl1lMRV0Htb" X-BeenThere: freebsd-hackers@freebsd.org X-Mailman-Version: 2.1.27 Precedence: list List-Id: Technical Discussions relating to FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 17 Oct 2018 03:31:56 -0000 This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --NztwEwbgfJvkJ04HVBJP9GJl1lMRV0Htb Content-Type: multipart/mixed; boundary="iVJ1ytEsUb9aACkVJa83rt1B8JQKRSmWE"; protected-headers="v1" From: Allan Jude To: freebsd-hackers@freebsd.org Message-ID: <248cd85b-f36e-58ea-873d-8d89846f1c93@freebsd.org> Subject: Re: High load and MySQL slow without apparent reason References: In-Reply-To: --iVJ1ytEsUb9aACkVJa83rt1B8JQKRSmWE Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: quoted-printable On 2018-10-16 22:25, Darek Margas wrote: > Hi Everyone, >=20 > I'm trying to refresh my old FreeBSD experience by moving MySQL platfor= m > from Linux onto FreebSD+ZFS. >=20 > Before I ask for your help I would like to give you some context. >=20 > The machine is Dell server 2x20 cores, Intel IXL NIC, 1TB of RAM and lo= ts > of SAS SSD drives. > The kernel is slightly modified by removing some unused stuff, replacin= g > ixl driver with latest from Intel website and enabling NUMA. > The whole thing runs number of MySQL daemons packed in jails (bridged > network) with settings optimized for ZFS ARC caching (O_DIRECT, small > buffers, etc). >=20 > This is 11.2-RELEASE. >=20 > When I tested it first time I found troubles with back pressure on ARC > whilst short in memory leading machine do death. I also found that > disabling ARC compression solved silent death but decided to make some > tunes to keep more memory free for sudden need. >=20 > Ran some tests, used it for replication salves, etc. >=20 > Here is the thing - how I crashed this machine without understanding wh= at > has happened. >=20 > First my tunes. I adjusted v_free_target and v_free_min aiming to 128G = and > 64G respectively. However, I overlooked fact that this is in pages not = in > 1k blocks. As result I set: >=20 > - 700G max ARC size > - 512G v_free_target > - 256G v_free_min You likely want to tune 'vfs.zfs.arc_free_target' to a value very close to v_free_target or atleast v_free_min to cause ZFS to give back memory at that level of memory shortage as well. >=20 > Obviously this is a nonsense, however, the machine worked calm until AR= C > got half of memory. Then shit happened. As I made machine with no swap = at > all I have got number of zombies and problems with reclaiming console (= say, > open VI which works, then exit and VI stays on console while became zom= bie). > That was "fixed" by disabling swapping via sysctl. I also noticed 25% o= f > CPU taken by "system" with nothing popping in top except pagedaemon and= zfs > (on arc_reclaim). >=20 > I have added 40G of swap, rebooted machine but kept wrong settings. >=20 > It was again calm until ARC got half of memory. This is when I found wh= at I > did and fixed v_free stuff to be >=20 > - 128G v_free_target > - 64G v_free_min >=20 > The machine started managing memory the right way, wiping inactive to > laundry and laundering only when needed. I still observed 25% of > unexplained load from "system" (floating 5-60%) but all seemed OK. >=20 > At this point I switched one replica to be master and put production > queries on it. >=20 > Summarizing the above - the machine had issues and has not been reboote= d > but seemed OK with memory management while having unexplained system lo= ad. >=20 > Once I switched my SQLs from Linux master to FreeBSD I noticed slow > performance. There is stored proc called every 15 minutes. On old machi= ne > and all others it takes around 30-40s to complete and previous master h= ad > spike in ROW executions to 650kps (one minute sample) while new one got= it > up to 350kps and run for nearly 3 minutes. >=20 > I started looking deeper and found: > - Made all MySQL settings the same (when possible as some follow platfo= rm) > with no improvement > - MySQL reload did not help > - Stopping all replicas running around on the same machine (5 of them) = to > release resources made it worse (over 5 minutes to complete call). Star= ting > replicas made it better again by one minute. >=20 > BTW - jail was limited to one NUMA zone and half cores. Not all replica= s > had the same NUMA and CPU group. >=20 > I copied ZFS content to test machine which is exactly the same and kick= ed > the same MySQL in same jail and with same settings. > - Test instance ran correctly within similar completion time to old Lin= ux > master > - ARC on test machine was loaded up to 700G so I thought it would be go= od > enough to compare but machine still had lots of memory >=20 > To make it closer I compiled "memory allocator" which simply allocates = and > fills memory until killed or system dies. >=20 > Run it on test machine first: > - No effect until v_mem_target passed > - Once passed pagedaemon kicked in, memory got wiped and shifted, swap = got > full (paging only anyway) > - Load around 20% appeared from system, similar to broken production ma= chine > - Got down to 50G passing v_free_min > - KIlled allocator > - After 1-2s freezing all got back to normal, load from system was gone= =2E > - Swap was in use for some time after but finally got clean (that was o= nly > 4G swap on test machine) > - After some time machine is still calm and MySQL fast >=20 > Repeated the same on production machine: > - All as above, except: > - after killing allocator machine got frozen for, say, 10-15s > - memory was released but load did not change - neither got much higher= > while allocating memory nor lower after. > - Machine remained slow >=20 > Finally I rebooted whole machine and now it is fast while building ARC.= I > believe it won't have the same issue soon as v_free stuff is set correc= tly, > however, I need to understand why this MySQL process suffered and wheth= er > it was possible to recover it without reboot. I can imagine it was > something running in a loop or contention on something otherwise unused= or > simply another clash in settings triggering something in unusual way bu= t > have no idea where to look to investigate it. Well, it's possible that > there is a bug too. >=20 > Before reboot I collected various vmstats, tops, ran ktrace on MySQL an= d > sysctl to dump settings. Not posting as don't know what would be useful= =2E >=20 > Could you please point me in right direction? >=20 > Cheers, > Darek > _______________________________________________ > freebsd-hackers@freebsd.org mailing list > https://lists.freebsd.org/mailman/listinfo/freebsd-hackers > To unsubscribe, send any mail to "freebsd-hackers-unsubscribe@freebsd.o= rg" >=20 --=20 Allan Jude --iVJ1ytEsUb9aACkVJa83rt1B8JQKRSmWE-- --NztwEwbgfJvkJ04HVBJP9GJl1lMRV0Htb Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.22 (MingW32) iQIcBAEBAgAGBQJbxq0kAAoJEBmVNT4SmAt+PHUQALxpVZdNoGRBNv0nDMw86Fzh yG/he3JC8eEqlIi+t34sPtTkwINc6F9QgRCSkWAe1DyCLDkVgZ8AHZgSeuiNFQVW tPP4UL5h33fjYO+BMEZy6hdVkHQZivZ9YjyhDuo/s9NjKTekpjk2V8ngOe2W6KD5 vt7GgN04jp43lCtQ4RR3toCjZzkMOZHgaMJZ34n9AOlb1YflJrAYbJpGt4eVnTPN 0DD+hq9RXkAqzPxBfQsCZLB2vFezAgFrv2GZ0AP0otKkZgpe9ahHPzk5899AvRm9 lBMzlW5Qh0I1cs+yfb3Uhb1VefQIuIuAPjSJQjengOdSdcEZWZQCU37IMGSrurm7 22HS3f65OGrId/dE9si4+nX6Vg/ZcSxNnsxt8bYS52Yq6q01HKWZFXp1728vCNvc hJ+7QN5AnCBPjFpUMHTRmzXLXulRdM3tIsRkFNn3n1FvCnk+SqnoQ8rOs3lpb2yp Xs/4z5cMahEggqIu6eukJMqo/cxxOHtIQ/0FL6EXndu6OrWJllDOZtnXtYRIEH5x 0M21Mi44h7WvLnyl/SDEYhzTxvPe+/DwrKTKfF4kWlf8wPJcgGP5uUCD+lcQI0ZM +NsZIDlsqqHuukHQ4Kho+kjZzo8neMZiCEBZRqWX3R0iM/00M2/uB8qNZtEFRMB6 25VJqU04/4DRiPgwPuCg =PYHj -----END PGP SIGNATURE----- --NztwEwbgfJvkJ04HVBJP9GJl1lMRV0Htb--