Query to fetch performance related details

MySQL & MariaDB
1

Hi Team,

Please help me to fetch below details from MySQL perspective (probably from information_schema, performance_schema or sys databases). This functionality is available in SQL.

Please note that MySQL instances are running in Linux platform.

  1. CPU utilization %
  2. CPU utilization against each query
  3. Total memory of Server and allocated memory for MySQL
  4. Total CPU Core of Server
  5. Disk utilization, at least for data directory partition

Required the customized query for fetching these details directly from MySQL, not recommended to explore PMM for this.

Thanks,
Sujith VG.

2
  1. Not available in MySQL using SQL (metric does not exist in mysql)
  2. Not available in MySQL using SQL (metric does not exist in mysql)
  3. Not available in MySQL using SQL (memory info is found in /proc and not accessible by MySQL)
  4. Not available in MySQL using SQL (this information is found in /proc/cpuinfo which is not accessible by MySQL)
  5. Overall dataset size can be calculated as follows:
SELECT CONCAT(ROUND(SUM(data_length) / (1024*1024*1024),2),'G') Data_Size,
    CONCAT(ROUND(SUM(index_length)/ (1024*1024*1024),2),'G') Index_Size,
    CONCAT(ROUND((sum(data_length)+sum(index_length))/(1024*1024*1024), 2),'G') Total_Size
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'sys')
  GROUP BY NULL

but that does not equal amount of space taken on disk. You need to add data_free to the calculation because files on disk can contain empty, free space.

3
  1. You can calculate potential max memory used by MySQL using this query MySQL Memory Usage · GitHub But this is not the total amount allocated to MySQL as MySQL can alloc/free memory as it needs based on queries.
4

If you’re using Percona MySQL, check out the ProcFS plugin which will allow you to query the /proc filesystem.