草庐IT

mysql - 对具有 3 亿行和每秒 50-100 个查询的 MySQL innoDB 表的查询随机减慢

coder 2023-10-05 原文

我有一个 MySQL 数据库，其中有一个包含大约 3 亿行的 InnoDB 表。最多有 10 个连接的客户端每秒发送 50-60 个读取查询。几个月来一切都很顺利，直到最近，MySQL 开始停滞，同时使用大量 CPU(100%+。uptime 命令显示值如 15、12、15。)。需要 500 毫秒的查询现在需要几秒，从几十秒到几百秒。执行 SHOW PROCESSLIST 显示查询卡在 Sending data 状态。

我无法弄清楚原因，感谢任何帮助。

服务器

英特尔(R) 至强(R) CPU E5 @ 2.40GHz | 12 个中央处理器 | 32 GB 内存

我的.cnf

innodb_file_per_table = 1
tmp-table-size                 = 32M
max-heap-table-size            = 32M
innodb-log-files-in-group      = 2
innodb-flush-method            = O_DIRECT
innodb-log-file-size           = 512M
innodb-buffer-pool-size        = 26G
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb_file_format = barracuda

表(名称:记录)

+----------------+------------+------+-----+---------+----------------+
| Field          | Type       | Null | Key | Default | Extra          |
+----------------+------------+------+-----+---------+----------------+
| id             | bigint(20) | NO   | PRI | NULL    | auto_increment |
| identifier     | int(11)    | YES  | MUL | 0       |                |
| timestamp      | int(11)    | YES  | MUL | NULL    |                |
| rtype          | int(5)     | YES  | MUL | NULL    |                |
| x1             | int(11)    | YES  |     | NULL    |                |
| x2             | int(11)    | YES  |     | NULL    |                |
| net            | bigint(20) | YES  |     | NULL    |                |
| created_at     | datetime   | NO   |     | NULL    |                |
+----------------+------------+------+-----+---------+----------------+

索引并在 WHERE 查询中使用:

  • 时间戳(UNIX 时间戳为 INT)
  • 标识符
  • rtype(五种可能 值，1-5)

数据大小

Data_length  = ~18 GB
Index_length = ~16 GB

查询

SELECT identifier, timestamp, x1 AS a, x2 AS b, net
    FROM records
    WHERE
        identifier=1010
        AND timestamp >=1463111100
        AND timestamp <= 1463738400
        AND rtype=5
    ORDER BY timestamp;

(返回大约 900 行。有时不到一秒即可完成，有时需要 10-100 秒)

查询分析

select_type   = SIMPLE
type          = index_merge
possible_keys = indeXidentifier, indeXtimestamp, indeXrtype
key           = indeXidentifier, indeXrtype
key_len       = 4,5
rows          = 10641
Extra         = Using intersect(indeXidentifier,indeXrtype); Using where

最佳答案

我有两个建议:

1 。更改多列索引中的列顺序。 推荐的顺序是:标识符、rtype、时间戳。

索引唯一扫描比索引范围扫描快，最好先出现。

2。像这样更改您的查询:

select * from(
  SELECT identifier, timestamp, x1 AS a, x2 AS b, net
    FROM records
    WHERE
        identifier=1010
        AND timestamp >=1463111100
        AND timestamp <= 1463738400
        AND rtype=5
  ) t1 ORDER BY timestamp;

避免使用索引进行排序。

关于mysql - 对具有 3 亿行和每秒 50-100 个查询的 MySQL innoDB 表的查询随机减慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/37631881/

innoDBmysqlcodetimestampsectiondatabasequery-optimization

有关mysql - 对具有 3 亿行和每秒 50-100 个查询的 MySQL innoDB 表的查询随机减慢的更多相关文章

  1. ruby - 具有身份验证的私有(private) Ruby Gem 服务器 - 2

    我想安装一个带有一些身份验证的私有(private)Rubygem服务器。我希望能够使用公共(public)Ubuntu服务器托管内部gem。我读到了http://docs.rubygems.org/read/chapter/18.但是那个没有身份验证-如我所见。然后我读到了https://github.com/cwninja/geminabox.但是当我使用基本身份验证(他们在他们的Wiki中有)时，它会提示从我的服务器获取源。所以。如何制作带有身份验证的私有(private)Rubygem服务器？这是不可能的吗？谢谢。编辑:Geminabox问题。我尝试“捆绑”以安装新的gem..

  2. ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2

    我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.

  3. ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2

    我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr

  4. ruby-on-rails - Rails 3.1 中具有相同形式的多个模型？ - 2

    我正在使用Rails3.1并在一个论坛上工作。我有一个名为Topic的模型，每个模型都有许多Post。当用户创建新主题时，他们也应该创建第一个Post。但是，我不确定如何以相同的形式执行此操作。这是我的代码:classTopic:destroyaccepts_nested_attributes_for:postsvalidates_presence_of:titleendclassPost...但这似乎不起作用。有什么想法吗？谢谢! 最佳答案 @Pablo的回答似乎有你需要的一切。但更具体地说...首先改变你View中的这一行对此#

  5. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  6. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  7. ruby - 具有两个参数的 block - 2

    我从用户Hirolau那里找到了这段代码:defsum_to_n?(a,n)a.combination(2).find{|x,y|x+y==n}enda=[1,2,3,4,5]sum_to_n?(a,9)#=>[4,5]sum_to_n?(a,11)#=>nil我如何知道何时可以将两个参数发送到预定义方法(如find)？我不清楚，因为有时它不起作用。这是重新定义的东西吗？ 最佳答案 如果您查看Enumerable#find的文档，您会发现它只接受一个block参数。您可以将它发送两次的原因是因为Ruby可以方便地让您根据它的“并行赋

  8. ruby-on-rails - 在 RSpec 中，如何以任意顺序期望具有不同参数的多条消息？ - 2

    RSpec似乎按顺序匹配方法接收的消息。我不确定如何使以下代码工作:allow(a).toreceive(:f)expect(a).toreceive(:f).with(2)a.f(1)a.f(2)a.f(3)我问的原因是a.f的一些调用是由我的代码的上层控制的，所以我不能对这些方法调用添加期望。 最佳答案 RSpecspy是测试这种情况的一种方式。要监视一个方法，用allowstub，除了方法名称之外没有任何约束，调用该方法，然后expect确切的方法调用。例如:allow(a).toreceive(:f)a.f(2)a.f(1)

  9. ruby-on-rails - 具有同名的模块和类 - 2

    我有一个模块stat存在于目录结构中:lib/stat_creator/stat/在lib/stat_creator/stat.rb中，我在lib/stat_creator/stat/目录中有我需要的文件，以及:moduleStatCreatormoduleStatendend当我使用该模块时，我将这些类称为StatCreator::Stat::Foo.new现在我想要一个存在于应用程序中的根Stat类。我在app/models中制作了我的Stat类，并在routes.rb中进行了设置。但是，如果我转到Rails控制台并尝试在应用程序/模型中使用Stat类，例如:Stat.by_use

  10. ruby-on-rails - 在具有 ActiveRecord 条件的相关模型中按字段排序 - 2

    我正在尝试按Rails相关模型中的字段进行排序。我研究的所有解决方案都没有解决如果相关模型被另一个参数过滤？元素模型classItem相关模型:classPriority我正在使用where子句检索项目:@items=Item.where('company_id=?andapproved=?',@company.id,true).all我需要按相关表格中的“位置”列进行排序。问题在于，在优先级模型中，一个项目可能会被多家公司列出。因此，这些职位取决于他们拥有的company_id。当我显示项目时，它是针对一个公司的，按公司内的职位排序。完成此任务的正确方法是什么？感谢您的帮助。PS-我

随机推荐