5-10K小型数据库的MariaDB优化

Question

运行基于SaaS LTS的MariaDB (1:10.1.44-0ubuntu0.18.04.1)，拥有近5000个数据库(每个租户1个)。每个租户的平均数据: 8MB的数据(磁盘上)和~50个表。CPU/Mem负载可以忽略不计。它已经运行了几年，每天增加大约5-20个租户。

然而。当dbs的nr增长到大约5150时，MariaDB服务器进程就会崩溃。不幸的是，到目前为止，我还没有能够提取出有用的日志消息。此外，启动MariaDB需要大约10分钟的时间，当我想要尝试新设置时，这是相当长的时间。

您会推荐哪些设置来降低崩溃的风险，并加快启动时间？

其他一些特点：

• 显示全局状态
• 显示变量
• 服务器有2CPU和8GB内存，其中25%在高峰时间可用。
• 在高峰时期，每个CPU核心的负载从未超过0.20
• 连接是短暂的，我预计永远不会有超过20个并发的db连接。
• 所有表都是InnoDB
• 每个数据库的模式可能不同(但98%的租户可能是相同的)
• 我使用股票配置，但以下情况除外：

innodb_file_per_table
open_files_limit        = 65536
log_warnings            = 3
innodb_buffer_pool_size = 1G

FWIW，我愿意升级到一个更新的版本的MariaDB，但考虑到新的不确定性的风险，我宁愿作出一个明智的决定，而不是一个飞跃的信念。

谢谢你的建议！

Answer 1

观测：

• 版本: 10.1.44-MariaDB-0ubuntu0.18.04.1
• 8GB内存
• 正常运行时间= 4d 23:42:13
• 您不在Windows上运行。
• 运行64位版本
• 您似乎正在完全(或大部分)运行InnoDB。

更重要的问题：

除非服务器上有很多其他应用程序，否则innodb_buffer_pool_size应该增加到5G。

目前设定为2000年的"table_open_cache“面临着压力，这一压力将增加一倍。

将table_definition_cache从400增加到1000

innodb_log_file_size相当小。然而，让它更大对你的旧版本来说是件痛苦的事，所以我现在不推荐。

你在用ARIA吗？

很多复杂的查询。有关他们的调查，请参见此：http://mysql.rjweb.org/doc.php/mysql_analysis#slow_查询_和_慢速日志

你有一堆存储过程？他们中的一些人准备并执行了？他们中的一些人似乎没有关闭(DEALLOCATE)他们。这可能会导致过多的内存使用。(我不知道这是否会导致坠机，但你应该把它们清理干净。)并考虑subquery_cache=off (参见optimizer_switch)

详细信息和其他观察：

( Opened_tables ) = 2,098,690 / 430933 = 4.9 /sec --打开表的频率--增加table_open_cache (现为2000年)

( Opened_table_definitions ) = 1,397,838 / 430933 = 3.2 /sec --打开.frm文件的频率--增加table_definition_cache (现在是400)和/或table_open_cache (现在是2000年)。

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000的预定循环.“可以通过降低lru_scan_depth来固定

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 --容量:最大/普通--推荐2。最大值应该相当于你的I/O子系统所能处理的IOP。(如果驱动器类型未知，则2000/200可能是合理的一对。)

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 1,753,906 / 7374538 = 23.8% --必须访问磁盘的写入请求--检查innodb_buffer_pool_size (现在是1073741824)

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 737,214,976 / (430933 / 3600) / 2 / 48M = 0.0612 -比率-(见记录)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 430,933 / 60 * 48M / 737214976 = 490 -从5.6.8开始的InnoDB日志轮转之间的分钟时间，这可以动态更改；确保也要更改my.cnf。-- (轮流60分钟的建议有点武断)。调整innodb_log_file_size (现在是50331648)。(AWS中无法更改)

( innodb_flush_method ) = innodb_flush_method = -- InnoDB应该如何要求操作系统编写块。建议O_DIRECT或O_ALL_DIRECT (Percona)避免双重缓冲。(至少对于Unix.)关于O_ALL_DIRECT的警告见chrischandler

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( innodb_flush_neighbors ) = 1 --将块写入磁盘时的次要优化。- SSD驱动器使用0；HDD使用1。

( innodb_io_capacity ) = 200 --每秒可以在磁盘上执行I/O操作。100用于慢速驱动器；200用于旋转驱动器；1000-2000用于SSD；乘以RAID因子。

( Innodb_deadlocks ) = 2 / 430933 = 0.017 /HR --死锁--显示引擎INNODB状态；查看最新的陷入僵局的查询。

( sync_binlog ) = 0 --使用1来增加安全性，而I/O =1的代价可能会导致大量的“查询结束”；=0可能导致“无法定位的二进制日志”，并在崩溃时丢失事务，但速度更快。

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF --是否记录所有死锁。--如果你被死锁所困扰，把这个打开警告:如果您有很多死锁，这可能会写入很多磁盘。

( innodb_buffer_pool_populate ) = OFF = 0 -- NUMA控制

( log_warnings ) = log_warnings = 3

( (Com_show_create_table + Com_show_fields) / Questions ) = (622768 + 622768) / 21607187 = 5.8% --淘气框架--花费了大量的精力重新发现模式。-向第三方供应商投诉。

( local_infile ) = local_infile = ON - local_infile (现在开始)= ON是一个潜在的安全问题

( Qcache_lowmem_prunes/Qcache_inserts ) = 2,656,624/4159715 = 63.9% --去除率(由于内存不足需要修剪的频率)

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 4531192) / 4235 / 16384 = 0.176 -- query_alloc_block_size对公式--调整query_alloc_block_size (现在为16384)

( Created_tmp_disk_tables ) = 1,309,758 / 430933 = 3 /sec --作为复杂选择的一部分创建磁盘“临时”表的频率--增加tmp_table_size (现在是16777216)和max_heap_table_size (现在是16777216)。检查使用内存而不是MyISAM时临时表的规则。也许轻微的模式或查询更改可以避免MyISAM。更好的索引和查询的重新制定更有可能有所帮助。

( Created_tmp_disk_tables / Questions ) = 1,309,758 / 21607187 = 6.1% --需要磁盘tmp表的查询的Pct。-更好的索引/无瑕疵/等等。

( Created_tmp_disk_tables / Created_tmp_tables ) = 1,309,758 / 2706252 = 48.4% --溢出到磁盘的临时表的百分比--可能会增加tmp_table_size (现在是16777216)和max_heap_table_size (现在是16777216)；改进索引，避免blobs等等。

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (27132 + 306536 + 20780 + 1296) / 372094 = 0.956 --每次提交语句(假设所有InnoDB) -- Low:可能有助于在事务中将查询分组；高:长事务会使各种事情紧张。

( Select_scan ) = 3,511,698 / 430933 = 8.1 /sec --全表扫描--添加索引/优化查询(除非它们是小表)

( Select_scan / Com_select ) = 3,511,698 / 14426841 = 24.3% -%的选择执行全表扫描。(可能被存储的例程愚弄了)-添加索引/优化查询

( Com_stmt_prepare - Com_stmt_close ) = 12,457,121 - 12445287 = 11,834 --有多少准备好的陈述没有结束。-结清准备好的陈述

( binlog_format ) = binlog_format = STATEMENT --语句/行/混合。-行优先5.7 (10.3)

( slow_query_log ) = slow_query_log = OFF --是否记录慢速查询。(5.1.12)

( long_query_time ) = 10 --定义“慢速”查询的截止值(秒)。-建议2

( Subquery_cache_hit / ( Subquery_cache_hit + Subquery_cache_miss ) ) = 356,216 / ( 356216 + 10183006 ) = 3.4% --子查询缓存命中率

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11，5.7.1)默认情况下，复制的语句不会显示在慢速日志中；这会导致它们显示出来。--在缓慢的日志中看到可能干扰奴隶读取的写入操作是有帮助的。

异常小：

Com_show_status = 0.0084 /HR
Handler_write = 0.11 /sec

异常大：

Acl_database_grants = 4,656
Acl_users = 4,658
Aria_pagecache_reads = 2.9 /sec
Com_create_db = 0.9 /HR
Com_drop_db = 0.14 /HR
Com_drop_user = 0.14 /HR
Com_grant = 0.9 /HR
Com_release_savepoint = 0.067 /HR
Com_rollback_to_savepoint = 1.9 /HR
Com_savepoint = 0.067 /HR
Com_show_create_table = 1.4 /sec
Com_show_fields = 1.4 /sec
Com_stmt_close = 29 /sec
Com_stmt_prepare = 29 /sec
Com_unlock_tables = 0.06 /sec
Feature_fulltext = 0.067 /sec
Feature_subquery = 34 /sec
Feature_timezone = 0.054 /sec
Handler_savepoint = 0.067 /HR
Handler_savepoint_rollback = 1.9 /HR
Innodb_pages0_read = 223,449
Tc_log_page_size = 4,096

异常字符串：

innodb_default_row_format = compact
innodb_fast_shutdown = 1