很快我们就需要对生产数据库进行架构更改.我们需要尽量减少这项工作的停机时间,但是,ALTER TABLE语句将运行很长一段时间.我们最大的表有1.5亿条记录,最大的表格文件是50G.所有表都是InnoDB,它被设置为一个大数据文件(而不是每个表的文件).我们在8核机器,16G内存和RAID10配置上运行MySQL 5.0.46.
我有一些MySQL调优的经验,但这通常集中在来自多个客户端的读取或写入.有关此主题的互联网上有很多信息,但是,关于(暂时)调整MySQL服务器以加速InnoDB表上的ALTER TABLE或INSERT INTO的最佳实践,似乎很少有信息可用. .SELECT FROM(我们可能会使用它而不是ALTER TABLE来获得更多机会来加快速度).
我们计划进行的架构更改是向所有表添加一个整数列,并将其作为主键,而不是当前主键.我们还需要保留"旧"列,因此不能覆盖现有值.
尽可能快地完成这项任务的理想设置是什么？
您需要更仔细地考虑您的要求.
在最简单的层面上,改变表的"最快"方法是尽可能少ALTER TABLE地使用,最好是一个.这是因为MySQL复制表的数据以更改模式并进行十五次更改,而制作单个副本显然(并且确实)比复制表十五次更快,一次进行一次更改.
但我怀疑你问的是如何以最少的停机时间做这个改变.我会这样做,你基本上综合了非块的ALTER TABLE工作方式.但它有一些额外的要求:
您需要一种方法来跟踪添加和更改的数据,例如后者的"已修改"日期字段或AUTO_INCREMENT前者的字段.
你需要空间在数据库上有两个表副本.
你需要一个时间段,表格的变化不会超过快照
基本技术就像你建议的那样,即使用INSERT INTO ... SELECT ....至少你是在前面,因为你是从InnoDB表开始的,所以SELECT不会阻止.我建议ALTER TABLE在新的空表上执行操作,这将节省MySQL再次复制所有数据,这意味着您需要在INSERT INTO ... SELECT ...语句中正确列出所有字段.然后你可以做一个简单的RENAME陈述来交换它.然后你需要做另一个INSERT INTO ... SELECT ... WHERE ...,也许是UPDATE ... INNER JOIN ... WHERE ...为了获取所有修改过的数据.您需要快速执行此操作INSERT,否则您的代码将开始向快照添加新行和更新,这将干扰您的更新.UPDATE (如果您可以将应用程序置于维护模式之前几分钟,则不会出现此问题RENAME.)
除此之外,还有一些与键和缓冲区相关的设置,您可以只为一个会话更改,这可能有助于主数据移动.喜欢的东西read_rnd_buffer_size而read_buffer_size将是有益的增加.
您可能希望从Percona工具包中查看pt-online-schema-change.基本上它的作用是:
复制原始表结构,运行ALTER.
将旧表中的行复制到新创建的表中.
使用触发器在复制时跟踪和同步更改.
当一切都完成后,它会通过重命名来交换表.
对于单实例数据库非常有效,但是如果使用复制可能会非常棘手,并且您无法负担停止从属并在以后重建它们.
还有这个一个很好的网络研讨会在这里.
PS:我知道这是一个老问题,只是回答有人通过搜索引擎点击这个问题.
设置奴隶
停止复制.
在奴隶上做ALTER
让奴隶赶上主人
交换主站和从站,因此从站成为生产服务器,结构改变,停机时间最短
不幸的是,这并不像staticsan在他的回答中那样简单.在线创建新表并移动数据很容易,在维护模式下进行清理也足够了,但是,Mysql RENAME操作会自动操作对旧表的任何外键引用.这意味着对原始表的任何外键引用仍将指向您重命名表的任何内容.
所以,如果你有任何对表的外键引用,你试图改变你就是要么改变那些表来替换你对新表的引用,要么更糟糕的是如果那个表很大你必须重复大的过程表二.
过去对我们起作用的另一种方法是处理一组处理alter的Mysql副本.我不是说这个过程的最佳人选,但它基本上包括打破一个从属服务器的复制,在该实例上运行补丁,一旦alter table完成就重新启动复制,以便赶上复制.复制赶上后,您将站点置于维护模式(如有必要),从主站切换到新的修补从站作为新的主数据库.
我唯一记不住的是,当你将其他奴隶指向新的主人时,他们也会得到应用的改变.对此过程的一个警告,我们通常使用它来在代码需要更改之前滚动更改补丁,或者在代码更改为不再引用列/键之后滚动更改补丁.
我测试了各种策略来加速一个转换表.最终我在特定情况下的速度增加了10倍.结果可能适用于您的情况,也可能不适用.但是,基于此,我建议尝试使用InnoDB日志文件/缓冲区大小参数.
简而言之,只增加innodb_log_file_size和innodb_log_buffer_size具有可测量的效果(小心!更改innodb_log_file_size是有风险的.请查看下面的更多信息).
基于粗略的写入数据速率(iostat)和cpu活动,瓶颈是基于io而不是数据吞吐量.在速度较快的500s运行中,写入吞吐量至少与硬盘所期望的相同.
尝试过的性能优化:
按照/sf/ask/17360801/中的建议选择*into outfile +加载数据infile
"禁用键"我错过了它在InnoDB中没有效果的事实,这也是基于结果的.除了最终的变更表之外,所有情况都是如此. 如何在innodb中禁用索引
innodb_buffer_pool_size,"磁盘I/O提示",网址为http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html
foreign_key_checks = 0等,"批量数据加载技巧",网址为http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
更大的innodb_log_file_size(参见下面的警告)和innodb_log_buffer_size.请参阅http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html和http://dev.mysql.com/doc/refman/5.1/en/innodb-上的 "记录提示".parameters.html #smavar_innodb_log_file_size和http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_log_buffer_size
更改innodb_log_file_size可能很危险.请参阅http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/链接中解释的技术(文件移动)在我的案例中运行良好.
另见http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/和http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb -log-file-size /有关innodb和调整日志大小的信息.更大的日志文件的一个缺点是崩溃后恢复时间更长.
测试运行和粗略计时:
新建createad表的简单加载数据:6500s
加载数据w.innodb_log_file_size = 200M,innodb_log_buffer_size = 8M,innodb_buffer_pool_size = 2200M,autocommit = 0; unique_checks = 0,foreign_key_checks = 0:500s
加载数据w.innodb_log_file_size = 200M,innodb_log_buffer_size = 8M:500s
相当于直接改变表w.datainnodb_log_file_size = 200M,innodb_log_buffer_size = 8M:500s
测试细节:表:InnoDB,6M行,2.8G磁盘,单个文件(innodb_file_per_table选项),主键是1个整数,+ 2个unque约束/索引,8列,平均.行长218个字节.服务器:Ubuntu 12.04,x86_64,虚拟机,8核,16GB,sata消费级磁盘,没有raid,没有数据库活动,微不足道的其他进程活动,其他和更小的虚拟机中的微不足道的活动.Mysql 5.1.53.除了增加1400M的innodb_buffer_pool_size之外,初始服务器配置是非常默认的.alter table添加了2个小列.我没有对原始的alter table进行计时,而是尝试使用等效的load data infile语句,最后我做了直接的alter table并获得了可比较的结果.
此问题至少与以下问题有关:
MySQL:在大表中添加一个字段
在大型mysql表中添加一个新列
mysql慢查询
MySQL"set unique_checks","set foreign_key_checks"与"alter table disable keys"
为InnoDB的ALTER TABLE优化MySQL
京公网安备 11010802040832号 | 京ICP备19059560号-6 