Re: Memory usage increasing constantly
Hello Rick, thank you for your suggestions. Unfortunately the behavior of my system is still very similar.
I have added the indexes as you suggested in order to reduce the full table scans and the slow queries. I still have some but, I expect them to be slow (and thay are intentionally run on background by my app).
About Aborted_clients, this was not an issue since I have restarted the app few times and this leads to abort the clients. This was not done by the app itself, which terminates correctly sessions.
Apart from that, I have noticed that out of 320 query per second, the real queries are around 80. This is because many of the queries carry with them
"SET TRANSACTION ISOLATION LEVEL XXXXX;" and "SET AUTOCOMMIT XXX". So, basically I think we are counting them in the 320qps.
We could optimize the application to avoid calling this instructions when not necessary, but I think they are irrelevant for this issue, and besides, some of them are done by DBCP (so they are out of our control).
That said, I was reading again your note: "Let's look at a few of the worst queries in the slowlog; with 320qps and such a sign Select_scan value, you may be generating a lot of tmp tables, thereby blowing out RAM.".
I can work in that direction, but, as this is a time consuming work first I want to make sure I'm following the right track.
How can we make sure that the system is generating a lot of tmp tables?
Is the memory allocated for tmp tables released after they get used?