☰

InnoDB 或 NDB 集群是否会提高性能

tag-icon • tag-icon mysql cluster innodb mysql-cluster
InnoDB 或 NDB 集群是否会提高性能

我们有相当大的 MySQL 数据库（总共约 35GB），最高可达 900qps。目前，性能不是大问题，但项目在不断增长，我宁愿提前考虑优化。

我最近了解了 InnoDB/NDB 集群解决方案，其中您可以添加至少 3 台服务器以实现数据库容错，但我想知道，由于有多台服务器参与处理流量，它是否会提高整体性能？

该项目基本上是一个公共广告平台，只有几个接收大部分流量的中央表 - 用户、广告等等。我无法指出一个弱点，有很多不同的东西可以通过基础设施进行优化（例如，用户之间有内部即时消息传递 - 我计划测试它与 MongoDB 的性能，我认为消息非常适合这种数据库架构）

以下是数据库服务器的查询统计信息：

查询统计

答案1

存在权衡。多种可写集群中的服务器仍然需要将所有写入操作发送到所有其他机器。 阅读受益于集群和/或从属服务器。写入仅从集群中略微受益。（分片是写入扩展的真正解决方案。）

甚至在单个主服务器上添加两个或多个从服务器也有助于写入一些。这是因为读现在分布在至少两个从属服务器上，从而减少了写入竞争。

快速成长时：

  • 如果磁盘空间超过一半，请小心。如果您需要ALTER一张大表，则可能需要足够的空间进行完整复制。而且您也不想耗尽磁盘空间。
  • 转储所花ALTERs的时间越来越长。
  • 观看写作（见上文）
  • 通常，即使在最优化的系统中，也会出现查询缓慢的情况。随着您的成长，它们会抬起丑陋的头颅。
  • 在某个时候（可能在 35G 之前，也可能在 35G 之后很久），RAM 的数量（想想“innodb_buffer_pool_size”和 NDB 的等价物）将成为一个严重的问题。通常可以通过避免表扫描和其他“优化”来推迟这一问题。
  • 注意长时间运行的交易。即使 1 秒也可能对您的 900 qps 产生影响。
  • 如果出现“尖峰”，则可能预示着更糟糕的事情即将发生。

如果您目前正在使用 InnoDB，您会发现要迁移到 NDB 需要进行一些结构性更改。事务模型完全不同（“最终一致性”）。根据应用程序的类型，这可能是一个问题，甚至是“障碍”。

仅在 MySQL/MariaDB 中，就有“InnoDB Cluster”（MySQL 8.0）和 Galera Cluster（PXC、MariaDB）。它们可能同样“优秀”。

以上所有操作至少需要 3 台机器，最好分布在至少 3 个数据中心。（是的，数据中心可能会瘫痪。）

如果您提供有关应用的更多详细信息，我或许可以讲得更多。即使SHOW CREATE TABLE是您最大的表格也可能提供很多见解。

如果你愿意，请按照说明进行操作这里；在改变拓扑或硬件之前，我可能能够帮助您进行更多的扩展。

更多的

由于几乎所有查询都是SELECTs，任何复制或集群拓扑都可以帮助您扩展。任意数量的从属服务器都可以提供任意数量的扩展。这些从属服务器可以挂在单个主服务器（或 InnoDB 集群）或 Galera 集群的 3 个节点上。NDB 的工作方式不同，但也可以任意扩展以进行读取。

因此，对你的问题，简单的回答是“是”。至于哪种解决方案，目前还不能确定哪种解决方案对你来说更好。也许最好的回答是“选择一个解决方案并运行它”。

答案2

对变量和全球状况的回顾：

观察结果：

* Version: 10.3.15-MariaDB
* 16 GB of RAM
* Uptime = 64d 10:48:05
* You are not running on Windows.
* Running 64-bit version
* You appear to be running entirely (or mostly) InnoDB.

更重要的问题：

  • 这是一个独立的 InnoDB 数据库吗？不是集群的，不是从属的，等等？

  • 查明您是否拥有 HDD 或 SSD；然后查看下面“详细信息”中的几项。

  • 您正在使用“查询缓存”，但其效率不高，可能会降低整个系统的速度。建议将其关闭或DEMAND一起使用，并仔细选择SELECTs要使用哪个SQL_CACHE。

  • 没有提交？您是否使用 autocommit=ON 并且曾经使用过 BEGIN？请描述典型的 DML 查询；我们可能会建议以不同的方式使用事务来减少 I/O。

  • 考虑从 更改REPLACE为INSERT ... ON DUPLICATE KEY UPDATE。

  • 您的问题是关于使用什么系统。请注意，网络带宽对您来说可能是一个大问题（Bytes_sent = 7666357 /sec）；因此解决查询的数量和详细程度可能很有用（并且与系统无关）。

  • 为什么打了这么多SHOW STATUS电话？

  • 大量 DELETE 的全表扫描。让我们来讨论一下这些扫描以及可能的改进方法，尤其是当它们在大型表上时。（http://mysql.rjweb.org/doc.php/deletebig）

详细信息和其他观察结果：

( Table_open_cache_misses ) = 14,420,381 / 5568485 = 2.6 /sec -- 可能需要增加 table_open_cache (现在为 2048)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096-- 页面清理器每秒的工作量。-- “InnoDB：page_cleaner：预期循环花费 1000 毫秒...”可通过降低 lru_scan_depth 来解决：考虑 1000/innodb_page_cleaners（现在为 4）。还请检查交换。

( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 6 = 0.667-- innodb_page_cleaners -- 建议将 innodb_page_cleaners（现在为 4）设置为 innodb_buffer_pool_instances（现在为 6）

( innodb_lru_scan_depth ) = 1,024 -- “InnoDB：page_cleaner：预期循环花费 1000ms...” 可以通过降低 lru_scan_depth 来修复

( innodb_doublewrite ) = innodb_doublewrite = OFF-- 额外的 I/O，但在崩溃时有额外的安全性。-- 对于 FusionIO、Galera、Slaves 和 ZFS 来说关闭是可以的。

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 182,569,362,432 / (5568485 / 3600) / 2 / 2048M = 0.0275-- 比率 -- （见会议纪要）

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 5,568,485 / 60 * 2048M / 182569362432 = 1,091-- InnoDB 日志轮换之间的分钟数从 5.6.8 开始，可以动态更改；请确保也更改 my.cnf。--（轮换之间 60 分钟的建议有些武断。）调整 innodb_log_file_size（现在为 2147483648）。（无法在 AWS 中更改。）

( innodb_flush_method ) = innodb_flush_method = fsync-- InnoDB 应如何要求操作系统写入块。建议使用 O_DIRECT 或 O_ALL_DIRECT (Percona) 来避免双重缓冲。（至少对于 Unix 而言。）有关 O_ALL_DIRECT 的注意事项，请参阅 chrischandler

( Innodb_row_lock_waits ) = 917,931 / 5568485 = 0.16 /sec-- 获取行锁的延迟频率。-- 可能是由可以优化的复杂查询引起的。

( innodb_flush_neighbors ) = 1-- 将块写入磁盘时进行小幅优化。-- 对于 SSD 驱动器使用 0；对于 HDD 使用 1。

( innodb_io_capacity ) = 200-- 磁盘每秒的 I/O 操作数。慢速驱动器为 100；旋转驱动器为 200；SSD 为 1000-2000；乘以 RAID 因子。

( sync_binlog ) = 0-- 使用 1 来增加安全性，但会花费一些 I/O 成本 =1 可能会导致大量的“查询结束”；=0 可能会导致“binlog 处于不可能的位置”并在崩溃时丢失事务，但速度更快。

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- 是否记录所有死锁。-- 如果您受到死锁的困扰，请启用此功能。注意：如果您有大量死锁，这可能会将大量数据写入磁盘。

( character_set_server ) = character_set_server = latin1 -- 通过将 character_set_server（现在是 latin1）设置为 utf8mb4 可能会有助于解决字符集问题。这是未来的默认设置。

( local_infile ) = local_infile = ON -- local_infile（现在为 ON）= ON 是一个潜在的安全问题

( query_cache_size ) = 128M-- QC 的大小 -- 太小 = 用处不大。太大 = 开销太大。建议为 0 或不超过 50M。

( Qcache_hits / Qcache_inserts ) = 1,259,699,944 / 2684144053 = 0.469-- 命中插入率 -- 越高越好 -- 考虑关闭查询缓存。

( Qcache_hits / (Qcache_hits + Com_select) ) = 1,259,699,944 / (1259699944 + 3986160638) = 24.0%-- 命中率 -- 使用 QC 的 SELECT -- 考虑关闭查询缓存。

( Qcache_inserts - Qcache_queries_in_cache ) = (2684144053 - 46843) / 5568485 = 482 /sec-- 失效次数/秒。

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (128M - 59914960) / 46843 / 16384 = 0.0968-- query_alloc_block_size 与公式 -- 调整 query_alloc_block_size（现在为 16384）

( Select_scan ) = 6,048,081 / 5568485 = 1.1 /sec-- 全表扫描 -- 添加索引/优化查询（除非它们是小表）

( Com_stmt_prepare - Com_stmt_close ) = 4,138,804,898 - 4129522738 = 9.28e+6-- 有多少准备好的语句尚未关闭。-- 关闭准备好的语句

( Com_replace ) = 28,182,079 / 5568485 = 5.1 /sec -- 考虑改为 INSERT ... ON DUPLICATE KEY UPDATE。

( binlog_format ) = binlog_format = MIXED-- 语句/行/混合。-- 5.7 (10.3) 首选行

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

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

( max_connect_errors ) = 999,999,999 = 1.0e+9—— 对黑客的一点小保护。 —— 可能不超过 200。

( Connections ) = 206,910,348 / 5568485 = 37 /sec-- 连接 -- 使用池？

异常小：

Com_show_tables = 0
Created_tmp_files = 0.12 /HR
Innodb_dblwr_pages_written = 0
Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache = 5,166
eq_range_index_dive_limit = 0
innodb_ft_min_token_size = 2
innodb_spin_wait_delay = 4
lock_wait_timeout = 86,400
query_cache_min_res_unit = 2,048

异常大：

Access_denied_errors = 93,135
Acl_table_grants = 10
Bytes_sent = 7666357 /sec
Com_create_trigger = 0.0026 /HR
Com_create_user = 0.0013 /HR
Com_replace_select = 0.086 /HR
Com_reset = 1 /HR
Com_show_open_tables = 0.02 /HR
Com_show_status = 0.18 /sec
Com_stmt_close = 741 /sec
Com_stmt_execute = 743 /sec
Com_stmt_prepare = 743 /sec
Delete_scan = 43 /HR
Executed_triggers = 1.5 /sec
Feature_fulltext = 0.62 /sec
Handler_read_last = 0.83 /sec
Handler_read_next = 357845 /sec
Handler_read_prev = 27369 /sec
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 16.2%
Innodb_row_lock_time_max = 61,943
Prepared_stmt_count = 3
Qcache_free_blocks = 24,238
Qcache_hits = 226 /sec
Qcache_inserts = 482 /sec
Qcache_total_blocks = 118,160
Select_range = 53 /sec
Sort_range = 47 /sec
Tc_log_page_size = 4,096
innodb_open_files = 10,000
max_relay_log_size = 1024MB
performance_schema_max_stage_classes = 160

异常字符串：

aria_recover_options = BACKUP,QUICK
ft_min_word_len = 2
innodb_fast_shutdown = 1
innodb_use_atomic_writes = ON
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
plugin_maturity = gamma

相关内容