我有一个大型复杂的神经科学数据集,我正试图将其组织到一个数据库中,以便与我的实验室进行并发和一致的访问。这是我第一次尝试使用 SQLite 进行小得多的实验,而且我已经咬得太多了。
包含神经记录的主表有大约 100 亿行。在用数据填充此表之前,我建立了一个复合主键(id, experiment_id)
,其中id
每一行都是唯一的。我这样做是为了可以按experiment_id
. 这在当时看来是个好主意。然而,一旦表被填充,在 phpMyAdmin 中添加额外的索引失败(错误弹出窗口指示新密钥已损坏)。
面对这个障碍,我找到了https://mariadb.com/kb/en/partition-maintenance/,它的第一个要点是“#1:在你知道它如何以及为什么会有帮助之前不要使用分区。 ”,然后是“表大小很少是性能问题”。因此,希望“重做”,我创建了一个具有相同列(减去该id
列)但没有分区方案和索引的新表并执行了:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
INSERT INTO tfconv_2 (experiment_id, electrode_id, trial_id, frequency_id, time_id, value_real, value_imag)
SELECT experiment_id, electrode_id, trial_id, frequency_id, time_id, value_real, value_imag
FROM tfconv;
COMMIT;
这运行了好几天(这并不奇怪,因为首先需要好几天才能将数据放入原始表中)。在 MariaDB 努力工作的同时,我可以在 phpMyAdmin 摘要页面中看到新表的行数在上升。监视我服务器上的资源,该进程似乎正在以一种常规的、系统的方式将数据复制到新表中。
然后,有一次我去检查它时,新表上的行数减少了。SHOW PROCESSLIST
透露:
Id User Host db Command Time State
10237 root localhost singlecell Killed 217191 Reset for next command
有了这个背景,我有三个问题:
- 为什么这个查询会失败?(错误日志文件在相关时间窗口内保持沉默。)
- 如果我再次尝试构建无分区表,您是否建议我做一些不同的事情?
- 有没有办法在原始分区表上设置索引,从而避免损坏?
您可能已经达到了由 控制的语句级超时
max_statement_time
。SHOW PROCESSLIST
输出显示状态的事实Killed
可能表明这是原因。它也可能是 MyPhpAdmin 中的超时或它用于连接到数据库的连接器。对于像这样的长时间运行的操作,与数据库的可靠连接是必不可少的。
另一种方法是完全消除网络。
screen
通过 SSH 在本地执行操作并使用类似保持终端活动的程序应该有助于避免由于客户端过早退出而取消操作的情况。另一种方法是定义一个新的一次性
EVENT
更改。这将消除所有通信故障,因为操作是在数据库内部完成的。如果要将数据复制到的表是空的,则可以在会话期间设置
autocommit=0
,unique_checks=0
和以在 InnoDB 中启用批量插入。如果未启用或将插入包装在显式事务中,foreign_key_checks=0
请记住COMMIT
您之后的事务。autocommit
mariadb.org 上的这篇博文描述了该功能的某些部分,并且使用足够新的 MariaDB 版本,这应该比“正常”插入快得多。MariaDB 服务器中的这个提交是实现这个批量加载的,它还给出了一个如何启用它的示例:
如果没有确切的错误,这不是一件容易估计的事情,但是根据您的研究,分区可能不会解决您的性能问题。