Была попытка перейти с MyISAM сначала на Aria, а затем и на InnoDB. Попытки оказались неуспешными: Aria почти сразу же начала тупить, как сейчас MyISAM (тогда я подумал, что просто Aria слишком никакущая, что, в общем то, подтверждали сторонние тесты от других людей); InnoDB утилизировала на 100% ssd только на одной репликации, ssd выдавал всего несколько сотен iops.
Подавленный и разочарованный решил вернуться обратно на MyISAM... и тут ждало новое разочарование: старые скрипты стали работать на много медленнее, чем до попытке перехода на другой движок.
Суть проблемы: CPU и SSD не утилизируются, при этом в processlist висит 20-30 запросов с статусом Sending data, локов нету у этих запросов. CPU отжирается максимум 3-4 (паралельно еще куча более простых запросов выполняется на сервере), SSD нагружены на 0-10%.
Сразу после перезагрузки, MariaDB на этих запросах начинает кушать больше 20 CPU, но 15-18CPU - это sys. Постепенно sys уменьшается, и приходим к стандартным 3-4CPU.
Во что может упираться MariaDB? Не исключаю, что проблемы могут быть в железе или в самой виртуалке, но как искать/проверять не знаю.
SSD при определенных нагрузках выдают 10-20к iops, скорость записи и чтения тестировали.
Конфиг виртуалки: 32CPU 49RAM 2SSD, там же php скрипты крутятся.
Конфиг MySQL и результат MySQLTuner:
[mysqld]
datadir=/var/lib/mysql
tmpdir = /mnt/ssd/tmp/
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=1
key_buffer_size = 16G
max_connections = 300
max_user_connections = 280
table_open_cache = 10000
table_definition_cache = 10000
open_files_limit = 165832
max_allowed_packet = 16M
log_warnings = 0
extra_port = 3307
extra_max_connections = 4
sql_mode = NO_ENGINE_SUBSTITUTION
skip-innodb
sort_buffer_size = 12M
read_buffer_size = 2M
join_buffer_size = 4M
thread_cache_size = 256
thread_stack=512K
thread_handling = one-thread-per-connection
read_rnd_buffer_size = 16M
net_buffer_length = 1048576
net_read_timeout = 600
net_write_timeout = 600
myisam_sort_buffer_size = 8G
aria_sort_buffer_size = 2G
aria_pagecache_buffer_size = 2G
query_cache_type = OFF
myisam_repair_threads = 4
max_heap_table_size = 512M
tmp_table_size = 512M
character-set-server=utf8
collation-server=utf8_general_ci
init-connect="SET NAMES utf8"
default_storage_engine="MyISAM"
slow_query_log_file = /var/log/mariadb/mariadb-slow.log
long-query-time = 5
slow_query_log = 0
skip-name-resolve
low_priority_updates = 1
>> MySQLTuner 1.7.15 - 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.3.14-MariaDB-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/rests_mapping_php_sphinx.err(0B)
[!!] Log file /var/lib/mysql/rests_mapping_php_sphinx.err doesn't exist
[!!] Log file /var/lib/mysql/rests_mapping_php_sphinx.err isn't readable.
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV -InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE +SPHINX
[--] Data in Aria tables: 49.7G (Tables: 5758)
[--] Data in MyISAM tables: 674.0G (Tables: 4483)
[--] Data in CSV tables: 0B (Tables: 2)
[--] Data in SPHINX tables: 0B (Tables: 8)
[!!] Total fragmented tables: 8
-------- Analysis Performance Metrics --------------------------------------------------------------
Use of uninitialized value $myvar{"innodb_stats_on_metadata"} in concatenation
(.) or string at mysqltuner.pl line 5769 (#1)
(W uninitialized) An undefined value was used as if it were already
defined. It was interpreted as a "" or a 0, but maybe it was a mistake.
To suppress this warning assign a defined value to your variables.
To help you figure out what was undefined, perl will try to tell you
the name of the variable (if any) that was undefined. In some cases
it cannot do this, so it also tells you what operation you used the
undefined value in. Note, however, that perl optimizes your program
anid the operation displayed in the warning may not necessarily appear
literally in your program. For example, "that $foo" is usually
optimized into "that " . $foo, and the warning will refer to the
concatenation (.) operator, even though there is no . in
your program.
[--] innodb_stats_on_metadata:
Use of uninitialized value $myvar{"innodb_stats_on_metadata"} in string eq at
mysqltuner.pl line 5770 (#1)
[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
[!!] User '***@%' does not specify hostname restrictions.
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 5d 21h 29m 48s (481M q [945.273 qps], 13M conn, TX: 1197G, RX: 145G)
[--] Reads / Writes: 71% / 29%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 49.1G
[--] Max MySQL memory : 28.6G
[--] Other process memory: 0B
[--] Total buffers: 18.5G global + 34.5M per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 23.3G (47.32% of installed RAM)
[OK] Maximum possible memory usage: 28.6G (58.22% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (37K/481M)
[OK] Highest usage of available connections: 47% (141/300)
[OK] Aborted connections: 0.03% (3468/13351535)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 337M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (63 temp sorts / 62M sorts)
[!!] Joins performed without indexes: 22328
[OK] Temporary tables created on disk: 0% (3K on disk / 34M total)
[OK] Thread cache hit rate: 99% (207 created / 13M connections)
[!!] Table cache hit rate: 15% (10K open / 66K opened)
[OK] Open file limit used: 15% (20K/125K)
[OK] Table locks acquired immediately: 98% (866M immediate / 884M locks)
[OK] Binlog cache memory access: 0% (0 Memory / 0 Total)
-------- 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.3.14-MariaDB-log)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 55.4% (9B used / 17B cache)
[OK] Key buffer size / total MyISAM indexes: 16.0G/147.6G
[OK] Read Key buffer hit rate: 99.9% (247B cached / 138M reads)
[!!] Write Key buffer hit rate: 4.8% (7B cached / 340M writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is disabled.
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 2.0G/27.8G
[OK] Aria pagecache hit rate: 99.6% (390M cached / 1M 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: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[!!] This replication slave is running with the read_only option disabled.
[!!] This replication slave is lagging and slave has 27174 second(s) behind master host.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Restrict Host for user@% to
user@SpecificDNSorIp
Adjust your join queries to always utilize indexes
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
https://bit.ly/1mi7c4C
Read this before increasing for MariaDB
https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here:
https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (125832) variable
should be greater than table_open_cache (10000)
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/good-dba/mariadb-sys for MariaDB
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 4.0M, or always use indexes with JOINs)
table_open_cache (> 10000)
performance_schema = ON enable PFS
(table_open_cache и table_definition_cache убавляли для тестов, потом вернем старые 50000 и куча Aria таблиц пока никак не используется, потом сами пересоздадутся на MyISAM)
