MySQL写性能瓶颈

Question

我们正在尝试诊断MySQL瓶颈，特别是用于数据写入的瓶颈。基本上，我们有一个进程，它从文件夹中选择文件并使用MySQL加载到LOAD DATA CONCURRENT INFILE表X中。目前看来，我们产生的文件比MySQL可以使用的文件还多。具体来说，我们每分钟产生大约一百万行(所有文件加在一起)，我们的摄取速率是每秒15K行(900,000次写入)(基于Show Engine INNODB Status)。

表X有31列，其中17列是整数，12浮点数和1 Char (10)，相当简单。它还具有一个自动增量整数列和一个时间戳(epoch)和auto-incr的主键。列。

MySQL实例信息(物理主机)：40 Core，256 GB RAM，8 SSD作为单个逻辑驱动器(RAID 0)我们同时使用Innodb和MYIASM，但是我们试图优化写入的表驻留在InnoDb中。

当前资源利用率: CPU 10%，系统内存50% (已分配)，磁盘利用率低

MySQL Config: table_cache = 1000
thread_cache = 60
max_heap_table_size = 8192M
join_buffer_size = 256K
sort_buffer_size = 256K
query_cache_size = 512M
query_cache_limit = 512M
key_buffer_size = 8000M 
bulk_insert_buffer_size = 2000M
myisam_sort_buffer_size = 8000M
tmp_table_size = 8192M 
myisam_repair_threads  = 5
innodb_open_files = 10000
open_files_limit = 10000
concurrent_insert = 2

#innodb
innodb_flush_log_at_trx_commit = 2
innodb_support_xa = 0
innodb_buffer_pool_size = 128G
innodb_buffer_pool_instances =8
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_log_buffer_size=256M
innodb_thread_concurrency=8
innodb_file_per_table innodb_stats_on_metadata = 0
innodb_io_capacity=10000

我们打开了性能模式，下面是按总等待时间排序的罪魁祸首。第一组为非INNODB事件，第二组为INNODB事件。

mysql> SELECT EVENT_NAME, SUM_TIMER_WAIT FROM events_waits_summary_global_by_event_name where event_name not like '%innodb%' ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;
+-------------------------------------------------------+-----------------+
| EVENT_NAME                                            | SUM_TIMER_WAIT  |
+-------------------------------------------------------+-----------------+
| wait/synch/cond/sql/MDL_context::COND_wait_status     | 247003180269200 |
| wait/synch/mutex/mysys/KEY_CACHE::cache_lock          | 225136157662400 |
| wait/io/file/myisam/dfile                             | 112586106594800 |
| wait/synch/cond/mysys/my_thread_var::suspend          | 36942967073600  |
| wait/io/file/sql/load                                 | 28575680068400  |
| wait/synch/mutex/sql/HA_DATA_PARTITION::LOCK_auto_inc | 15671510821200  |
| wait/synch/rwlock/myisam/MYISAM_SHARE::key_root_lock  | 7580580832000   |
| wait/io/file/sql/global_ddl_log                       | 759675521600    |
| wait/io/file/myisam/kfile                             | 478606412800    |
| wait/synch/mutex/sql/LOCK_open                        | 317343598000    |
| wait/io/file/sql/FRM                                  | 281702406000    |
| wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock     | 237524998400    |
| wait/synch/mutex/mysys/THR_LOCK::mutex                | 148067737600    |
| wait/synch/cond/myisam/MI_SORT_INFO::cond             | 53355489600     |
| wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data        | 40690076800     |
| wait/io/file/sql/slow_log                             | 32342937200     |
| wait/io/file/sql/partition                            | 32000921600     |
| wait/synch/cond/mysys/IO_CACHE_SHARE::cond            | 20865078800     |
| wait/synch/mutex/mysys/THR_LOCK_open                  | 18582940800     |
| wait/synch/mutex/sql/THD::LOCK_thd_data               | 15040836800     |
+-------------------------------------------------------+-----------------+




mysql> SELECT EVENT_NAME, SUM_TIMER_WAIT FROM events_waits_summary_global_by_event_name where event_name like '%innodb%' ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;
+-----------------------------------------------+-----------------+
| EVENT_NAME                                    | SUM_TIMER_WAIT  |
+-----------------------------------------------+-----------------+
| wait/io/file/innodb/innodb_data_file          | 161894926356400 |
| wait/synch/mutex/innodb/log_sys_mutex         | 78901379729200  |
| wait/io/file/innodb/innodb_log_file           | 54154967108000  |
| wait/synch/mutex/innodb/log_flush_order_mutex | 31350108798800  |
| wait/synch/rwlock/innodb/btr_search_latch     | 29658726116800  |
| wait/synch/mutex/innodb/buf_pool_mutex        | 26124486124000  |
| wait/synch/mutex/innodb/kernel_mutex          | 9542359283600   |
| wait/synch/mutex/innodb/trx_undo_mutex        | 4636908475600   |
| wait/synch/mutex/innodb/autoinc_mutex         | 2176569546400   |
| wait/synch/mutex/innodb/fil_system_mutex      | 1580851583600   |
| wait/synch/mutex/innodb/dict_sys_mutex        | 764196921200    |
| wait/synch/mutex/innodb/flush_list_mutex      | 493146643200    |
| wait/synch/mutex/innodb/ibuf_mutex            | 415302790000    |
| wait/synch/rwlock/innodb/dict_table_stats     | 346864396800    |
| wait/synch/mutex/innodb/mutex_list_mutex      | 341627459200    |
| wait/synch/rwlock/innodb/checkpoint_lock      | 263200847600    |
| wait/synch/mutex/innodb/rw_lock_list_mutex    | 247598360000    |
| wait/synch/mutex/innodb/trx_doublewrite_mutex | 84526368000     |
| wait/synch/mutex/innodb/rseg_mutex            | 76199049600     |
| wait/synch/mutex/innodb/ibuf_bitmap_mutex     | 12117140800     |
+-----------------------------------------------+-----------------+

如果有人能给我们指明正确的方向，我会非常感激的。

此外，我们一直在调整innodb_concurrency_tickets、innodb_write_io_threads、innodb_flash_log_at_trx_commit等无害数据库变量，但还没有观察到性能的重大变化。

Answer 1

步骤1:关闭查询缓存。每写一次都会导致清除该表QC中的所有条目！

query_cache_type = OFF
query_cache_size = 0

请提供SHOW CREATE TABLE。摆脱不必要的索引将提高INSERT性能，可能会显着地提高。听起来你好像

PRIMARY KEY(ts, id)
UNIQUE(id)

如果你能

PRIMARY KEY(ts, id)
INDEX(id)

是的，UNIQUE对于AUTO_INCREMENT并不是必需的(假设您没有刻意尝试插入dup is )。

所有的列都像实际的一样小吗？也就是说，当SMALLINT没有签名时，您是否盲目地使用INT？越小->越少I/O >越快。(而你似乎是I/O约束的。)

如果使用的是FusionIO，则可以安全地关闭双写缓冲区。

考虑过在“同一时间”使用多个连接插入多个数据集吗？

作为摄入的一部分，您是否对数据进行任何处理(例如，规范化)？