这是我的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 设置提供最佳配置
将innodb_buffer_pool_size设置为可用RAM 的 70% 左右。 mysqltuner建议的4.4G将处理您当前的所有数据。 如果你期望它增长，那就给它更多。 此设置可能有助于I/O(而不是 CPU(。
(实际上"InnoDB 读取缓冲区效率：95.00%(920105182次点击/968548737总数("说 paultry 128M buffer_pool似乎可以充分处理"工作集"。
您无法针对"优化以获得更好的 CPU 使用率"进行优化。您可以找到慢速查询并处理索引(尤其是"复合"索引(和查询公式。这些将有助于 CPU。
http://mysql.rjweb.org/doc.php/mysql_analysis
对 my.cnf 或 my.ini [mysqld] 部分的建议
innodb_buffer_pool_size=6G
thread_handling=pool-of-threads
经过 24 小时的正常运行时间后，发布新的 MySQLTuner 完整报告，以便我们可以看到您的进度，即使此 Alpha 版本不用于生产。