Parallel queries and transaction isolation levels

ב PostgreSQL 9.6 הופיעו שאילתא במקבילה (Parallel queries). זוהי פונקציונלית מאוד חזקה. מהות שאילתא במקבילה, היא בכך כי יש עכשיו ל planer יש את היכולת לפריד את שאילתא לתתי-שאילתות, לבצע אותם במקביל, ולאחר מכן לבצע אגרגציה עם התוצאות. לשם כך, ישנם מספר ישויות חדשים - Workers ו Gather. Workers מבצעים תת-שאילתות, Gather - משלב את התוצאות. עבור המספר המרבי של Workers קיים פרמטר max_parallel_workers_per_gather בקובץ postgresql.conf. זה לא הגיוני להגדיר ערך זה גבוה יותר מאשר מספר הליבות CPU הזמינות (CPU core).

מטרת מאמר זה הוא לחקור את התנהגות של PostgreSQL ברמות שונות של בידוד הטרנסקציה.

ניצור טבלת בדיקה:

CREATE TABLE test AS

SELECT generate_series AS id, random() AS num

FROM generate_series(1, 10000000);

Query returned successfully: 10000000 rows affected, 10.4 secs execution time.

ANALYZE test;

מגדירים ערך  max_parallel_workers_per_gather = 2

SET max_parallel_workers_per_gather TO 2;

בPostgreSQL  הפרמטר "READ COMMITTED" - ברירת המחדל של רמת הבידוד.

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

EXPLAIN (ANALYZE,BUFFERS,VERBOSE)

SELECT count(*) FROM test;

"Finalize Aggregate  (cost=107139.46..107139.47 rows=1 width=8) (actual time=685.295..685.295 rows=1 loops=1)"

"  Output: count(*)"

"  Buffers: shared hit=2276 read=51943 dirtied=22340 written=22284"

"  ->  Gather  (cost=107139.25..107139.46 rows=2 width=8) (actual time=684.373..685.289 rows=3 loops=1)"

"        Output: (PARTIAL count(*))"

"        Workers Planned: 2"

"        Workers Launched: 2"

"        Buffers: shared hit=2276 read=51943 dirtied=22340 written=22284"

"        ->  Partial Aggregate  (cost=106139.25..106139.26 rows=1 width=8) (actual time=665.056..665.056 rows=1 loops=3)"

"              Output: PARTIAL count(*)"

"              Buffers: shared hit=2112 read=51943 dirtied=22340 written=22284"

"              Worker 0: actual time=650.413..650.413 rows=1 loops=1"

"                Buffers: shared hit=680 read=13072 dirtied=5624 written=5601"

"              Worker 1: actual time=660.495..660.495 rows=1 loops=1"

"                Buffers: shared hit=374 read=15640 dirtied=6809 written=6797"

"              ->  Parallel Seq Scan on public.test  (cost=0.00..95722.40 rows=4166740 width=0) (actual time=0.020..400.515 rows=3333333 loops=3)"

"                    Buffers: shared hit=2112 read=51943 dirtied=22340 written=22284"

"                    Worker 0: actual time=0.020..382.334 rows=2544120 loops=1"

"                      Buffers: shared hit=680 read=13072 dirtied=5624 written=5601"

"                    Worker 1: actual time=0.018..389.476 rows=2962415 loops=1"

"                      Buffers: shared hit=374 read=15640 dirtied=6809 written=6797"

"Planning time: 0.046 ms"

"Execution time: 685.363 ms"

COMMIT;

כפי שאתם יכולים לראות PostgrSQL מבצע ספירה של מספר השורות בשני תהליכים.

מגדירים את רמת הבידוד "REPEATABLE READ"

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

EXPLAIN (ANALYZE,BUFFERS,VERBOSE)

SELECT count(*) FROM test;

"Finalize Aggregate  (cost=107139.46..107139.47 rows=1 width=8) (actual time=555.967..555.967 rows=1 loops=1)"

"  Output: count(*)"

"  Buffers: shared hit=2660 read=51559"

"  ->  Gather  (cost=107139.25..107139.46 rows=2 width=8) (actual time=555.776..555.962 rows=3 loops=1)"

"        Output: (PARTIAL count(*))"

"        Workers Planned: 2"

"        Workers Launched: 2"

"        Buffers: shared hit=2660 read=51559"

"        ->  Partial Aggregate  (cost=106139.25..106139.26 rows=1 width=8) (actual time=548.366..548.366 rows=1 loops=3)"

"              Output: PARTIAL count(*)"

"              Buffers: shared hit=2496 read=51559"

"              Worker 0: actual time=538.556..538.557 rows=1 loops=1"

"                Buffers: shared hit=769 read=12367"

"              Worker 1: actual time=550.884..550.884 rows=1 loops=1"

"                Buffers: shared hit=483 read=12985"

"              ->  Parallel Seq Scan on public.test  (cost=0.00..95722.40 rows=4166740 width=0) (actual time=0.045..322.132 rows=3333333 loops=3)"

"                    Buffers: shared hit=2496 read=51559"

"                    Worker 0: actual time=0.016..348.308 rows=2430160 loops=1"

"                      Buffers: shared hit=769 read=12367"

"                    Worker 1: actual time=0.014..296.150 rows=2491580 loops=1"

"                      Buffers: shared hit=483 read=12985"

"Planning time: 0.023 ms"

"Execution time: 556.867 ms"

COMMIT;

PostgreSQL גם משתמש בשאילתא במקבילה.

עכשיו את רמת הבידוד המחמירה ביותר "SERIALIZABLE"

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

EXPLAIN (ANALYZE,BUFFERS,VERBOSE)

SELECT count(*) FROM test;

"Aggregate  (cost=179057.19..179057.20 rows=1 width=8) (actual time=1079.089..1079.089 rows=1 loops=1)"

"  Output: count(*)"

"  Buffers: shared hit=2592 read=51463"

"  ->  Seq Scan on public.test  (cost=0.00..154056.75 rows=10000175 width=0) (actual time=0.045..619.160 rows=10000000 loops=1)"

"        Output: id, num"

"        Buffers: shared hit=2592 read=51463"

"Planning time: 0.019 ms"

"Execution time: 1079.106 ms"

COMMIT;

והפעם PostgreSQL לא השתמש בשאילתה במקבילה! למה? את התשובה לשאלה זו נמצאת בהערות ל'וד של planner.c:

We can’t use parallelism in serializable mode because the predicate locking code is not parallel-aware.  
It’s not catastrophic if someone tries to run a parallel plan in serializable mode; it just won’t get any workers and will run serially.  
But it seems like a good heuristic to assume that the same serialization level will be in effect at plan time and execution time, so don’t generate a parallel plan if we’re in serializable mode.

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