Mysql:一张表有70亿条记录

Question

我需要在 Mysql 表中保存大约 78 亿条记录。该表是读取和写入密集型的。我必须保持每小时至少 0.2 亿条记录的插入率。而在表上搜索不应超过 10 秒。我们有一个 UI，用户可以从中根据不同的列属性进行搜索。

搜索最多的查询可以是这样的:

1. select * from mytable where prop1='sip:+100008521149' and
prop2='asdsa'
order by event_timestamp desc limit 10;

2. select * from mytable where prop1='sip:+100008521149'
order by event_timestamp desc limit 10;

3. select * from mytable where prop2='asdsa'
order by event_timestamp desc limit 10;

目前表上有2个索引:

1- idx_1(prop1,event_timestamp)
2- idx_2(prop2,event_timestamp)

InnoDB设置如下:

    innodb_buffer_pool_size = 70G
    innodb_log_file_size = 4G
    innodb_io_capacity=2000
    innodb_io_capacity_max=6000
    innodb_lru_scan_depth=2000
    innodb_flush_log_at_trx_commit=2
    innodb_log_buffer_size=16M
    innodb_thread_concurrency = 0
    innodb_read_io_threads = 64
    innodb_write_io_threads = 64
    innodb_autoinc_lock_mode = 2
    bulk_insert_buffer_size=33554432
    query_cache_type=1
    query_cache_size=64M
    innodb_flush_neighbors=0
    expire_logs_days=10
    max-connections=500
    long_query_time = 5
    read_buffer_size=16M
    sort_buffer_size=16M
    read_rnd_buffer_size=16M
    innodb_doublewrite = 0
    innodb_flush_method=O_DIRECT

---

Machine's RAM size is 99 GB.

一旦启动系统很快，但当记录达到 2.2 亿时性能下降很多。虽然我们正在使用 LOAD INFILE，但插入速度非常慢。搜索索引参数时搜索速度非常快。看起来缓冲池不够。

我有几个问题:

1. 是否可以通过此配置支持此类数据。

2. 7 的理想和实用的缓冲池大小应该是多少亿条记录。

3. DATA+INDEX 的大小接近 150 GB，只有 2.2 亿记录。看来我需要 TB 的 ram。
4. 我们正在考虑 Master/Slave 配置来制作 congif 用于读取和在各自的服务器上写显性。
5. 还有其他更好的方法来设计此解决方案吗？
6. 增加更多索引可以使 UI 搜索更好，但增加单个索引索引使插入速度降低了很多倍。

更新:1

问- 表格比 RAM 大得多，对吗？ buffer_pool 不能做得足够大——它必须小于 ram，否则性能会受到影响。

A- RAM 大小为 100 GB，缓冲池为 70 G。是的，数据大小比 RAM 大。

Q- 请提供 SHOW CREATE TABLE;我需要调查几个问题。 (数据类型、字段大小等)

A- 所有字段都是字符串类型。我们都使用了 varchar(127)。 PK 是自动生成的 id bigint(20)。

Q- LOAD DATA INFILE 中有多少条记录？你直接加载到表中吗？ LOAD 的频率是多少？

A- 每个文件 100000 条记录。多个线程正在将数据从 CSV 文件加载到数据库。在初始迁移中，我们必须不断加载它直到 6.5 亿条记录。之后频率将降低到每 15 分钟一次左右。

Q- Master+Slave:请记住，所有写入操作也是在 Slave 上执行的。如果您有很多读取，那么不止一个 Slave 会分散读取，从而获得一些缩放。

A- 我们目前正在使用 MASTER/SLAVE 方法进行测试。

我们用 MYISAM 创建了 MASTER，没有索引。 MASTER 将用于插入。SLAVE 有 INNODB 和 2 个索引。将对其执行搜索。两者都是不同的机器，不共享 RAM 或 CPU。应用程序在第三台机器上。

问 - 你们有旋转驱动器吗？还是固态硬盘？A-如何检查？

问 - 您的行似乎很大。是否有 TEXT 或 BLOB？如果是这样，SELECT * 可能是一个严重的性能负担。

A- 是的，行有 50 列，但数据大约有 15-20 列。我们不能减少数据类型的大小，因为所有字段都可以容纳任意数量的字母数字数据。都是 TEXTS，没有 BLOBS。

Answer 1

关闭查询缓存:每次 INSERT 发生时，它必须清除 QC 中的所有条目——即每秒 5555 次!

query_cache_type = 0
query_cache_size = 0

第一个查询需要INDEX(prop1, prop2, event_timestamp)。 (prop1和prop2可以互换)

使用添加的索引，三个查询中的每一个都将触及索引中的不超过 10 行，并且对数据执行不超过 10 次随机(？)提取。在最坏的情况下，这只有大约 11 次磁盘命中。 @Bernd 的“懒惰评估”不会让它变得更好。

该表比 RAM 大得多，对吗？ buffer_pool 不能足够大——它必须小于 ram，否则性能会受到影响。

请提供SHOW CREATE TABLE;有几个问题我需要调查。 (数据类型、字段大小等)

LOAD DATA INFILE 中有多少条记录？你 LOAD 直接进入表吗？ LOAD 的频率是多少？

Master+Slave:请记住，所有的写入也是在 Slave 上执行的。如果你有很多读取，那么不止一个 Slave 会分散 reads，从而获得一些扩展。

您有旋转驱动器吗？还是 SSD？

您的行似乎很大。是否有 TEXTs 或 BLOBs？如果是这样，SELECT * 可能会造成严重的性能负担。