InnoDB: INSERT/UPDATE的性能非常差

Question

最近，我在插入和更新方面遇到了很大的问题。在我的网站上，80%的查询都是被选择的，但是在查询缓慢的日志中，只有插入和更新。我的专用服务器有32 i7内存，核心i7，但没有SSD驱动器。

mysqltuner说：

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.14
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
[--] Data in MyISAM tables: 203M (Tables: 23)
[--] Data in InnoDB tables: 1G (Tables: 96)
[--] Data in MEMORY tables: 5M (Tables: 4)
[!!] Total fragmented tables: 37

-------- Security Recommendations  -------------------------------------------
Warning: Using a password on the command line interface can be insecure.
[OK] All database users have passwords assigned
Warning: Using a password on the command line interface can be insecure.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 10h 54m 18s (3M q [93.602 qps], 131K conn, TX: 22B, RX: 713M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 4.0G global + 1.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 4.2G (12% of installed RAM)
[OK] Slow queries: 0% (535/3M)
[OK] Highest usage of available connections: 4% (7/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/98.7M
[OK] Key buffer hit rate: 99.7% (10M cached / 28K reads)
[OK] Query cache efficiency: 66.2% (1M cached / 2M selects)
[!!] Query cache prunes per day: 290517
[!!] Sorts requiring temporary tables: 16% (43K temp sorts / 261K sorts)
[OK] Temporary tables created on disk: 17% (20K on disk / 119K total)
[OK] Thread cache hit rate: 99% (7 created / 131K connections)
[OK] Table cache hit rate: 26% (413 open / 1K opened)
[OK] Open file limit used: 0% (125/100K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 1.3G/3.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
    query_cache_size (> 32M)
    sort_buffer_size (> 256K)
    read_rnd_buffer_size (> 256K)

我已经尝试过优化MySQL配置，但这并没有多大帮助。my.cnf：

[mysqld]
innodb_buffer_pool_size = 3GB
innodb_flush_method = O_DIRECT

innodb_additional_mem_pool_size=16M
innodb_autoextend_increment = 128M
innodb_log_file_size = 500M
innodb_log_buffer_size = 750M

query_cache_type = 1
query_cache_limit = 32M
query_cache_size  = 32M

tmp_table_size = 220M
max_heap_table_size = 220M

sql_mode=
event-scheduler=1
long_query_time=2

我的慢速查询日志中的大多数查询都非常简单。桌子是中等的，所以是有线的。然而，最有问题的查询是插入到具有~900 k记录(InnoDB)的表中：

CREATE TABLE `topic_marking` (
    `topic_id` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
    `user_id` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
    `forum_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    `mark_time` INT(10) UNSIGNED NOT NULL,
    UNIQUE INDEX `topic_id_2` (`topic_id`, `user_id`),
    INDEX `forum_id` (`forum_id`),
    INDEX `topic_id` (`topic_id`),
    INDEX `user_id` (`user_id`),
    CONSTRAINT `topic_marking_ibfk_1` FOREIGN KEY (`topic_id`) REFERENCES `topic` (`topic_id`) ON DELETE CASCADE,
    CONSTRAINT `topic_marking_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE,
    CONSTRAINT `topic_marking_ibfk_3` FOREIGN KEY (`forum_id`) REFERENCES `forum` (`forum_id`) ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT;

如您所见，有许多索引和放弃键。我认为这可能是个问题，所以我决定删除所有外键，只留下两个索引(topic_id_2和forum_id)。嗯，这有点帮助，但并没有消除一个问题。

除了购买额外的SDD驱动器之外，我还能做什么来提高性能呢？

我有50个查询/秒。简单的UPDATE查询分析结果如下：

starting
0.000028
checking permissions
0.000004
Opening tables
0.000017
init
0.000014
System lock
0.000026
updating
0.000033
end
0.000003
Waiting for query cache lock
0.000002
end
0.000066
query end
7.456112
closing tables
0.000019
freeing items
0.000013
logging slow query
0.000054
cleaning up
0.000011

所以query_end需要7秒的时间！什么意思？

Answer 1

THis可能是tx的脸红。

请查看https://stackoverflow.com/questions/6937443/query-end-step-very-long-at-random-times中类似的内容。

在那里，加上

innodb_flush_log_at_trx_commit =0

帮了忙。

如果运行一个典型的7200 run大而慢的光盘，您的IOPS预算是非常有限的。这里你唯一真正的步骤是撒谎(比如向上--不是背诵，或者在写回模式下，在它发生之前确认一次写入)，或者得到更多的IOPS (这是SSD真正发光的地方--你可以很容易地得到一个60.000 IOPS的SSD --这是预算的400倍。

撒谎是不可取的，除非你的电池到位，以弥补电力故障-可能与一个raid控制器，有电池在顶部，也。如果这是一种投资，那么SSD绝对是最具成本效益的方法。

你最狡猾的就是在IO的预算上。使用更多linux的SOmeone可以介入并告诉您如何度量它--我认为有一个iotop命令可以做到这一点。