主页 / dba / 问题 / 10840
Accepted
Dolan Antenucci
Asked: 2012-01-18 14:39:56 +0800 CST

如何优化我的 mysql 设置以更快地创建索引？

  • 772

我有一台运行 Ubuntu 10.04 的服务器，通过包安装了 Mysql 5.1x。该系统有 128GB 内存，8 个内核，并有 4TB 的可用空间用于存储 Mysql 和 Mysql tmp。

我有一个这样的 MyISAM：

CREATE TABLE `data_store` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uniqname` varchar(150) NOT NULL,
  `data` blob,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM CHARSET=latin1;

我插入了 8 亿条记录（插入前大约 350gb 数据），然后尝试添加以下索引：

ALTER TABLE data_store DISABLE KEYS;
ALTER TABLE data_store ADD INDEX uniqname_index (uniqname);
ALTER TABLE data_store ENABLE KEYS;

（关于DISABLE KEYS命令，我看到其他地方建议在插入数据之前使用，并且由于该命令从未到达ENABLE KEYS，我认为它对我没有任何用途。我主要只是在我对我的描述中包含了彻底的内容。我在做。）

当我启动索引作业时，显示的第一个状态SHOW PROCESSLIST是“正在复制到 tmp 表”。

几个小时后，我检查 bac，即使在 24 小时后，状态仍会显示在“Repair With Keycache”消息中。我尝试在稍旧的服务器上运行该作业，3 天后，它仍然保持“使用 keycache 修复”状态。因此，我取消了这台较新机器上的 create-index 命令。

我读过“使用 Keycache 修复”可能非常慢，在许多情况下，“通过排序修复”是首选。

基于一些 Stack Exchange 和网上的随机帖子，我在 Mysql 服务器中添加了以下设置：

myisam_sort_buffer_size = 80G
bulk_insert_buffer_size = 80G
myisam_repair_threads   = 8
max_heap_table_size     = 20G
myisam_max_sort_file_size = 500G
tmp_table_size          = 20G
key_buffer_size         = 20G
sort_buffer_size        = 20G
join_buffer_size        = 20G

我重新启动了作业，同样的过程再次发生（复制到 tmp 文件，然后通过密钥缓存修复）。

在我终止作业后，我注意到在 mysql/error.log 中有一条消息，“myisam_sort_buffer_size 太小”。这发生在当天早些时候，而不是我杀死工作的时候。

问题

  • 我会走错路吗？我只是希望能够通过某个键（uniqname）快速查找我的数据。

  • 从我的表开始，从头开始添加索引，使用DISABLE KEYS命令，插入我的 8 亿条记录，然后再开始，有什么好处ENABLE KEYS吗？我在其他地方读到这可以防止复制 tmp 表（这可能只能节省我几个小时？）

  • 我想要这个“通过排序修复”吗？

mysql index

1 个回答

  1. Best Answer

    对于初学者，我暂时不会触及缓冲区大小。您在问题中的尺寸太大了。

    这是另一个观察结果：您有 BLOB 数据。哎呀，您的临时表将很快占用空间。你可以这样做：

    通过将此行添加到 /etc/fstab 创建一个名为 /var/tmpfs 的 32GB RAM 磁盘

    none                    /var/tmpfs              tmpfs   defaults,size=32g        1 2

    接下来，创建一个名为 /mysqltmp 的文件夹并在其上挂载 RAM 磁盘

    mkdir /mysqltmp
chown mysql:mysql /mysqltmp
mount /mysqltmp /var/tmpfs

    将此添加到 my.cnf 并重新启动 mysql

    [mysqld]
tmpdir=/mysqltmp

    现在，任何通过 DDL 生成的 tmp 表都会进入 RAM 磁盘。

    这是另一个观察结果：为什么不创建一个单独的表来使 BLOB 数据远离唯一名称？

    CREATE TABLE `data_store_name` SELECT id,uniqname FROM `data_store` WHERE 1=2;
ALTER TABLE `data_store_name` ADD PRIMARY KEY (id);
ALTER TABLE `data_store_name` ADD UNIQUE KEY (uniqname);
ALTER TABLE `data_store_name` ADD INDEX name_id_ndx (uniqname,id);
INSERT INTO `data_store_name` SELECT id,uniqname FROM `data_store`;

    这将防止在索引时移动 BLOB 数据。

    从这里开始，您必须始终使用它的名称加入 data_store，如下所示：

    SELECT
    A.uniqname,B.data
FROM
    (SELECT * FROM data_store_name WHERE uniqname = 'mydataname') A
    LEFT JOIN
    data_store B USING (id)
;

    进行这些更改将避开处理 keycache、RAM 磁盘和 tmp 表的整个混乱局面。

    试试看 ！！！

    • 6

相关问题