为什么 SQLite 在特定机器上如此缓慢（~2 q/s）？

php performance sqlite

2022-08-31 00:23:05

在我的一台服务器上（i7 Ivy Core，32 GB RAM，Debian 6 @ 64bit，PHP 5.4.10），我遇到了SQLite的极慢插入速度。以下测试程序报告仅 2.2 次插入/秒（插入 30 行为 14 秒）。

unlink("test.db");

$db = new PDO('sqlite:test.db');

$db->exec("CREATE TABLE test (dummy INT)");

$count = 30;

$t = microtime(true);
for ($i=0; $i<$count; $i++) {
  $db->exec("INSERT INTO test VALUES ($i)")
   or die("SQLite error: ".$db->errorInfo()[2]."\n");
}
$elapsed = microtime(true)-$t;
echo sprintf("%d inserts in %.3f secs (%.1f q/s)\n", 
  $count, $elapsed, $count/$elapsed);

输出：

$ time php test.php
30 inserts in 13.911 secs (2.2 q/s)

real    0m14.634s
user    0m0.004s
sys     0m0.016s

我知道我可以通过包装/围绕插入语句（这给了我200000 q / s）来加速这一点，但即使没有事务，这应该更快。在其他（较旧的）机器（相同的PHP版本）上，我达到300多个查询/秒，没有显式事务。BEGINEND

这可能是什么原因？我是否必须调整 Sqlite 或 O/S？

答案 1

我已经在Linux 64bit机器上进行了类似的测试，以了解SQLite3需要时间的位置。strace -C -tt

% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 99.03    0.004000          32       124           fsync
  0.64    0.000026           0       222           mprotect
  0.32    0.000013           0       216           munmap

明显的延迟在函数中，即：fsync

配置
取决于常规磁盘 I/O（请查看，iotopiostat)
很大程度上依赖于IOSS（因此，文件系统和磁盘分配 - 您可能会在ext3上获得一个值，在xfs上获得另一个值，在btrfs上获得第三个值）
当然，间接地取决于底层硬件及其怪癖或调优。

通过关闭同步，我的 SQLite3 性能提高了大约三千倍：

$db = new PDO('sqlite:test.db');

$db->exec('pragma synchronous = off;');

我在两台非常相似的机器上也有两个不同的值（一台有ext4，另一台XFS，但我不确定这是主要原因 - 它们的负载配置文件也不同）。

顺便说一句，使用预准备语句在最快级别（从45k到110k INSERT，每批3000次，因为以该速度30 INSER必然给出虚假时序）的执行速度大约翻了一番，并将最低速度从大约6提高到大约150。

因此，这（使用预准备语句）可能是一个很好的解决方案，可以在不接触文件同步的情况下改进重复操作，即，同时仍然明确确保数据风险级别保持不变。之后，我会尝试事务或fsync（甚至可能是内存日志），具体取决于数据中断的风险和价值。

在从头开始设计系统时，在不同的FS上进行一些测试肯定是可取的。

在不同文件系统（同一台机器）上进行测试

ext4 (acl,user_xattr,data=order)         5.5 queries/s
using transactions                       170 queries/s
disabling fsync                        16000 queries/s
using transactions and disabling fsync 47200 queries/s

在临时文件系统上，很便宜，因此关闭它几乎没有什么好处。大部分时间都花在保护上，所以交易是关键。fsync

tmpfs                                  13700 queries/s
disabling fsync                        15350 queries/s
enabling transactions                  47900 queries/s
using transactions and disabling fsync 48200 queries/s

当然，必须考虑适当的数据组织和索引，对于大型数据集，很可能更重要。

更新：为了提高性能，还可以将SQLite日志放入内存中pragma journal_mode=MEMORY;

另外，你可以告诉 ext3/4 不要费心在 SQLite 数据库上更新一次（不过，这在很大程度上取决于实现）。您可以尝试添加到数据库所在的文件系统中，如果它有效，则可以将其放入（您也可以使用而不是更极端的：noatime/etc/fstabrelatimenoatime

sudo mount /var -o remount,noatime

答案 2