Setahun yang lalu, saya ditugaskan untuk menyelesaikan isu prestasi dalam sistem kritikal untuk syarikat tempat saya bekerja. Ia adalah cabaran yang sukar, malam tanpa tidur dan lebih banyak lagi rambut gugur, Bahagian belakang menggunakan PostgreSQL, dan selepas banyak usaha dan menggali, penyelesaiannya ternyata semudah satu baris:
ALTER USER foo SET work_mem='32MB';
Sekarang, sejujurnya, ini mungkin atau mungkin tidak menyelesaikan isu prestasi anda dengan segera. Ia sangat bergantung pada corak pertanyaan anda dan beban kerja sistem anda. Walau bagaimanapun, jika anda seorang pembangun bahagian belakang, saya harap siaran ini menambah alat lain pada senjata anda untuk menangani masalah, terutamanya dengan PostgreSQL ?
Dalam siaran ini, kami akan mencipta senario untuk mensimulasikan kemerosotan prestasi dan meneroka beberapa alatan untuk menyiasat masalah, seperti EXPLAIN, k6 untuk ujian beban dan juga menyelam ke dalam kod sumber PostgreSQL. Saya juga akan berkongsi beberapa artikel untuk membantu anda menyelesaikan isu berkaitan.
Mari kita cipta sistem mudah untuk menganalisis prestasi pemain bola sepak. Buat masa ini, satu-satunya peraturan perniagaan ialah menjawab soalan ini:
SQL berikut mencipta model data kami dan mengisinya:
CREATE TABLE players (
player_id SERIAL PRIMARY KEY,
nationality TEXT,
age INT,
position TEXT
);
CREATE TABLE matches (
match_id SERIAL PRIMARY KEY,
match_date DATE,
home_team TEXT,
away_team TEXT
);
CREATE TABLE player_stats (
player_stat_id SERIAL PRIMARY KEY,
player_id INT REFERENCES players(player_id),
match_id INT REFERENCES matches(match_id),
goals INT,
assists INT,
minutes_played INT
);
-- Populate players with a range of nationalities, ages, and positions
INSERT INTO players (nationality, age, position)
SELECT
('Country' || (1 + random()*100)::int), -- 100 different nationalities
(18 + random()*20)::int, -- Ages between 18 and 38
(ARRAY['Forward', 'Midfielder', 'Defender', 'Goalkeeper'])[ceil(random()*4)::int]
FROM generate_series(1, 10000);
Skrip untuk memulakan dan mengisi pangkalan data tersedia dalam repositori github.
Ya, kami boleh mereka bentuk pangkalan data untuk meningkatkan prestasi sistem, tetapi matlamat utama di sini adalah untuk meneroka senario yang tidak dioptimumkan. Percayalah, anda mungkin akan menghadapi sistem seperti ini, di mana sama ada pilihan reka bentuk awal yang lemah atau pertumbuhan yang tidak dijangka memerlukan usaha yang ketara untuk meningkatkan prestasi.
Untuk mensimulasikan isu yang berkaitan dengan konfigurasi work_mem, mari buat pertanyaan untuk menjawab soalan ini: Siapakah 2000 pemain teratas yang paling banyak menyumbang kepada gol?
SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps JOIN players p ON ps.player_id = p.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000;
Baiklah, tetapi bagaimanakah kita boleh mengenal pasti kesesakan dalam pertanyaan ini? Seperti DBMS lain, PostgreSQL menyokong perintah EXPLAIN, yang membantu kami memahami setiap langkah yang dilaksanakan oleh perancang pertanyaan (dioptimumkan atau tidak).
Kami boleh menganalisis butiran seperti:
Anda boleh mengetahui lebih lanjut tentang perancang/pengoptimum PostgreSQL di sini:
Cakap murah, jadi mari kita selami contoh praktikal. Mula-mula, kami akan mengurangkan work_mem kepada nilai terkecilnya, iaitu 64kB, seperti yang ditakrifkan dalam kod sumber:
ALTER USER foo SET work_mem='32MB';
Seterusnya, mari analisa output arahan EXPLAIN:
CREATE TABLE players (
player_id SERIAL PRIMARY KEY,
nationality TEXT,
age INT,
position TEXT
);
CREATE TABLE matches (
match_id SERIAL PRIMARY KEY,
match_date DATE,
home_team TEXT,
away_team TEXT
);
CREATE TABLE player_stats (
player_stat_id SERIAL PRIMARY KEY,
player_id INT REFERENCES players(player_id),
match_id INT REFERENCES matches(match_id),
goals INT,
assists INT,
minutes_played INT
);
-- Populate players with a range of nationalities, ages, and positions
INSERT INTO players (nationality, age, position)
SELECT
('Country' || (1 + random()*100)::int), -- 100 different nationalities
(18 + random()*20)::int, -- Ages between 18 and 38
(ARRAY['Forward', 'Midfielder', 'Defender', 'Goalkeeper'])[ceil(random()*4)::int]
FROM generate_series(1, 10000);
Kita dapat lihat bahawa masa pelaksanaan ialah 82.718ms, dan Algoritma Isih yang digunakan ialah gabungan luaran. Algoritma ini bergantung pada cakera dan bukannya memori kerana data melebihi had 64kB work_mem.
Untuk makluman anda, modul tuplesort.c menandakan apabila algoritma Isih akan menggunakan cakera dengan menetapkan keadaan kepada SORTEDONTAPE pada baris ini. Interaksi cakera dikendalikan oleh modul logtape.c.
Jika anda seorang yang visual (seperti saya), terdapat alatan yang boleh membantu anda memahami output EXPLAIN, seperti https://explain.dalibo.com/. Di bawah ialah contoh yang menunjukkan nod dengan langkah Isih, termasuk butiran seperti Kaedah Isih: gabungan luaran dan Isih Ruang Digunakan: 2.2MB:
Bahagian "Statistik" amat berguna untuk menganalisis pertanyaan yang lebih kompleks, kerana ia menyediakan butiran masa pelaksanaan untuk setiap nod pertanyaan. Dalam contoh kami, ia menyerlahkan masa pelaksanaan yang mencurigakan tinggi—hampir 42ms—dalam salah satu nod Isih:
Seperti yang ditunjukkan oleh output EXPLAIN, salah satu sebab utama masalah prestasi ialah nod Isih menggunakan cakera. Kesan sampingan isu ini, terutamanya dalam sistem dengan beban kerja yang tinggi, adalah lonjakan dalam metrik Write I/O (Saya harap anda memantau perkara ini; jika tidak, semoga berjaya apabila anda memerlukannya!). Dan ya, walaupun pertanyaan baca sahaja boleh menyebabkan tulis pancang, kerana algoritma Isih menulis data ke fail sementara.
Apabila kami melaksanakan pertanyaan yang sama dengan work_mem=4MB (lalai dalam PostgreSQL), masa pelaksanaan berkurangan lebih 50%.
SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps JOIN players p ON ps.player_id = p.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000;
Dalam output EXPLAIN ini, salah satu nod Isih kini menggunakan algoritma dalam ingatan, heapsort. Untuk konteks, perancang memilih heapsort hanya apabila ia lebih murah untuk dilaksanakan daripada quicksort. Anda boleh menyelam lebih dalam ke dalam proses membuat keputusan dalam kod sumber PostgreSQL.
Selain itu, nod Isih kedua, yang sebelum ini menyumbang hampir 40ms masa pelaksanaan, hilang sepenuhnya daripada pelan pelaksanaan. Perubahan ini berlaku kerana perancang kini memilih HashJoin dan bukannya MergeJoin, kerana operasi cincang muat dalam ingatan, menggunakan kira-kira 480kB.
Untuk butiran lanjut tentang algoritma sertai, lihat artikel ini:
Work_mem lalai tidak selalu mencukupi untuk mengendalikan beban kerja sistem anda. Anda boleh melaraskan nilai ini pada peringkat pengguna menggunakan:
ALTER USER foo SET work_mem='32MB';
Nota: Jika anda menggunakan kumpulan sambungan atau pengumpul sambungan, adalah penting untuk mengitar semula sesi lama agar konfigurasi baharu berkuat kuasa.
Anda juga boleh mengawal konfigurasi ini pada peringkat transaksi pangkalan data. Mari jalankan API mudah untuk memahami dan mengukur kesan perubahan work_mem menggunakan ujian beban dengan k6:
k6-test.js
CREATE TABLE players (
player_id SERIAL PRIMARY KEY,
nationality TEXT,
age INT,
position TEXT
);
CREATE TABLE matches (
match_id SERIAL PRIMARY KEY,
match_date DATE,
home_team TEXT,
away_team TEXT
);
CREATE TABLE player_stats (
player_stat_id SERIAL PRIMARY KEY,
player_id INT REFERENCES players(player_id),
match_id INT REFERENCES matches(match_id),
goals INT,
assists INT,
minutes_played INT
);
-- Populate players with a range of nationalities, ages, and positions
INSERT INTO players (nationality, age, position)
SELECT
('Country' || (1 + random()*100)::int), -- 100 different nationalities
(18 + random()*20)::int, -- Ages between 18 and 38
(ARRAY['Forward', 'Midfielder', 'Defender', 'Goalkeeper'])[ceil(random()*4)::int]
FROM generate_series(1, 10000);
API telah dilaksanakan dalam Go dan mendedahkan dua titik akhir yang melaksanakan pertanyaan dengan konfigurasi work_mem yang berbeza:
main.go
SELECT p.player_id, SUM(ps.goals + ps.assists) AS total_score FROM player_stats ps JOIN players p ON ps.player_id = p.player_id GROUP BY p.player_id ORDER BY total_score DESC LIMIT 2000;
Di bawah ialah fail karang docker yang mengandungi semua kebergantungan yang diperlukan untuk menjalankan ujian beban:
docker-compose.yaml
/*
* workMem is forced to be at least 64KB, the current minimum valid value
* for the work_mem GUC. This is a defense against parallel sort callers
* that divide out memory among many workers in a way that leaves each
* with very little memory.
*/
state->allowedMem = Max(workMem, 64) * (int64) 1024;
Kami boleh menetapkan pembolehubah persekitaran ENDPOINT untuk mentakrifkan senario untuk diuji: /low-work-mem atau /optimized-work-mem. Jalankan ujian menggunakan: docker compose up --abort-on-container-exit. Untuk contoh ini, saya menggunakan Docker versi 20.10.22.
Titik TAMAT Ujian: /low-work-mem - work_mem=64kB
BEGIN; -- 1. Initialize a transaction.
SET LOCAL work_mem = '64kB'; -- 2. Change work_mem at transaction level, another running transactions at the same session will have the default value(4MB).
SHOW work_mem; -- 3. Check the modified work_mem value.
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) -- 4. Run explain with options that help us to analyses and indetifies bottlenecks.
SELECT
p.player_id,
SUM(ps.goals + ps.assists) AS total_score
FROM
player_stats ps
INNER JOIN
players p ON p.player_id = ps.player_id
GROUP BY
p.player_id
ORDER BY
total_score DESC
LIMIT 2000;
--
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=18978.96..18983.96 rows=2000 width=12) (actual time=81.589..81.840 rows=2000 loops=1) |
Output: p.player_id, (sum((ps.goals + ps.assists))) |
Buffers: shared hit=667, temp read=860 written=977 |
-> Sort (cost=18978.96..19003.96 rows=10000 width=12) (actual time=81.587..81.724 rows=2000 loops=1) |
Output: p.player_id, (sum((ps.goals + ps.assists))) |
Sort Key: (sum((ps.goals + ps.assists))) DESC |
Sort Method: external merge Disk: 280kB |
Buffers: shared hit=667, temp read=860 written=977 |
-> GroupAggregate (cost=15076.66..17971.58 rows=10000 width=12) (actual time=40.293..79.264 rows=9998 loops=1) |
Output: p.player_id, sum((ps.goals + ps.assists)) |
Group Key: p.player_id |
Buffers: shared hit=667, temp read=816 written=900 |
-> Merge Join (cost=15076.66..17121.58 rows=100000 width=12) (actual time=40.281..71.313 rows=100000 loops=1) |
Output: p.player_id, ps.goals, ps.assists |
Merge Cond: (p.player_id = ps.player_id) |
Buffers: shared hit=667, temp read=816 written=900 |
-> Index Only Scan using players_pkey on public.players p (cost=0.29..270.29 rows=10000 width=4) (actual time=0.025..1.014 rows=10000 loops=1)|
Output: p.player_id |
Heap Fetches: 0 |
Buffers: shared hit=30 |
-> Materialize (cost=15076.32..15576.32 rows=100000 width=12) (actual time=40.250..57.942 rows=100000 loops=1) |
Output: ps.goals, ps.assists, ps.player_id |
Buffers: shared hit=637, temp read=816 written=900 |
-> Sort (cost=15076.32..15326.32 rows=100000 width=12) (actual time=40.247..49.339 rows=100000 loops=1) |
Output: ps.goals, ps.assists, ps.player_id |
Sort Key: ps.player_id |
Sort Method: external merge Disk: 2208kB |
Buffers: shared hit=637, temp read=816 written=900 |
-> Seq Scan on public.player_stats ps (cost=0.00..1637.00 rows=100000 width=12) (actual time=0.011..8.378 rows=100000 loops=1) |
Output: ps.goals, ps.assists, ps.player_id |
Buffers: shared hit=637 |
Planning: |
Buffers: shared hit=6 |
Planning Time: 0.309 ms |
Execution Time: 82.718 ms |
COMMIT; -- 5. You can also execute a ROLLBACK, in case you want to analyze queries like INSERT, UPDATE and DELETE.
TUTUP UJIAN: /optimized-work-mem - work_mem=4MB
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT
p.player_id,
SUM(ps.goals + ps.assists) AS total_score
FROM
player_stats ps
INNER JOIN
players p ON p.player_id = ps.player_id
GROUP BY
p.player_id
ORDER BY
total_score DESC
LIMIT 2000;
--
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=3646.90..3651.90 rows=2000 width=12) (actual time=41.672..41.871 rows=2000 loops=1) |
Output: p.player_id, (sum((ps.goals + ps.assists))) |
Buffers: shared hit=711 |
-> Sort (cost=3646.90..3671.90 rows=10000 width=12) (actual time=41.670..41.758 rows=2000 loops=1) |
Output: p.player_id, (sum((ps.goals + ps.assists))) |
Sort Key: (sum((ps.goals + ps.assists))) DESC |
Sort Method: top-N heapsort Memory: 227kB |
Buffers: shared hit=711 |
-> HashAggregate (cost=2948.61..3048.61 rows=10000 width=12) (actual time=38.760..40.073 rows=9998 loops=1) |
Output: p.player_id, sum((ps.goals + ps.assists)) |
Group Key: p.player_id |
Batches: 1 Memory Usage: 1169kB |
Buffers: shared hit=711 |
-> Hash Join (cost=299.00..2198.61 rows=100000 width=12) (actual time=2.322..24.273 rows=100000 loops=1) |
Output: p.player_id, ps.goals, ps.assists |
Inner Unique: true |
Hash Cond: (ps.player_id = p.player_id) |
Buffers: shared hit=711 |
-> Seq Scan on public.player_stats ps (cost=0.00..1637.00 rows=100000 width=12) (actual time=0.008..4.831 rows=100000 loops=1)|
Output: ps.player_stat_id, ps.player_id, ps.match_id, ps.goals, ps.assists, ps.minutes_played |
Buffers: shared hit=637 |
-> Hash (cost=174.00..174.00 rows=10000 width=4) (actual time=2.298..2.299 rows=10000 loops=1) |
Output: p.player_id |
Buckets: 16384 Batches: 1 Memory Usage: 480kB |
Buffers: shared hit=74 |
-> Seq Scan on public.players p (cost=0.00..174.00 rows=10000 width=4) (actual time=0.004..0.944 rows=10000 loops=1) |
Output: p.player_id |
Buffers: shared hit=74 |
Planning: |
Buffers: shared hit=6 |
Planning Time: 0.236 ms |
Execution Time: 41.998 ms |
Hasilnya menunjukkan bahawa titik akhir dengan work_mem yang lebih tinggi mengatasi yang mempunyai konfigurasi yang lebih rendah. Kependaman p90 menurun lebih 43ms dan daya pemprosesan bertambah baik dengan ketara di bawah beban kerja ujian.
Jika metrik persentil adalah baharu kepada anda, saya syorkan anda mempelajari dan memahaminya. Metrik ini sangat membantu untuk membimbing analisis prestasi. Berikut ialah beberapa sumber untuk anda bermula:
Selepas bermimpi tentang masalah itu, bangun beberapa kali untuk mencuba penyelesaian baharu, dan akhirnya mendapati bahawa work_mem boleh membantu, cabaran seterusnya ialah memikirkan nilai yang sesuai untuk konfigurasi ini. ?
Nilai lalai 4MB untuk work_mem, seperti banyak tetapan PostgreSQL yang lain, adalah konservatif. Ini membolehkan PostgreSQL berjalan pada mesin yang lebih kecil dengan kuasa pengiraan yang terhad. Walau bagaimanapun, kita mesti berhati-hati untuk tidak merosakkan contoh PostgreSQL dengan ralat di luar ingatan. Satu pertanyaan, jika cukup kompleks, boleh menggunakan berbilang kali ganda memori yang ditentukan oleh work_mem, bergantung pada bilangan operasi seperti Isih, Gabungan Gabung, Gabungan Hash (dipengaruhi oleh hash_mem_multiplier), dan banyak lagi. Seperti yang dinyatakan dalam dokumentasi rasmi:
adalah perlu untuk mengingati fakta ini apabila memilih nilai. Operasi isih digunakan untuk ORDER BY, DISTINCT, dan cantuman cantuman. Jadual cincang digunakan dalam cantuman cincang, pengagregatan berasaskan cincang, nod ingatan dan pemprosesan subkueri IN berasaskan cincang.
Malangnya, tiada formula ajaib untuk menetapkan work_mem. Ia bergantung pada memori tersedia, beban kerja dan corak pertanyaan sistem anda. Pasukan TimescaleDB mempunyai alat untuk autotala dan topik itu dibincangkan secara meluas. Berikut ialah beberapa sumber yang sangat baik untuk membimbing anda:
Tetapi pada penghujung hari, IMHO, jawapannya ialah: UJIAN. UJIAN HARI INI. UJIAN ESOK. UJIAN SELAMANYA. Teruskan ujian sehingga anda menemui nilai yang boleh diterima untuk kes penggunaan anda yang meningkatkan prestasi pertanyaan tanpa meletupkan pangkalan data anda. ?
Atas ialah kandungan terperinci Penalaan Prestasi PostgreSQL: Kuasa work_mem. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!
Bagaimanakah cara saya menyediakan WeChat untuk memerlukan persetujuan saya apabila orang menambahkan saya ke kumpulan?
Penyelesaian kepada tetapan semula Sambungan
Apakah pelayan yang dikecualikan daripada pendaftaran?
Sebab mengapa akses tapak web menggesa ralat pelayan dalaman
Apakah perdagangan mata wang digital
Penggunaan kelas kalendar dalam java
Caj sewa pelayan
Perkhidmatan pecutan CDN tanpa pendaftaran
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
