主页 / dba / 问题 / 119593
Accepted
Nick
Asked: 2015-10-30 09:15:57 +0800 CST

优化 400GB MyISAM 表的速度

  • 772

我们有带有图像 url 信息的单个表的 MariaDB 服务器。该表在磁盘上大约有 400GB，可能包含 400M 行。

表分为 1024 个分区。

所有查询都与此类似：

select * from container where id in (1234, 1235 ... );

sql 通常需要 2 秒才能执行。

每行包含一个图像 url、标题和关键字。

Keybufer 设置为 8GB。

这个设置运行良好，直到我们开始插入额外的行。我们尝试了普通插入，也尝试了低优先级插入。这两种情况都很慢。

我想知道我们还能调整什么以加快选择速度。

更新：
该表没有索引，除了 bigint 字段上的主键，例如 primary key(id)

更新 2：
这里有更多信息：

创建表

CREATE TABLE `container` (
  `id` bigint(20) NOT NULL,
  `data` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=binary
/*!50100 PARTITION BY KEY (id)
PARTITIONS 1024 */

data字段存储标准 JSON。它是UTF8文本，但是由于输入数据的编码错误，我们被迫将其存储为blob +二进制编码。

磁盘大小

# du -h /usr/local/mysql/var/mydb/
371G    /usr/local/mysql/var/mydb/

数数（*）

> select count(*) from container;
+-----------+
| count(*)  |
+-----------+
| 409036295 |
+-----------+
1 row in set (0.04 sec)

我的配置文件

[mysqld]
server-id = 1

port=3306
socket=/tmp/mysql.sock
skip_name_resolve

open-files-limit=64000

#Flush every 5 min (300 sec)
set-variable = flush_time=900

#Max Clients
set-variable = max_connections=5050
set-variable = max_user_connections=5000
set-variable = back_log=50

set-variable = table_open_cache=1024
set-variable = table_definition_cache=1024

#INSERT While SELECT-ing. Default is 1 (1 = On if have no hole, 2 = On if have hole)
set-variable = concurrent_insert=2

#Interactive timeout 60 min (from console)
set-variable = interactive_timeout=3600

#non-interactive timeout 3 hours
set-variable = wait_timeout=10800

set-variable = key_buffer_size=8192M
set-variable = max_allowed_packet=5M
set-variable = sort_buffer_size=256M

set-variable = tmp_table_size=512M
set-variable = max_heap_table_size=64M

#all updates will wait for selects
set-variable = low_priority_updates=1

#preforking
set-variable = thread_cache_size=64

#----- SLOW QUERIES -----

set-variable = long_query_time=2
set-variable = log_slow_queries=mysql-slow.log

#----- CASHE -----

# SELECT SQL_CACHE * from x
set-variable = query_cache_type=0
set-variable = query_cache_limit=1M
set-variable = query_cache_size=128M
mysql mariadb

2 个回答

  1. 假设你SELECTing只是 by the PRIMARY KEY，那么以下将加快速度：

    • 不 PARTITIONing。它只会减慢此类查询，尤其是因为有 1024 个分区。
    • 切换到 InnoDB。PRIMARY KEY与数据“聚类” ；这将在获取的每一行上保存磁盘命中。缩小key_buffer_size到 50M 并提高innodb_buffer_pool_size到可用 RAM 的 70%。InnoDB 还避免了表锁。
    • 在客户端压缩 JSON 文本；数据类型必须是BLOB（现在错误地是）。

    注意：InnoDB会将磁盘空间扩大2-3倍；压缩将取回该空间。（请用一百万行进行测试。尝试不同的ROW_FORMATs；我不知道哪一个最适合您的情况。并验证SELECT速度以及读写之间是否存在干扰。）

    你真的同时有5000个连接吗？他们可能互相绊倒，互相拖慢对方的速度。

    您已经关闭了一半的查询缓存；也做query_cache_size=0

    • 1
  2. Best Answer

    我们在没有分区的情况下对 MyISAM 进行了分析，但速度较慢。

    我们确实测试了 InnoDB，我们成功地加快了插入速度，但是选择仍然没有我们想要的那么快。

    后来，我们迁移到 TokuDB。

    由于 TokuDB，目前表大约有 100 GB，查询速度非常非常快。

    • 0

相关问题