راه‌های تشخیص کوئری‌های کند در پستگرس PostgreSQL
در صورتی که از پایگاه داده پستگرس (Postgresql) استفاده می‌کنید، خوب است بدانید که چه گزینه‌هایی برای بررسی مشکلات عملکردی پستگرس و مشخص کردن این که روی سرور چه اتفاقی می‌افتد وجود دارد. پیدا کردن نقاط ضعف عملکردی و کوئری‌های کند دقیقا همان چیزی است که در این پست بررسی می‌کنیم.
روش‌های زیادی برای ارزیابی مشکلات عملکردی وجود دارند، با این حال ما از سه روش کلیدی برای ارزیابی سریع کوئری‌ها و برطرف کردن عملکرد بد سیستم پایگاه داده استفاده می‌کنیم که عبارت‌اند از:
- استفاده بهینه از لاگ کوئری‌های کند
- چک کردن دستورات اجرا با استفاده از دستور explain
- تکیه کردن بر اطلاعات تجمیع شده در pg_stat_statements
برای آنالیز کردن عملکرد پستگرس و پیدا کردن نقاط حساس از متدهای مختلفی استفاده می‌کنیم، هر کدام از این روش‌ها مزایا و معایب خاص خود را دارند که در این مطلب به تفصیل بررسی می‌کنیم.
استفاده کردن از لاگ کوئری‌های کند
استفاده از لاگ کوئری‌ها یک روش سنتی برای شناسایی کوئری‌های کند است. ایده کلی به این صورت است:
اگر یک کوئری بیش از زمان تعیین شده به طول بیانجامد، یک خط به لاگ ارسال میشود. با استفاده از این روش کوئری کند شناسایی شده و در نتیجه ادمین دیتابیس قادر است آن را به راحتی شناسایی کند.
در کانفیگ اولیه پستگرس این لاگ فعال نیست و باید آن را فعال کنید. در این جا چند انتخاب دارید، اگر می‌خواهید این لاگ را به صورت Global فعال کنید بایستی فایل postgresql.conf را به شکل زیر تغییر دهید:
log_min_duration_statement = 5000
با وارد کردن دستور بالا، پستگرس، کوئری‌هایی که بیشتر از 5 ثانیه طول بکشند را کوئری کند تشخیص داده و آن ها را به لاگ میفرستد. با انجام این تغییر در فایل ذکر شده، نیازی به ری‌استارت کردن سرویس نیز وجود ندارد و یک reload به صورت زیر کافی است:
1- postgres=# SELECT pg_reload_conf(); 2- pg_reload_conf 3- ---------------- 4- t 5- (1 row)
با ویرایش کردن postgresql.conf تغییر بر روی کل دیتابیس انجام خواهد شد که به نظر میرسد خیلی دقیق نباشد. اگر می‌خواهید دقت را بالا ببرید بایستی برای یک یوزر خاص یا یک دیتابیس خاص ویرایش زیر را انجام دهید:
1- postgres=# ALTER DATABASE test SET log_min_duration_statement = 5000; 2- ALTER DATABASE
Alter Database به ما اجازه می‌دهد که تغییرات را بر روی یک پایگاه داده خاص انجام بدهیم.
در این جا برای تست یک کوئری کد را اجرا می‌کنیم:
1- postgres=# \c test 2- You are now connected to database "test" as user "hs". 3- test=# SELECT pg_sleep(10); 4- pg_sleep 5- ---------- 6- 7- (1 row)
نتیجه به صورت لاگ زیر نشان داده میشود:
2018-08-20 08:19:28.151 CEST [22845] LOG: duration: 10010.353 ms statement: SELECT pg_sleep(10);
با استفاده از این روش می‌توانید بلافاصله از کوئری‌های کند با خبر شوید و هر زمان یکی از کوئری‌ها کند باشد متوجه آن خواهید شد. هرچند که مزیت این روش میتواند تبدیل به ضعف آن نیز بشود چرا که روش استفاده کردن از لاگ کوئری‌های کند به صورت تکی به دنبال کوئری ها می‌گردد ولی اگر عملکرد بد دیتابیس پستگرس توسط تعداد زیادی از کوئری‌های نه چندان کند ایجاد شده باشد چه میشود؟
برای مثال فرض کنید هر کوئری 10 ثانیه به طول می‌انجامد و ما آن را به عنوان کوئری کند شناسایی می‌کنیم؛ تا اینجا همه چیز خوب پیش می‌رود، ولی اگر 1 میلیون کوئری داشته باشیم که هرکدام 500 میلی ثانیه طول بکشند چه؟ تمام این‌ها هیچ‌وقت در لاگ کوئری‌های کند ظاهر نمی‌شوند چرا که به نظر سریع می‌رسند. در نتیجه اگر برای پیدا کردن کوئری‌های کند فقط به این روش متکی باشید احتمالا موفق نخواهید بود.
چک کردن برنامه‌های اجرایی ناپایدار
گاهی اوقات همه چیز در پایگاه داده خوب پیش می‌رود ولی هر از گاهی یک کوئری خراب میشود در نتیجه باید آن را پیدا کرده و دوباره به حالت نرمال باز گردانیم. یکی از راه‌های انجام این کار استفاده از ماژول auto_explain است.
ایده تقریبا شبیه به همان چیزی است که در روش قبل استفاده می‌شد: هر زمان کندی در سیستم اتفاق افتاد ورودی لاگ را ایجاد می‌کنیم. با استفاده از auto_explain تمام نقشه اجرایی (و نه فقط کوئری‌ها) در فایل لاگ نمایش داده می‌شوند. مثال زیر را در نظر بگیرید:
1- test=# CREATE TABLE t_demo AS 2- SELECT * FROM generate_series(1, 10000000) AS id; 3- SELECT 10000000 4- test=# CREATE INDEX idx_id ON t_demo (id); 5- CREATE INDEX 6- test=# ANALYZE; 7- ANALYZE
Table ای که در اینجا ساختیم 10 میلیون ردیف دارد. حال بیایید به کوئری زیر نگاه کنیم:
1- test=# explain SELECT * FROM t_demo WHERE id < 10; 2- QUERY PLAN 3- --------------------------------------------------------------------------- 4- Index Only Scan using idx_id on t_demo (cost=0.43..8.61 rows=10 width=4) 5- Index Cond: (id < 10) 6- (2 rows) 7- 8- test=# explain SELECT * FROM t_demo WHERE id < 1000000000; 9- QUERY PLAN 10- ------------------------------------------------------------------ 11- Seq Scan on t_demo (cost=0.00..169248.60 rows=10000048 width=4) 12- Filter: (id < 1000000000) 13- JIT: 14- Functions: 2 15- Inlining: false 16- Optimization: false 17- (6 rows)
کوئری‌ها تقریبا شبیه به هم هستند اما پستگرس از پلن اجرایی متفاوت‌تری استفاده می‌کند. اولین کوئری فقط تعداد معینی از ردیف‌ها را به دست می‌آورد بنابراین به دنبال اسکن به صورت index خواهد رفت. کوئری دوم تمام داده‌ها را بارگیری می‌کند و بنابراین اسکن متوالی را ترجیح می‌دهد. اگر چه به نظر میرسد که نمایش داده‌ها مشابه هستند اما زمان اجرا متفاوت خواهد بود. اولین کوئری در یک میلی ثانیه اجرا میشود در حالی که دومی ممکن است تا 1 ثانیه طول بکشد.
پیدا کردن یک کوئری که به هر دلیلی مدت زمان زیادی به طول بیانجامد دقیقا زمانی است که فرد میتواند از auto_explain استفاده کند. ایده کل به این صورت است که: اگر یک کوئری از یک آستانه خاص زمانی پیش تر رفت، Postgresql میتواند این پلن را به فایل لاگ ارسال کند.
مثال زیر را در نظر بگیرید:
1- test=# LOAD 'auto_explain'; 2- LOAD 3- test=# SET auto_explain.log_analyze TO on; 4- SET 5- test=# SET auto_explain.log_min_duration TO 500; 6- SET
دستور load ماژول auto_explain را در دیتابیس بارگذاری می‌کند. در یک سیستم تولیدی می‌توان از Postgresql.conf یا Alter Database/Alter Table نیز برای بارگذاری ماژول استفاده کرد. اگر می‌خواهید تغییر در postgresql.conf نیز انجام شود، خط زیر را به فایل کانفیگ اضافه کنید:
session_preload_libraries = 'auto_explain';
Session_preload_libraries اطمینان حاصل می‌کند که ماژول به طور پیش فرض بر روی تمام کانکشن‌های دیتابیس تنظیم شده باشد. دیگر نیازی به دستور Load نیست، بعد از ایجاد اولین تغییر می‌توانید کوئری زیر را اجرا کنید:
1- test=# SELECT count(*) FROM t_demo GROUP BY id % 2; 2- count 3- --------- 4- 5000000 5- 5000000 6- (2 rows)
کوئری به بیش از 500 میلی ثانیه احتیاج دارد و سپس در لاگ نمایش داده میشود:
1- 2018-08-20 09:51:59.056 CEST [23256] LOG: duration: 4280.595 ms plan: 2- Query Text: SELECT count(*) FROM t_demo GROUP BY id % 2; 3- GroupAggregate (cost=1605370.36..1805371.32 rows=10000048 width=12) 4- (actual time=3667.207..4280.582 rows=2 loops=1) 5- Group Key: ((id % 2)) 6- -> Sort (cost=1605370.36..1630370.48 rows=10000048 width=4) 7- (actual time=3057.351..3866.446 rows=10000000 loops=1) 8- Sort Key: ((id % 2)) 9- Sort Method: external merge Disk: 137000kB 10- -> Seq Scan on t_demo (cost=0.00..169248.60 rows=10000048 width=4) 11- (actual time=65.470..876.695 rows=10000000 loops=1)
مزیت این رویکرد این است که شما می‌توانید کوئری های کند را به راحتی شناسایی کنید و ببینید که چه زمان کوئری در مورد برنامه تصمیم اشتباهی می‌گیرد. با این حال جمع‌آوری اطلاعات هنوز مشکل است چرا که ممکن است میلیون‌ها کوئری در برنامه داشته باشید.
چک کردن Pg_Stat_Statements
روش سوم استفاده از pg_stat_statements است. ایده این روش گروه‌بندی کوئری‌های یکسانی است که فقط پارامترهای متفاوتی دارند. فعال کردن این ویژگی به شما کمک می‌کند که از همه اتفاقات سیستم باخبر شوید. برای فعال کردن pg_stat_statements خط زیر را به Postgresql.conf اضافه کرده وسیستم را ری‌استارت کنید:
1- shared_preload_libraries = 'pg_stat_statements'
سپس CREATE EXTENSION pg_stat_statements را در دیتابیس خود اجرا کنید و Postgresql برای شما یک نما ایجاد می‌کند.
1- test=# CREATE EXTENSION pg_stat_statements; 2- CREATE EXTENSION 3- test=# \d pg_stat_statements 4- View "public.pg_stat_statements" 5- Column | Type | Collation | Nullable | Default 6- ---------------------+------------------+-----------+----------+--------- 7- userid | oid | | | 8- dbid | oid | | | 9- queryid | bigint | | | 10-query | text | | | 11-calls | bigint | | | 12-total_time | double precision | | | 13-min_time | double precision | | | 14-max_time | double precision | | | 15-mean_time | double precision | | | 16-stddev_time | double precision | | | 17-rows | bigint | | | 18-shared_blks_hit | bigint | | | 19-shared_blks_read | bigint | | | 20-shared_blks_dirtied | bigint | | | 21-shared_blks_written | bigint | | | 22-local_blks_hit | bigint | | | 23-local_blks_read | bigint | | | 24-local_blks_dirtied | bigint | | | 25-local_blks_written | bigint | | | 26-temp_blks_read | bigint | | | 27-temp_blks_written | bigint | | | 28-blk_read_time | double precision | | | 29-blk_write_time | double precision | | |
این نما به ما می‌گوید که کدام کوئری چند بار اجرا شده است و در مورد کل زمان اجرای کوئری‌ها و همچنین در مورد توزیع زمان اجرا برای دسته‌های مختلف از کوئری‌ها به ما اطلاعات می‌دهد.
در ادامه می‌توانید داده های به دست آمده از pg_stat_statments را مورد آنالیز نیز قرار دهید.
مزیت این روش در این است که شما می‌توانید میلیون‌ها کوئری سریع را پیدا کنید. علاوه بر آن این روش در مورد رفتار I/O کوئری‌های نیز به شما اطلاعات می‌دهد. نکته منفی اینجا ست که دنبال کردن کوئری‌هایی که بیشتر مواقع سریع و بعضی اوقات کند هستند مشکل است.
نتیجه
در پایان بایستی بدانید که از چه تکنیکی، چه زمانی استفاده کنید تا بتوانید کوئری‌های آهسته را به خوبی تشخیص دهید. امیدواریم این مطلب به شما ایده کلی در زمینه ردیابی مشکلات عملکردی سیستم را داده باشد. در صورتی که از دیتابیس ابری پستگرس پنکیک استفاده می‌کنید، می‌توانید از داشبورد دیتابیس لاگ‌های پستگرس را مشاهده کنید.
دیدگاه خود را ثبت کنید
تمایل دارید در گفتگوها شرکت کنید؟در گفتگو ها شرکت کنید.