mysql is getting killed reddit / freebsd ---------------------------------------------------------------------- Aeze2eith · 2020-09-26 17:24 Hi, I have a 12.1-RELEASE-p10 VM with 8GB RAM + 2 GB swap running mail services for 6-7 private/family domains. Issue #1: the free memory is always about 1.5GB but there is always ~30-50 MB swap in use. Issue #2: I wouldn't worry much about this swap usage (although I don't really understand why it's being used) but sometimes mysql gets killed: ``` kernel: pid 77167 (mysqld), jid 0, uid 88, was killed: out of swap space ``` The frequency is random (from 2 days to 1 week) and the time when it happens is also random. Uptime also doesn't matter. I collect basic metrics (load, memory/swap usage, etc.) in every minute and just before this event there is only this micro amount of swap is being used and free memory is always between 1.2-1.5GB. Where should I start to investigate? Thanks in advance. ---------------------------------------------------------------------- antiduh · 2020-09-26 17:43 A little bit of swap usage isn't necessarily bad or wrong. Idle ram is wasted ram because ram can always be used to cache disk data. So if the OS notices that a chunk of allocated ram hasn't been used in a long time, it makes sense to move it to swap and free that ram for cache. https://www.freebsd.org/doc/en_US.ISO8859- 1/books/faq/misc.html#idp50046200 Can you give us the settings for your jail and for mysql? And can you dump the vm.* sysctl's? Something important to understand is that freebsd will try to reserve swap space for anonymous memory even if it doesn't actually write to swap. If you try to allocate memory and it needs to be committed memory, but there's no uncommitted space in swap, then the allocation may fail with the exact error you received. The behavior is controlled by the various vm.* sysctl's, with vm.overcommit being the most important among them. https://wiki.freebsd.org/SystemTuning > The vm.overcommit sysctl defines the overcommit behaviour of the vm subsystem. The virtual memory system always does accounting of the swap space reservation, both total for system and per-user. Corresponding values are available through sysctl vm.swap_total, that gives the total bytes available for swapping, and vm.swap_reserved, that gives number of bytes that may be needed to back all currently allocated anonymous memory. Setting bit 0 of the vm.overcommit sysctl causes the virtual memory system to return failure to the process when allocation of memory causes vm.swap_reserved to exceed vm.swap_total. Bit 1 of the sysctl enforces RLIMIT_SWAP limit (see getrlimit(2)). Root is exempt from this limit. Bit 2 allows to count most of the physical memory as allocatable, except wired and free reserved pages (accounted by vm.stats.vm.v_free_target and vm.stats.vm.v_wire_count sysctls, respectively). So even if you have free swap space, your problem may go away hf you allocate more swap. Freebsd recommends you match your ram with swap. ---------------------------------------------------------------------- daemonpenguin · 2020-09-26 20:54 Issue #1 isn't a problem. That is normal and expected behaviour. Memory that is almost never used is often swapped out even if you don't need to, just so it doesn't need to be swapped out later. Issue #2: Is the MySQL process running in a jail? It sounds to me like one of two things is happening. Either the process is in a jail and cannot "see" swap space, causing it to always be out of swap. Or some big query (or queries) is being run occasionally that uses an insane amount of RAM. I used to have this happen when a website once that would have memory spikes if it suddenly got hammered by spam bots. Other thoughts: Do you need MySQL for the mail service? Is it web- facing? I don't thin any of my mail servers use MySQL and the servers that do have MySQL behind the firewall to avoid people hammering it with traffic. ---------------------------------------------------------------------- Aeze2eith · 2020-09-26 21:42 Hi, Thanks for your reply. Here is the sysctl export: https://pastebin.com/LL8YyJzi mysql is not running in jail. Swap size: do you suggest to create a swap file matching the size of the RAM as it will probably solve the issue? This means that something happens that eats up all the free RAM _and_ all the swap suddenly within 1 minute. ---------------------------------------------------------------------- Aeze2eith · 2020-09-26 21:51 Hi, Thanks for the reply. mysql is not running in jail and is only available on localhost and a socket file so there is no hammering from the net. mysql is needed because of the easy management of virtual domains and mailboxes by the respective owners of the domains via a web ui. Also there is a nextcloud instance running but I have checked all the logs and nothing happens at the very same time when mysql is killed. The websites are static with no mysql backend so I'd exclude these from the investigation. That particular kill log implies for me that something suddenly ate up all the free ram and all the free swap so mysql had to be killed. Can we assume that mysql does something and that's why it's killed or mysql is just a victim? There are many other processes (clamav, rspamd, postfix, dovecot, apache, etc.) which were never killed. Probably collecting metrics in every minute is not enough, I should collect it in every 10-15th second until the next kill. The thing is that this server is a replacement of a Ubuntu server with the very same parameters which has run for years with no such problems. ---------------------------------------------------------------------- mzs47 · 2020-09-26 22:27 Idk whether this helps, but some years ago(around 2016), I had similar issue with MySQL, it was using some feature that was causing the memory usage to exceed and getting killed, something was enabled by default in version 5.5 and later on FreeBSD 10.x onward. I don't recall what it was, but disabling it stopped this for me. ---------------------------------------------------------------------- Fortescue · 2020-09-27 01:41 What's in your my.cnf file? Also, what version of MySQL? If you're gathering some metrics, you may want to include something to keep an eye on the amount of RAM that your mysqld process is consuming. Perhaps it has a memory leak, or simply needs to be tuned to not consume so much ram (see my.cnf). ---------------------------------------------------------------------- istarian · 2020-09-27 03:02 I don't quite follow them, but it does sound like they're saying something causes the OS to go move the contents of a chunk of memory to swap, but all the swap space is tied up somehow... If space isn't an issue you could double the swap space and see it makes any difference. I.e. whether your problem still occurs and whether the frequency changes. ---------------------------------------------------------------------- daemonpenguin · 2020-09-27 15:02 As to whether MySQL is doing something that gets it killed or just a victim... It could be either. My past experience has typically been that MySQL is doing something (like running a big query) which suddenly consumes too much RAM and gets it killed. But it could be other processes using memory/swap and MySQL just happens to be coming along and is the next process to try to access memory/swap and dies. My gut reaction though is MySQL is probably the problem. If your system was regularly running low on RAM/swap, you'd expect services to run low on memory and/or die randomly. (One day it would be MySQL, the next day it would be Postfix, the next day it would be Apache...) Since it always seems to be MySQL that is terminating, I suspect MySQL is the problem. Maybe try upgrading to a newer version of MySQL? Or monitoring its memory usage every 10 seconds? Or move MySQL to another server and see what happens when your other services connect to MySQL over the network rather than locally? ---------------------------------------------------------------------- vivekkhera · 2020-09-27 16:53 The only reason to match swap to RAM is for kernel dumps on crash. I haven’t done that in forever since I have no use for the crash dumps. Besides that they never happen unless I have hardware faults that are obvious from other symptoms. Also making 256GB of swap on a large DB server is just a waste of space. ---------------------------------------------------------------------- antiduh · 2020-09-27 17:37 > This means that something happens that eats up all the free RAM and all the swap suddenly within 1 minute. There's a misunderstanding here. FreeBSD has a class of memory called [anonymous memory][1]. Memory returned by `malloc` to applications is part of anonymous memory. All anonymous memory counts against swap *commitment* or *reservation*. You can look up how much commitment there is against swap using `vm.swap_reserved`. This commitment has nothing do with how many pages of memory are currently written to swap. It has to do with having enough swap for all extant allocations. However, the `vm.overcommit` bitmask controls this behavior. If you set the first bit (bit 0) in `vm.overcommit`, then the OS will return failure when `vm.swap_reserved` (amount of swap commitment/reservation) would be greater than `vm.swap_total` (amount of available swap space). By default, `vm.overcommit` is set to zero, indicating that it allows more anonymous memory to be allocated than there is swap. Here's your values: vm.swap_reserved: 12152737792 vm.swap_total: 2147483648 vm.overcommit: 0 Which all say: * "You have about 2 GiB of swap space." * "You have about 12 GiB of swap commitment." * "Swap commitment is greater than available swap, so you are in the overcommitted state." * "The overcommitted state is allowed because bit 0 in `vm.overcommit` is set to 0." ... So all of that to say: now that I have the values, I don't think the size of your swap is the problem. That said, if you wouldn't mind I would try increasing the size of your swap space just as a sanity check to see if it makes the problem any better. Now that we've ruled out that hypothesis, we'll have to think of another one. [1]: https://wiki.freebsd.org/Memory ---------------------------------------------------------------------- antiduh · 2020-09-27 17:39 See my other reply. Commitment is not the same as actual usage. ---------------------------------------------------------------------- istarian · 2020-09-27 17:43 Okay. All I'm say is that failure, maybe repeated, of a request to commit/allocate/etc resources could cause crashing. ---------------------------------------------------------------------- antiduh · 2020-09-27 17:46 Could you also paste your mysql config? ---------------------------------------------------------------------- antiduh · 2020-09-27 17:48 Could you tell us how swap is provided? Is swap backed by a file inside the vm? Is swap backed by a disk partition in the VM? How is disk provided to the VM? Is the VM ever starved of disk IO? ---------------------------------------------------------------------- antiduh · 2020-09-27 17:53 Could you post all syslogs that occur around when mysql is killed? ---------------------------------------------------------------------- antiduh · 2020-09-27 17:56 Could you post the output of `vmstat -z`? ---------------------------------------------------------------------- antiduh · 2020-09-27 18:15 Yep, I agree with you. ---------------------------------------------------------------------- Aeze2eith · 2020-09-28 09:29 Hi All, First thanks for all the replies and your time. Sorry for the delayed answer but I had to work and didn't have time (and it seems this week will be the same). Last evening 4 processes were killed at the same time: ``` Sep 27 18:04:44 myserver kernel: pid 76680 (rspamd-2.5), jid 0, uid 236, was killed: out of swap space Sep 27 18:04:46 myserver kernel: pid 28831 (mysqld), jid 0, uid 88, was killed: out of swap space Sep 27 18:04:48 myserver kernel: pid 76900 (rspamd-2.5), jid 0, uid 236, was killed: out of swap space Sep 27 18:04:49 myserver kernel: pid 78432 (clamd), jid 0, uid 106, was killed: out of swap space ``` Nothing has happened before, there were some imap connections. This was the status before the kill: ``` memory: 2282M free swap: 36M in use 6:04PM up 4 days, 8:12, 0 users, load averages: 0.97, 0.82, 0.75 ``` This is the actual my.cnf: https://pastebin.com/27CZXpDJ I have run ``MySQLTuner`` and it gave some hints but this is the most interesting: ``` [--] Physical Memory : 7.4G [--] Max MySQL memory : 6.0G ``` I have started some tuning and I'll see what can I achieve. But apart from this I still don't get why the processes were killed. ---------------------------------------------------------------------- TorMazila · 2020-09-29 18:18 I'd: 1. tune down mysql memory usage 2. limit number of simultaneous smtpd or their equivalents 3. check out&limit antivirus memory usage (limit number of sessions for checking content) 4. limit ZFS ARC memory usage 5. consider going swapless as "if anything goes wrong" and you start hitting a swap for real - the system will become useless anyway, especially if the swap is on HDD (compare several MB/s of HDD to several GB/s of RAM) 6. patch a kernel to reboot (shutdown\_nice) if it hits "out of swap space" as you never know what is going to be killed and how useless your system will become. 8GB RAM is more than enough to run way more than "several family domains". ---------------------------------------------------------------------- TorMazila · 2020-09-30 10:02 As I suspected - you have "full set" of anti-everything services. Limit their checking to 1-2 sessions at once (you don't have 10-core CPU anyway). Limit apache instances to something reasonable (10-20), use nginx as a frontend and limit connections to the backend. Mysql gets killed because it's the largest memory consumer and killing it "solves" all the issues at once. Not that funny as killing md0 device, though. OOM killer can render system semi-useless, e.g. if it kills sshd ;). ---------------------------------------------------------------------- TorMazila · 2020-09-30 10:31 I don't think you have even 100MB of databases or do weird select- join-mubo-jumbo queries, so you definitely should tune down mysql to use tens-hundreds MB. AFAIR there's an example my-small.cnf with minimized ram usage - a good place to start. open\_files\_limit = 32768 table\_open\_cache = 16384 divide those by 8-16 or even 32 ​ innodb\_buffer\_pool\_size = 1G how does this compare to innodb\_data\_file\_path = ibdata1:128M:autoextend (I see there's autoextend but what is the real size?) key\_buffer\_size can be tuned down as well. The buffers should be comparable to your real data sizes and not absurdly high numbers. Surely, you should have some "data volume growth estimation". ​ And you must limit how many clamd threads (you don't use clamav to check the mail but use clamd, right?) can check mail and such.