Changes between Version 1 and Version 2 of Monitoring


Ignore:
Timestamp:
05/17/26 22:46:46 (9 days ago)
Author:
213192
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Monitoring

    v1 v2  
    11=== Мониторинг
    22
    3 == Query Latency Distribution
     3== Benchmark Report – Оптимизација на индекси и query-и во PostgreSQL
    44
    5 Просечниот execution time не ја претставува реалната состојба во системот. На пример 990 прашалници имаат време на извршување 1мс, додека 10 имаат 5000мс. Просекот е 50мс, но може да видиме дека тие 10 прашалници се outliers. Токму овие 10 спори прашалници можат да го нарушат корисничкото искуство.
     5Целта на оваа анализа е да се испита влијанието на индексите, query оптимизацијата, scan стратегиите, join операциите и sorting механизмите врз перформансите на PostgreSQL при извршување на комплексни аналитички SQL query-иња.
    66
    7 Пример за брз прашалник:
     7За секој query беа направени мерења пред оптимизација и мерења по оптимизација. Анализата е направена со: EXPLAIN ANALYZE; EXPLAIN (ANALYZE, BUFFERS).
     8
     9Q1 - Анализа на player valuations
     10
    811{{{
    9 SELECT * FROM players WHERE player_id = 10;
     12EXPLAIN (ANALYZE, BUFFERS)
     13SELECT *
     14FROM (
     15    SELECT
     16        p.player_id,
     17        p.last_name,
     18        pv.market_value_in_eur,
     19        ROW_NUMBER() OVER (
     20            PARTITION BY p.player_id
     21            ORDER BY pv.date DESC
     22        ) AS rn,
     23        AVG(pv.market_value_in_eur) OVER (
     24            PARTITION BY p.player_id
     25        ) AS avg_value
     26    FROM players p
     27    JOIN player_valuations pv
     28        ON p.player_id = pv.player_id
     29    WHERE pv.market_value_in_eur > 1000000
     30) t
     31WHERE rn = 1
     32ORDER BY avg_value DESC
     33LIMIT 10000;
    1034}}}
    1135
    12 Пример за спор прашалник:
     36Пред оптимизацијата, прашалникот ги дава следниве резултати: 273мс време на извршување, Seq Scan, процесирани редици 131462, одстранети редици 339478, Sort Type: External Merge. Главните проблеми се тоа што има Full Sequential Scan на player_valuations и скапо филтрирање.
     37
     38Ги додаваме следниве индекси:
     39
    1340{{{
    14 SELECT
    15     p.player_name,
    16     COUNT(t.transfer_id),
    17     AVG(t.transfer_fee),
    18     SUM(m.attendance)
    19 FROM players p
    20 JOIN transfers t ON p.player_id = t.player_id
    21 JOIN matches m ON m.home_team_id = p.club_id
    22               OR m.away_team_id = p.club_id
    23 GROUP BY p.player_name;
     41CREATE INDEX idx_pv_player_date
     42ON player_valuations(player_id, date DESC);
     43
     44CREATE INDEX idx_pv_value
     45ON player_valuations(market_value_in_eur);
    2446}}}
    2547
    26 Исто така, имаме два типа на прашалници: еден што има 10000 повици на ден со време на извршување 2мс и друг со 50 повици на ден со 5 секунди време на извршување. Вториот прашалник претставува поголем проблем за нашиот систем бидејќи корисникот не сака да чека slow response.
     48По оптимизацијата добиваме 248мс време на извршување, Bitmap Heap Scan и повторно за sorting се користи External Merge. Најголемата промена е тоа што за филтерот market_value_in_eur > 1000000 се користи Bitmap Index Scan, наместо скенирање на цела табела, се читаат само потребните heap pages. Сепак и покрај индексите има многу редици што се процесираат и сортингот останува главен bottleneck.
    2749
    28 == Hot Data vs Cold Data Monitoring
     50Q2 – Анализа на трансфери
    2951
    30 Како што веќе видовме, системот располага со повеќе табели. Некои од нив се користат повеќе од останатите и може да се каже дека во реален систем 20% од податоците генерираат 80% од оптоварувањето.
    31 
    32 Класичен пример може да најдеме во табелата players. Од сите играчи кои се во табелата, најинтересни се познатите играчи - топ играчи. Токму затоа овие играчи ќе се најдат во повеќе прашалници од останати играчи и ќе се создаде row-level contention. Мониторинг резултат од гореспоменатиот проблем е тоа што играчот со id 10 ќе има 1200 queries/min, додека тој со id 187 ќе има 5 queries/min. Истите сценарија можат да се најдат и во други табели.
    33 
    34 Од друга страна, имаме и cold data која ретко се користи, нема locks и contention.
    35 
    36 == Пример
    37 
    38 Во Transfermarkt системот, „deadline day“ претставува екстремен случај на оптоварување, при што во краток временски интервал се извршуваат илјадници трансфер операции. За разлика од нормалниот режим, каде системот е претежно read-oriented, во овој период се јавува нагло зголемување на write операции и конкурентни трансакции.
    39 
    40 Во наредните примери ќе видиме и зашто ова е едно од најпредизивикувачките сценарија во системот.
    41 
    42 1. Complex Transfer Transaction
    43 
    44 Секој трансфер не е едноставен INSERT, туку составена трансакција:
    4552{{{
    46 BEGIN;
    47 
    48 INSERT INTO transfers(player_id, from_club_id, to_club_id, transfer_fee, season)
    49 VALUES (25, 3, 8, 12000000, '2025');
    50 
    51 UPDATE players
    52 SET market_value = market_value + 1500000,
    53     club_id = 8
    54 WHERE player_id = 25;
    55 
    56 UPDATE clubs
    57 SET budget = budget - 12000000
    58 WHERE club_id = 8;
    59 
    60 UPDATE clubs
    61 SET budget = budget + 12000000
    62 WHERE club_id = 3;
    63 
    64 COMMIT;
     53EXPLAIN ANALYZE
     54SELECT DISTINCT
     55    p.last_name,
     56    t.from_club_id,
     57    t.to_club_id,
     58    TO_CHAR(t.transfer_fee, '999,999,999') AS transfer_fee,
     59    cg.club_id
     60FROM transfers t
     61JOIN players p ON p.player_id = t.player_id
     62LEFT JOIN club_games cg ON cg.club_id = t.from_club_id
     63WHERE t.transfer_date >= '2021-01-01' AND t.transfer_fee IS NOT NULL
     64ORDER BY transfer_fee DESC 
     65LIMIT 10000;
    6566}}}
    6667
    67 Може да видиме дека имаме 4 write операции во 3 различни табели и сето ова е само за еден играч, додека на deadline day имаме илјадници трансфери. Исто така, системот влегува во contention режим поради тоа што сите трансакции таргетираат исти ентитети (player_id = 25, club_id = 8) и latency и queries/sec се зголемуваат драстично. Уште еден проблем со кој се соочуваме е transaction queueing, а тоа се случува бидејќи трансакцијата која се извршува зад себе остава други трансакции кои чекаат ресурси. Овој проблем е чест и во нормални услови, но на deadline day е многу посериозен.
     68Query-то прави join помеѓу transfers, players, club_games, филтрира трансфери по 2021 година, ги исклучува NULL transfer fees, сортира по transfer fee, враќа top 10,000 резултати.
    6869
    69 == Спојување на две табели во една
    70 
    71 Во последниот пример ќе направиме денормализација на системот, односно табелите players и player_valuations ќе ги споиме во една и ќе споредиме што е подобро.
     70ПРЕД ОПТИМИЗАЦИЈА прашалникот има време на извршување од 7.8с, чита 2.5 милиони редици, користи Seq Scan, Incremental Sort и Hash Join + Nested Loop. Главен проблем е што PostgreSQL прави SeqScan на transfers и players - прашалникот враќа голем дел од табелата. Потоа, ги додаваме следниве индекси:
    7271
    7372{{{
    74 SELECT
    75 p.last_name,
    76 pv.market_value_in_eur,
    77 pv.date
    78 FROM players p
    79 JOIN player_valuations pv
    80 ON p.player_id = pv.player_id
    81 WHERE p.player_id = 10
    82 ORDER BY pv.date DESC
    83 LIMIT 1;
     73CREATE INDEX idx_transfers_date
     74ON transfers(transfer_date);
     75
     76CREATE INDEX idx_transfers_player
     77ON transfers(player_id);
     78
     79CREATE INDEX idx_transfers_date_player
     80ON transfers(transfer_date, player_id);
    8481}}}
    8582
    86 Ова query има JOIN и ORDER BY + LIMIT.
     83и со мерењето ги добиваме следниве резултати: 3.5с време на извршување, што е значително подобрување, и повторно користење на Seq Scan. Сепак, главното прашање е зашто сеуште користиме Seq Scan. Кога враќаме 20% или повеќе од табелата, PostgreSQL знае дека е поевтино да користи Seq Scan од Index Scan.
     84
     85Q3 - Комплексен aggregation query
    8786
    8887{{{
    89 CREATE TABLE players_with_value AS
    90 SELECT
    91 p.player_id,
    92 p.last_name,
    93 p.current_club_id,
    94 pv.market_value_in_eur,
    95 pv.date
    96 FROM players p
    97 JOIN player_valuations pv
    98 ON p.player_id = pv.player_id;
    99 }}}
    100 {{{
    101 SELECT last_name, market_value_in_eur
    102 FROM players_with_value
    103 WHERE player_id = 10
    104 ORDER BY date DESC
    105 LIMIT 1;
     88EXPLAIN (ANALYZE, BUFFERS)
     89SELECT
     90    p.player_id,
     91    p.last_name,
     92    COUNT(a.game_id) AS appearances_count,
     93    MAX(pv.market_value_in_eur) AS max_market_value
     94FROM players p
     95LEFT JOIN appearances a
     96    ON p.player_id = a.player_id
     97LEFT JOIN player_valuations pv
     98    ON p.player_id = pv.player_id
     99WHERE EXISTS (
     100    SELECT 1
     101    FROM transfers t
     102    WHERE t.player_id = p.player_id
     103    AND t.transfer_fee > 5000000
     104)
     105GROUP BY
     106    p.player_id,
     107    p.last_name
     108HAVING COUNT(a.game_id) > 10
     109ORDER BY max_market_value DESC
     110LIMIT 8000;
    106111}}}
    107112
    108 Користејќи ја новокреираната табела извршивме query кој не користи JOIN и има побрз пристап. Сепак, целосна денормализација не е најдоброто решение. За најдобри резултати во нашиот систем ќе искористиме partial denormalization, односно ќе направиме нова табела со моменталната/последната вредност на играчот:
     113Главни проблеми се full scans на appearances и player_valuations, join спојува 9 милиони редици пред агрегацијата и скапа агрегација.
     114
     115Ги додаваме следниве индекси:
     116
    109117{{{
    110 CREATE TABLE players_current_value AS
    111 SELECT DISTINCT ON (player_id)
    112 player_id,
    113 market_value,
    114 date
    115 FROM player_valuations
    116 ORDER BY player_id, date DESC;
     118CREATE INDEX idx_transfers_player_fee
     119ON transfers(player_id, transfer_fee);
     120
     121CREATE INDEX idx_appearances_player_game
     122ON appearances(player_id, game_id);
     123
     124CREATE INDEX idx_player_valuations_player_value
     125ON player_valuations(player_id, market_value_in_eur);
    117126}}}
    118127
    119 Предности што ги добиваме со ова се тоа што нема непотребно скенирање на целата историја доколку корисникот не ја бара, помалку write операции и помал contention.
     128Ако ги споредиме резултатите пред и по оптимизацијата ќе видиме дека времето на извршување се намалило 4119мс -> 3325мс, за transfers access наместо Seq Scan користиме Index Only Scan и за appearances access наместо Seq Scan користиме Partial Index Only Scan. Најголемо подобрување имаме во EXISTS subquery-то поради индексот на transfers, додека appearances не е целосно оптимизирано и сеуште има heap fetches, односно PostgreSQL мора да оди во table pages. Најголем проблем останува join explosion.
     129
     130
     131
     132