Question :
Are there any variables and formula that can help me calculate CPU/Load value of MySQL ?
Answer :
You could make use of sysstat (also here) and/or nmon – great free tools! Among the sysstat tools are iostat and mpstat (check the link). There’s also top – you could look at that also! The pidstat (no separate wiki entry – see sysstat) might be right up your alley – I would urge you to explore the man pages!
What you want are baselines so that when things start to go wrong, you know:
-
what has been happening over the last minute, hour, day, week, month, year…?
-
what’s happening now – is your I/O swamped, or is it your CPU or your RAM? Also, the network should be under surveillance!
-
what’s happened when you changed parameter X or cancelled report Y
You should be saving the output. The example is for top, but you should be keeping all of your monitoring stats so that you can graph it and trace problems when they occur. Ideally, I would be putting the output into a database – MySQL in your case. For example, sar (System Activity Report) is the sysstat tool for commands:
used to report on various system loads, including CPU activity,
memory/paging, interrupts, device load, network and swap space
utilization
Even though sysstat isn’t part of the Linux kernel proper, I would consider it an “honourary kernel module”.
Normally, tool recommendations are off-topic here, but these tools have been around since forever and are unlikely to disappear anytime soon. If these don’t suit you, a quick internet search will reveal many more – you really are spoilt for choice when it comes to (impressive) F/LOSS monitoring tools!
From your MySQL root login when at the command prompt,
status;
will include the last 2 lines with many valuable pieces of information. Example follows –
Uptime: 10 days 8 hours 45 min 36 sec
Threads: 7 Questions: 61 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 12 Queries per second avg: 0.000