题 MySQL慢速写入


插入下表最多需要70秒才能完成:

CREATE TABLE IF NOT EXISTS `productsCategories` (
  `categoriesId` int(11) NOT NULL,
  `productsId` int(11) NOT NULL,
  PRIMARY KEY (`categoriesId`,`productsId`),
  KEY `categoriesId` (`categoriesId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表中大约有100,000行,磁盘上占用7MB。

MySQL中是否有一些设置可以提高写入性能?

我的 my.cnf 文件如下:

log-slow-queries="/var/log/mysql/slow-query.log"
long_query_time=1 
log-queries-not-using-indexes

innodb_buffer_pool_size=4G
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT

query_cache_size = 6G
key_buffer_size = 284M
query_cache_limit = 1024M
thread_cache_size = 128
table_cache = 12800

sort_buffer_size=2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

read_buffer_size=128K

open_files_limit               = 1000
table_definition_cache         = 1024
table_open_cache               = 6000

max_heap_table_size=512M
tmp_table_size=4096M

max_connections=1000

thread_concurrency = 24

这是硬件设置:

  • 戴尔R710
  • RAID10
  • 48G内存

鉴于这种硬件,我不认为这个问题会成为硬件瓶颈。


7
2017-07-10 09:41




你能提供一些客观数据吗?日志,您的测试/基准测试方法和结果,那种事情? - womble♦
你想看什么样的日志?唯一能让我知道为什么需要这么长时间的东西就是de MTOP。我测试的东西是将表更改为myisam表,更高的内存限制,更高的线程。 - Ronn0
改进mysql的最佳设置是切换到postgres lol - Antony Gibbs
thread_concurrency = 24无效...你可以转储该行 - Antony Gibbs


答案:


观察#1

引起我注意的第一件事是桌子结构

CREATE TABLE IF NOT EXISTS `productsCategories` (
  `categoriesId` int(11) NOT NULL,
  `productsId` int(11) NOT NULL,
  PRIMARY KEY (`categoriesId`,`productsId`),
  KEY `categoriesId` (`categoriesId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

请注意 categoriesId index和PRIMARY KEY以相同的列开头。这是一个冗余索引。由于这个表是InnoDB,所以 categoriesId 索引是冗余的另一个原因:所有二级索引都包含gen_clust_index(也称为聚簇索引;请参阅 什么是mysql中使用的gen_clust_index?

如果删除了 categoriesId 索引

ALTER TABLE productsCategories DROP INDEX categoriesId;

这将改善INSERT 显着 因为不必进行额外的辅助和群集索引维护。

观察#2

如果您正在进行任何批量插入操作,则需要大量操作 批量插入缓冲区

请看我过去的帖子:

观察#3

你的日志文件太小了!!!它应该是InnoDB缓冲池的25%,在你的情况下应该是1G。看到 我关于如何调整InnoDB日志文件大小的帖子

观察#4

请不要设置innodb_thread_concurrency !!! 我亲自在Percona Live NYC学到了这一点。默认情况下,它在MySQL 5.5,MySQL 5.1 InnoDB插件和Percona Server 5.1+中被禁用。

观察#5

您需要使用innodb_file_per_table。如果这被禁用,我在ibdata1上进行文件维护是一场噩梦。请阅读 我关于如何清理InnoDB实现这一点的帖子

观察#6

如果您使用的是MySQL 5.5或Percona Server,则必须设置某些选项以使InnoDB使用多个CPU /多个内核。请参阅 我在这些设置上的帖子

观察#7

你有 innodb_log_buffer_size=4M。默认值为8M。这将导致重做日志的两次刷新。那也会抵消你的 innodb_flush_log_at_trx_commit=2设置。请将其设置为32M。另外,请看 关于innodb_log_buffer_size的MySQL文档

根据这些观察,请添加或替换以下设置:

[mysqld]
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000
innodb_file_per_table
innodb_log_file_size=1G
innodb_log_buffer_size=1G
bulk_insert_buffer_size = 256M

16
2017-07-10 11:31



哇谢谢!我确实打印了所有要点并立即进行处理并将其保存在大脑中以帮助其他人。非常感谢! - Ronn0
query_cache_size 也是巨大的。每个插入都需要刷新最多6GB的缓存。 - Aaron Brown
@AaronBrown我同意。我在高性能MySQL书中读到,InnoDB在查询缓存中的InnoDB表上使用事务ID执行繁琐的操作,这使得使用查询缓存变得不必要,更不用说缓慢了。实际上,MySQL 4.1具有对InnoDB禁用的查询缓存。 - RolandoMySQLDBA
我认为查询缓存完全独立于存储引擎。它基本上通过强制所有内容进行序列化所有写操作,并扫描查询缓存以使查询无效。我从来没有找到它的用例,它总是引起问题。 - Aaron Brown
还有一件事要添加:query_cache_size = 6G < - 这完全是荒谬的。查询缓存通常更好地禁用,并且肯定不应该大于32M或64M。维护6G查询缓存的开销肯定会损害性能。 - Gavin Towey


你应该检查一下 innodb_log_file_size,默认设置为5M,这对于写密集设置来说非常低。考虑将其设置为100M。你将不得不删除旧的 ib_logfile* 文件以便使用新设置启动数据库。在DB服务器运行时请不要删除日志文件,您必须先将其停止。您可能应首先备份旧的日志文件,而不仅仅是删除它们。


0
2017-07-10 10:03