如何为特定的数据库负载指定机器？

Question

我们的主要身份验证数据库用于100k+用户，并且正在增长，它是共享机器上的VM，我们希望将其提取到自己的隔离硬件中。这将使我们隔离资源，并有助于使优化更加清晰。然而，我不清楚什么是我们正在做的事情的理想机器，或者如何确定它。所以我向你寻求一些建议。

• 我们在Debian上使用MariaDB 10.1.26 (以前的mysql)，它有10 it的内存和分配给它的6个内核。CPU主要是空闲的。
• 在三个数据库中大约有320个表，没有一个表非常大，每个表都在100 is以下。
• 磁盘上的数据大小为24 on，ibdata1为2.2GB。InnoDB表中的数据: 1019.8M (表: 37)。
• 读写: 98% / 2%
• 总缓冲区: 1.9G全局+ 2.9M /线程(2500个最大线程)
• 达到最大内存使用量: 4.6G (占已安装RAM的47.52%)

这台机器被复制到第二台(然后从第二台到第三台)，所以中继日志占用了大量的空间。此服务器用于帐户身份验证，它通常进行选择，并对InnoDB行进行读取，因此它的数据非常少，但需要快速运行。平均而言，有230个连接线程，但在某些时间段，连接的尖峰导致我们到达max_connections，而且随着时间的推移，我们一直在增加连接，并且一直在调整各种参数，但是我们最大限度地利用了可用的内存。在有些情况下，有些查询花费的时间比应该的要长，这可能是网络问题，或者当我们达到最大连接时，在缓慢的查询日志中没有什么显示出来，因为它们是典型的查询，通常是相当快的，但有时比它们应该花费的时间更长。

• 我们没有慢速查询(在2800万次查询中)
• 极少数中止的连接
• 不需要临时表
• 没有索引就没有联接。

我们确实在磁盘上创建了临时表: 42% (磁盘/ 3K总数上的1K)，这有点高，我不知道为什么会这样。

• 我们的线程缓存命中率: 99% (创建了735个/ 913K连接)
• 表缓存命中率: 97% (253打开/ 259打开)。
• 开放文件限制: 0% (50/16K)
• 99%的表锁是立即获得的(在2900万台中)。

我们对所有表都使用InnoDB。一些InnoDB统计数据：

• InnoDB线程并发:0
• 激活每个表的InnoDB文件
• InnoDB缓冲池/数据大小: 1.2G/1019.8M
• InnoDB缓冲池实例:1
• InnoDB读取缓冲效率: 99.98% (240354196次/ 240398682次)
• InnoDB写入日志效率: 75.01% (302451次/ 403237次)
• InnoDB日志等待: 0.00% (0等待/ 100786写入)

我有以下最后一年的munin图表，我很乐意根据要求提供这些图表。它们中有太多不能单独提供:二进制/中继日志使用；不同的命令/秒；线程和连接计数；表缓存、打开的文件、打开的表；处理程序活动(写入、更新、删除等)；InnoDB缓冲池大小、页和修改后的页；InnoDB缓冲池活动(读取、创建和写入页)；InnoDB检查点时代；InnoDB历史记录列表长度；数字和大小InnoDB插入缓冲区；InnoDB IO (文件读/写、日志写入和文件同步)；InnoDB IO待定；InnoDB日志缓冲区大小、KB刷新和写入；InnoDB行操作；InnoDB信号量；InnoDB事务。我也有类型的排序；表锁的数量；临时磁盘表；以及线程数。

• tps = 3.342
• qps = 270.123

依据如下：

use information_schema;
select VARIABLE_VALUE into @num_queries from GLOBAL_STATUS where VARIABLE_NAME = 'QUESTIONS';
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME = 'UPTIME';
select VARIABLE_VALUE into @num_com from GLOBAL_STATUS where VARIABLE_NAME = 'COM_COMMIT';
select VARIABLE_VALUE into @num_roll from GLOBAL_STATUS where VARIABLE_NAME = 'COM_ROLLBACK';
select (@num_com + @num_roll) / @uptime as tps, @num_queries / @uptime as qps;

• 显示变量
• 显示全局变量
• 显示全局状态
• iostat -x输出
• ulimit -a：core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 39926 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 39926 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
• df -h：Filesystem Size Used Avail Use% Mounted on udev 4.9G 0 4.9G 0% /dev tmpfs 1001M 452K 1000M 1% /run /dev/vda1 45G 28G 15G 65% / tmpfs 4.9G 0 4.9G 0% /dev/shm tmpfs 5.0M 0 5.0M 0% /run/lock tmpfs 4.9G 0 4.9G 0% /sys/fs/cgroup tmpfs 1001M 0 1001M 0% /run/user/0
• 免费-h：total used free shared buff/cache available Mem: 9.8G 2.8G 4.1G 452K 2.8G 6.7G Swap: 0B 0B 0B

Answer 1

大多数表都是MyISAM？这可能会阻碍你的表演。相互矛盾的信息："320表“；”InnoDB表中的数据: 1019.8M (表:37)“；”我们对所有表使用InnoDB“

“最大达到内存使用量: 4.6G"，"10G内存”--您是否有其他应用程序运行在同一个VM中？如果不是的话，你可能没有充分利用内存.或者不是："InnoDB缓冲池/数据大小: 1.2G/1019.8M“

“专上第二名”-为什么？这样做是有充分理由的，但如果第二次死亡，就会使第三次死亡变得毫无用处。

“连接的尖峰使我们到达max_connections”--这可能是一个严重的问题。它是由MyISAM中的表锁引起的吗？很慢的询问？DDL操作(ALTER等)？有些情况下，在客户端(如webserver)控制连接比增加max_connections更好。我需要对正在发生的事情有一个更好的感觉。

在尖峰时期，Threads_running会上升吗？当STATUS超过20岁时，MySQL很可能会自食其力。延迟受到影响，吞吐量停滞，甚至下降。

“我们没有慢速查询”。我敢打赌，long_query_time仍然处于10秒的默认状态。改为1。

“磁盘上创建的临时表: 42%”--糟糕。但是，让我们通过慢速日志查找“最差”查询来接近它。

请遵循http://mysql.rjweb.org/doc.php/mysql_分析中的慢速日志建议

图表:大多数都会很无聊。但尖峰可能很有趣。特别是在你的“连接尖峰”的同时显示异常活动的图表排列。(列出度量标准可能就足够了，而不是实际提供图表。)

270 qps和3 tps -适度(一项调查显示，100个qps约为中位数，1900年为第90个百分位数。)

显然，由于您没有使用open_file_limit = 1024，所以显然没有几个表。

“您无法从性能问题中调整自己的方式”(而且大多数可调性都是可以的)。因此，我认为Slowlog (见上面的链接)是下一步的最佳选择。

全局状态和变量的

分析

观测：

• 版本:10.1.26-MariaDB-0+借方9u1
• 10 GB内存
• 正常运行时间= 1d 05:53:01
• 您不在Windows上运行。
• 运行64位版本
• 您似乎正在完全(或大部分)运行InnoDB。

更重要的问题：

这里的值表示您主要是在运行InnoDB。

key_buffer_size = 50M
innodb_buffer_pool_size = 4G

Innodb已删除的行与插入的行非常接近--您在做什么样的处理？

伪COMMITs？(Com_insert + Com_update + Com_delete + Com_replace) < Com_commit

更改慢速日志参数。

每秒20个管理命令--发生了什么事？

详细信息和其他观察：

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (512M - 1.2 * 1449 * 1024) / 10240M = 5.0% -内存浪费在key_buffer中的百分比。-降低key_buffer_size。

( Key_blocks_used * 1024 / key_buffer_size ) = 1,449 * 1024 / 512M = 0.28% --使用key_buffer的百分比。高水渍。-降低key_buffer_size以避免不必要的内存使用。

( innodb_buffer_pool_size / _ram ) = 1250M / 10240M = 12.2% --用于InnoDB buffer_pool的内存的百分比

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (512M / 0.20 + 1250M / 0.70) / 10240M = 42.4% --大多数可用的ram都可以用于缓存。-- http://mysql.rjweb.org/doc.php/memory

( innodb_buffer_pool_size ) = 1250M - InnoDB数据+索引缓存-128百万(旧的缺省值)非常小。

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 21,062 / 79999 = 26.3% --目前还没有使用的buffer_pool -- innodb_buffer_pool_size比必要的要大吗？

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 855,522 / 3597243 = 23.8% --必须访问磁盘的写入请求--检查innodb_buffer_pool_size

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 252,449,792 / (107581 / 3600) / 2 / 156M = 0.0258 -比率-(见记录)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 107,581 / 60 * 156M / 252449792 = 1,161 -从5.6.8开始的InnoDB日志轮转之间的分钟时间，这可以动态更改；确保也要更改my.cnf。-- (轮流60分钟的建议有点武断)。调整innodb_log_file_size。(AWS中无法更改)

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( Innodb_rows_deleted / Innodb_rows_inserted ) = 50,443 / 50359 = 1 --搅动--“不要排队，只管去做。”(如果MySQL被用作队列的话)。

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

( max_connections ) = 2,500 --最大连接数(线程)。影响各种拨款。--如果max_connections太高，各种内存设置都很高，那么可能会耗尽内存。

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

( query_alloc_block_size / _ram ) = 16,384 / 10240M = 0.00% --用于解析。RAM的Pct

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

( Created_tmp_disk_tables / Created_tmp_tables ) = 1,400 / 3347 = 41.8% --溢出到磁盘的临时表的百分比--可能会增加tmp_table_size和max_heap_table_size；改进索引；避免blobs等等。

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

( binlog_format ) = binlog_format = STATEMENT --语句/行/混合。行是首选的；它可能成为默认的。

( expire_logs_days ) = 0 --自动清除绑定日志(在这么多天之后)有多快--太大(或零)=消耗磁盘空间；太小=需要对网络/机器崩溃作出快速响应。(如果log_bin =OFF，则不相关)

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

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

( Connections ) = 922,332 / 107581 = 8.6 /sec --连接--增加wait_timeout；使用池？

( thread_cache_size ) = 256 --在使用线程池时，需要保留多少额外的进程(在使用线程池时不相关)(自定义为5.6.8；基于max_connections) 0对于非Windows来说效率很低；10可能很好。超过100个可能导致OOM。

异常小：

Handler_read_next / Handler_read_key = 0.557
Handler_read_rnd_deleted = 0
Handler_tmp_write = 6 /sec
Innodb_buffer_pool_pages_made_young = 0.57 /HR
Innodb_secondary_index_triggered_cluster_reads = 10.2MB
Rows_tmp_read = 0.8M
interactive_timeout = 300
max_heap_table_size = 1MB
max_tmp_tables * tmp_table_size / _ram = 0.31%
min(max_heap_table_size, tmp_table_size) = 1MB

异常大：

Acl_column_grants = 27
Acl_function_grants = 2
Acl_procedure_grants = 5
Acl_table_grants = 37
Com_admin_commands = 20 /sec
Com_drop_db = 0.1 /HR
Com_show_binlogs = 12 /HR
Com_show_slave_hosts = 0.033 /HR
Handler_discover = 1.7 /HR
Innodb_read_views_memory = 23,864
Max_used_connections = 691
Slave_connections = 2
Slaves_connected = 0.033 /HR
Threads_cached = 150
back_log = 550
host_cache_size = 728
innodb_defragment_fill_factor = 0.9
innodb_lru_scan_depth / innodb_io_capacity = 5.12
max_relay_log_size = 1024MB

异常字符串：

Slave_heartbeat_period = 1800
innodb_fast_shutdown = 1
myisam_stats_method = NULLS_UNEQUAL
opt_s__engine_condition_pushdown = off

Answer 2

关于每秒my.cnf 米舍尔德分段速率= RPS的建议

# 08/18/2018 Suggestions by mysqlservertuning com
log_error=scaup-error.log  # you REALLY do NOT want to be flying BLIND.
max_connections=1000  # from 2500 since ~700 max_used_connections
max_heap_table_size=2M  # from 1M for additional RAM based results
tmp_table_size=2M  # from 1M 2 be = max_heap_table_size & reduce created_tmp_disk_tables
read_rnd_buffer_size=192K  # from 256K to reduce handler_read_rnd_next RPS
aria_pagecache_buffer_size=10M  # from 128M since 99% unused
aria_pagecache_division_limit=50  # for WARM cache & reduce aria_pagecache_reads RPS
key_buffer_size=20M  # from ~512M since 99% unused
key_cache_division_limit=50  # from 100 for WARM cache & reduce key_reads count
expire_logs_days=10  # from 0 for some historical logging
innodb_buffer_pool_size=4G  # from ~1.2G for INNODB data, ndx in RAM & GROWTH
thread_cache_size=100  # from 256 per refman CAP at 100 to avoid OOM

您在这一天有165个回滚，如果可能的话，由于回滚的开销，需要进行研究和纠正。

请记住，每个工作日只有1次更改，监视器，如果更改看起来有害，请从my.cnf中删除并通知我。

此外，在my.cnf中只有1行与VARIABLE_NAME完全相同，以避免混淆。最后一个相同的VARIABLE_NAME将在my.cnf中使用。

更多建议，请查看我的个人资料，网络配置文件的联系方式，包括我的Skype ID。