不间断睡眠是导致我的Python程序运行缓慢的原因吗？如果是，我该如何解决？

Question

我有以下选择语句（使用sqlite3和pysqlite模块）：

self.cursor.execute("SELECT precursor_id FROM MSMS_precursor "+
  "JOIN spectrum ON spectrum_id = spectrum_spectrum_id "+
  "WHERE spectrum_id = spectrum_spectrum_id "+
  "AND ROUND(ion_mz,9) = ? AND ROUND(scan_start_time,4) = ? "+
  "AND msrun_msrun_id = ?", select_inputValues)

在 Python 中运行需要55秒。如果直接在 SQLite 命令行上运行，只需要15毫秒。现在，我注意到当它在这个步骤中时，Python 程序会进入无法打断的休眠状态 (31283 ndeklein 18 0 126m 24m 3192 D 1.0 0.0 2:02.50 python，在top输出中的D标志)，并且CPU利用率从100%下降到约1%。现在我在执行本文中询问的查询时注意到了这一点，我还查看了在这篇文章中询问的查询运行时的top输出 here。在此期间，top 还显示它进入了无法打断的睡眠状态，尽管在 R 和 D 之间切换，并且仅减速到约50%（它根据是否处于D或R状态而波动）。

所以现在我认为这是使我的查询变慢的原因（如果无法打断的睡眠与程序速度无关，请纠正我）。如果这是正确的，那么我该如何确保程序不会进入这种状态？

---

更新1：

使用Python的EXPLAIN QUERY PLAN返回：

(0, 0, 1, u'SCAN TABLE spectrum (~50000 rows)')

使用sqlite命令行的EXPLAIN QUERY PLAN返回：

0|0|1|SCAN TABLE spectrum (~50000 rows)
0|1|0|SEARCH TABLE MSMS_precursor USING INDEX fk_MSMS_precursor_spectrum_spectrum_id_1 (spectrum_spectrum_id=?) (~2 rows)

使用Python的EXPLAIN返回结果为：

(0, u'Trace', 0, 0, 0, u'', u'00', None)

使用sqlite的EXPLAIN返回：

0|Trace|0|0|0||00|
1|Real|0|1|0|438.718658447|00|
2|Real|0|2|0|692.6345000000001|00|
3|Integer|1|3|0||00|
4|Goto|0|39|0||00|
5|OpenRead|1|33|0|13|00|
6|OpenRead|0|39|0|5|00|
7|OpenRead|2|41|0|keyinfo(1,BINARY)|00|
8|Rewind|1|35|0||00|
9|Column|1|8|5||00|
10|RealAffinity|5|0|0||00|
11|Integer|4|6|0||00|
12|Function|2|5|4|round(2)|02|
13|Ne|2|34|4||6a|
14|Column|1|12|4||00|
15|Ne|3|34|4|collseq(BINARY)|6c|
16|Column|1|0|8||00|
17|IsNull|8|34|0||00|
18|Affinity|8|1|0|d|00|
19|SeekGe|2|34|8|1|00|
20|IdxGE|2|34|8|1|01|
21|IdxRowid|2|7|0||00|
22|Seek|0|7|0||00|
23|Column|1|0|9||00|
24|Column|2|0|10||00|
25|Ne|10|33|9|collseq(BINARY)|6b|
26|Column|0|1|5||00|
27|RealAffinity|5|0|0||00|
28|Integer|9|6|0||00|
29|Function|2|5|11|round(2)|02|
30|Ne|1|33|11||6a|
31|Column|0|0|13||00|
32|ResultRow|13|1|0||00|
33|Next|2|20|0||00|
34|Next|1|9|0||01|
35|Close|1|0|0||00|
36|Close|0|0|0||00|
37|Close|2|0|0||00|
38|Halt|0|0|0||00|
39|Transaction|0|0|0||00|
40|VerifyCookie|0|31|0||00|
41|TableLock|0|33|0|spectrum|00|
42|TableLock|0|39|0|MSMS_precursor|00|
43|Goto|0|5|0||00|

而 iostat 返回：

io-bash-3.2$ iostat
Linux 2.6.18-194.26.1.el5 (ningal.cluster.lifesci.ac.uk)         06/04/2012

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           14.35    0.00    0.30    0.01    0.00   85.34

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               1.16         4.55        17.22    1520566    5752802
sda1              0.00         0.02         0.00       5074         34
sda2              1.16         4.53        17.22    1515184    5752768
sdb               0.00         0.02         0.00       5108          0
dm-0              2.29         3.88        16.70    1297226    5579336
dm-1              0.00         0.00         0.00        928          0
dm-2              0.11         0.65         0.52     216106     173432

更新2

我将数据库迁移到了MySQL，这里的查询仅需要约0.001秒， 尽管对于我执行的所有其他查询来说，它实际上比sqlite慢（我为sqlite进行了优化，因此可能或可能不会让人感到惊讶）。

- Niek de Klein

---

你是否在Python和SQLite shell中使用相同版本的sqlite库？通常，Python pysqlite版本非常古老。 - schlenk

你能验证一下 EXPLAIN 分析对于每个查询是否相同，无论是在 Python 中还是在命令行工具中？（你可能需要编写一些自定义的 Python 代码来进行调试。） - ghoti

@ghoti 我知道这是同一个查询，因为我直接从 Python 查询中复制了查询，只是删除了 self.cursor.execute（''和+''）。 - Niek de Klein

@Donal Fellows 是的，它是同一个数据库。 - Niek de Klein

@ghoti，我更新了我的问题，并附上了EXPLAIN和EXPLAIN QUERY PLAN的结果。 - Niek de Klein

Answer 1

如我在您之前提出的问题的回答中提到的那样，您是否尝试过给sqlite模块apsw一个机会？从网站上看到的信息：

APSW是SQLite嵌入式关系数据库引擎的Python包装器。与其他包装器（如pysqlite）不同，它专注于成为SQLite API到Python的最小层次的翻译。文档有一节介绍APSW和pysqlite之间的区别。

我自己尝试了一下，似乎确实更好地反映了SQL语句由“真正”的Sqlite（即客户端或C库）执行的方式。

- user610650

---

我还没有尝试过这个，因为我找不到如何安装它来替代已经存在的合并版本，而不是使用默认安装的版本或使用-fetch下载一个新版本。 - Niek de Klein

我是APSW的作者。如果您想使用现有的SQLite库，则在构建时只需省略--fetch参数即可。这在http://apidoc.apsw.googlecode.com/hg/build.html#finding-sqlite-3中有记录。 - Roger Binns

Answer 2

SQLite和Python存在性能问题。阅读此线程以获取更多信息。那里有一些建议-尝试它们，可能会起作用-例如在连接字段上添加索引或使用pysqlite。

http://www.mail-archive.com/[e-mail]/msg253067.html

- kjp

---

我正在使用pysqlite，并且在连接操作中使用了索引。此外，它在通过Python连接MySQL和在SQLite命令行中使用时都能快速工作。 - Niek de Klein

你在spectrum_spectrum_id上有一个索引，但我没有看到spectrum表上的spectrum_id有一个。这个问题是特定于Python+SQLite的，所以它在SQLite命令行中运行得很快是可以的。另外，你使用的pysqlite版本是哪个？ - kjp

我在spectrum表的spectrum_id上有一个索引。我的pysqlite版本是2.6.3。 - Niek de Klein