How to find, in Mysql, using performance schema, which queries are causing how much cpu% increase?

Question

I have been trying to get more information, on how to attribute how much cpu% any running query takes on our mysql, with no avail. I have enabled

1. General Log
2. Slow log
3. Performance schema

but the data I get from these data sources is giving me queries that I can optimize, which I do but no solid way to attribute how much cpu% any query takes up. Any pointers in right direction would be really helpful.

Example data

General Log

*************************** 1. row ***************************

  event_time: 2018-03-17 13:17:52
   user_host: YYYYY[YYYYY] @  [XXX.XX.XX.XX]
   thread_id: 16850427
   server_id: 122252267
command_type: Query
    argument: UPDATE inventory_details AS did1 INNER JOIN ( SELECT MAX(id) max_id FROM daily_inventory_details WHERE shop_id = '1160' AND inventory_item_id = 3461 GROUP BY shop_id, inventory_item_id) did2 ON did1.id = did2.max_id SET ideal = ideal + 0.071, updated_at = NOW()

Slow Log

*************************** 21. row ***************************

2018-03-17 21:03:39
00:00:01
SELECT did1.id, smii.price, did1.count_unit, did1.count_order, did1.portion_unit, did1.portion_count, did1.beginning, did1.ist, did1.deliveries_local, did1.deliveries_own, did1.ending, did1.ideal, did1.waste FROM inventory_details did1 INNER JOIN( SELECT MAX(id) max_id FROM inventory_details WHERE shop_id = '1199' GROUP BY shop_id, inventory_item_id) did2 ...

Performance Schema

*************************** 1. row ***************************

    SCHEMA_NAME: faasos_platform
                     DIGEST: ae1de22d8ed625ed3e9547cbaa89c5d3
                DIGEST_TEXT: SET `time_zone` = ? 
                 COUNT_STAR: 3885851
             SUM_TIMER_WAIT: 161144275036000
             MIN_TIMER_WAIT: 14549000
             AVG_TIMER_WAIT: 41469000
             MAX_TIMER_WAIT: 19984412000
              SUM_LOCK_TIME: 9833622000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2017-10-25 02:11:32
                  LAST_SEEN: 2018-03-17 22:04:36

Extra note

One of the usage of being able to attribute increase of decrease of CPU% to queries would to RCA the increase in this cpu% at 2:15am.

Answer 1

Here are a couple things which should help you:

1) Query Profiling

Digital Ocean has a great article on creating logs for query profiles. This combines the logs you've outlined above with additional information about query times as you've requested. https://www.digitalocean.com/community/tutorials/how-to-use-mysql-query-profiling

It won't give you explicit cpu usage, but the query time will help give you an idea of what is taking a long time to run. Don't fool yourself, however, in that just looking at the query time itself is not the only important factor of a query's performance. Just because a query takes a long time doesn't necessarily mean it's not doing what it's supposed to. That's why you should also want to examine the structure of the query itself.

2) Using EXPLAIN

Using explain will show you the underlying relations used by mysql to generate the output of your queries. For example, you almost never want to do full table scans when relating tables together and should be using indicies instead (hopefully it's obvious why).

https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

There is also a workbench tool for using explain here:

https://dev.mysql.com/doc/workbench/en/wb-tutorial-visual-explain-dbt3.html

3) Application Issues

Finally, don't forget to look into your application in addition to the queries themselves. There are situations where you might actually be running queries unnecessarily. I remember a situation a while back where a query was being run thousands and thousands of times unnecessarily just because of the code structure. Capturing the number of times a query is run would require you to parse your logs and count for yourself how many times and how often the queries are being run - so there is no explicit tool available for this.

Additional Tools

If you want real time info there are also tools such as MyTop which is pretty handy.

This post covers a lot of info about it:

https://blog.serverdensity.com/how-to-monitor-mysql/