PHP 8.2 وملف SQLite محلي: لماذا تتباطأ الكتابة بعد 40 ميغابايت

خادم Ubuntu 22.04، PHP 8.2 مع pdo_sqlite، وعامل CLI يستهلك طابورًا ويكتب النتائج في ملف SQLite محلي. الملف بدأ صغيرًا: 12 ميغابايت. عند تجاوز 40 ميغابايت بدأ الزمن لكل دفعة من 500 صف يقفز من 0.3 ثانية إلى 4-6 ثوانٍ، وظهر SQLSTATE[HY000]: General error: 5 database is locked مرة كل بضع دقائق. لا تغيير في الكود، ولا في الجهاز.

أول ما فعلته هو إيقاف المعاملات التلقائية. كان العامل ينفذ كل INSERT كمعاملة مستقلة، أي 500 عملية fsync على القرص. جمعت الدفعة في معاملة واحدة:

$pdo->exec('PRAGMA journal_mode = WAL');
$pdo->exec('PRAGMA synchronous = NORMAL');
$pdo->exec('PRAGMA busy_timeout = 5000');

$pdo->beginTransaction();
$stmt = $pdo->prepare('INSERT INTO events (id, payload, created_at) VALUES (?, ?, ?)');
foreach ($batch as $row) {
    $stmt->execute([$row['id'], $row['payload'], $row['created_at']]);
}
$pdo->commit();

هذا وحده أعاد الزمن إلى ~0.2 ثانية للدفعة. لكن database is locked لم يختفِ، لأن قارئًا منفصلًا (سكربت تقارير) كان يفتح الملف بوضع rollback journal ولا يغلق الاتصال. WAL سمح للقارئ والكاتب بالعمل معًا، وbusy_timeout أعطى الكاتب فرصة الانتظار بدل الفشل الفوري.

الجزء الأهم كان الفهرسة. كان الجدول بلا فهرس على created_at، وسكربت التقارير يمسح الجدول كاملًا كل دقيقة، فيحجب الكتابة أثناء المسح. أضفت:

CREATE INDEX idx_events_created_at ON events (created_at);

المسح تحول إلى بحث في الفهرس، وزمن التقرير نزل من ثوانٍ إلى أجزاء من الثانية. الثمن: كل INSERT أصبح يدفع تكلفة تحديث الفهرس، وحجم الملف كبر قليلًا. مع 500 صف في الدفعة كان هذا مقبولًا، لكنه ليس مجانيًا.

حدود ما رأيته: WAL ينفع مع قارئ واحد أو قليلين على نفس الجهاز، وليس حلًا لخادم ويب متعدد العمليات يكتب بكثافة. أيضًا synchronous = NORMAL يعني أن انقطاع الكهرباء قد يفقد آخر معاملة، وهذا مقبول هنا لأن البيانات قابلة لإعادة البناء من الطابور، وغير مقبول في دفتر مالي. وbusy_timeout يخفي الانتظار ولا يلغيه؛ إذا تجاوز الكاتب المهلة سيعود الخطأ. جربت رفع المهلة إلى 30 ثانية فاختفت الأخطاء لكن زمن الاستجابة تدهور بصمت، وهذا أسوأ من خطأ ظاهر.

السؤال الذي لم أحسمه: هل الأفضل إبقاء SQLite مع WAL وفهرس واحد، أم نقل البيانات إلى PostgreSQL منذ البداية بدل معالجة هذا النمو التدريجي؟