How to find out why MySQL is using CPU?

Question

MySQL

I have been struggling with the mysql setup for several days now. I can't get stable work. Server 8 cores and 32 GB of RAM.
At first, the database was just slow in processing requests. For several days I set the parameters on the recommendation of mysqltuner. Now, on any mysql settings, most of the time mysql loads the processor to the full. LA on average stays at the level of 17 15 14. At the same time, RAM is occupied by no more than half. Several simple sites on wordpress, but most templates have self-written sql queries. For some reason, Object cache and caching plugins do not work very well: either they don’t cache at all, or the cache lives very little with any settings. All InnoDB tables.
Common log errors:

• upstream timed out (110: Connection timed out) while reading response header from upstream
  
• recv() failed (104: Connection reset by peer) while reading response header from upstream
  
• Lock wait timeout exceeded - set lock_wait_timeout = 20
  
• Waiting for table metadata lock - set autocommit = 0 in my.cnf
  

Timeouts in nginx, php-fpm, mysql are sky high.
There were more mistakes, but I didn't write them down.
There are a lot of queries without indexes in slow log, but if you execute them in the console, then most of them are completed in less than 0.01 seconds.
Now in the show processlist there are many requests with the status "Copying to tmp table". I will still raise the parameters of the cache and buffers.
UPD : At the moment, I transferred the entire database to the RAM, because. its volume allows It works noticeably faster, but still there are several hanging requests in the "Copying to tmp table" status, although I raised the parameters like tmp_table_size, max_heap_table_size and some other sort and join buffers by 2 times.
UPD:did not help. The load has risen, but less than it was. LA jumps from 3 to 14 on the first value. 504 Gateway Time-out went again because of the base.

Answer 1

In slow log, a bunch of queries without indexes

Actually.
From the console - launch through SELECT SQL_NO_CACHE. Most likely you have query cache active.