Python PeeWee操作SQLite偶现插入延迟过高问题排查求助

我正在开发一款程序，用于记录OpenCV读取的视频帧对应的GPS信息，采用PeeWee作为ORM框架操作SQLite数据库。正常情况下，数据库插入操作耗时<8ms，但偶尔会超过100ms，无法定位问题原因，寻求排查帮助。程序部署在Nvidia AGX Orin设备上，当执行到第10938次插入操作时，延迟达到了274ms。

import json
import cv2
from datetime import datetime,timedelta
import time
import sqlite3
import pynmea2
import uuid
from peewee import *
from loguru import logger
import os


db_another = SqliteDatabase('robu_another3.db',pragmas={"journal_mode": "wal","cache_size":-1024*64,"page_size":32768,"synchronous":"normal","temp_store":"memory"}, timeout=40)

class LandscapeFrameTest(Model):
    timestamp = DateTimeField(verbose_name='时间戳', null=False)
    class Meta:
        table_name = 'frame_records_test'
        database = db_another
        order_by = ('timestamp',)

happen = 0
happen_10 = 0
happen_20 = 0
happen_100 = 0
all_time = 0

cap = cv2.VideoCapture("/dev/video2", cv2.CAP_V4L)
image_width = 1920
image_height = 1080
cap.set(cv2.CAP_PROP_FRAME_WIDTH, image_width)
cap.set(cv2.CAP_PROP_FRAME_HEIGHT, image_height)
cap.set(cv2.CAP_PROP_FPS, 10)           #帧数
print('Opened: ',cap.isOpened())

db_another.drop_tables([LandscapeFrameTest])
db_another.create_tables([LandscapeFrameTest])

while True:
    ret, frame_read = cap.read()
    print(frame_read.shape)
    time_now = datetime.now()
    timestr_now_str = time_now.strftime('%Y-%m-%d %H:%M:%S.%f')
    print(timestr_now_str)
    frame_object = {"photo_time":timestr_now_str}
    start_p = time.time()
    f = LandscapeFrameTest(timestamp = frame_object['photo_time'])
    f.save(force_insert=True)
    end_p = time.time()
    framedb_cost = (end_p-start_p)*1000
    all_time = all_time+1
    logger.debug(db_another.get_primary_keys('frame_records_test'))
    if framedb_cost > 100:
        happen_100 = happen_100 +1
    elif framedb_cost > 20:
        happen_20 = happen_20 +1
    elif framedb_cost > 10:
        happen_10 = happen_10 +1
    logger.debug('Cost {} For {} ,Happen 100 {},Happen 20 {},Happen 10 {},After {}.'.format(framedb_cost,frame_object['photo_time'],happen_100,happen_20,happen_10,all_time))
    if framedb_cost > 100:
        break
    frame_nums = LandscapeFrameTest.select().count()
    logger.debug("Handle Frame Database  Frame All Count  {}".format(frame_nums))

2023-02-19 07:47:07.216 | DEBUG    | main::63 - Handle Frame Database  Frame All Count  10936
(1080, 1920, 3)
2023-02-19 07:47:07.311356
2023-02-19 07:47:07.312 | DEBUG    | main::52 - ['id']
2023-02-19 07:47:07.312 | DEBUG    | main::59 - Cost 0.9157657623291016 For 2023-02-19 07:47:07.311356 ,Happen 100 0,Happen 20 3,Happen 10 0,After 10937.
2023-02-19 07:47:07.313 | DEBUG    | main::63 - Handle Frame Database  Frame All Count  10937
(1080, 1920, 3)
2023-02-19 07:47:07.411241
2023-02-19 07:47:07.685 | DEBUG    | main::52 - ['id']
2023-02-19 07:47:07.686 | DEBUG    | main::59 - Cost 274.2321491241455 For 2023-02-19 07:47:07.411241 ,Happen 100 1,Happen 20 3,Happen 10 0,After 10938.

可能的原因 #

• SQLite WAL checkpoint 触发：WAL模式下，当WAL文件大小达到主数据库的70%时，SQLite会自动执行checkpoint操作，将WAL中的数据合并到主库，这个过程会短暂阻塞写入。
• 磁盘I/O波动：AGX Orin的存储设备（如eMMC）可能被后台进程占用带宽，导致写入延迟突增。
• ORM额外开销：PeeWee的对象创建、字段校验等逻辑，加上Python的垃圾回收，可能偶尔导致延迟升高。
• 数据库锁竞争：若有其他进程/线程访问该数据库文件，会引发锁等待，拖慢插入速度。
• 系统资源占用：高延迟发生时，AGX Orin的CPU、内存可能被其他进程抢占。

验证与优化步骤 #

1. 移除额外数据库查询：注释掉每次插入后的db_another.get_primary_keys和LandscapeFrameTest.select().count()，这两个操作会额外消耗数据库资源，可能放大延迟。
2. 对比原生SQL插入性能：替换PeeWee的插入代码为原生SQL，判断是否是ORM导致的问题：

   start_p = time.time()
   db_another.execute_sql("INSERT INTO frame_records_test (timestamp) VALUES (?)", (frame_object['photo_time'],))
   end_p = time.time()
   

3. 监控WAL文件与checkpoint：定期打印robu_another3.db-wal文件大小，确认高延迟是否发生在checkpoint触发节点；也可手动设置wal_autocheckpoint调整触发阈值，例如：

   db_another.execute_sql("PRAGMA wal_autocheckpoint=10000")
   

4. 系统资源监控：在延迟发生时，用iostat查看磁盘I/O负载，top查看CPU、内存占用情况，确认是否有后台进程干扰。
5. 开启SQLite性能追踪：添加pragmas={"trace": "profile"}到数据库连接，查看高延迟时的SQL执行细节。

内容的提问来源于stack exchange，提问作者Shawn