mysql - MariaDB MySQL 优化

Question

我有点坚持在我的 VPS 上设置 MariaDB 的最佳性能。我有:6 芯； 16GB 内存； 300GB 固态硬盘；云端Linux；精简版

 >>  MySQLTuner 1.7.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.2.13-MariaDB-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/dev.whitespot.co.err(611K)
[OK] Log file /var/lib/mysql/dev.whitespot.co.err exists
[OK] Log file /var/lib/mysql/dev.whitespot.co.err is readable.
[OK] Log file /var/lib/mysql/dev.whitespot.co.err is not empty
[OK] Log file /var/lib/mysql/dev.whitespot.co.err is smaller than 32 Mb
[!!] /var/lib/mysql/sqlhost.err contains 4467 warning(s).
[!!] /var/lib/mysql/sqlhost.err contains 164 error(s).
[--] 8 start(s) detected in /var/lib/mysql/dev.whitespot.co.err
[--] 1) 2018-02-14  3:17:43 140627065792640 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-02-14  3:08:56 140369082124416 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-02-14  3:04:17 140342812088448 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-02-14  2:27:29 139698717120640 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-02-14  2:27:05 140353362925696 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-02-14  0:59:46 140394864134272 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-02-14  0:58:53 140612241397888 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-02-14  0:40:07 140392565569664 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7 shutdown(s) detected in /var/lib/mysql/sqlhostname.err
[--] 1) 2018-02-14  3:17:39 140368255764224 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-02-14  3:08:52 140342016706304 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-02-14  3:04:13 139688655320832 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-02-14  2:27:26 140353156269824 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-02-14  2:26:59 140384804599552 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-02-14  0:59:42 140611259844352 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-02-14  0:58:49 140391611623168 [Note] /usr/sbin/mysqld: Shutdown complete

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

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'netdata@localhost' has no password set.
[!!] User 'jb9363_user@%' hasn't specific host restriction.
[!!] User 'robak@%' hasn't specific host restriction.
[!!] User 'robak_alexadam@%' hasn't specific host restriction.
[!!] User 'robak_francesco@%' hasn't specific host restriction.
[!!] User 'robak_g@%' hasn't specific host restriction.
[!!] User 'robak_pro@%' hasn't specific host restriction.
[!!] User 'robak_tom@%' hasn't specific host restriction.
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 8h 12m 49s (4M q [162.139 qps], 51K conn, TX: 41G, RX: 1G)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 15.7G
[--] Max MySQL memory    : 9.8G
[--] Other process memory: 1.5G
[--] Total buffers: 8.4G global + 4.7M per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 8.5G (54.58% of installed RAM)
[OK] Maximum possible memory usage: 9.8G (62.60% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[!!] Slow queries: 36% (1M/4M)
[OK] Highest usage of available connections: 8% (24/300)
[OK] Aborted connections: 0.13%  (66/51527)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (24 temp sorts / 563K sorts)
[!!] Joins performed without indexes: 10333
[!!] Temporary tables created on disk: 69% (195K on disk / 282K total)
[OK] Thread cache hit rate: 99% (24 created / 51K connections)
[OK] Table cache hit rate: 98% (7K open / 7K opened)
[OK] Open file limit used: 38% (9K/25K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 31.3% (84M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/295.8M
[OK] Read Key buffer hit rate: 99.9% (34M cached / 34K reads)
[!!] Write Key buffer hit rate: 12.9% (112K cached / 97K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 8.0G/1.6G
[OK] InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/8.0G should be equal 25%
[OK] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 64 for 8 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% (205255768 hits/ 205283702 total)
[!!] InnoDB Write Log efficiency: 77.83% (185463 hits/ 238296 total)
[OK] InnoDB log waits: 0.00% (0 waits / 52833 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 93.9% (2M cached / 181K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/lib/mysql/sqlhostname.err file
    Control error line(s) into /var/lib/mysql/sqlhostname.err file
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)

我已经安装了一些 SQL 监控工具。这个软件每小时给我发一封关于慢查询的电子邮件。日志文件现在是 500mb...在 12 小时内创建的日志:/

我通过 LiteSpeed + LS Cache 在这个仅 VPS 的 WordPress 网站上运行。请帮我优化一下。我并不是说网站很慢，但我认为更好的 MySQL 优化对于整个服务器性能来说会更好，甚至可能会加快 wordpress 安装速度。

Answer 1

请发布一封每小时慢速查询电子邮件，以便我们可以看到您的可用报告并提供帮助。

你现在可以在 my.cnf/ini [mysqld] 部分做的事情来改善情况，

max_connections=50  # from 300 to reduce RAM footprint
change next 4 on one day, please   shutdown/restart
#sort_buffer_size=val  # lead with # to use default
#read_buffer_size=val  # lead with # to use default
#read_rnd_buffer_size=val  # lead with # to use default
#join_buffer_size=val  # lead with # to use default
thread_cache_size=50  # from ? to reduce threads_created
innodb_thread_concurrency=8  # from 0 to enable
innodb_lru_scan_depth=128  # from 1024 to avoid overloading page_cleaners
innodb_page_cleaners=64  # from def for autolimit to be = innodb_buffer_pool_instances
innodb_buffer_pool_dump_at_shutdown=ON  # to prepare for WARM IBPool
innodb_buffer_pool_load_at_startup=ON  # to WARM the innodb_buffer_pool
innodb_flush_neighbors=0  # should be OFF when using SSD, there is not latency

祝你好运，在你的声誉达到 15 左右之前，你将无法投票或接受任何东西。在你的情况下，将更改限制为每天一次，这样你就可以通过监控看到效果，以了解这种分析和实现的效果为你做。

使用 MySQLCalculator.com 将花费 2 分钟宝贵的时间来查看更改前后的 RAM 占用情况。