MySQL可以合理地对数十亿行执行查询吗?


255

我计划将扫描从质谱仪存储在MySQL数据库中,并想知道存储和分析这些数据是否可以远程使用。我知道根据环境的不同,性能会有很大差异,但我会看到粗略的数量级:查询需要5天还是5毫秒?

输入格式

每个输入文件包含一次光谱仪;每次运行都包含一组扫描,每次扫描都有一个有序的数据点阵列。存在元数据的升级,但是大多数文件由数组32或64位整数或浮点数组成。

主机系统

|----------------+-------------------------------|
| OS       | Windows 2008 64-bit      |
| MySQL version | 5.5.24 (x86_64)        |
| CPU      | 2x Xeon E5420 (8 cores total) |
| RAM      | 8GB              |
| SSD filesystem | 500 GiB            |
| HDD RAID    | 12 TiB            |
|----------------+-------------------------------|

使用可忽略的processortime在服务器上运行一些其他服务。

文件统计

|------------------+--------------|
| number of files | ~16,000   |
| total size    | 1.3 TiB   |
| min size     | 0 bytes   |
| max size     | 12 GiB    |
| mean       | 800 MiB   |
| median      | 500 MiB   |
| total datapoints | ~200 billion |
|------------------+--------------|

数据点的总数是非常粗略的估计。

提议的架构

我正在计划做“正确”的事情(即像疯了一样正常化数据)并且会有一个runs表,一个spectra表,外键为runs ,一个datapoints表,外键为spectra

200亿个数据点问题

我将分析多个光谱,甚至可能是多次出现,导致查询可能触及数百万行。假设我正确地索引(这是另一个问题的主题)并且我没有尝试在网络上调试数百个MiB,那么MySQL处理这个问题是否合理呢?

附加信息

扫描数据将来自基于XML的mzML格式的文件。此格式的内容位于存储数据的元素中。每个扫描产生> = 2个元件这两者合在一起,形成如下形式的二维(或多个)阵列[[123.456, 234.567, ...], ...]

这些数据是一次性写入,因此更新性能和事务安全性无关紧要。

我对数据库架构的天真计划是:

runs

| column name | type    |
|-------------+-------------|
| id     | PRIMARY KEY |
| start_time | TIMESTAMP  |
| name    | VARCHAR   |
|-------------+-------------|

spectra

| column name  | type    |
|----------------+-------------|
| id       | PRIMARY KEY |
| name      | VARCHAR   |
| index     | INT     |
| spectrum_type | INT     |
| representation | INT     |
| run_id     | FOREIGN KEY |
|----------------+-------------|

datapoints

| column name | type    |
|-------------+-------------|
| id     | PRIMARY KEY |
| spectrum_id | FOREIGN KEY |
| mz     | DOUBLE   |
| num_counts | DOUBLE   |
| index    | INT     |
|-------------+-------------|

这合理吗?


所以,正如你可能已经推断的那样,我是程序员,而不是实验室的生物学家,所以我对科学的了解程度几乎与实际的科学家不同。

这是我将要处理的数据类型的单一频谱(扫描)图:

Viewer screenshot

该软件的目标是确定峰值的位置和重要程度。我们现在使用专有的软件包来解决这个问题,但是我们想要编写我们自己的分析程序(在R中),因此我们知道在工作表上发生了什么。正如您所看到的,绝大多数数据都不太有用,但我们不想丢弃那些算法遗漏的潜在有用数据。一旦我们得到了一个我们满意的可能峰值列表,管道的其余部分将使用该峰值列表而不是数据点的原始列表。我认为将rawdatapoints存储为大blob就足够了,因此如果需要可以重新分析它们,但只将峰值保留为不同的数据库条目。在这种情况下,每个光谱只会有十几个峰值,因此疯狂缩放的东西不应该是一个问题。

+4

[关于利用MySQL进行扩展的Facebook](http://gigaom.com/cloud/facebook-shares-some-secrets-on-making-mysql-scale/) 03 7月. 122012-07-03 18:35:39

+6

由于这是原始A/D轮询质谱仪数据,因此将其存储在数据库中似乎非常愚蠢。我将获取原始数据,转储,处理它,并将处理后的结果存储在数据库中。结果将是(a)每行存储一个波形的波形,(b)与校准曲线等那些波形相关的其他数据,以及(c)数据库中的结果行。这样可以减少数十亿行的膨胀。当您想要重新运行初始分析时,您将有效地编辑一些参数,运行巨大的计算操作,并将新结果存储在数据库中。 03 7月. 122012-07-03 21:58:45

101

我不是很熟悉您的需求,但是将数据点存储在数据库中可能有点过分。这听起来几乎就像通过将每个像素作为单独的记录存储在关系数据库中来存储图像库的方法。

作为一般规则,在大多数情况下将二进制数据存储在数据库中是错误的。通常有更好的方法来解决问题。虽然在关系数据库中存储二进制数据本身并不是错误的,但通常缺点超过收益。顾名思义,关系数据库最适合存储关系数据。二进制数据不是关系数据。它会增加数据库的大小(通常很大),可能会损害性能,并可能导致有关维护数十亿条记录的MySQL实例的问题。好消息是有些数据库特别适合存储二进制数据。其中一个,虽然并不总是很明显,但是你的文件系统!只需为二进制文件提供目录和文件命名结构,将它们与任何其他可通过查询产生价值的数据一起存储在MySQL数据库中。

另一种方法是使用基于文档的存储系统来处理数据点(也许是光谱)数据,并使用MySQL进行运行(或者将运行放入与其他数据库相同的数据库中)。

+5

为什么在数据库中存储二进制数据被认为是错误的?(部分原因是因为我很好奇,但也因为我能想到它的用例。) 03 7月. 122012-07-03 16:44:05

+13

如果二进制数据没有单独的值,则不应将其存储为唯一行。图像上的像素500x325无关紧要。 03 7月. 122012-07-03 17:39:50

+1

这是一个非常好的观点。我们应该保留原始文件,以防我们以后需要再次取出内容,但是存储图像的类比很好。我们不需要访问每个数据点(除非我们重做峰值提取),因此只需存储提取的统计信息就会好得多。 04 7月. 122012-07-04 16:16:25


103

我曾经使用过非常大的(Terabyte +)MySQL数据库。我们拥有的最大的表格实际上超过十亿行。这是使用MySQL 5.0,所以事情可能会有所改善。

有效。MySQL在大多数时间正确处理数据。但这非常笨拙。(如果您想要使用数TB的六西格玛级可用性,请不要使用MySQL。我们是一家没有DBA且资金有限的初创公司。)

仅备份和存储数据是一项挑战。如果需要,恢复表需要数天。

我们在10-100万行范围内有许多表。表格的任何重要联接都太耗费时间并且需要永远。因此,我们编写了存储过程来“遍历”表并处理对'id'范围的连接。通过这种方式,我们一次处理10-100,000行数据(加入id的1-100,000然后100,001-200,000等)。这比加入整个表格要快得多。

在非基于主键的非常大的表上使用索引也要困难得多。Mysql 5.0将索引存储为两部分 - 它将索引(主索引除外)存储为主键值的索引。因此,索引查找分为两部分:首先MySQL转到索引并从中获取需要查找的主键值,然后在主键索引上进行第二次查找以查找这些值的位置。

这样做的结果是,对于非常大的表(1-200万加行),对表的索引更具限制性。您需要更少,更简单的索引。即使是不直接在索引上的简单select语句也可能永远不会回来。哪些条款必须达到索引或忘记它。

但话说回来,事情确实奏效了。我们能够将MySQL与这些非常大的表一起使用,并进行计算并获得正确的答案。

试图对2000亿行数据进行分析需要非常高端的硬件和大量的手持和耐心。只是以可以从中恢复的格式备份数据将是一项重要工作。

我同意srini.venigalla's answer将数据标准化为疯狂可能不是一个好主意。使用那么多数据在多个表上进行连接将使您面临文件排序的风险,这可能意味着您的某些查询将永远不会再回来。使用简单的整数键进行无符号化将为您提供更好的成功机会。

我们所拥有的一切都是InnoDB。关于MyISAM与InnoDB:主要的是不要混合两者。由于MySQL缓存密钥和其他数据的方式,您无法真正优化服务器。如果可以,请为服务器中的所有表选择一个或另一个。MyISAM可能有助于解决一些速度问题,但它可能无助于需要完成的整体DBA工作 - 这可能是一个杀手。

  0

自5.0起,MySQL在索引(...)部门的改进很多。看看它现在的表现会很有趣。 07 10月. 172017-10-07 08:01:19


69

像疯了一样规范化数据

在这种情况下,将数据标准化为疯狂可能不是正确的策略。通过将数据存储在标准化表单中以及非常适合您的应用程序的物化视图的形式,保持选项的开放性。此类应用程序的关键是不编写特殊查询。查询建模比数据建模更重要。从目标查询开始,努力实现最佳数据模型。

Is this reasonable?

我还会创建一个包含所有数据的附加平面表。

run_id | spectrum_id | data_id |  |

我将使用此表作为所有查询的主要来源。原因是避免必须进行任何连接。没有索引的连接将使您的系统非常不可用,并且在如此庞大的文件上拥有索引将同样可怕。

策略是,首先在上面的表中查询,将结果转储到临时表中,并将临时表与Run和Spectrum的查找表连接,并获取所需的数据。


你有没有分析你的写需求与阅读需求?放弃SQL并转向非标准数据存储机制将非常诱人。在我看来,它应该是最后的手段。

要加快写入速度,您可能需要尝试使用Handler Socket方法。Percona,如果我记得的话,在他们的安装包中打包Handler Socket。(与Percona无关!)

http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html


32

简短的答案是肯定的 - 随着行数的增加,精确的模式,您选择的数据类型和操作的重要性会增加。

规范化数据的程度取决于您计划对存储数据执行的操作。您的“数据点”表特别有问题 - 您是否计划将任何给定光谱的第n个点与任何其他光谱的第m个进行比较?如果没有,单独存储可能是一个错误。如果您的数据点不是独立但只在相关光谱的上下文中有意义,那么您不需要PRIMARY KEY - 光谱的外键和'nth'列(您的'索引'列?)就足够了。

定义必须执行的频谱间和频谱内操作,然后找出最便宜的方法来完成它们。如果所有需要都是相等的,那么它们可能会被非规范化 - 可能还有一些预先计算的统计元数据可以帮助您进行操作。如果绝对需要对单个数据点进行in-SQL访问,请确保将每行的大小减小到最小字段数和可能的最小数据类型。

我曾经亲自管理过的最大的MySQL是大约1亿行。在此大小,您希望通过乘以每行的固定大小(想想指针算术)来获得keep your rows and thus your fields fixed-size -- this allows MySQL to efficiently calculate the position of any row in the table - 尽管具体细节取决于您计划使用的存储引擎。使用MyISAM,如果你可以逃脱它,它在速度上弥补的可靠性缺乏,在你的情况下它应该足够了。用CHAR(n)替换VARCHAR等可变大小的字段,并在读取查询中使用RTRIM()。

一旦您的表行是固定宽度,您可以通过仔细评估MySQL的integer datatypes (其中一些是非标准的)来减少字节数。通过将4字节的INT转换为3字节的MEDIUMINT,您可以节省每1字节的成本,每百万行节省大约1MB - 这意味着更少的磁盘I/O和更有效的缓存。使用smallest possible datatypes that you can get away with 。仔细评估浮点类型,看看是否可以用4字节FLOAT或甚至<8字节fixed-point NUMERICs 8字节fixed-point NUMERICs 。运行测试以确保您选择的任何内容都不会在以后咬你。

根据数据集的预期属性和所需的操作,可以进一步节省您的值的更不寻常的编码(预期的模式/重复,可以编码为一组值的索引,原始数据可能只有意义地有助于元数据并被丢弃等) - 尽管异乎寻常的,不直观的,破坏性的优化只有在尝试了其他每个选项时都是值得的。

最重要的是,无论你最终做什么,都不要以为你已经选择了完美的架构,然后盲目地开始倾倒数以百万计的记录。好的设计需要时间来发展。创建一个大但可管理(例如,1-5%)的测试数据集,并验证模式的正确性和性能。了解不同的操作如何执行(http://dev.mysql.com/doc/refman/5.0/en/using-explain.html)并确保您平衡架构以支持最常用的操作。

我说简短吗?哎呦。无论如何,祝你好运!


12

嗯......我看到你选择这种数据结构的两个原因:

  • 你真的需要做任何数据点与任何数据点查询
  • 您打算在SQL中执行所有逻辑

现在,我建议您仔细研究一下您的要求,并确认至少有一个上述假设是正确的。如果两者都不是真的,那么你只是让事情变慢。对于这种数据集,我建议首先找出如何访问数据,需要什么样的准确度等等 - 然后围绕这些设计数据库。

PS:请记住,每个数据点至少需要36 + 5个字节,因此200B数据点应至少为您提供8.2 TB所需空间。

PPS:你不需要id列在datapoints表,一个PRIMARY KEY (spectrum_id, index)可能就足够了(只是提防index可能是保留字)


6

将要存储的数据是什么类型的机器?它是共享存储设备吗?

决定你的查询时间的最终因素将是你的硬盘。数据库及其查询优化器旨在尽可能减少磁盘I/O的数量。鉴于您只有3个表,这将非常可靠地完成。

硬盘的读/写速度将比内存速度慢200-300倍。寻找具有非常快的延迟和快速读写速度的硬盘。如果所有这些数据都在一个2 TB驱动器上,那么您可能需要等待很长时间才能完成查询。硬驱动器延迟大约为10-15毫秒,而内存延迟小于10纳秒。硬驱动器延迟可能比内存延迟慢1000-2000倍。在整个系统中,机械臂在硬盘上的移动是最慢的。

你有多少RAM?16 GB?让我们说让你拥有32条记录。你有16000个文件。如果您要对所有数据点进行线性扫描,您可以轻松地在寻道时间内完成5-10秒。然后考虑传输速率50mb/s?大约7个小时。此外,任何临时保存的数据都必须存储在硬盘中,以便为读取的新数据腾出空间。

如果您正在使用其他用户正在积极使用的共享存储设备......您最好的选择是在晚上运行所有内容。

减少嵌套查询的数量也有帮助。嵌套查询会产生临时表,这会使您的硬盘更加瘫痪。我希望你的硬盘上有足够的可用空间。

查询优化一次只能查看1个查询。因此无法优化嵌套的select语句。但是,如果您知道特定的嵌套查询将导致返回一个小数据集,请保留它。查询优化使用直方图和粗略假设,如果您对数据和查询有所了解,那么请继续执行。

您对数据存储在磁盘上的方式了解得越多,您编写查询的速度就越快。如果所有内容都按顺序存储在主键上,则对从嵌套查询返回的主要键进行排序可能是有益的。此外,如果您可以预先减少需要分析的数据集集,请执行此操作。根据您的系统,您可以查看每个文件大约1秒的数据传输时间。

如果您要修改Name值(varchars),我会将其更改为具有最大大小的数据类型,它将防止碎片,并且折衷只是几个字节的内存。也许是最大100的NVARCHAR。

至于关于非规范化表的评论。我认为最好只将数据点存储在较大的组中(可能是光谱),然后用python或与数据库交互的语言进行数据分析。除非你的SQL向导。

+3

您强调硬盘驱动器与内存延迟之间存在巨大差异,但是您的数字会下降1000倍。如果硬盘驱动器的延迟大约为10毫秒,内存为10ns,则延迟时间不会相差1,000倍,而是因为百万! 03 7月. 122012-07-03 22:28:39


11

编辑:

不要在MYSQL中使用存储在单个磁盘上的数据。只需从单一介质读取大量数据就需要数小时。你需要SCALE OUT,而不是UP。

如果要进行有效的数据分析,则需要对数据进行非规范化。您不是在这里设计在线系统。你想要紧缩数字,相应地设计。

原来的答案在下面。


答案将根据您的疑问而有所不同,MySQL可能不是这项工作的最佳工具。您可能希望查看可以扩展“out”而不是“up”的解决方案。如果你愿意付出一些努力,也许你应该看看Map Reduce解决方案,比如Hadoop。

如果您想进行更多即席查询Google's BigQuery解决方案可能非常适合您。Google I/O 2012的相关演示文稿: Crunching Big Data with BigQuery

因此,解决方案将取决于这是否是一次性的事情,如果你想合理地支持即席查询。


22

似乎将数据点数据从XML中分离出来的唯一原因(与运行的时间和类型等元数据相反)和数据库形式,就是在分析数组中的光谱时 - 也许找到全部以某种签名运行。现在只有你知道你的问题域,但这可能类似于存储以96kHz采样的音乐,每行1个样本。我不确定尺寸是否比使用数据更重要。查询数据相当于在披头士乐队的所有歌曲中询问歌曲2分钟的相对幅度。如果您知道可能执行的分析类型,则很可能在信号上执行这些分析并将其存储在有关运行的元数据中可能更有意义。

我也不确定你的源数据是否稀疏。数据库中的频谱完全可能只包含非零条目,而原始XML确实包含零条目,因此您的总行数可能远小于源数据中的行数。

所以,像许多问题一样,在询问MySQL处理模型之前,退回并查看模型以及如何使用它可能比担心性能更合适。


在查看了您的问题更新后,我认为将二进制数据存储为BLOB或仅指向文件的指针的模型就足够了,并且可以修改模型以存储有关数据首次发现时已识别的重要峰值的数据读。


16

我运行一个包含大约50个数据库服务器的网络分析服务,每个服务器包含超过1亿行的许多表,还有几个往往超过10亿行,有时高达20亿(在每台服务器上)。

这里的表现很好。这是非常规范化的数据。但是 - 我对阅读这个问题的主要担心是,你将超过这些表的42亿行标记(可能不是“运行”但可能是另外两个),这意味着你需要使用BIGINT而不是INT来实现主键/外键。

与INT相比,索引列中的BIGINT字段的MySQL性能非常可怕 。我错误地做了一次这样的表,我认为可能会超过这个尺寸的表,一旦它达到几亿行,性能就会非常糟糕。我没有原始数字但是当我说不好时,我的意思是Windows ME不好。

此列是主键。我们将它转​​换为INT和presto magico,性能再次良好。

我们当时的所有服务器都在Debian 5和MySQL 5.0上。我们已经升级到Debian 6和Percona MySQL 5.5,所以事情可能会有所改善。但根据我在这里的经验,不,我认为它不会很好。


5

对我来说,这听起来像是一个使用场景,你想要像“关系列商店” as described here

我可能误解了设计,但如果您主要处理大量数组,则将它们存储在典型的面向行的表中意味着每个元素都与切片类似。如果您有兴趣以典型的方式查看切片,这是有道理的,但如果您一次真正查看整个列,则可能效率较低。

检索数组时,不仅可能不需要将其与规范化产生的另一个表连接,而且可以将系列检索为数组而不是散列。

我真的可能误解了这个问题,我甚至没有提出具体的解决方案。

Here's another talk可能是相关的,即使它实际上不是当前或可部署的解决方案。


4

我建议你尝试分区你的桌子。我们在一张表(股票市场数据)中有超过80万行,并且可以快速访问它。

根据您打算如何搜索数据,您应该设计分区。在我们的情况下,日期工作很好,因为我们查询具体的日期。

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

http://www.slideshare.net/datacharmer/mysql-partitions-tutorial


7

没有人提到,因此我的建议。看一眼大规模分片MySQL解决方案。例如,看到这个备受推崇tumblr presentation

这个概念是:

  • 而不是一个额外的大型数据库
  • 使用许多小部分保存原始数据的一部分

因此,您可以水平缩放,而不是尝试提高垂直性能。谷歌的BigTableGFS还使用便宜的水平可伸缩节点来存储和查询数PB的数据。

但是,如果需要在不同的分片上运行查询,则会出现问题。


如果有人感兴趣,我前一段时间做了一个hello-world分片应用程序。这是讨论here 在博客文章中。我使用了RavenDB和C#,但细节无关紧要,而且想法是一样的。


16

无论它是否有效,您总是会遇到与单个存储介质相同的问题:磁盘速度很慢。100 MB/s(非常适合旋转介质)只需3个小时即可读取 1TB表;假设没有分析或寻求或其他延迟减慢您的速度。

这就是几乎每个“大数据”安装都使用某种分布式数据存储的原因。你可以花费8倍的钱建立一台超级惊人的计算机来运行你的数据库,但是如果你有很多可以并行扫描的数据,你几乎总是能够在8台更便宜的计算机上分配负载。

hadoop这样的项目专门为这样的目的而构建。您构建了一大堆廉价计算机的集群,将数据分布在所有计算机上,并且并行查询它们。它只是围绕这个想法构建的六种解决方案中的一种,但它是一种非常受欢迎的解决方案。


4

对,但是...

我曾经使用过20亿行的表格。但是,只有使用PK的查询才会很快。

最重要的是,硬件具有足够的RAM以适应内存中的整个表。当这成为一个问题(当时最大为96GB)时,进行垂直分区,保持每台机器上的表集大小足够小,以便仍然适合内存。此外,这些机器通过10Gb光纤连接,因此网络吞吐量不是很大的问题。

BTW。您的架构看起来像什么,它可以融入的NoSQL解决方案,使用run_id作为哈希的光谱和关键spectrum_id作为散列数据点的关键。


3

我在我的博客上写过这个主题: http://www.tocker.ca/2013/10/24/improving-the-performance-of-large-tables-in-MySQL.html

重复一些要点:

  • 随着B树变大而不适合内存,B树会降级(MySQL并不是唯一的)。
  • InnoDB确实有一些功能可以帮助维持一些性能(改变缓冲;以前称为“插入缓冲区”)。
  • 分区也有帮助。

在我的帖子中,Tim Callaghan的评论与此相关: http://www.tokutek.com/resources/benchmark-results/benchmarks-vs-innodb-hdds/#iiBench

其中显示使用iibench基准测试插入10亿行。