主页 / server / 问题 / 406319
Accepted
Ronn0
Asked: 2012-07-11 01:41:30 +0800 CST

MySQL 慢写

  • 772

插入下表最多需要 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内存

鉴于此硬件，我不认为问题是硬件瓶颈。

mysql innodb

2 个回答

  1. Best Answer

    观察#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索引和 PRIMARY KEY 以同一列开头。这是一个冗余索引。由于这个表是 InnoDB，categoriesId索引是冗余的另一个原因：所有二级索引都包含 gen_clust_index 的键（又名聚簇索引；看看gen_clust_index 在 mysql 中的用途是什么？）

    如果您删除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
    • 17

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

    • 0

相关问题