插入下表最多需要 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内存
鉴于这种硬件,我认为问题不在于硬件瓶颈。
答案1
观察#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
索引和主键以同一列开头。这是一个冗余索引。由于此表是 InnoDB,因此索引categoriesId
是冗余的,原因如下:所有二级索引都包含 gen_clust_index(又称聚集索引;请参阅mysql 中 gen_clust_index 有什么用?)
如果你删除categoriesId
索引
ALTER TABLE productsCategories DROP INDEX categoriesId;
这将改善 INSERT戏剧性地因为不必进行额外的辅助和聚集索引维护。
观察#2
如果你正在执行任何批量插入操作,你需要一个大的批量插入缓冲区。
请参阅我过去关于此问题的帖子:
- https://dba.stackexchange.com/a/16979/877
- https://dba.stackexchange.com/a/9893/877
- https://dba.stackexchange.com/a/2948/877
观察#3
您的日志文件大小太小了!它应该是 InnoDB 缓冲池的 25%,在您的情况下应该是 1G。请参阅我的帖子关于如何调整 InnoDB 日志文件的大小。
观察#4
请不要设置innodb_thread_concurrency!!!我在 Percona Live NYC 亲身体验过不要管这个设置。它在 MySQL 5.5、MySQL 5.1 InnoDB Plugin 和 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。另外,请参阅MySQL 文档中有关 innodb_log_buffer_size 的内容。
根据这些观察,请添加或替换以下设置:
[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
答案2
您应该检查innodb_log_file_size
,默认设置为 5M,对于写入密集型设置来说,这个值太低了。考虑将其设置为 100M。您必须删除旧ib_logfile*
文件才能使用新设置启动数据库。请不要在数据库服务器运行时删除日志文件,您必须先停止它。您可能应该先备份旧日志文件,而不是直接删除它们。