my.cnf (Centos 8)中mysql (mariadb优化10.5)的优化设置

Question

以下是我的mysqltuner结果：

 [--] Skipped version check for MySQLTuner script
    [!!] Successfully authenticated with no password - SECURITY RISK!
    [!!] Your MySQL version 10.5.0-MariaDB is EOL software!  Upgrade soon!
    [OK] Operating on 64-bit architecture

    -------- Log file Recommendations ------------------------------------------------------------------
    [!!] Log file  doesn't exist

    -------- Storage Engine Statistics -----------------------------------------------------------------
    [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
    [--] Data in InnoDB tables: 4.4G (Tables: 227)
    [OK] Total fragmented tables: 0

    -------- Analysis Performance Metrics --------------------------------------------------------------
    [--] innodb_stats_on_metadata: OFF
    [OK] No stat updates during querying INFORMATION_SCHEMA.

    -------- Security Recommendations ------------------------------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [!!] There is no basic password file list!

    -------- CVE Security Recommendations --------------------------------------------------------------
    [--] Skipped due to --cvefile option undefined

    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 50m 57s (911K q [298.114 qps], 2K conn, TX: 3G, RX: 104M)
    [--] Reads / Writes: 99% / 1%
    [--] Binary logging is disabled
    [--] Physical Memory     : 31.2G
    [--] Max MySQL memory    : 3.2G
    [--] Other process memory: 0B
    [--] Total buffers: 417.0M global + 18.9M per thread (151 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 568.3M (1.78% of installed RAM)
    [OK] Maximum possible memory usage: 3.2G (10.24% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (0/911K)
    [OK] Highest usage of available connections: 5% (8/151)
    [OK] Aborted connections: 0.00%  (0/2906)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [!!] Query cache may be disabled by default due to mutex contention.
    [!!] Query cache efficiency: 0.0% (0 cached / 888K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (286 temp sorts / 50K sorts)
    [!!] Joins performed without indexes: 18
    [!!] Temporary tables created on disk: 81% (15K on disk / 18K total)
    [OK] Thread cache hit rate: 99% (8 created / 2K connections)
    [OK] Table cache hit rate: 93% (93 open / 99 opened)
    [OK] table_definition_cache(400) is upper than number of tables(391)
    [OK] Open file limit used: 0% (28/4K)
    [OK] Table locks acquired immediately: 100% (143 immediate / 143 locks)

    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    [--] Memory used by P_S: 0B
    [--] Sys schema isn't installed.

    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 32 thread(s).
    [--] Using default value is good enough for your version (10.5.0-MariaDB)

    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 18.2% (24M used / 134M cache)
    [!!] Cannot calculate MyISAM index size - re-run script as root user

    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [--] InnoDB Thread Concurrency: 0
    [OK] InnoDB File per table is activated
    [!!] InnoDB buffer pool / data size: 128.0M/4.4G
    [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 96.0M * 1/128.0M should be equal to 25%
    [OK] InnoDB buffer pool instances: 1
    [--] Number of InnoDB Buffer Pool Chunk : 1 for 1 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: 95.00% (920105182 hits/ 968548737 total)
    [!!] InnoDB Write Log efficiency: 33.6% (1776 hits/ 5285 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 7061 writes)

    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/320.0K
    [OK] Aria pagecache hit rate: 98.6% (936K cached / 13K reads)

    -------- 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: MIXED
    [--] XA support enabled: ON
    [--] Semi synchronous replication Master: OFF
    [--] Semi synchronous replication Slave: OFF
    [--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    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).
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: 

Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 4.4G) if possible.
    innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

谁能优化这个，我该怎么做？让事情变得更快？

我想优化以更好的CPU使用。

服务器规范: 16核心32线程32 GB内存

需要对mysql设置进行优化配置

Answer 1

将innodb_buffer_pool_size设置为大约70%的可用内存。mysqltuner建议的4.4G将处理所有当前数据。如果你希望它长出来，那就给它更多。此设置可能有助于I/O (而不是CPU)。

(实际上，"InnoDB读取缓冲区的效率: 95.00% (920105182次点击/ 968548737次)“表示，简单的128 m buffer_pool似乎能够充分处理”工作集“。

您不能为“优化以更好地使用CPU”进行调优。您可以找到缓慢的查询，并处理索引(特别是“复合”索引)和查询公式。这些将有助于CPU。

http://mysql.rjweb.org/doc.php/mysql_analysis

Answer 2

关于my.cnf或my.ini mysqld部分的建议

innodb_buffer_pool_size=6G
thread_handling=pool-of-threads

在24小时的正常运行时间后，发布一份新的MySQLTuner完整报告，这样我们就可以看到您的进展，即使在生产中不会使用这个Alpha版本。