对Debian上的单个MySQL查询使用多个核

Question

我正在运行一个MySQL服务器在VM (VMWare)上进行测试，Debian是客户操作系统。客户有四个模拟CPU核，所以我将thread_concurrency设置为四个。

我正在大型表上进行昂贵的连接，这可能需要几分钟时间，但我在来宾操作系统上看到，一次只使用一个核心。无论所涉及的表使用哪个存储引擎(用MyISAM和InnoDB进行测试)，都会发生这种情况。此外，当执行这些大型查询时，整个数据库似乎被阻塞，我不能并行执行任何额外的查询。奇怪的是，htop显示，用于查询的核心在查询运行时发生了变化！

这一切为什么要发生？

这是来自SHOW FULL PROCESSLIST;的相关条目(没有其他查询)：

| 153 | root       | localhost | pulse_stocks  | Query   |   50 | Copying to tmp table | 
SELECT DISTINCT * FROM 
`pulse_stocks`.`stocks` sto,
`pulse_new`.`security` sec
WHERE
(sto.excntry = sec.excntry AND sto.stock_id = sec.ibtic) OR
( sto.isin = sec.isin AND sto.isin <> "" AND sec.isin <> "" )
ORDER BY
sto.id
LIMIT 0, 30 

没有其他查询待定。另一个有趣的观察是，如果我省略了MySQL部分，那么ORDER BY将在一秒钟内回答这个查询。

这就是SHOW ENGINE INNODB STATUS;所展示的：

=====================================
120316  9:55:56 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 49 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 47258, signal count 47258
Mutex spin waits 0, rounds 10260, OS waits 39
RW-shared spins 94442, OS waits 47210; RW-excl spins 1, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 5381
Purge done for trx's n:o < 0 1810 undo n:o < 0 0
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 7503, OS thread id 140316748777216
MySQL thread id 154, query id 654 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 5380, ACTIVE 105 sec, process no 7503, OS thread id 140316748977920
 fetching rows, thread declared inside InnoDB 429
mysql tables in use 2, locked 0
MySQL thread id 153, query id 623 localhost root Copying to tmp table
SELECT DISTINCT * FROM 
    `pulse_stocks`.`stocks` sto,
    `pulse_new`.`security` sec
WHERE
    (sto.excntry = sec.excntry AND sto.stock_id = sec.ibtic) OR
    ( sto.isin = sec.isin AND sto.isin <> "" AND sec.isin <> "" )
ORDER BY
    sto.id
LIMIT 0, 30

Trx read view will not see trx with id >= 0 5381, sees < 0 5381
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
116089 OS file reads, 7 OS file writes, 7 OS fsyncs
1063.16 reads/s, 117085 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
0 inserts, 0 merged recs, 0 merges
Hash table size 17393, node heap has 1 buffer(s)
0.00 hash searches/s, 14.73 non-hash searches/s
---
LOG
---
Log sequence number 0 38201270
Log flushed up to   0 38201270
Last checkpoint at  0 38201270
0 pending log writes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 20638760; in additional pool allocated 994816
Dictionary memory allocated 162680
Buffer pool size   512
Free buffers       0
Database pages     511
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 816631, created 0, written 1
7597.72 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 964 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 7503, id 140316711446272, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 160338394
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1495933.31 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Answer 1

您可能会发现这令人惊讶，但是您应该将innodb_thread_concurrency设置为0(这是无限并发性)。这将允许InnoDB存储引擎决定要发出多少并发票。

2011年MySQL 5月26日，我写了一篇关于InnoDB多核合作的文章(也是MySQL 5.1.38 InnoDB插件)。

根据MySQL文档，线程_并发性变量仅适用于Solaris。

我还有一个问题:您的加入是否涉及MyISAM和InnoDB？MyISAM的全表锁定行为使InnoDB的行级锁定和MVCC无效.。

如果您不使用MySQL 5.5，则使用请尽快升级，以便设置InnoDB的多核订婚选项。

更新2012-03-19 08:30美国东部时间

从MySQL 5.1.38开始，您可以安装InnoDB插件来使用新的多核连接设置。但是，您必须正确地调整设置。

实际上，没有配置

• InnoDB for MySQL 4.1在单线程环境中运行得比它之前或之后的所有其他版本的MySQL运行得更好。
• InnoDB 5.1插件在单CPU环境中运行得比所有其他版本的MySQL在它之前或之后运行得更好
• 我在2011年11月写了一篇关于这个的文章：为什么MySQL5.5比5.1慢(linux，使用mysqlslap)
• 我还在2011年6月写了一篇文章，介绍如何在MySQL、Percona和MariaDB：如何正确地执行MySQL烘焙？上运行自己的基准测试
• 我所有的断言都基于Percona，他实际上针对MySQL：http://www.mysqlperformanceblog.com/2011/10/10/mysql-versions-shootout/的所有版本运行了性能基准。

Answer 2

任何版本的MySQL都没有在单个连接中使用多个核的代码。

Percona在Xtradb中跨多个连接使用多个核做得更好。InnoDB在大约8个核心上耗尽了蒸汽；Xtradb在32左右就耗尽了。

“大型查询”可以锁定其他连接所需的表(或表中的行)。让我们看一下查询和表。