Changes between Initial Version and Version 1 of Phase4_Performance_Analysis

Timestamp:

05/09/26 19:44:37 (2 weeks ago)

Author:

226052

Comment:

--

Phase4_Performance_Analysis

@@ +1 @@
+= Мерење на перформанси
+
+Извршена е анализа на перформансите на базата film_rental во PostgreSQL. Се споредуваат неоптимизирани табели и оптимизирани табели:
+* rental_copy - непартиционирана табела, користена како почетна состојба за споредба;
+* rental - партиционирана табела според rental_date, со дополнителни индекси;
+* payment_copy - непартиционирана табела за плаќања;
+* payment - партиционирана табела според payment_date, со индекс за датум и вработен.
+За оваа анализа беше користено pgbench. Секој тест се извршува со повеќе паралелни клиенти, при што се мерат просечна латентност и TPS, односно број на трансакции во секунда. Во сите тестови бројот на неуспешни трансакции беше 0.
+
+
+Пример за извршување на benchmark тест:
+
+pgbench -n -U admin -d film_rental -c 10 -j 2 -T 60 -f /tmp/movie_recommendation_rental_copy.sql
+
+Каде:
+-n означува дека pgbench не ја иницијализира стандардната pgbench шема;
+-U admin е корисникот со кој се поврзува кон базата;
+-d film_rental е базата врз која се извршува тестот;
+-c 10 означува 10 паралелни клиенти;
+-j 2 означува 2 работни нишки;
+-T 60 означува времетраење од 60 секунди;
+-f ја задава SQL датотеката што се извршува.
+
+Истата команда се повторуваше со различен број клиенти, на пример 10, 20, 30 и 50, и со SQL датотеки за неоптимизираната и оптимизираната верзија.
+
+== Детални резултати
+
+=== Q1 - Movie Recommendation
+Прашалникот прави препорака на филмови за конкретен корисник. Се анализираат претходните изнајмувања, омилените категории, рејтинзите и популарноста на филмовите во 2024 година.
+
+{{{
+WITH customer_history AS (
+    SELECT DISTINCT
+        f.film_id,
+        f.rating,
+        fc.category_id
+    FROM rental_copy r
+    JOIN inventory i ON r.inventory_id = i.inventory_id
+    JOIN film f ON i.film_id = f.film_id
+    JOIN film_category fc ON f.film_id = fc.film_id
+    WHERE r.customer_id = 101
+),
+preferred_categories AS (
+    SELECT
+        category_id,
+        COUNT(*) AS category_score
+    FROM customer_history
+    GROUP BY category_id
+),
+candidate_films AS (
+    SELECT
+        f.film_id,
+        f.title,
+        f.rating,
+        fc.category_id,
+        pc.category_score
+    FROM film f
+    JOIN film_category fc ON f.film_id = fc.film_id
+    JOIN preferred_categories pc ON fc.category_id = pc.category_id
+    WHERE NOT EXISTS (
+        SELECT 1
+        FROM customer_history ch
+        WHERE ch.film_id = f.film_id
+    )
+),
+film_popularity AS (
+    SELECT
+        i.film_id,
+        COUNT(r.rental_id) AS rental_count
+    FROM rental_copy r
+    JOIN inventory i ON r.inventory_id = i.inventory_id
+    WHERE r.rental_date >= DATE '2024-01-01'
+      AND r.rental_date < DATE '2025-01-01'
+    GROUP BY i.film_id
+)
+SELECT
+    cf.film_id,
+    cf.title,
+    cf.rating,
+    c.name AS category,
+    cf.category_score,
+    COALESCE(fp.rental_count, 0) AS yearly_rentals,
+    (cf.category_score * 10 + COALESCE(fp.rental_count, 0)) AS recommendation_score
+FROM candidate_films cf
+JOIN category c ON cf.category_id = c.category_id
+LEFT JOIN film_popularity fp ON cf.film_id = fp.film_id
+WHERE cf.rating IN (
+    SELECT rating
+    FROM customer_history
+)
+ORDER BY recommendation_score DESC, cf.title
+LIMIT 10;
+}}}
+
+За оптимизираната верзија се користи истата логика, но наместо rental_copy се користи партиционираната табела rental.
+
+|| '''Clients''' || '''Threads''' || '''rental_copy latency''' || '''rental_copy TPS''' || '''rental latency''' || '''rental TPS''' ||
+|| 10            || 2             || 2235.603 ms               || 4.473                 || 1083.428 ms          || 9.230            ||
+|| 20            || 4             || 4036.198 ms               || 4.955                 || 2208.459 ms          || 9.056            ||
+|| 50            || 4             || 9487.494 ms               || 5.270                 || 5247.761 ms          || 9.528            ||
+
+Q1 покажува значително подобрување кај оптимизираната табела. Кај 10 клиенти латентноста е намалена за околу 51.5%, а TPS е зголемен за околу 106.3%. Кај 50 клиенти латентноста е намалена за околу 44.7%, а TPS е зголемен за околу 80.8%. Ова се случува бидејќи прашалникот користи customer_id, inventory_id и rental_date. Индексите помагаат при пребарување на историјата на корисникот, а партиционирањето помага при делот од прашалникот кој ја пресметува популарноста за 2024 година.
+
+=== Q2 - Popular Films 2024
+Прашалникот ги пресметува најизнајмуваните филмови во 2024 година. Се користи филтер по датум, спојување со inventory и film, групирање по филм и сортирање според бројот на изнајмувања.
+{{{
+SELECT
+    f.film_id,
+    f.title,
+    f.rating,
+    COUNT(r.rental_id) AS rental_count
+FROM rental_copy r
+JOIN inventory i ON r.inventory_id = i.inventory_id
+JOIN film f ON i.film_id = f.film_id
+WHERE r.rental_date >= DATE '2024-01-01'
+  AND r.rental_date < DATE '2025-01-01'
+GROUP BY f.film_id, f.title, f.rating
+ORDER BY rental_count DESC
+LIMIT 20;
+}}}
+
+За оптимизираната верзија се користи истата логика, но наместо rental_copy се користи партиционираната табела rental.
+
+|| '''Clients''' || '''rental_copy latency''' || '''rental_copy TPS''' || '''rental latency''' || '''rental TPS''' || '''Промена''' ||
+|| 10 || 2222.438 ms || 4.500 || 1628.696 ms || 6.140 || ~26.7% побрзо ||
+|| 20 || 4205.239 ms || 4.756 || 3828.958 ms || 5.223 || ~8.9% побрзо ||
+|| 30 || 6545.865 ms || 4.583 || 6648.265 ms || 4.512 || ~1.6% побавно ||
+|| 50 || 11077.453 ms || 4.514 || 11290.484 ms || 4.429 || ~1.9% побавно ||
+
+Q2 има мешани резултати. Кај 10 и 20 клиенти партиционираната табела е побрза, но кај 30 и 50 клиенти резултатот е речиси ист или малку полош.
+Ова покажува дека партиционирањето не секогаш гарантира подобрување. Кај овој прашалник, освен читање од rental, има и GROUP BY, ORDER BY и LIMIT. При поголем број паралелни клиенти главен трошок може да станат агрегацијата и сортирањето, а не само читањето на редови од табелата.
+
+
+Q3 - Monthly Revenue
+Прашалникот пресметува месечен приход по продавница за 2024 година. Се пресметуваат број на плаќања, вкупен приход, просечна, минимална и максимална уплата.
+
+{{{
+SELECT
+    date_trunc('month', p.payment_date) AS month,
+    s.store_id,
+    COUNT(p.payment_id) AS payments_count,
+    SUM(p.amount) AS total_revenue,
+    AVG(p.amount) AS avg_payment,
+    MIN(p.amount) AS min_payment,
+    MAX(p.amount) AS max_payment
+FROM payment_copy p
+JOIN staff s ON p.staff_id = s.staff_id
+WHERE p.payment_date >= DATE '2024-01-01'
+  AND p.payment_date < DATE '2025-01-01'
+GROUP BY date_trunc('month', p.payment_date), s.store_id
+ORDER BY month, s.store_id;
+}}}
+
+За оптимизираната верзија се користи истата логика, но наместо payment_copy се користи партиционираната табела payment.
+
+|| '''Clients''' || '''payment_copy latency''' || '''payment_copy TPS''' || '''payment latency''' || '''payment TPS''' || '''Промена''' ||
+|| 10 || 2627.300 ms || 3.806 || 1055.953 ms || 9.470 || ~59.8% побрзо ||
+|| 30 || 8545.741 ms || 3.511 || 3325.437 ms || 9.021 || ~61.1% побрзо ||
+|| 50 || 16138.221 ms || 3.098 || 5321.565 ms || 9.396 || ~67.0% побрзо ||
+
+Q3 покажува најстабилно подобрување кај аналитичките прашалници. Прашалникот пресметува месечен приход по продавница за 2024 година, а табелата payment е партиционирана според payment_date. Бидејќи условот користи временски интервал за 2024 година, PostgreSQL може да ја чита партицијата за 2024 година наместо целата табела. Затоа латентноста е намалена за околу 60-67%, а TPS е зголемен за околу 149-203%.
+
+=== Q4 - INSERT rental
+
+Овој тест мери внесување нови редови во табелата за изнајмувања. За benchmark редовите се користи посебен датум 2026-03-07 12:00:00, за да се разликуваат од оригиналните податоци.
+
+{{{
+INSERT INTO rental_copy (rental_date, inventory_id, customer_id, staff_id)
+VALUES (
+    TIMESTAMP '2026-03-07 12:00:00',
+    floor(random() * 50000 + 1)::int,
+    floor(random() * 1000000 + 1)::int,
+    floor(random() * 50 + 1)::int
+);
+}}}
+
+За оптимизираната верзија се користи истата логика, но внесувањето се прави во партиционираната табела rental.
+
+|| '''Clients''' || '''rental_copy latency''' || '''rental_copy TPS''' || '''rental latency''' || '''rental TPS''' || '''Промена''' ||
+|| 10 || 3.121 ms || 3204.533 || 2.855 ms || 3502.623 || ~8.5% побрзо ||
+|| 30 || 2.949 ms || 10173.818 || 3.147 ms || 9533.949 || ~6.7% побавно ||
+|| 50 || 6.756 ms || 7400.777 || 3.088 ms || 16190.222 || ~54.3% побрзо ||
+
+INSERT операциите имаат мешани резултати. Кај 10 и 50 клиенти партиционираната табела е побрза, но кај 30 клиенти непартиционираната табела има малку подобар резултат.
+Причината е тоа што при внесување PostgreSQL мора да одреди во која партиција припаѓа новиот ред и мора да ги ажурира индексите. Тоа додава мал overhead, но при поголемо конкурентно оптоварување партиционираната структура може да помогне затоа што записите се насочуваат кон конкретна партиција.
+
+=== Q5 - UPDATE rental
+Овој тест мери ажурирање на изнајмувања. Се избира еден ред со benchmark датумот и со return_date IS NULL, па потоа се поставува return_date = now().
+{{{
+UPDATE rental_copy
+SET return_date = now()
+WHERE rental_id = (
+    SELECT rental_id
+    FROM rental_copy
+    WHERE rental_date = TIMESTAMP '2026-03-07 12:00:00'
+      AND return_date IS NULL
+    LIMIT 1
+);
+}}}
+
+За оптимизираната верзија се користи истата логика, но ажурирањето се прави врз партиционираната табела rental.
+
+|| '''Clients''' || '''rental_copy latency''' || '''rental_copy TPS''' || '''rental latency''' || '''rental TPS''' || '''Промена''' ||
+|| 10 || 590.026 ms || 16.948 || 7.044 ms || 1419.709 || ~98.8% побрзо ||
+|| 30 || 1483.097 ms || 20.228 || 12.318 ms || 2435.437 || ~99.2% побрзо ||
+|| 50 || 2646.029 ms || 18.896 || 16.689 ms || 2996.003 || ~99.4% побрзо ||
+
+UPDATE операцијата покажува најголемо подобрување. Условот користи конкретен rental_date, па кај партиционираната табела PostgreSQL може да пребарува само во релевантната партиција. Кај rental_copy системот мора да пребарува низ голема непартиционирана табела. Затоа латентноста е многу поголема, а TPS е многу помал.
+
+== Вкупно подобрување
+Партиционираните табели и индексите покажуваат значително подобрување кај прашалници кои се временски ограничени и кај операции кои можат да се насочат кон конкретна партиција.
+Најголеми придобивки:
+
+* Q3 - Monthly Revenue: околу 64.5% просечно намалување на латентноста.
+* Q5 - UPDATE rental: околу 99.2% просечно намалување на латентноста.
+* Q1 - Movie Recommendation: околу 45.8% просечно намалување на латентноста.
+
+Мешани резултати:
+* Q2 - Popular Films има подобрување кај 10 и 20 клиенти, но мало влошување кај 30 и 50 клиенти.
+* Q4 - INSERT има подобрување кај 10 и 50 клиенти, но мало влошување кај 30 клиенти.
+
+Ова покажува дека оптимизацијата мора да се мери. Партиционирањето не е автоматско решение за сите прашалници. Најмногу помага кога WHERE условот ја користи колоната по која е направено партиционирањето.
+
+== Операции на читање со мешани резултати
+
+* Q1 - Movie Recommendation: јасно подобрување кај сите нивоа на конкурентност.
+* Q2 - Popular Films: подобро кај помал број клиенти, но речиси исто или малку полошо кај поголем број клиенти.
+* Q3 - Monthly Revenue: најдобар пример каде партиционирањето директно го намалува бројот на редови што треба да се читаат.
+
+Кај читањата кои содржат големи агрегации и сортирања, како Q2, главниот проблем не е секогаш табелата од која се чита. Понекогаш главниот трошок е обработката по читањето, односно GROUP BY и ORDER BY.
+
+== Операции на запишување
+
+* INSERT: просечно подобрување, но со варијации.
+* UPDATE: многу големо подобрување поради филтрирање по rental_date.
+
+Кај INSERT, партиционирањето може да додаде overhead затоа што секој ред мора да се насочи во соодветна партиција. Кај UPDATE, партиционирањето е многу корисно кога условот ја користи партициската колона, бидејќи пребарувањето се ограничува на мала партиција наместо на целата табела.
+
+== Заклучок
+За ова специфично работно оптоварување:
+
+* Партиционирањето е многу корисно за временски извештаи, особено кај payment и rental.
+* Индексите значително помагаат кај прашалници со WHERE услови и JOIN операции врз често користени колони.
+* Q3 и Q5 покажуваат најголемо подобрување, затоа што директно ја користат колоната по која е направено партиционирањето.
+* Q2 покажува дека партиционирањето не го решава секој проблем, бидејќи агрегацијата и сортирањето можат да станат главен bottleneck.
+* INSERT операциите треба да се мерат внимателно, бидејќи партиционирањето и индексите можат да додадат мал overhead.
+
+Најсоодветен пристап за оваа база е да се задржи партиционирањето по датум за големите трансакциски табели rental и payment, да се користат индекси само за најчестите филтри и спојувања, и редовно да се проверуваат резултатите со EXPLAIN ANALYZE и pgbench.
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+