CPU and Memory Consumption!!

systemali · December 30, 2020, 4:21pm

Hello everyone,

Although i have a fine running Percona mysql server ver.5.5, but almost daily when the concurrent connections exceed 100 or even closer to that figure, My CPU starts to tremble

I am not sure what settings in the configuration do i need to tweak so that it can sustain these many or more users with ease.

My Server configuration is as follows :-

Also the memory is almost consumed by the server in a couple of days after a weekly server restart. There is hardly any thing left.

But, my main concern is the CPU, If anybody could suggest something ?

Thank you

scott.nemes · December 30, 2020, 4:30pm

As we discussed in chat, the big thing you need to do is try and capture what is going on during the spike with the “show processlist” and “show engine innodb status \G” commands. Chances are there is a query or queries that are locking up some key tables and causing other threads to wait which will cause churn on your system.

You said your CPU was around 25-35%, which is not too bad actually. And we verified that your system was not using any swap memory, which is also good. Some additional metrics that will be nice to gather during the spike for reference are the system load and the IOWAIT%. You can get those through the below commands:

“uptime” (for system load)
“sar 5 5” (for IOWAIT%)

Let me know once you are able to capture the data and we can look into it some more.

systemali · December 30, 2020, 4:31pm

Thank you Scott as always for such a wonderful support.

I’ll get back to you with the “system load” and the “IOWAIT%” outputs during work hours.

Take care !

systemali · December 30, 2020, 4:31pm

Hello Scott,

With regards to the primary details, Please find them mentioned below, But the other two commands
namely “show processlist” and “show engine innodb status \G” are scrolling into multiple screens
and i am not able to download them.

Can you also let me know how can i capture them in a file and download them on my PC to upload
it here on the board ?

[root@Newdbsrv ~]# free -g
total used free shared buffers cached
Mem: 47 36 11 0 0 0
-/+ buffers/cache: 35 11
Swap: 50 0 50

[root@Newdbsrv ~]# uptime
15:22:42 up 2 days, 2:52, 3 users, load average: 3.95, 3.76, 3.37

[root@Newdbsrv ~]# sar 5 5

Linux 2.6.32-358.6.1.el6.x86_64 (Newdbsrv) 11/27/2013 x86_64 (24 CPU)

03:22:48 PM CPU %user %nice %system %iowait %steal %idle
03:22:53 PM all 8.17 0.00 1.20 0.09 0.00 90.54
03:22:58 PM all 13.44 0.00 0.61 0.01 0.00 85.95
03:23:03 PM all 10.70 0.00 0.60 0.06 0.00 88.64
03:23:08 PM all 18.11 0.00 0.60 0.02 0.00 81.27
03:23:13 PM all 19.32 0.00 0.94 0.05 0.00 79.69
Average: all 13.95 0.00 0.79 0.05 0.00 85.22

As always Appreciate all your assistance.

scott.nemes · December 30, 2020, 4:31pm

Yeah those stats look good actually, so the system is not doing a whole lot. So I would not be worried currently.

A consistent %IOWAIT of 3-5% is not too uncommon for an average busy system, which you are nowhere near. And depending on the work load, it could go much higher than that at times and not be too big of an issue as long as it’s not sustained.

You are only using around 25% of your CPU at most, so you have a lot of room there. Once you start getting closer to 70% CPU usage (30% idle) then you should start to pay a bit more attention to it, but chances are your I/O will become an issue first in most cases.

To capture the processlist and InnodDB data, you could run the commands from the command line and output it to a file:

mysql -e “show processlist;” > ~/processlist.log
mysql -e “show engine innodb status \G” > ~/innodb_status.log