MySQL Forums
Forum List  »  Performance

Re: How To Use More Resources
Posted by: Rick James
Date: January 07, 2015 11:21PM

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.)

Options: ReplyQuote


Subject
Views
Written By
Posted
1747
January 07, 2015 11:12AM
747
January 07, 2015 07:14PM
1133
January 07, 2015 09:14PM
Re: How To Use More Resources
1088
January 07, 2015 11:21PM
702
January 07, 2015 11:42PM
784
January 08, 2015 05:48PM
845
January 09, 2015 02:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.