Hi ,
I have a problem with my MySQL. I’ve about 10 MySQL processes which totally consumed 360% of my CPU usage. WOW!! so it causes overloading in my OS. I want to find the detail information of each MySQL process so that I could find which process consumed my system.
how can I do that?
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
@javadroid
There’s a few tools you can use to gauge performance, or check resource usage. If you’re using the defaults for MySQL though, chances are, that’s one issue that’s leading to the spikes. The defaults for MySQL aren’t meant for production use and aren’t really optimized for more than light traffic.
htop
htop
will provide a more detailed look with better sorting than standardtop
. You can hitF6
to sort by CPU, MEM, etc. You can also use F6 to expand the process tree and view child processes.mytop
mytop
will give you information about threads, queries, key performance, etc in real time.mysqltuner
https://github.com/major/MySQLTuner-perl
MySQL Tuner isn’t a monitoring agent, so to speak, as much as it is a tool to gauge performance and help optimize your configuration. It’ll make suggestions on how you can improve performance, as well as detail how your current configuration is working for you. Its very detailed.
The one thing to note about MySQL Tuner is that MySQL needs to be running for at least 24 hours before each run so that it can gather proper stats. So if you run MySQL Tuner, and then make a few changes, unless those changes cause larger issues than you’re already having, you need to give it a bit of time before running it again.
MySQL CLI
You can also just run this command from the CLI, though
mytop
would be a better option since it’s continuous and live, whereas this is a one-off command.@javadroid
There’s a few tools you can use to gauge performance, or check resource usage. If you’re using the defaults for MySQL though, chances are, that’s one issue that’s leading to the spikes. The defaults for MySQL aren’t meant for production use and aren’t really optimized for more than light traffic.
htop
htop
will provide a more detailed look with better sorting than standardtop
. You can hitF6
to sort by CPU, MEM, etc. You can also use F6 to expand the process tree and view child processes.mytop
mytop
will give you information about threads, queries, key performance, etc in real time.mysqltuner
https://github.com/major/MySQLTuner-perl
MySQL Tuner isn’t a monitoring agent, so to speak, as much as it is a tool to gauge performance and help optimize your configuration. It’ll make suggestions on how you can improve performance, as well as detail how your current configuration is working for you. Its very detailed.
The one thing to note about MySQL Tuner is that MySQL needs to be running for at least 24 hours before each run so that it can gather proper stats. So if you run MySQL Tuner, and then make a few changes, unless those changes cause larger issues than you’re already having, you need to give it a bit of time before running it again.
MySQL CLI
You can also just run this command from the CLI, though
mytop
would be a better option since it’s continuous and live, whereas this is a one-off command.@javadroid
You can use this command
top -p <pid>
to get details about a single process.Use
pgrep mysqld
to find the process IDs of your mysql server then use the first command to find details about each process.Hope this helps.