Re: MySql Swapping issues
Hi Machiel, On 8/09/2016 05:24, Machiel Richards wrote: ... Biggest issue at the moment is mysql taking all physical memory until nothing left, the starts swapping as well until that is completely used up. Since you are using MySQL 5.7, you can consider enabling memory instrumentation in the Performance Schema: UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%'; That may help to locate where the memory is used. To monitor the memory usage use either the sys.memory_% views (https://dev.mysql.com/doc/refman/5.7/en/sys-schema-views.html) or the Performance Schema summary tables (https://dev.mysql.com/doc/refman/5.7/en/memory-summary-tables.html). Note that there is a little extra overhead instrumenting the memory. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySql Swapping issues
- Original Message - > From: "Machiel Richards" <machiel.richa...@gmail.com> > Subject: Re: MySql Swapping issues > > When I monitor the MySQL processlists, there is constantly about 30-35 > processes > and half of them never change simply showing as : > > Connect | 124678 | Waiting for an event from Coordinator 15-ish? That feels like a lot, but then I'm not particularly familiar with parallel replication. Might be normal for your configuration. > The other half of the processes are queries and inserts and I can see those > being processed and going off the list fairly regularly. So those disconnect normally, which /should/ clean up any session memory. I'm pretty much out of obvious things to point at. Random toughts: - typo in some dynamically-assigned buffer sizing (not a lot of those, though) - I have in the past seen memory use increase at times when the logs couldn't get flushed to disk; but I assume something like that would become apparent at restart time anyway - A memory leak of that size seems improbable, but it's always worth checking the bug reports for your server version or test other versions Maybe someone else has some more ideas, or has seen similar behaviour? -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySql Swapping issues
In terms of the amount of processes, that is the max amount ever seen. The servers are monitored 24/7 so it is constantly under hawk eye from both staff and monitoring systems. The other memory values I was referring to are things like the open-table cache, and other buffers as you assumed yes. They use innodb engine for their databases. In terms of setting up the cronjobs using ps, this has been done using several methods and it always come down to only one process using all memory and that is mysql server. Even cpu usage is fairly high sometimes, however this was found to be when they run large updates or reports. Biggest issue at the moment is mysql taking all physical memory until nothing left, the starts swapping as well until that is completely used up. On 07 Sep 2016 15:47, "Johan De Meersman" <vegiv...@tuxera.be> wrote: - Original Message - > From: "Machiel Richards" <machiel.richa...@gmail.com> > Subject: MySql Swapping issues > >I had a look at the settings and the innodb buffer pool on one server > is set to about 70% of the physical memory and the others to about 30% of > physical memory. Not unreasonable, especially given the memory sizes you give. >All other buffer and memory relevant parameters are set to fairly low > values as they were recently decreased with no difference in the symptoms. Good, although 'fairly low' is vague :-) For most purposes, there's no need to tune the specialised buffers at all. I assume you mean myisam key cache and the like. > In terms of server and queries, The smallest server have 64Gb of > physical memory and the biggest server have 128Gb of physical memory and > the biggest server database size is about 600Gb odd. That's a large difference,but not necessarily a problem, as long as your active dataset fits in the bufferpool. > I had a look at the processes running and there are at best 38 > processes running including the replication processes. Is that what you see whenever you look, or is it from a trending tool like Munin or Cacti? The former can be very deceiving, especially with connect-select-quit applications like PHP sites. I strongly recommend setting up proper trending if you don't have it, so you can see what's going on when you're not looking, too - and compare to past activity. Personally I use Munin; the standard plugins in there are a good base, but there's a very good one at https://github.com/kjellm/munin-mysql. I have my own fork of that, too, which contains a number of extra graphs that depend on considerable modifications of the main module. Cacti is pretty much just as good (and iirc the kjellm plugin is actually based on a cacti plugin); I just prefer the way munin is managed. >I do not see any other hardware related issues and swappiness settings > have been configured to 1. For DB-only servers (and really, production servers in general) I generally opt to not have any swap at all. Once you start using it, it's a slow death struggle anyway; better off to just have it die immediately and fix the configuration. >Any ideas , links, advice, etc... will be appreciated. Memory creep is often hard to diagnose; set up a simple cronjob that runs PS; sorts by memory use and outputs the top lines to a log every half hour or so. You can then do some sed/awk/gnuplot magic on that to see what process keeps growing. If it turns out that it actually *is* the mysql server, that may be a memory leak, but just as probably it could be a maintenance schedule somewhere that suddenly bursts a couple of dozen connections, exhausting server memory. Pretty hard to tell you more without telemetry :-) -- Unhappiness is discouraged and will be corrected with kitten pictures.
Re: MySql Swapping issues
- Original Message - > From: "Machiel Richards" <machiel.richa...@gmail.com> > Subject: MySql Swapping issues > >I had a look at the settings and the innodb buffer pool on one server > is set to about 70% of the physical memory and the others to about 30% of > physical memory. Not unreasonable, especially given the memory sizes you give. >All other buffer and memory relevant parameters are set to fairly low > values as they were recently decreased with no difference in the symptoms. Good, although 'fairly low' is vague :-) For most purposes, there's no need to tune the specialised buffers at all. I assume you mean myisam key cache and the like. > In terms of server and queries, The smallest server have 64Gb of > physical memory and the biggest server have 128Gb of physical memory and > the biggest server database size is about 600Gb odd. That's a large difference,but not necessarily a problem, as long as your active dataset fits in the bufferpool. > I had a look at the processes running and there are at best 38 > processes running including the replication processes. Is that what you see whenever you look, or is it from a trending tool like Munin or Cacti? The former can be very deceiving, especially with connect-select-quit applications like PHP sites. I strongly recommend setting up proper trending if you don't have it, so you can see what's going on when you're not looking, too - and compare to past activity. Personally I use Munin; the standard plugins in there are a good base, but there's a very good one at https://github.com/kjellm/munin-mysql. I have my own fork of that, too, which contains a number of extra graphs that depend on considerable modifications of the main module. Cacti is pretty much just as good (and iirc the kjellm plugin is actually based on a cacti plugin); I just prefer the way munin is managed. >I do not see any other hardware related issues and swappiness settings > have been configured to 1. For DB-only servers (and really, production servers in general) I generally opt to not have any swap at all. Once you start using it, it's a slow death struggle anyway; better off to just have it die immediately and fix the configuration. >Any ideas , links, advice, etc... will be appreciated. Memory creep is often hard to diagnose; set up a simple cronjob that runs PS; sorts by memory use and outputs the top lines to a log every half hour or so. You can then do some sed/awk/gnuplot magic on that to see what process keeps growing. If it turns out that it actually *is* the mysql server, that may be a memory leak, but just as probably it could be a maintenance schedule somewhere that suddenly bursts a couple of dozen connections, exhausting server memory. Pretty hard to tell you more without telemetry :-) -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySql Swapping issues
Hi Guys I am hoping this mail find everyone to be good. I am looking for some more info or perhaps some advice on an issue that I am busy investigating. There are 4 databases that I am busy looking at that seems to be building up memory usage over the course of 3 to 4 days and once physical memory is used up it starts swapping to the point where even swap space is used up and the server dies. I had a look at the settings and the innodb buffer pool on one server is set to about 70% of the physical memory and the others to about 30% of physical memory. All other buffer and memory relevant parameters are set to fairly low values as they were recently decreased with no difference in the symptoms. In terms of server and queries, The smallest server have 64Gb of physical memory and the biggest server have 128Gb of physical memory and the biggest server database size is about 600Gb odd. I had a look at the processes running and there are at best 38 processes running including the replication processes. I do not see any other hardware related issues and swappiness settings have been configured to 1. I have worked on so many larger and busier databases with less memory without experiencing this issue and thus far I have not been able to find the root cause for this issue. The current version is 5.7.13. Any ideas , links, advice, etc... will be appreciated. Regards -- I am a bomb technician...If you see me running, TRY TO KEEP UP!
