Имеется простенький сайт, который в большой степени расчитан на работу с базой данных, на загрузку одной страницы требуется минимум 5-10 SELECT'ОВ, UPDATE тоже довольно часто используется, а вот INSERT не особо. Не могу правильно настроить конфигурацию..
Характеристики сервераVPS (KVM)
2 GB RAM
20 GB SSD
Версия сервера: 10.1.37-MariaDB-0+deb9u1 - Debian 9.6
Версия PHP: 7.0.33-0+deb9u1
mysqltuner>> MySQLTuner 1.6.18 - Major Hayden
>> Bug reports, feature requests, and downloads at mysqltuner.com
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 10.1.37-MariaDB-0+deb9u1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics --------------------------------------------- --------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 400K (Tables: 19)
[--] Data in MyISAM tables: 79M (Tables: 45)
[OK] Total fragmented tables: 0
-------- Security Recommendations ---------------------------------------------- --------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 15h 45m 18s (6M q [109.093 qps], 400K conn, TX: 12G, RX: 424M)
[--] Reads / Writes: 79% / 21%
[--] Binary logging is disabled
[--] Physical Memory : 2.0G
[--] Max MySQL memory : 7.2G
[--] Other process memory: 45.5M
[--] Total buffers: 696.0M global + 33.6M per thread (200 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 964.5M (48.17% of installed RAM)
[!!] Maximum possible memory usage: 7.2G (370.01% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/6M)
[OK] Highest usage of available connections: 4% (8/200)
[OK] Aborted connections: 0.00% (1/400964)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache efficiency: 26.0% (1M cached / 6M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 126K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 49% (8K on disk / 17K total)
[OK] Thread cache hit rate: 99% (8 created / 400K connections)
[OK] Table cache hit rate: 96% (170 open / 176 opened)
[OK] Open file limit used: 1% (173/16K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 1 thread(s).
[--] Using default value is good enough for your version (10.1.37-MariaDB-0+deb9u1)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 20.9% (56M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/11.6M
[OK] Read Key buffer hit rate: 100.0% (35M cached / 6K reads)
[!!] Write Key buffer hit rate: 48.6% (1M cached / 791K writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 77.6% (39K cached / 8K reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/400.0K
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[!!] InnoDB Read buffer efficiency: 80.56% (1537 hits/ 1908 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB 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:
MySQL started within 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.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
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:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 128M)
max_heap_table_size (> 128M)
Простой
