程序员经验分享-hwhale

18M+行表的子查询和MySQL缓存

2024-04-15

由于这是我的第一篇文章，我似乎只能发布 1 个链接，因此我在底部列出了我所指的网站。简而言之，我的目标是让数据库更快地返回结果，我尝试包含尽可能多的相关信息，以帮助在帖子底部提出问题。

机器信息

8 processors
model name      : Intel(R) Xeon(R) CPU           E5440  @ 2.83GHz
cache size      : 6144 KB
cpu cores       : 4 

top - 17:11:48 up 35 days, 22:22, 10 users,  load average: 1.35, 4.89, 7.80
Tasks: 329 total,   1 running, 328 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni, 87.4%id, 12.5%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8173980k total,  5374348k used,  2799632k free,    30148k buffers
Swap: 16777208k total,  6385312k used, 10391896k free,  2615836k cached

然而，我们正在考虑将 mysql 安装移动到集群中具有 256 GB RAM 的另一台机器上

表信息

我的 MySQL 表看起来像

CREATE TABLE ClusterMatches 
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cluster_index INT, 
    matches LONGTEXT,
    tfidf FLOAT,
    INDEX(cluster_index)   
);

它有大约 18M 行，有 1M 个唯一 cluster_index 和 6K 个唯一匹配。我在 PHP 中生成的 sql 查询如下所示。

SQL查询

$sql_query="SELECT `matches`,sum(`tfidf`) FROM 
(SELECT * FROM Test2_ClusterMatches WHERE `cluster_index` in (".$clusters.")) 
AS result GROUP BY `matches` ORDER BY sum(`tfidf`) DESC LIMIT 0, 10;";

其中 $cluster 包含大约 3,000 个逗号分隔的 cluster_index 字符串。该查询使用大约 50,000 行，运行时间大约为 15 秒，当再次运行相同的查询时，运行时间大约为 1 秒。

Usage

  1. 可以假定表的内容是静态的。
  2. 并发用户数低
  3. 上面的查询是当前将在表上运行的唯一查询

Subquery

基于这篇文章 [stackoverflow: Cache/Re-Use a Subquery in MySQL][1] 以及查询时间的改进，我相信我的子查询可以被索引。

mysql> EXPLAIN EXTENDED SELECT `matches`,sum(`tfidf`) FROM 
(SELECT * FROM ClusterMatches WHERE `cluster_index` in (1,2,...,3000) 
AS result GROUP BY `matches` ORDER BY sum(`tfidf`) ASC LIMIT 0, 10;

+----+-------------+----------------------+-------+---------------+---------------+---------+------+-------+---------------------------------+
| id | select_type | table                | type  | possible_keys | key           | key_len | ref  | rows  | Extra                           |
+----+-------------+----------------------+-------+---------------+---------------+---------+------+-------+---------------------------------+
|  1 | PRIMARY     |  derived2            | ALL   | NULL          | NULL          | NULL    | NULL | 48528 | Using temporary; Using filesort | 
|  2 | DERIVED     | ClusterMatches       | range | cluster_index | cluster_index | 5       | NULL | 53689 | Using where                     | 
+----+-------------+----------------------+-------+---------------+---------------+---------+------+-------+---------------------------------+

根据额外信息中的这篇旧文章[优化 MySQL：查询和索引][2] - 这里看到的不好的是“使用临时”和“使用文件排序”

MySQL 配置信息

查询缓存可用，但由于大小当前设置为零而被有效关闭


mysqladmin variables;
+---------------------------------+----------------------+
| Variable_name                   | Value                |
+---------------------------------+----------------------+
| bdb_cache_size                  | 8384512              | 
| binlog_cache_size               | 32768                | 
| expire_logs_days                | 0                    |
| have_query_cache                | YES                  | 
| flush                           | OFF                  |
| flush_time                      | 0                    |
| innodb_additional_mem_pool_size | 1048576              |
| innodb_autoextend_increment     | 8                    |
| innodb_buffer_pool_awe_mem_mb   | 0                    |
| innodb_buffer_pool_size         | 8388608              |
| join_buffer_size                | 131072               |
| key_buffer_size                 | 8384512              |
| key_cache_age_threshold         | 300                  |
| key_cache_block_size            | 1024                 |
| key_cache_division_limit        | 100                  |
| max_binlog_cache_size           | 18446744073709547520 | 
| sort_buffer_size                | 2097144              |
| table_cache                     | 64                   | 
| thread_cache_size               | 0                    | 
| query_cache_limit               | 1048576              |
| query_cache_min_res_unit        | 4096                 |
| query_cache_size                | 0                    |
| query_cache_type                | ON                   |
| query_cache_wlock_invalidate    | OFF                  |
| read_rnd_buffer_size            | 262144               |
+---------------------------------+----------------------+

基于这篇关于[Mysql数据库性能转向][3]的文章，我相信我需要调整的值是

  1. 表缓存
  2. 密钥缓冲区
  3. 排序缓冲区
  4. 读缓冲区大小
  5. record_rnd_buffer（对于 GROUP BY 和 ORDER BY 术语）

确定需要改进的领域 - MySQL 查询调整

  1. 将匹配的数据类型更改为指向另一个表的 int 索引 [如果 MySQL 包含像 TEXT 或 BLOB 这样的可变长度字段，MySQL 确实会使用动态行格式，在这种情况下，这意味着需要在磁盘上完成排序。解决方案不是避开这些数据类型，而是将这些字段拆分到关联的表中。][4]
  2. 对新的匹配索引字段建立索引，以便 GROUP BYmatches根据以下语句，发生速度更快 [“您可能应该为您要选择、分组、排序或连接的任何字段创建索引。”][5]

Tools

为了调整性能，我计划使用

  1. [解释][6]参考[输出格式][7]
  2. [ab - Apache HTTP 服务器基准测试工具][8]
  3. [分析][9] 和 [日志数据][10]

未来数据库大小

目标是构建一个可以拥有 1M 个唯一 cluster_index 值、1M 个唯一匹配值、大约 3,000,000,000 个表行、查询响应时间约为 0.5 秒的系统（我们可以根据需要添加更多 ram 并将数据库分布在集群中）

问题

  1. 我认为我们希望将整个记录集保留在 RAM 中，以便查询不会接触磁盘，如果我们将整个数据库保留在 MySQL 缓存中，是否就不再需要 memcachedb 了？
  2. 尝试将整个数据库保留在 MySQL 缓存中是否是一个糟糕的策略，因为它没有被设计为持久性的？像 memcachedb 或 redis 这样的东西会是更好的方法吗？如果是的话为什么？
  3. 当查询完成时，查询创建的临时表“结果”是否会自动销毁？
  4. 我们是否应该从 Innodb 切换到 MyISAM [因为它适合读取大量数据，而 InnoDB 适合写入大量数据][11]？
  5. 我的缓存在我的[查询缓存配置][12]中似乎没有打开，因为它为零，为什么查询当前在我第二次运行它时发生得更快？
  6. 我可以重组我的查询以消除“使用临时”和“使用文件排序”的发生，我应该使用联接而不是子查询吗？
  7. 如何查看MySQL[Data Cache][13]的大小？
  8. 您建议将 table_cache、key_buffer、sort_buffer、read_buffer_size、record_rnd_buffer 值的大小作为起点？

Links

  • 1：stackoverflow.com/questions/658937/cache-re-use-a-subquery-in-mysql
  • 2：databasejournal.com/features/mysql/article.php/10897_1382791_4/Optimizing-MySQL-Queries-and-Indexes.htm
  • 3：debianhelp.co.uk/mysqlperformance.htm
  • 4：20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/
  • 5：20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/
  • 6：dev.mysql.com/doc/refman/5.0/en/explain.html
  • 7：dev.mysql.com/doc/refman/5.0/en/explain-output.html
  • 8：httpd.apache.org/docs/2.2/programs/ab.html
  • 9: mtop.sourceforge.net/
  • 10：dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
  • 11：20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/
  • 12：dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html
  • 13：dev.mysql.com/tech-resources/articles/mysql-query-cache.html

换桌子

根据这篇文章中的建议如何为 order by 和 group by 查询选择索引 http://mysqldba.blogspot.com/2008/06/how-to-pick-indexes-for-order-by-and.html表格现在看起来像

CREATE TABLE ClusterMatches 
(
    cluster_index INT UNSIGNED, 
    match_index INT UNSIGNED,
    id INT NOT NULL AUTO_INCREMENT,
    tfidf FLOAT,
    PRIMARY KEY (match_index,cluster_index,id,tfidf)
);
CREATE TABLE MatchLookup 
(
    match_index INT UNSIGNED NOT NULL PRIMARY KEY,
    image_match TINYTEXT
);
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

caching

mysql

subquery

memcachedb

18M+行表的子查询和MySQL缓存 的相关文章

随机推荐

  • 使用 Codable 和 CodableFirebase 快速解析 Firebase 值时获取 nil

    我正在使用 Firebase 实时数据库 在 swift 和外部库 CodableFirebase 中使用可编码方法 我已经创建了模型结构 但是当我尝试使用模型结构解析值 因为我正在获取所有值 时 它给了我零 我的数据库有一些键 我可能无法

  • Web API 属性路由和验证 - 可能吗？

    我正在尝试将 Web API 中基于属性的路由与模型验证结合起来 我根本无法让它按照我的预期工作 class MyRequestModel DefaultValue DefaultView public string viewName ge

  • 未捕获（承诺中）：错误：无法读取未定义的属性

    组件使用参数将用户从服务中取出 Component selector users providers UserService template p user id p export class UserPageComponent imple

  • 为什么Xcode自动创建带下划线的变量？

    为什么在最新版本的 Xcode dp 4 中声明的变量是retain nonatomic是否在变量名前使用下划线 这会创建某种类型安全吗 例如 我创建一个属性 property retain nonatomic IBOutlet UILab

  • 如何在本地进行 Facebook Messenger Bot 开发？

    设置 webhooks 时 它会说Secure URL是必须的 我在用ngrok https ngrok com 隧道本地主机地址 这里有一个例子 https github com wit ai node wit https github

  • 如果 Objective C 是 C 的严格超集，那么为什么它不能编译呢？

    考虑以下源文件 它是 至少应该是 有效的 C void id 我正在尝试编译它gcc c test m 但我收到以下错误 test m 1 error id redeclared as different kind of symbol

  • 如何从R中的不同函数将数据依次写入csv文件中？

    我有一个 CSV 文件 我想根据我执行的功能将数据写入另一个 CSV 文件 例如 Data csv Identity State City BusinessName BusinessNeed 12 California Los Angele

  • 通过 angular-cli 生成的角度组件的自定义项目级模板

    如何将自定义角度组件原理图添加到现有项目 我想要例如my page将复制现有的组件模板 node modules schematics angular component 文件 但带有编辑过的模板 我希望它可以通过以下方式实现angular

  • UnionBy Linq 实现

    我需要 Union 的实现来比较对象的属性 而不是对象本身 我想出了以下几点 public static IEnumerable

  • 实时清除数据容器的内容

    My problem is best explained by the architecture below 基本上 我必须清除NSMutableData实时对象 或任何其他对象 即我无法阻止其包含线程 有没有办法 API 来做到这一点 u

  • 32feet.net 如何在 C# 中异步发现附近的蓝牙设备

    我正在尝试使用32英尺 NET http 32feet codeplex comC 应用程序中的蓝牙库用于检测附近的设备 我的小应用程序的目的是通过人们手机的蓝牙功能让电脑知道谁在房间里 执行此类操作的最佳方法是让我想要 跟踪 的设备连接一

  • 将网络摄像头从浏览器流式传输到 RTMP 服务器

    我正在尝试将一些内容从浏览器的网络摄像头实现流式传输到随机 RTMP 服务器 我让它工作到每 2 秒将 WEBM 我相信是 VP8 编码的电影片段发送到我的服务器的部分 但棘手的部分是从该部分将其发送到 RTMP 服务器 对 FFMPEG

  • 如何简单地解析没有指定年份的日期？

    我有一个工具 它似乎可以给我日期 但没有指定我需要转换的年份 并且我正在使用 Java 来完成任务 实际上是 Groovy 但在本例中足够接近 示例日期是 13 Dec 12 00 00 它应该指的是 12 13 2011 因为年份未指定

  • HTML 俄语

    我必须设计一个俄语版本的网站 我从翻译那里得到文本 我把它复制到Dreamweaver的代码中 但它不起作用 我有平常的头 我应该怎么办 您应该将文件的编码更改为 UTF 8 您可以执行此过程 当您Save As文件在记事本中或者您可以使用

  • OpenSSL 上的 EVP_DecryptFinal_ex 错误

    我正在使用 OpenSSL EVP 例程使用 AES 128 cbc 模式进行解密 我使用 NIST 站点指定的测试向量来测试我的程序 该程序似乎在 EVP DecryptFinal ex 例程处失败 谁能告诉我有什么问题吗 另外 我如何在

  • 处理 Swift 2 中异步闭包错误的最佳方法？

    我使用了大量的异步网络请求 顺便说一句 iOS 中的任何网络请求都需要异步 并且我正在寻找更好的方法来处理来自 Apple 的错误dataTaskWithRequest哪个不支持throws 我有这样的代码 func sendRequest

  • 创建名称为“securityConfig”的 bean 时出错：自动装配依赖项注入失败

    我正在尝试结合 Java config 和 xml config 进行 Spring 安全身份验证 但我收到一个错误 创建名称为 securityConfig 的 bean 时出错 自动装配依赖项注入失败 我的代码似乎有什么问题 一直在谷歌

  • 无法在 Ubuntu 上的 PyCharm 上启动终端

    我想运行一段代码 为此 我在我的 Ubuntu 机器上安装了 PyCharm 现在 当我打开 PyCharm 应用程序并尝试打开终端时 它会抛出错误并且不会打开终端 java io IOException Exec tty错误 未知的pyc

  • 管理面板中的 django choicefield 过滤器

    默认情况下 django 管理员list filter提供型号选择中所有可用的过滤器 但除了那些我还想要一个过滤器 我们可以说它是 无 过滤器 class Mymodel char choice field choices 1 txt1 2

  • 18M+行表的子查询和MySQL缓存

    由于这是我的第一篇文章 我似乎只能发布 1 个链接 因此我在底部列出了我所指的网站 简而言之 我的目标是让数据库更快地返回结果 我尝试包含尽可能多的相关信息 以帮助在帖子底部提出问题 机器信息 8 processors model name

热门标签