7نکته کلیدی برای آن که به بالاترین سطح از کارایی MySQL دست پیدا کنید

برای یک اپلیکیشن خرید موبایلی، زمان پاسخ‎دهی نباید بیشتر از یکی دو ثانیه باشد و برای یک کارمند صفحه HR، پاسخ‌دهی ممکن است بتواند چند ثانیه طولانی‎تر باشد. تحقيقات زیادی روی نحوه تأثیر میزان عملکرد بر رفتار کاربران صورت گرفته است، از جمله این‌که 79 درصد مشتريان دیگر تمایلی به بازگشت به یک وب‎سایت کند را ندارند و یا 40 درصد از بازدیدکنندگان اگر بارگذاری یک وب‎سایت بیشتر از سه ثانیه طول بکشد، آن را ترک می‎کنند. استاندارد هرچه باشد، حفظ عملکرد خوب برای هر کاربردی ضروری است، در غیر این صورت کاربران ناراضی خواهند بود و بدتر از آن ممکن است خدمات شما را رها کنند. یکی از عواملی که تأثیر زیادی روی عملکرد یک اپلیکیشن دارد عملکرد پایگاه داده آن است. تعامل بین اپلیکیشن‎ها، وب‎سایت‎ها و پایگاه‎های داده در تعیین سطح عملکرد کلی خدمات ما نقش بزرگی دارد. هرچه اندازه و زمان بارگذاری یک پایگاه داده افزایش پیدا می‎کند، عملکرد نیز کاهش می‎یابد. هسته مرکزی چنین تعاملی نحوه پرس‌وجوی اپلیکیشن‎ها با پایگاه داده و چگونگی پاسخ پایگاه داده به این درخواست‎ها است. با هر نوع معیار سنجشی که مد نظر شما باشد،  MySQL یکی از معروف‎ترین سیستم‎های مدیریت پایگاه داده است و سازمان‎های بیشتری به MySQL به‌عنوان راهکار پایگاه داده در محیط کاری خود نقل مکان می‎کنند. روش‎های زیادی برای پیکربندی MySQL برای کمک به اطمینان از این‌که پایگاه داده شما بتواند در کمترین زمان ممکن به‌سرعت به درخواست‎ها پاسخ دهد وجود دارد. 

نکته کلیدی 1: روش استفاده از MySQL را یاد بگیرید

دو تصمیم مهم درباره هر پایگاه داده‎ای که باید به آن توجه کنید، طراحی چگونگی ارتباط بین اجزای اپلیکیشن با ساختار جداول موجود در پایگاه داده و طراحی نحوه دریافت داده‎های مورد نیاز اپلیکیشن به فرم مورد نظر آن (کوئری‎ها) است.
در اپلیکیشن‎های پیچیده ساختار جداول و کوئری‎ها نیز پیچیده هستند. اگر می‎خواهید به عملکرد و گسترش‌پذیری مورد نیاز اپلیکیشن خود دست پيدا كنيد، نمی‎توانید تنها به دانش نحوه کوئری‌گیری بسنده کنید. به‌جای آزمون و خطا، باید نحوه استفاده از فرمان EXPLAIN را یاد بگیرید. این فرمان به شما نشان می‎دهد چگونه یک کوئری اجرا می‎شود و این بینش را به شما می‎دهد که چه عملکردی را می‎توانید انتظار داشته باشید و این کوئری با تغییر اندازه داده چگونه بسط پیدا می‎کند. ابزارهای مختلفی (مثل MySQL Workbench) وجود دارد که می‎تواند خروجی EXPLAIN را به‌صورت بصری و قابل درک به شما نشان دهند، اما شما برای درک بهتر آن همچنان باید اصول پایه را یاد بگیرید. خروجی فرمان EXPLAIN با دو فرمت مختلف ارائه می‎شود: فرمت قدیمی‎ جدول و فرمت پیشرفته‎تر با ساختار اسناد JSON که جزئیات بسیار بیشتری را ارائه می‎کند. (فهرست 1)

mysql> explain format=json select avg(k) from sbtest1 where id between 1000 and 2000 \G
*************************** 1. row ***************************
EXPLAIN: {
  “query_block”: {
    “select_id”: 1,
    “cost_info”: {
      “query_cost”: “762.40”
    },
    “table”: {
      “table_name”: “sbtest1”,
      “access_type”: “range”,
      “possible_keys”: [
        “PRIMARY”
      ],
      “key”: “PRIMARY”,
      “used_key_parts”: [
        “id”
      ],
      “key_length”: “4”,
      “rows_examined_per_scan”: 1874,
      “rows_produced_per_join”: 1874,
      “filtered”: “100.00”,
      “cost_info”: {
        “read_cost”: “387.60”,
        “eval_cost”: “374.80”,
        “prefix_cost”: “762.40”,
        “data_read_per_join”: “351K”
      },
      “used_columns”: [
        “id”,
        “k”
      ],
      “attached_condition”: “(`sbtest`.`sbtest1`.`id` between 1000 and 2000)”
    }
  }
}

فهرست 1

یکی از اجزایی که باید به آن توجه داشته باشید هزینه محاوره (query cost) است. این مفهوم نشان می‎دهد یک محاوره خاص از لحاظ صرف منابع کلی برای اجرا شدن چقدر برای MySQL هزینه‎بر است. به‎ طور مثال، اگر می‎گوییم یک محاوره ساده هزینه‎ای کمتر از 1000 رکورد دارد، به مجموع فرآیندهایی اشاره می‎کنیم که هزینه‎ای را به MySQL وارد می‎کنند تا چرخه درخواست و پاسخ‌گویی را کامل کند. اگر می‎گوییم محاوره‎هایی با بهای بین 1000 تا 100000 به محاوره با بهای متوسطی اشاره داریم که اگر شما در هر ثانیه تنها صدها نمونه (نه هزاران یا میلیون‎ها) از آن را اجرا کنید، معمولاً مشکلی در عملکرد نداشته و سریع محسوب اجرا می‎شوند. محاوره‎هایی با بهای بیشتر از 100000 پرهزینه محسوب می‎شوند. اغلب این محاوره‎ها مادامی‎ که تنها کاربر سیستم باشید همچنان سریع هستند، اما باید با دقت به این موضوع فکر کنید که قرار است چند بار از چنین محاوره‎هایی در تعاملات اپلیکیشن خود استفاده کنید (به‌ویژه در مواردی که تعداد کاربران رو به رشد است). مسلماً این ارقام سنجش عملکرد همگی تقریبی هستند، اما یک اصل کلی را نشان می‎دهند. سیستم شما بر اساس معماری و پیکربندی که دارد ممکن است چرخه کاری این کوئری‎ها را بهتر یا بدتر اداره کند. یکی دیگر از عوامل تعیین‌کننده بهای کوئری این است که آیا این کوئری به‌درستی از ایندکس‎ها استفاده می‎کند. فرمان EXPLAIN می‎تواند به شما بگوید آیا یک کوئری توانسته از ایندکس‎ها استفاده کند یا خیر. به همین دلیل است که یادگیری استفاده از EXPLAIN بسیار پراهمیت است.

مطلب پیشنهادی

متخصص و متبحر در مدیریت بزرگ داده‌ها

با انواع مختلف بانک‌های اطلاعاتی NoSQL آشنا شوید

نکته کلیدی 2: ایندکس‎ها را درست ایجاد کنید 

 یک ایندکس با کاهش مقدار داده موجود در پایگاه داده که کوئری‎ها باید اسکن کنند، عملکرد کوئری را بهبود می‎بخشد. ایندکس‎ها در MySQL برای افزایش سرعت دسترسی در پایگاه داده و کمک به اجرای قید و بندهای پایگاه داده (مثل UNIQUE و FOREIGN KEY) استفاده می‎شوند. ایندکس در پایگاه داده شبیه به ایندکس (فهرست‌بندی) کتاب عمل می‎کند. آن‌ها در مکان مخصوص خود نگهداری می‎شوند و شامل اطلاعاتی هستند که قبلاً در پایگاه داده اصلی موجود بوده است. آن‌ها یک روش مرجع یا نقشه به مکان قرارگیری داده هستند. ایندکس‎ها هیچ داده‎ای را در پایگاه داده تغییر نمی‎دهند و تنها به مکان داده اشاره می‎کنند. هیچ ایندکسی وجود ندارد که در همه حالت برای همه کارها مناسب باشد. شما همیشه باید ایندکس‎ها را بر اساس وضعیت کوئری‎هایی که سیستم اجرا می‎کند مشخص کنید. پایگاه‎های داده‎ای که به‌خوبی ایندکس می‎شوند نه‌تنها سریع‎تر اجرا می‎شوند، بلکه حتی یک کمبود کوچک ایندکس می‎تواند یک پایگاه داده را به‌میزان زیادی با کاهش سرعت مواجه کند. از فرمان EXPLAIN (توصیه شده در بخش قبل) برای پیدا کردن ایندکس‎های گم‌شده و اضافه کردن آن‌ها استفاده کنید. اما مراقب باشید بی‎جهت ایندکس‎هایی که احتیاج ندارید را اضافه نکنید. ایندکس‎های غیرضروری نتیجه معکوس دارند و سرعت پایگاه داده را کم می‎کنند.

نکته کلیدی 3: از گزينه‎های پیش‎فرض اجتناب کنید 

مثل هر نرم‎افزار دیگری MySQL نیز تنظیمات زیادی برای پیکربندی دارد و خیلی از این تنظیمات توسط مدیران سرور نادیده گرفته می‎شود و در حالت پیش‌فرض خود باقی می‎ماند. برای دریافت بهترین عملکرد در MySQL مهم است که تنظیمات پیکربندی آن را به‌درستی یاد بگیرید و از آن مهم‎تر این‌که آن‌ها را برای دریافت بهترین نتیجه در محیط پایگاه داده خود تنظیم کنید.
در حالت پیش‌فرض MySQL برای محیط‎های توسعه در مقیاس کوچک تنظیم شده است نه مقیاس‎های بزرگ کاری. شما معمولاً باید MySQL را به‌گونه‎ای تنظیم کنید تا از تمام منابع حافظه موجود استفاده و هر تعداد اتصال مورد نیاز اپلیکیشن را فراهم کند. در ادامه با سه تنظیم مورد نیاز برای افزایش عملکرد MySQL آشنا خواهیم شد.
innodb_buffer_pool_size: buffer pool جایی است که داده‎ها و ایندکس‎ها در حافظه موقت (کش) نگهداری می‎شوند. دلیل اصلی توصیه به استفاده از مقدار زیاد RAM روی سرور پایگاه داده شما نیز همین است. اگر تنها از موتور ذخیره‌سازی InnoDB استفاده می‎کنید، معمولاً 80 درصد از حافظه موجود خود را به buffer pool اختصاص می‎دهید. همچنین اگر کوئری‎های خیلی پیچیده را اجرا می‎کنید یا تعداد اتصالات هم‌زمان به پایگاه داده شما بسیار زیاد است یا تعداد بسیار زیادی جدول دارید، باید این مقدار را کاهش دهید تا حافظه بیشتری برای مصارف دیگر باقی بماند.
بعد از اینکه مقدار InnoDB buffer pool را تنظیم کردید، باید مطمئن شوید این مقدار را بیش از اندازه تعیین نکرده باشید تا باعث swapping نشود. چنین اتفاقی به‌شدت به عملکرد پایگاه داده شما لطمه می‎زند. راهی ساده برای بررسی این موضوع نگاه کردن به Swapping Activity در نمودار System Overview در ابزار مانیتورینگ و مدیریت Percona است. (شکل 1) اگر شما بار اول مقدار innodb_buffer_pool_size را درست انتخاب نکردید، جای نگرانی نیست. در نسخه MySQL 5.7 می‎توانید بدون نیاز به ری‎استارت کردن سرور MySQL اندازه InnoDB buffer pool را به‌صورت پویا تغییر دهید.

شکل 1

innodb_log_file_size: این گزینه نشان‌دهنده اندازه یک فایل گزارش InnoDB است. در حالت پیش‌فرض InnoDB از دو مقدار استفاده می‎کند، به همین دلیل می‎توانید این رقم را دو برابر کنید تا اندازه فضای چرخه بازگشت لاگ مورد استفاده InnoDB برای تبادلات بادوام افزایش پیدا کند. این کار اعمال تغییرات در پایگاه داده را نیز بهینه‌سازی می‎کند. هرچه فضای بازگشتی که شما انتخاب می‎کنید بزرگ‌تر باشد، عملکردی که برای نوشتن در فضای کاری خود به دست می‎آورید نیز بهتر خواهد شد، اما در زمان قطع برق سیستم یا سایر مشکلات دیگر زمان بیشتری برای بازیابی خسارت نیاز خواهد بود. اما از کجا باید بدانیم که آیا عملکرد MySQL توسط اندازه فایل لاگ InnoDB محدود شده است؟ ساده‎ترين راه مشاهده میزان مصرف فضای لاگ از طریق داشبورد InnoDB Metrics ابزار Percona است. در نمودار شکل 2 اندازه فایل لاگ InnoDB به‌اندازه کافی بزرگ نیست، زیرا فضای استفاده شده بسیار نزدیک به‌میزان فضای لاگ بازگشتی است (نشان داده شده توسط خط قرمز). اندازه فایل لاگ شما باید حداقل 20 درصد بزرگ‌تر از مقدار فضای مورد استفاده برای حفظ عملکرد سیستم شما در حالت بهینه باشد. (شکل 2)

max_connections: اپلیکیشن‎های مقیاس بزرگ اغلب به تعداد خیلی بیشتری کانکشن از مقدار پیش‌فرض احتیاج دارند. برخلاف سایر متغیرها، اگر شما این مقدار را درست انتخاب نکنید، مشکلی در رابطه با عملکرد نخواهید داشت. در عوض اگر تعداد کانکشن‎ها به‌اندازه مورد نیاز اپلیکیشن شما نباشد، اپلیکیشن قادر به اتصال به پایگاه داده نخواهد بود و کاربران نمی‎توانند از خدمات شما استفاده کنند (شبیه به از کار افتادن وب‎سایت). به همین دلیل است که انتخاب درست این متغیر پراهمیت است. درباره کاربردهای پیچیده با چندین اجزای در حال اجرا روی چندین سرور، تشخیص تعداد کانکشن مورد نیاز کار چندان راحتی نیست. خوشبختانه MySQL امکان مشاهده تعداد کانکشن‎های مورد استفاده در اوج عملیات را ساده کرده است. معمولاً شما باید اطمینان حاصل کنید بین حداکثر تعداد کانکشن‎هایی که اپلیکیشن شما استفاده می‎کند با حداکثر تعداد کانکشن‎های موجود حداقل 30 درصد فاصله وجود داشته باشد. یک راه ساده برای مشاهده این ارقام استفاده از MySQL Connections Graph در داشبورد MySQL Overview ابزار مدیریت و مانیتورینگ Percona است. شکل 3 یک سیستم سالم را نشان می‎دهد که تعداد کانکشن‎های موجود در آن به ‌اندازه کافی است. (شکل 3)

موضوعی که نباید فراموش کرد این است که اگر پایگاه داده شما کند اجرا ‎شود، اپلیکیشن‎ها اغلب تعداد زیادی کانکشن ایجاد می‎کنند. در چنین شرایطی باید به‌جای اختصاص دادن تعداد کانکشن‎های بیشتر مشکل عملکرد پایگاه داده را برطرف کنید. کانکشن بیشتر می‎تواند مشکل اصولی عملکرد را بیشتر کند. توجه داشته باشید وقتی شما متغیر max_connections را خیلی بیشتر از مقدار پیش‌فرض تعیین می‎کنید، باید به افزایش سایر پارامترها مثل اندازه کش جدول و تعداد فایل‎هایی که MySQL اجازه باز کردن آن را می‎دهد نیز توجه کنید.

نکته کلیدی 4: پایگاه داده را در حافظه نگه دارید 

طی سال‎های گذشته با حضور درایوهای حالت جامد (SSD) شاهد تحول بزرگی در افزایش سرعت انتقال داده بوده‎ایم. حتی با وجود این‌که انواع SSD بسیار سریع‎تر از هارد درایوهای چرخان هستند، اما همچنان با سرعت انتقال داده در RAM قابل مقايسه نیستند. این اختلاف نه‌تنها در میزان عملکرد ذخیره‌سازی قابل مشاهده است، بلکه برای کارهای اضافه دیگری که پایگاه داده در زمان بازیابی داده از فضای ذخيره‌سازی دیسک باید انجام دهد نیز اثرگذار است. با پیشرفت‎های سخت‌افزاری اخیر، امکان قرار گرفتن پایگاه داده روی حافظه نیز افزایش پیدا کرده است. خبر بهتر این است که برای به دست آوردن حداکثر مزایای عملکرد درون حافظه لازم نیست تمام پایگاه داده خود را روی حافظه پیاده‎سازی کنید. شما تنها باید مجموعه داده‎های کاری خود را به حافظه منتقل کنید.
احتمالاً با مقالاتی برخورد کرده‎اید که ارقام مشخصی را (از 10 تا 33 درصد) برای اختصاص دادن سهم پایگاه داده به حافظه اعلام می‎کنند. در واقع، هیچ رقم ثابت و مشخصی برای همه کاربردها وجود ندارد. به‌جای اینکه به‌دنبال یک عدد جادویی مشخص باشید، باید بررسی کنید پایگاه داده شما چه میزان ورودی خروجی را در حالت پایدار خود (معمولاً چند ساعت پس از راه‌اندازی) اجرا می‎کند. به وضعیت خواندن داده توجه کنید، زیرا زمان مورد نیاز برای خواندن اگر پایگاه داده شما روی حافظه باشد، می‎تواند کاملاً از بین برود. زمان مورد نیاز برای نوشتن هر چقدر هم که حافظه در اختیار داشته باشید، باز هم اتفاق خواهد افتاد. در شکل 4 میزان ورودی خروجی در InnoDB I/O Graph داشبورد InnoDB Metrics ابزار مانیتورینگ و مدیریت Percona را مشاهده می‎کنید.

نکته کلیدی 5: از فضای ذخيره‌سازی SSD استفاده کنید 

اگر پایگاه داده شما در حافظه جا نمی‎شود (یا حتی می‎شود)، برای نوشتن داده‎های خود و جلوگیری از مشکلات عملکرد در زمان راه‌اندازی پایگاه داده همچنان به یک فضای ذخيره‌سازی پرسرعت نیاز دارید. این روزها فضای ذخيره‌سازی سریع‎تر را تنها روی SSD می‎توان پیدا کرد. برخی از متخصصان به‌واسطه قیمت پایین‎تر و قابلیت اطمینان بیشتر همچنان طرف‌دار دیسک‎های گردان هستند، اما این روزها انواع SSD با قیمت‎هایی معقولانه‎تر عملکرد و قابلیت اطمینان فوق‌العاده‎ای را ارائه می‎کنند.
اما نباید فراموش کرد تمام انواع SSD یکسان ساخته نمی‎شوند. برای سرورهای داده شما باید از یک SSD که مخصوص حجم کاری سرورها طراحی شده است استفاده کنید تا محافظت از داده‎های شما به‌خوبی انجام شود. از SSD مصرفی طراحی شده برای کامپیوتر دسكتاپ و لپ‎تاپ پرهیز کنید. یک SSD متصل شده از طریق فناوری Intel Optane یا NVMe بهترین عملکرد را ارائه می‎کند. حتی اگر از طریق SAN, NAS یا کلاود از فضای ذخيره‌سازی استفاده می‎کنید، باز هم SSD عملکرد بسیار بهتری نسبت به دیسک‎های گردان ارائه می‎کند.

نکته کلیدی 6: گسترش‌پذیری 

حتی قدرتمندترين سرورها هم محدودیت‎های خود را دارند. شما برای گسترش قابلیت سرور خود دو راه پیش رو دارید: خرید سخت‌افزار بیشتر که هزینه‎بر است و سخت‌افزار به‌سرعت منسوخ می‎شود. یا گسترش ظرفیت کاری که مزایای خاص خود را دارد. در این روش از مزایای چند سیستم کوچک‎تر و ارزان‎تر هم‌زمان بهره‎مند می‎شوید و از آن‌جا که پایگاه داده در بین بیش از یک ماشين فیزیکی گسترش پیدا کرده است، در مقابل خرابی‎های سخت‌افزاری روی یک سیستم واحد مصون باقی می‎ماند.
هرچند گسترش سیستم پایگاه داده دارای مزیت است، اما محدودیت‎های خاص خود را نیز دارد. گسترش پایگاه داده مثل MySQL Replication یا Percona XtraDB Cluster برای هم‌گام‌سازی داده‎ها نیاز به تکرار و رونوشت دارد. اما در عوض شما عملکرد بیشتر و قابلیت دسترسی بالاتری را به دست خواهید آورد. همچنین باید اطمینان حاصل کنید اپلیکیشن‎های متصل به معماری کلاستری می‎توانند به داده‎های مورد نیاز خود دسترسی داشته باشند که معمولاً از طریق پراکسی سرورها و لود بالانسرهایی مثل ProxySQL و HAProxy انجام می‎شود.

نکته کلیدی 7: علاج واقعه قبل از وقوع

همیشه بهترین طراحی سیستم آن‌هایی هستند که آینده را هم در نظر دارند و MySQL نیز از این قاعده مستثنا نیست. بعد از اینکه محیط MySQL خود را راه‌اندازی، اجرا و بهینه‌سازی کردید، نباید دیگر آن را به حال خود رها کنید. محیط‎های پایگاه داده معمولاً تحت تأثیر تغییرات سیستم یا حجم کار قرار می‎گیرند. باید خود را برای غافلگيری‎هایی پیش‌بینی نشده مثل ترافیک انبوه، اشکالات برنامه و مشکلات خروجی MySQL آماده کنید. این‌ها مسائلی هستند که دیر یا زود اتفاق می‎افتد.
بعد از بروز چنین اتفاقاتی، باید بتوانيد به‌سرعت آن‌ها را برطرف کنید. تنها روش انجام این کار در اختیار داشتن راهکارهای نظارتی و ابزار مناسب آن است. با این روش می‎توانید اتفاقات در حال انجام در زمان اجرای محیط پایگاه داده خود را مشاهده و وضعیت سرور داده خود را تجربه و تحلیل کنید. در حالت ایده‎آل چنین راهکاری به شما امکان می‎دهد قبل از رخ دادن مشکلات از آن جلوگیری كنيد. نمونه‎هایی از ابزارهای مانیتورینگ شامل (Monyog، MySQL Enterprise Monitor و Percona Monitoring and Management PMM) هستند که یک دیدگاه عملی عالی برای نظارت و عیب‌یابی ارائه می‎کنند.
هرچه شرکت‎های بیشتری به پایگاه‎های داده منبع باز (به‌ویژه MySQL) برای مديريت و خدمت‌رسانی داده‎های تجاری خود در مقیاس بزرگ روی می‎آورند، باید روی نگهداری و اجرای بهینه این پایگاه‎های داده نیز تمرکز کنند. همان ‎طور که برای رونق کسب و کار شما رعایت تمام اصول تجارت حیاتی است، عملکرد پایگاه داده شما نیز می‎تواند نقشی مهم در کسب و کارتان داشته باشد. MySQL یک راهکار پایگاه داده عالی برای قدرت بخشیدن به برنامه‎ها و وب‎سایت‎های شما است، اما باید آن را با نیاز شما سازگار کرد و برای پیدا کردن و جلوگیری از تنگناها و مسائل مربوط به عملکرد آن را تحت نظر قرار داد.