افزایش کارایی خواندن از دیتابیس های Postgre

[vc_row][vc_column][vc_column_text]

مقدمه

در این مقاله به معرفی دیتاسنتر و راهکارهایی در جهت افزایش کارایی خواندن از دیتابیس های Postgres خواهیم پرداخت. در حال حاضر نرم‌ افزارهای بسیاری برای مدیریت پایگاه داده ساخته شده‌ اند که هر دارای یک سری مزایا و معایب هستند. برای مثال به منظور ذخیره‌ سازی داده ‌ها بر پایگاه پیشرفته‌ ای مانند Oracle نیاز به ذخیره سازی داده ‌ها به صورت فایل متنی داریم، که روند پیچیده، زمان ‌بر و طاقت فرسا است. امروزه پایگاه های داده و زبان SQL هستند که مانند قلب تپنده ‌ای اطلاعات را در جهان به جریان انداخته ‌اند.
به همین دلیل است که ارتقا امنیت و افزایش کارایی این سیستم پیچیده بسیار با اهمیت است. به همین منظور در حدود 40 سال قبل پروژه ‌ای جهت ایجاد یک سیستم مدیریت پایگاه داده جدید با نام PostgreSQL آغاز شد و در حال حاضر به یکی از پیشرفته‌ ترین پایگاه‌ های داده Open Source در جهان تبدیل شده است. این سیستم علاوه بر رقابت با پایگاه قدرتمندی مثل اوراکل، حتی MySQL را نیز به چالش می ‌کشد.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

معرفی اجمالی Postgre و ارزیابی آن

در اواسط دهه 1980 در دانشگاه برکلی، پروژه‌ ای تحت عنوان ingres در دست توسعه بود، در این میان بود که نرم افزار Postgre متولد شد. پایگاه داده PostgreSQL یک سیستم مدیریتی پایگاه داده شیء محور (Object Relational Database Management System) است. این نرم افزار در حال حاضر به عنوان برنامه ‌ای آزاد تلقی می‌ شود و مانند دیگر پروژه های Open Source، کمپانی خاصی مسئول توسعه آن نیست. در حال حاضر جامعه ‌ای از توسعه دهندگان و برنامه ‌نویس ‌ها به توسعه و بهینه سازی این نرم افزار اشتغال دارند.

این نرم‌ افزار مثل بسیاری از پایگاه‌ های داده دیگر، طیف گسترده ‌ای از انواع داده‌ ها را پشتیبانی می‌ کند. این داده‌ ها ممکن است آرایه ‌هایی با طول متغیر (تا یک گیگابایت)، آدرس های IPv4 و IPv6، بلوک های CIDR,XML و UUID باشند. همچنین کاربران می ‌توانند داده‌ های جدیدی را در سیستم به وجود آورند برای مثال داده های پیشرفته GIS از این انواع هستند.
تاکنون آزمایش ‌های بسیاری به منظور ارزیابی سرعت خواندن از دیتابیس های postgre و کارایی این نرم‌ افزار در دنیا صورت گرفته است. نخستین آزمایش استاندارد و در ابعاد گسترده در سال 2007 و با استفاده از سیستم Sun Java System Application Server 9.0 Platform Edition انجام گرفت. در این آزمایش PostgreSQL توانست به سرعتی معادل JOPS778 دست یابد. که از این نظر کاملا با مقدار سرعت JOPS874 که توسط Oracle10 به دست آمده بود قابل مقایسه است.
مقدار JOPS که مخفف عبارت jAppServer Operations Per Second است در آزمایشی به نام SPECjAppServer2004 به دست می ‌آید. در این سرور، برای هر JOPS هزینه ‌ای معادل هفتاد دلار مصرف می‌ شود و همانطور که مشاهده کردید PostgreSQL هزینه ‌ی به مراتب کمتری را نسبت به Oracle به سرور تحمیل می ‌کند.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

راهکارهایی برای افزایش کارایی خواندن از دیتابیس های Postgre

پیش از آن که به موضوع ارتقا کیفیت و سرعت خواندن از دیتابیس ‌های Postgre بپردازیم، لازم است که راه ‌هایی را برای یافتن نقاط ضعف به شما معرفی کنیم. این نقاط ضعف اغلب به کوئری‌ های کند مربوط می ‌شود. در ادامه و در بحث افزایش کارایی مباحث دیگری را نیز جهت بهبود عملکرد PostgreSQL عنوان خواهیم کرد.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

چگونه کوئری‌ های کند که عملکرد سیستم را ضعیف می ‌کنند، بیابیم؟

سه روش به منظور شناسایی کوئری‌ های کند وجود دارد که به ترتیب به آن‌ ها خواهیم پرداخت:

1. استفاده ازQuery log ها: در این روش شما برای سیستم تعریف می ‌کنید که در صورت طول کشیدن یک کوئری بیش از مدت از تعیین شده، یک خط به لاگ ارسال شود. ادمین دیتابیس با مشاهده این موضوع، متوجه وجود کوئری کند شده و آن را شناسایی می ‌کند. لاگ کوئری ‌ها در واقع گزارش عملکرد را به ادمین و سرور ارائه می‌ کنند. این قابلیت به صورت پیشفرض در postgre فعال نیست و در صورتی که می‌ خواهید آن را فعال کنید، لازم است که در فایل Postgresql.conf تغییرات زیر را اعمال کنید:

log_min_duration_statement = time (ms)

این فایل را می ‌توانید در آدرس زیر بیابید:

/etc/postgresql/9.6/main/postgresql.conf

کافیست در دستور فوق به جای تایم، زمان مورد نظر خود را بر حسب میلی ‌ثانیه وارد کنید. در این صورت لاگ هر کوئری‌ را که بیشتر از زمان فوق انجام آن طول بکشد، به شما نمایش خواهد داد. ضمنا به منظور غیرفعال کردن دستور فوق می ‌توانید آن را در قسمت زمان عدد 1- را وارد کنید.

لازم به ذکر است است که این روش در ابعاد بالا و حجم زیادی از کوئری ‌های کند جواب ‌گو نخواهد بود.

2. استفاده از pg_stat_statements: این روش به علت دید کلی ‌تری که از سیستم به شما می ‌دهد، بیشتر مورد قبول است و با این روش بهتر می ‌توان به افزایش کارایی خواندن از دیتابیس های postgre پرداخت. برای فعال کردن این قابلیت لازم است که خط زیر را به Postgresql.conf اضافه کنید:

1- shared_preload_libraries = ‘pg_stat_statements’

حال سیستم را Restart کرده و CREATE EXTENSION pg_stat_statements را در آن اجرا کنید. در این حالت Postgre مجموعه ‌ای از اطلاعات کوئری‌ های مختلف را در اختیار شما قرار می ‌دهد و شما را از توزیع زمانی آن‌ ها آگاه می‌ کند. در این روش شما به راحتی می‌ توانید کوئری ‌های کند را از میان میلیون ‌ها کوئری نیز شناسایی کنید.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

ترفندهایی برای افزایش کارایی خواندن از دیتابیس های Postgre

در ادامه روش‌ های افزایش کارایی و بهبود عملکرد Postgre را به ترتیب بیان می‌ کنیم، در صورتی که احساس کردید مشکلی در سرور شما وجود دارد، روش‌ های زیر را به ترتیب دنیال کنید.

دیتابیس خود را رصد کنید: هنگامی که احساس کردید یک جای کار خواندن از دیتابیس می ‌لنگد، اولین کاری که باید انجام دهید، مانیتورینگ و رصد عملکرد است. اولویت شما در رصد سرور بهتر است که CPU Utilizitaion باشد، سپس می‌ توانید به بررسی تعداد کانکشن‌ ها و فضای خالی دیسک هم توجه کنید. هنگامی که میزان عملکرد CPU شما به بالاتر از 80 درصد می ‌رسد، یعنی ایرادی در سرور وجود دارد. شما می ‌توانید یک هشدار را برای این میزان از CPU تنظیم کنید، که به موقع بتوانید ایرادات موجود را رفع کنید.
مشکلات سخت افزاری را بررسی کنید: بسیاری از مشکلاتی که با بررسی‌ های فراوان به راه ‌حل نمی‌ رسند، ریشه در سخت افزار سرور شما دارند. سرعت خواندن از دیتابیس ‌های postgre ارتباط مستقیمی با توانایی پردازش CPU شما دارد. بنابراین اگر سیستم شما ضعیف باشد و یا دچار مشکل شود، قطعا این روند با اختلال مواجه خواهد شد.
آنالیز کوئری ‌های کند: همانطور که در بالا گفته شد، یافتن کوئری‌ های کند یکی از مهم ‌ترین اصول بهبود عملکرد و بالا بردن سرعت خواندن از دیتابیس ‌های موجود در Postgre است. حال که کوئری ‌های کند یافت شدند، لازم است آن‌ ها را آنالیز کنیم. انجام آنالیز به کمک ابزارهایی انجام می‌ شود که به آن‌ ها لاگ آنالیزور (Log Analyzer) می‌ گویند.
یافتن لاگ آنالیزور مناسب: لاگ آنالیزورهای بسیاری در فضای اینترنت موجود هستند، توجه داشته باشید که در خصوص دیتابیس ‌های Postgre باید از انواع Open Source آن‌ ها استفاده کنید. همچنین این لاگ آنالیزورها اغلب پولی هستند و نیاز به خرید دارند و برخی از آن‌ ها نیز رایگان هستند. تعدادی از لاگ آنالیزورهای معروف رایگان عبارتند از:

Graylog
ELK Stack
Octopussy
Checkmk
Loggly

دریافت گزارش از لاگ آنالیزور: لاگ آنالیزورها مشکلات سرور شما را به صورت کامل به شکل یک گزارش (Report) در اختیار شما قرار می‌ دهند. در این گزارش به دنبال علائم @ ,[ ], ، زمان و IP باشید. این ‌ها اطلاعاتی هستند که می ‌توانید با تفسیر آن ‌ها متوجه ایرادات موجود شوید. هر یک از این لاگ آنالیزورها دارای یک دفترچه راهنما به منظور شناسایی این علائم است. برای مثال شما می‌ توانید علائم مخصوص به لاگ آنالیزور Pgbadgre را در اینجا مشاهده کنید.
کوئری ‌های دارای مشکل را بهینه سازی کنید: ‌قطعا مهم‌ ترین بخش در افزایش دادن سرعت خواندن از دیتابیس ‌های Postgres به بهینه سازی کوئری‌ های دارای مشکل برمی ‌گردد. به همین منظور لازم است که ابتدا کوئری مورد نظر را از CodeBase مربوطه استخراج کنید. زمانی که می‌ خواهید کوئری در آن اجرا شود را تعیین کنید.

توجه داشته باشید که زمان مناسب برای اجرای یک کوئری به مبحثی به نام Explain Plan مربوط می ‌شود. خواندن این پلان ‌ها ممکن است دشوار باشد، به همین منظور می ‌توانید از این لینک نیز کمک بگیرید.

در ابتدا باید به تعداد سطرها (Rows) و حلقه‌ها (Ioops) توجه کنید. سعی کنید هرچه زودتر این مقادیر را کاهش دهید، این کار باعث افزایش سرعت جست و جو و خواندن دیتابیس‌ ها می ‌شود. در واقع شما با انجام این کار زمان بارگزاری داده‌ ها را کاهش می ‌دهید.

نمایه‌ های از دست رفته و بلا استفاده را بیابید: گاهی اوقات که اشکالی در کدنویسی وجود دارد ممکن است مربوط به وجود دستورات اضافی و یا نبود دستوراتی لازم باشد. به روز رسانی و یافتن ایرادات این حوزه به منظور افزایش کارایی خواندن از دیتابیس های Postgre را مدیریت نمایه‌ها یا Manage Index می ‌نامند. به منظور یافتن نمایه‌ های از دست رفته یا پاک شده می ‌توان از دستور زیر استفاده کرد:

[/vc_column_text][vc_column_text]

SELECT relname
    FROM pg_class
    WHERE relkind = 'r'
    AND relhaspkey IS false;

[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]در موارد خاص، این قضیه برعکس است یعنی نمایه ‌ها وجود دارند، اما استفاده نمی ‌شوند. در این حالت، سیستم شما زمان خود را برای به ‌روز نگه‌داشتن این نمایه ‌ها از دست می دهد. نمایه های بلا استفاده را با دستور زیر تشخیص داده و آن‌ ها را حذف کنید:

SELECT *
    FROM pg_stat_user_tables
    WHERE schemaname='public'
    ORDER BY seq_scan DESC;

[/vc_column_text][vc_column_text]توجه داشته باشید که در مواردی که Table شما جدید باشد، ممکن است سطرهای یک نمایه به حدکافی نرسیده باشند و به همین دلیل مورد استفاده قرار نگیرند. در این مواقع از حذف نمایه‌ های بدون استفاده به مدت چند هفته پرهیز کنید.[/vc_column_text][vc_empty_space height=”20px”][/vc_column][/vc_row]