主页 / dba / 问题 / 327049
Accepted
lio89
Asked: 2023-05-13 06:46:00 +0800 CST

为我的服务器调整 MYSQL 8.0.32

  • 772

编辑： 用户@mustaccio 让我承认我必须提出激发调整需求的问题：

查询在较旧的服务器上运行得更快，功能更弱，MySQL 5.7.42 版花费的时间减少了三倍。以下是其中之一的非常简化的示例：

询问：

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;

表定义：

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;

我正在使用专用服务器：

  • 内存32 GB
  • CPU Intel Xeon D-2141I @2.20GHz（显示“处理器总数：16”，速度 2200.000 MHz，缓存 16384 KB）

总共 20 个数据库：

  • 每个都有250张桌子。

    • 在这 250 个中，一个大小约为 700MB，大约有 4 个大小约为 300MB。那么剩下的就是80MB以下

  • 较重的数据库是 100 GB

    • 其中较重的表（作为例外）为 4.5 GB，其次是 10 个约 2.5GB，以及约 40 个 1.2GB。

这是我的my.cnf配置：

[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

这些是 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.

这些是来自 PHPMyAdmin 的红色变量，表示有警报（根据 mysqlturner，MySQL 在过去 24 小时内启动，因为我重新启动了 MySQL 服务）：

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

这些是我在调查时从别人的帖子中读到的提示：

第一个：来自用户@RolandoMySQLDBA 的how-large-should-be-mysql-innodb-buffer-pool-size

“这将为您提供 RIBPS，推荐的 InnoDB 缓冲池大小，基于所有 InnoDB 数据和索引，额外增加 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

我的结果是 10

他还说：

这将为您提供此时 InnoDB 缓冲池中的 InnoDB 数据正在使用多少 GB 内存。

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;

我的结果是12.84

并且他还对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;

我的结果是9G

用户@max 和@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:

另一篇文章来自用户@Abdul Manaf，答案来自@RolandoMySQLDBA

设置什么 innodb 缓冲池和为什么

他说

您需要计算的是在任何给定时刻在当前 DB Server 上加载了多少 InnoDB 缓冲池。

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;

我的结果是 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!

innodb

2 个回答

  1. innodb_buffer_pool_size=4G  --> 20G (~70% of RAM)
tmp_table_size=768M         --> 300M (<1% of RAM)
max_heap_table_size=768M    --> 300M (<1% of RAM)

    The 10G you computed for the buffer_pool will be fine for now; will your dataset grow over time? If it is too low, your queries will need more I/O; if it is too high, you may swap, which is even worse for performance.

    Be aware that the %full increases over time, but will level out at something based on your dataset size or ~95%.

    Then...

    If you have CPU problems: Ask for help with indexes.

    If you have memory problems due to other apps, lower the buffer_pool.

    There are an abnormally large number of tables; why?

    If you would like further (updated) analysis of GLOBAL STATUS and VARIABLES, see http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

    In some versions, the tables have been moved from information_schema to performance_schema.

    • 1
  2. Best Answer

    Thanks to all of you who replied!

    Already tuned the server with the help provided. Noticed that I was using MY ISAM tables.

    After convert them all to INNODB, I gained a lot of speed !

    So this is solved for me !

    • 0

相关问题