EDIT: User @mustaccio let me acknowledge that I must present the problem that motivates the need for the tuning:
There are querys running faster in an older server, less powerful, MySQL Version 5.7.42 It's taking three times less time. The following is a very simplified example of one of these:
QUERY:
INSERT INTO
mydatabase.mytable
SET
CODMED=56304,
CODCLOS='82618',
CONTACTOID=51482,
TRIM_ALT_GRAL=8,
TRIM_ANT_GRAL=10,
TRIM_ACT_GRAL=6,
TRIM_ACT_RMM=2,
OS_TO=101,
OS_PAF=49.5,
OS_OS=51,
OS_OS_F=1,
OS_PA_F=1;
TABLE DEFINITION:
CREATE TABLE `mytable` (
`CODIGO` int NOT NULL,
`CODCLOS` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
`CODMED` int NOT NULL,
`CONTACTOID` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL COMMENT 'id de contacto',
`VALOR2` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`LINEASXMERCADOS_COMPLETO` json DEFAULT NULL,
`LINEASXMERCADOS` json DEFAULT NULL,
`LINEASXMERCADOS_M5G` varchar(5000) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '{}' COMMENT 'VALOR4LM',
`OBRASSOCIALES` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '{}',
`OS_OS` int NOT NULL,
`OS_PR` int DEFAULT NULL,
`OS_PA` int DEFAULT NULL COMMENT 'PAM',
`OS_PAF` int NOT NULL,
`OS_X` int DEFAULT NULL COMMENT 'varios',
`OS_TO` int NOT NULL,
`OS_PR_F` int NOT NULL DEFAULT '0',
`OS_OS_F` int NOT NULL DEFAULT '0',
`OS_PA_F` varchar(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '0',
`TRIM_ALT_GRAL` int NOT NULL,
`TRIM_ALT_RMM` int DEFAULT NULL,
`TRIM_ANT_GRAL` int NOT NULL,
`TRIM_ANT_RMM` int DEFAULT NULL,
`TRIM_ACT_GRAL` int NOT NULL,
`TRIM_ACT_RMM` int NOT NULL,
`EST_GRAL` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`EST_RMM` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`STAMP` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
ALTER TABLE `mytable`
ADD PRIMARY KEY (`CODIGO`),
ADD UNIQUE KEY `CODMED_2` (`CODMED`),
ADD KEY `TRIM_ACT_GRAL` (`TRIM_ACT_GRAL`),
ADD KEY `CONTACTOID` (`CONTACTOID`),
ADD KEY `CODCLOS` (`CODCLOS`),
ADD KEY `TRIM_ACT_RMM` (`TRIM_ACT_RMM`),
ADD KEY `LINEASXMERCADOS_M5G` (`LINEASXMERCADOS_M5G`(333));
ALTER TABLE `mytable`
MODIFY `CODIGO` int NOT NULL AUTO_INCREMENT;
I'm working with a dedicated server:
- RAM 32 gb
- CPU Intel Xeon D-2141I @2.20GHz (it says "Total processors: 16", Speed2200.000 MHz, Cache 16384 KB)
20 Databases in total:
Each one has 250 tables.
- From those 250, one as a size of around 700MB and there are about 4 of a size around 300MB. Then the rest is below 80MB
Heavier database is 100 GB
- The heavier table from it (as an exception) is 4.5 GB, followed by 10 of about 2.5GB, and around 40 of 1.2GB of size.
This is my my.cnf configuration:
[mysqld]
disable-log-bin=1
default-authentication-plugin=mysql_native_password
performance-schema=1
innodb_file_per_table=1 # Ensure that each innodb table is it's own binary data block just in case there's corruption.
# query_cache_size=64M
thread_cache_size=4
key_buffer_size=32M
max_allowed_packet=268435456
table_open_cache=512 # max 2048, can be increased if more Opened tables - SHOW STATUS LIKE 'Opened_tables';
wait_timeout=28800
max_user_connections=25
open_files_limit=40000
#delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
#delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts
myisam_sort_buffer_size=2M # can be increased per sessions if needed for alter tables (indexes, repair)
#query_cache_limit=2M # leave at default unless there is a good reason
#join_buffer=2M # leave at default unless there is a good reason
#sort_buffer_size=2M # leave at default unless there is a good reason
#read_rnd_buffer_size=256K # leave at default unless there is a good reason
#read_buffer_size=2M # leave at default unless there is a good reason
collation_server=utf8_unicode_ci
character_set_server=utf8
#innodb_flush_method=O_DSYNC # Do not enable this without a good reason - it doesn't work well on new dedis
innodb_buffer_pool_size=4G
innodb_thread_concurrency=16 # Number of physical + virtual CPU's, be careful of adding more
tmp_table_size=768M
max_heap_table_size=768M
max_connections=150
table_definition_cache=512 # increase by the same factor as table_open_cache
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=64M
general_log=0
slow_query_log=0
log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;
long_query_time=20
innodb_io_capacity=100
innodb_buffer_pool_instances=4
mysqlx=0
sql_mode="NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES"
innodb_log_file_size=768M
Those are the recommendations from mysqltuner:
>> MySQLTuner 2.1.1
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 8.0.32
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql.log exists
[--] Log file: /var/log/mysql.log (0B)
[--] Log file /var/log/mysql.log is empty. Assuming log-rotation. Use --server-log={file} for explicit file
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM -ndbcluster -ndbinfo +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 108.0G (Tables: 4194)
[--] Data in InnoDB tables: 5.9G (Tables: 2269)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8.0+
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 4h 29m 19s (13M q [854.998 qps], 20K conn, TX: 8G, RX: 6G)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory : 29.3G
[--] Max MySQL memory : 42.8G
[--] Other process memory: 0B
[--] Total buffers: 4.8G global + 257.9M per thread (150 max threads)
[--] Performance_schema Max memory usage: 181M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 7.3G (24.88% of installed RAM)
[!!] Maximum possible memory usage: 42.8G (146.11% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (4/13M)
[OK] Highest usage of available connections: 6% (9/150)
[OK] Aborted connections: 0.10% (20/20208)
[!!] CPanel and Flex system skip-name-resolve should be on
[--] Query cache has been removed since MySQL 8.0
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 4M sorts)
[!!] Joins performed without indexes: 4818
[OK] Temporary tables created on disk: 0% (0 on disk / 60K total)
[OK] Thread cache hit rate: 99% (187 created / 20K connections)
[OK] Table cache hit rate: 99% (23M hits / 23M requests)
[!!] table_definition_cache (512) is less than number of tables (6795)
[OK] Open file limit used: 1% (423/40K)
[OK] Table locks acquired immediately: 99% (20M immediate / 20M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 181.6M
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled since MySQL 8.0.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 16
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 4.0G / 5.9G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (37.5%): 768.0M * 2 / 4.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 4
[--] Number of InnoDB Buffer Pool Chunk: 32 for 4 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.99% (393143981 hits / 393168510 total)
[!!] InnoDB Write Log efficiency: 58.44% (506487 hits / 866740 total)
[OK] InnoDB log waits: 0.00% (0 waits / 360253 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
MySQL was started within the last 24 hours: recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
name resolution is enabled due to cPanel doesn't support this disabled.
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Buffer Key MyISAM set to 0, no MyISAM table detected
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
long_query_time (<= 10)
skip-name-resolve=0
join_buffer_size (> 256.0K, or always use indexes with JOINs)
table_definition_cache (512) > 6795 or -1 (autosizing if supported)
key_buffer_size=0
innodb_buffer_pool_size (>= 5.9G) if possible.
innodb_log_file_size should be (=512M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
These are the variables from PHPMyAdmin that are coloured red, signaling there are alerts (MySQL was started within the last 24 hours according the mysqlturner, because I restarted the MySQL Service):
Handler read rnd: 12 M
Handler read rnd next: 1.7 G
Innodb buffer pool reads: 24.5 k
Key reads: 44 M
Opened tables: 213.1 k
Select full join: 5 k
Select range check: 23
Sort merge passes: 1.5 k
Table locks waited: 1.1 k
Those are tips I read from anothers posts while I was investigating:
First one: how-large-should-be-mysql-innodb-buffer-pool-size from user @RolandoMySQLDBA
"This will give you the RIBPS, Recommended InnoDB Buffer Pool Size, based on all InnoDB Data and Indexes, with an additional 60%.".
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A
My result is 10
He also said:
This will give you how many actual GB of memory is in use by InnoDB Data in the InnoDB Buffer Pool at this moment.
SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;
My result is 12.84
And he also made another formula for the InnoDB Buffer Pool Size:
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1.25 growth) BBB
WHERE ENGINE='InnoDB'
) AA
) A;
My result is 9G
There is still another recomendation from the user @max and @RickJames
SELECT
'Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests' AS Expression,
'Read requests that had to hit disk' AS Meaning,
ROUND((SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Innodb_buffer_pool_reads'
)
/
(SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) * 100, 2) AS Val,
'0-2%' AS Recommend,
'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
'Innodb_pages_read / Innodb_buffer_pool_read_requests' AS Expression,
'Read requests that had to hit disk' AS Meaning,
ROUND((SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Innodb_pages_read'
)
/
(SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) * 100, 2) AS Val,
'0-2%' AS Recommend,
'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
'Innodb_pages_written / Innodb_buffer_pool_write_requests' AS Expression,
'Write requests that had to hit disk' AS Meaning,
ROUND((SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Innodb_pages_written'
)
/
(SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Innodb_buffer_pool_write_requests'
) * 100, 2) AS Val,
'0-15%' AS Recommend,
'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
'Innodb_buffer_pool_reads / Uptime' AS Expression,
'Reads' AS Meaning,
ROUND((SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Innodb_buffer_pool_reads'
)
/
(SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Uptime'
)) AS Val,
'0-100/sec' AS Recommend,
'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
'(Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) / Uptime' AS Expression,
'InnoDB I/O' AS Meaning,
ROUND(((SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Innodb_buffer_pool_reads'
)
+
(SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Innodb_buffer_pool_pages_flushed'
))
/
(SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Uptime'
)) AS Val,
'0-100/sec' AS Recommend,
'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
'Innodb_buffer_pool_pages_flushed / Uptime' AS Expression,
'Writes (flushes)' AS Meaning,
ROUND((SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Innodb_buffer_pool_pages_flushed'
)
/
(SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Uptime'
)) AS Val,
'0-100/sec' AS Recommend,
'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
'Innodb_buffer_pool_wait_free / Uptime' AS Expression,
'Counter for when there are no free pages in buffer_pool. That is, all pages are dirty.' AS Meaning,
ROUND((SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Innodb_buffer_pool_wait_free'
)
/
(SELECT
VARIABLE_VALUE
FROM
performance_schema.global_status
WHERE
VARIABLE_NAME = 'Uptime'
)) AS Val,
'0-1/sec' AS Recommend,
'First be sure innodb_buffer_pool_size is set reasonably; if still trouble, decrease innodb_max_dirty_pages_pct' AS WhatToDoIfOutOfRange
Those are my results: 
And the other post is from user @Abdul Manaf and the answer from @RolandoMySQLDBA
what-to-set-innodb-buffer-pool-and-why
He said
What you need to calculate is how much of the InnoDB Buffer Pool is loaded at any given moment on the current DB Server.
SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;
My result is 12.85
According to what has been posted, it may seems that the way is more or less clear.. but I would like your help, especially since I don't have a development server and I can only do modifications on the production server, I'm worried about causing harm.
Thanks!
