Version: 5.6.22 (hot off the press!)
32 GB of RAM
You are running on Windows.
Running 64-bit version (good)
You appear to be running entirely (or mostly) InnoDB. (good)
The More Important Issues:
( innodb_buffer_pool_size / _ram ) = 5,368,709,120 / 32768M = 15.6% -- % of RAM used for InnoDB buffer_pool
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (8M / 0.20 + 5368709120 / 0.70) / 32768M = 22.4% -- Most of available ram should be made available for caching.
( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests ) = 4,502,512 / 84487877 = 5.3% -- Read requests that had to hit disk
-- Increase innodb_buffer_pool_size if you have enough RAM.
( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 4,677,576 / 84487877 = 5.5% -- Read requests that had to hit disk
-- Increase innodb_buffer_pool_size to 20G unless that will cause swapping.
--
http://mysql.rjweb.org/doc.php/memory
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,417,791 / 60 * 48M / 82671682048 = 14.4 -- Minutes between InnoDB log rotations
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- Other metrics imply that 48M is OK; don't bother to change it.
( Innodb_dblwr_writes ) = 7,505,971 / 1417791 = 5.3 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)
( Queries ) = 10,390,569,428 / 1417791 = 7328 /sec -- Queries (including inside SP)
-- >3000 _may_ be stressing server
-- OK, it is a busy machine; please describe more about what it is doing. Especially discuss the use (or non-use) of transactions in InnoDB.
( (Queries-Questions)/Queries ) = (10390569428-126565469)/10390569428 = 98.8% -- Fraction of queries that are inside Stored Routines.
-- Is the code buried in Stored Routines? Can they be made more efficient? Especially: Are you using cursors?? If so, let's try to replace them with single queries -- This could make a huge improvement.
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (115539062 + 2192694 + 0 + 8353883 + 21826602 + 0) / 1417791 = 104 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives
-- Are there 70 times as many SELECTs as writes?
( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)
( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest turning it on and using 2
Innodb_data_fsyncs = 31/sec
Innodb_os_log_fsyncs = 17/sec
innodb_flush_log_at_trx_commit = 1
-- This is the slowest setting; consider changing to 2.
You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.
A lot of other readings (table cache, connections, etc) are in very good shape.
What is the disk subsystem like? RAID? SSD?
> But it is often I hace to Delete information from the table like once a month, and I delete the records of a Full month the syntax is this delete from mkphis where pohfeclle < '2014/01/31 23:59:59' ;
> But this will take forever, because there are about 15M of Records per month.
Consider using PARTITIONing -- DROP PARTITION is instantaneous. Yes, that DELETE will be slow. More on this topic:
http://mysql.rjweb.org/doc.php/partitionmaint
> KEY `mkphis_i3` (`idctm`,`idtra`,`pohfee`),
> KEY `mkphis_i4` (`idctm`,`idtra`,`pohfee`),
Redundant index? DROP one of them.
Technically, there is nothing "wrong" with use of DECIMAL. However, MySQL users usually use INT/MEDIUMINT/SMALLINT/TINYINT and usually use UNSIGNED. Also, FLOAT (or DOUBLE) is often used for non-money values that are not integers. (These datatype changes would probably not help performance by a noticeable amount.)
Back to the original question (UPDATE not making use of the hardware)...
* The tuning suggestions above would generally speed things up (less CPU, less I/O).
* If you are using a cursor to do the UPDATE, that could be a huge factor. Can we see the UPDATE and the SP it is probably in?
* MySQL does not use more than one core for one connection. This is probably the main answer, since it looks like 3 cores are busy (20% of 16 cores). That is probably 1 core for the UPDATE, plus 2 for other things. At 7K qps and up to 55 connections, I suspect that SELECTs are keeping the other 2 core's worth of CPU busy.
* The lack of I/O implies that things a being well cached. (This suggests that increasing the buffer_pool_size may not help the UPDATE much.)