הגדרות PRAGMA ב-SQLite: כוונון חיבורים לסביבת ייצור

PRAGMA היא הדרך לדבר עם המנוע

PRAGMA היא פקודה ייחודית ל-SQLite שקוראת או משנה את אופן הפעולה של המנוע. מריצים אותה כמו כל SQL אחר, אבל במקום לגעת בנתונים שלכם, היא נוגעת בתצורה של מסד הנתונים.

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

כשמריצים PRAGMA כשאילתה, היא מחזירה את הערך הנוכחי. כשמריצים אותה כהשמה, היא משנה את הערך:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

המודל המנטלי: רוב ה-PRAGMA חלות על חיבור בודד. פותחים חיבור חדש, וברירות המחדל חוזרות. לכן לקוד בסביבת ייצור יש בדרך כלל בלוק קטן של פקודות PRAGMA שרצות מיד אחרי שכל חיבור נוצר.

הבסיס לסביבת ייצור

אם אתם זוכרים רק חמש PRAGMA, זכרו את אלה:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;

SELECT 'configured' AS status;

זו ברירת מחדל הגיונית כמעט לכל אפליקציה שמשתמשת ב-SQLite כמאגר הראשי שלה. כדאי להבין כל אחת מהן בנפרד, ושאר העמוד עובר עליהן.

journal_mode = WAL

מצב היומן קובע איך SQLite הופכת כתיבות לעמידות. ברירת המחדל, DELETE, משתמשת ביומן rollback: כותבים חוסמים קוראים וקוראים חוסמים כותבים. זה בסדר לכלי שורת פקודה, וכואב לאפליקציית ווב.

WAL (Write-Ahead Logging) הופך את המצב. קוראים וכותבים לא חוסמים זה את זה: קוראים רואים תמונת מצב עקבית בזמן שכותב מבצע commit. עדיין יש כותב אחד בכל פעם, אבל הקריאות נשארות מהירות תחת עומס.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

כמה דברים שכדאי לדעת:

• journal_mode הוא קבוע: אחרי שמגדירים אותו, הוא נשאר כך עבור קובץ מסד הנתונים. לא צריך להגדיר אותו בכל חיבור, אבל זה גם לא מזיק.
• WAL יוצר שני קבצים נוספים לצד ה-.db: קובץ -wal וקובץ -shm. אל תמחקו אותם כשמסד הנתונים פתוח.
• WAL לא עובד טוב על מערכות קבצים ברשת (NFS, SMB). השאירו את מסד הנתונים בדיסק מקומי.

יש עמוד נפרד על מצב WAL ומקביליות שמעמיק יותר. בינתיים: הפעילו אותו.

synchronous = NORMAL

synchronous קובע באיזו אגרסיביות SQLite כותבת לדיסק. הפשרה היא בין עמידות למהירות.

• FULL (ברירת מחדל): כתיבה לדיסק אחרי כל commit. עמידות מקסימלית. איטי יותר.
• NORMAL: כתיבה לדיסק בנקודות ביקורת בטוחות. בטוח עם WAL. מהיר יותר.
• OFF: מערכת ההפעלה מחליטה. מהיר, אבל מסכן השחתה בהפסקת חשמל.

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

המספר בתוצאה (1) מתאים ל-NORMAL. במצב WAL, NORMAL היא ההגדרה המומלצת: לא מאבדים טרנזקציות שבוצעו בקריסה, ורק בהפסקת חשמל יש סיכון לאבד את האחרונות שבהן. לרוב האפליקציות זה האיזון הנכון.

אל תשתמשו ב-OFF אלא אם אתם ממלאים מסד נתונים חד-פעמי שאפשר ליצור מחדש מאפס.

foreign_keys = ON

זו מפילה אנשים. SQLite תומכת במפתחות זרים, אבל האכיפה כבויה כברירת מחדל, וזו הגדרה ברמת החיבור:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO books (title, author_id) VALUES ('Ghosts', 999);

עם foreign_keys = ON, ההכנסה האחרונה נכשלת: אין סופר עם id 999. בלי ה-PRAGMA, SQLite כותבת בשמחה את השורה היתומה, ואתם מגלים את הבלגן חודשים אחר כך.

הריצו PRAGMA foreign_keys = ON; כפקודה הראשונה בכל חיבור חדש. רוב ה-ORM עושים את זה אוטומטית, ואם אתם משתמשים בדרייבר הגולמי, זה עליכם.

busy_timeout = 5000

SQLite מאפשרת כותב אחד בכל פעם. אם חיבור שני מנסה לכתוב בזמן שהראשון באמצע טרנזקציה, הוא מקבל SQLITE_BUSY ומוותר מיד, כברירת מחדל.

busy_timeout אומר ל-SQLite לחכות ולנסות שוב במקום זה:

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

הערך הוא במילישניות. 5000 פירושו "חכו עד 5 שניות לנעילה לפני שמוותרים". בשילוב עם WAL, זה מעלים את רוב שגיאות ה-database is locked המיותרות באפליקציות מקביליות.

אם אתם מוצאים את עצמכם מעלים את זה מעל 30 שניות, הפתרון האמיתי הוא כנראה טרנזקציות קצרות יותר, לא המתנה ארוכה יותר.

cache_size

cache_size קובע כמה דפים של מסד הנתונים SQLite מחזיקה בזיכרון. יותר מטמון פירושו פחות קריאות מהדיסק, ולכן שאילתות מהירות יותר על נתונים חמים.

לערך יש שתי צורות:

• מספר חיובי: דפים. עם גודל הדף שבברירת המחדל, 4 KB, הערך 2000 הוא 8 MB.
• מספר שלילי: קיביבייטים. -20000 הוא 20 MB בלי קשר לגודל הדף.

PRAGMA cache_size = -20000;
PRAGMA cache_size;

הצורה השלילית קלה יותר להבנה: אתם אומרים "תנו לי 20 MB של מטמון" במקום לעשות חשבון עם גודל הדף. לאפליקציה קטנה, 20 עד 50 MB זה די והותר. לעומס עבודה עתיר קריאות על מסד נתונים גדול יותר, העלו את זה. כמו synchronous, גם cache_size חל על חיבור בודד.

mmap_size

קלט ופלט ממופה זיכרון מאפשר ל-SQLite לקרוא חלקים מקובץ מסד הנתונים ישירות ממטמון הדפים של מערכת ההפעלה, בלי העתקה. זה יכול להאיץ קריאות במסדי נתונים גדולים:

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

זה 256 MB. SQLite תמפה עד כמות כזו של מסד הנתונים לזיכרון אם יש מקום. מערכת ההפעלה מטפלת בדפדוף, כך שאתם לא באמת מקצים 256 MB מראש: אתם מאפשרים מיפוי של עד כמות כזו.

mmap_size מצטיין בעומסי עבודה עתירי קריאות. הוא גם לא מזיק במסדי נתונים קטנים. ברירות המחדל שמרניות, ולכן העלאה שלו היא בדרך כלל רווח.

PRAGMA optimize

מתכנן השאילתות משתמש בסטטיסטיקות כדי לבחור אינדקסים. סטטיסטיקות מיושנות פירושן תוכניות גרועות. PRAGMA optimize מעדכנת את הסטטיסטיקות האלה בזול:

PRAGMA optimize;

הדפוס המומלץ הוא להריץ אותה ממש לפני סגירה של חיבורים ארוכי חיים: כיבוי האפליקציה, סוף של handler שמחזיק חיבור לזמן מה. היא מהירה (בדרך כלל מילישניות) ועושה עבודה רק כשמשהו באמת צריך עדכון.

זה לא אותו דבר כמו ANALYZE, שבונה מחדש את כל הסטטיסטיקות. optimize היא הקרובה הקלה שאפשר להריץ לעתים קרובות.

קריאת כל ההגדרות

אם אתם רוצים לראות עם איזו תצורה חיבור מוגדר כרגע, שלפו את ה-PRAGMA בלי השמה:

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA foreign_keys;
PRAGMA busy_timeout;
PRAGMA cache_size;
PRAGMA page_size;
PRAGMA temp_store;

שימושי בדיבאג: כשמתחברים מדרייבר אחר ותוהים למה ההתנהגות השתנתה, זה כמעט תמיד הבדל ב-PRAGMA.

יש גם PRAGMA pragma_list;, שמציגה כל PRAGMA שהגרסה שלכם תומכת בה:

PRAGMA pragma_list;

לא משהו שצריך לשנן, אבל שימושי כשצריך.

הגדרות ששייכות ליצירה, לא לזמן ריצה

כמה PRAGMA מגדירות את קובץ מסד הנתונים עצמו ונכנסות לתוקף רק לפני שנוצרות טבלאות כלשהן:

• PRAGMA page_size = 8192;: גודל הדף בדיסק. ברירת המחדל היא 4096, וזה בסדר לרוב עומסי העבודה. דפים גדולים יותר עוזרים עם שורות גדולות.
• PRAGMA encoding = 'UTF-8';: קידוד הטקסט.

PRAGMA page_size = 8192;
PRAGMA encoding = 'UTF-8';
CREATE TABLE ...

אם משנים את page_size במסד נתונים קיים, צריך להריץ VACUUM כדי שזה ייכנס לתוקף. הגדירו את אלה פעם אחת, בזמן היצירה, ותשכחו מהם.

קטע אמיתי להגדרת חיבור

בקוד אפליקציה, זה יושב בדרך כלל במקום שפותח את החיבור. ברמה הרעיונית:

-- להריץ פעם אחת בכל חיבור חדש:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- להריץ מדי פעם, או לפני סגירה:
PRAGMA optimize;

temp_store = MEMORY שומר טבלאות ואינדקסים זמניים ב-RAM, מה שמאיץ שאילתות שצריכות למיין או לצבור בלי אינדקס.

זו כל רשימת הבדיקה לסביבת ייצור. חצי תריסר שורות, ו-SQLite עוברת מ"בסדר לפיתוח" ל"מתאימה לעומס עבודה אמיתי".

הבא: שגיאות נפוצות

גם עם PRAGMA טובות, תיתקלו בשגיאות הרגילות של SQLite: database is locked, disk I/O error, constraint failed. העמוד הבא מסביר מה כל אחת מהן באמת אומרת ואיך לתקן אותה.