تصویر محمدعلی اکبری

برای افزایش سرعت پایگاه داده قبل از خرید سخت افزارهای قوی‌تر، هنوز دو کار می‌توانیم انجام دهیم.
- بهینه سازی تنظیمات پایگاه داده
- بهینه سازی کوئری‌ها (Queries)


برای بهینه سازی MySQL باید آن را برای شرایطی که قرار است در آن قرار بگیرد، آماده کنیم. همانگونه که در بهینه سازی Apache قبل‌تر بیان کردیم، بدون داشتن تخمینی از سنگینی بار موجود روی سرور، نمی‌توانیم آن را بهینه کنیم.
در پایگاه داده، اطلاعات همیشه بر روی دیسک ذخیره می‌شوند، بنابراین علاوه بر افزایش سرعت دسترسی به دیسک، یکی از تلاش‌های ما کاهش تعداد دسترسی به دیسک است.
مورد بعدی که بیشتر به نحوه نوشتن کدها مربوط می‌شود، بهینه بودن درخواست‌های پایگاه داده (queries) است. در اینجا سعی می‌کنیم ابزار لازم برای پیدا کردن درخواست‌های غیر بهینه را معرفی کنیم. بقیه کار به عهده برنامه‌نویس خواهد بود.
درخواست‌های کند (Slow queries)
در پایگاه داده، جداول بر روی دیسک ذخیره می‌شوند. از آنجا که دسترسی به دیسک یک عمل بسیار زمان‌بر محسوب می‌شود، اطلاعاتی با نام "فهرست‌ها (indexes)" به پایگاه داده کمک می‌کند اطلاعات مورد نظر را بسیار سریع تر پیدا کند. بدون وجود فهرست‌ها، برای پیدا کردن ردیفی خاص در پایگاه داده، باید تک تک ردیف‌های جدول خوانده و مقایسه شود. به عنوان مثال در جدولی با 1000 ردیف، پیدا کردن ردیفی در فهرست، 100 برابر سریع تر از پیدا کردن آن ردیف در جدول مربوطه است. دلیل سرعت بالای جستجو روی فهرست‌ها، الگوریتم‌های ذخیره سازی آن است. پایگاه داده MySQL برای اینکار بسته به شرایط و نوع داده‌ها از الگوریتم‌های B-trees، R-trees و hash استفاده می‌کند.
از فهرست‌ها در شرایط زیر استفاده می‌شود:
- جستجو برای شرط های داخل WHERE
- برای یافتن دو طرف شرط در JOIN
- برای یافتن مقادیر توابع MIN() و MAX() روی جداول
- برای مرتب سازی و گروه بندی نتایج
- هنگامی که نتایج درخواست تنها توسط اطلاعات فهرست در دسترس باشد.
ساخت و به روزرسانی فهرست‌ها به خودی خود یک عملیات زمان‌بر و پر هزینه است، لذا نباید همه ستون‌های جداول اطلاعاتی را فهرست‌بندی کنیم. از طرفی در صورتی که یک درخواست نیاز به فهرست داشته باشد و فهرست را برای آن تعریف نکرده باشیم و یا قادر به استفاده از فهرست نباشد، پایگاه داده مجبور به خواندن تک تک ردیف‌ها از روی دیسک می‌شود. در کل به درخواست‌هایی که برای اجرا زمان زیادی نیاز داشته باشند، درخواست‌های کند (Slow queries) گفته می‌شود. حالا این سوال مطرح است که کدام ستون‌ها از جداول باید فهرست داشته باشند؟
اولین پاسخ بررسی منطقی درخواست‌ها است، به این صورت که همه درخواست‌های پایگاه داده در نرم‌افزار را بررسی و در صورت لزوم فهرست‌های مورد نظر را تعریف کنیم.
راه دوم استفاده از خود پایگاه داده برای تشخیص این درخواست‌ها است. به این صورت که درخواست‌های کند توسط پایگاه داده کشف می‌شوند و برنامه‌نویس باید برای بهینه سازی آن تلاش کند.
برای کشف درخواست‌های کند (Slow queries) تنظیمات زیر را در my.cnf اعمال می‌کنیم:
 
[mysqld]
; enable the slow query log, default 10 seconds
slow_query_log
slow_query_log_file = /var/log/slow-queries.log
; log queries taking longer than 5 seconds
long_query_time = 5
; log queries that don't use indexes even if they take less than long_query_time
; MySQL 4.1 and newer only
log-queries-not-using-indexes

به این ترتیب لاگ گرفتن از درخواست‌های کند فعال شده و در فایل slow-queries.log ذخیره می‌شود. همچنین تمامی درخواست‌هایی که از فهرست‌ها استفاده نمی‌کنند، لاگ خواهند شد. بعد از اجرای چند صفحه از برنامه، با نگاهی به لاگ‌های جمع آوری شده، می‌توانید گلوگاه‌های سیستم را شناسی و برای رفع آنها اقدامات لازم را انجام دهید.
- توجه داشته باشید که فایل slow-queries.log باید توسط MySQL قابل نوشتن باشد.
- برای اعمال این تغییرات MySQL را دوباره راه اندازی کنید.
- بعد از بهینه سازی حتما این تنظیمات را غیر فعال کنید.
- برای خواندن بهتر نتایج از mysqldumpslow استفاده کنید.
حافظه نهان درخواست‌ها (Query cache)
معمولا نرم‌افزارهای تحت وب باعث اجرای درخواست‌های تکراری زیادی توسط پایگاه داده می‌شوند. به عنوان مثال در هر صفحه عنوان سایت، نام کاربر، پیکربندی‌های دیگر صفحه و اطلاعات زیادی به صورت تکراری از پایگاه داده درخواست می‌شوند. با فرض در نظر نگرفتن حافظه نهان در سطح نرم‌افزار، پایگاه داده برای پاسخگویی به این درخواست‌ها باید تعداد زیادی عملیات تکراری نظیر پردازش درخواست، اجرای درخواست، یافتن نتایج و بازگرداندن نتایج را انجام دهد. در صورت فعال شدن حافظه نهان برای پایگاه داده، نتایج درخواست‌های تکراری در حافظه سیستم ذخیره می‌شوند و از این پس این نتایج بدون اجرای مجدد درخواست، بازگردانده می‌شوند. در حالت پیش فرض، حافظه نهان درخواست‌ها غیر فعال است. برای مشاهده وضعیت حافظه نهان از دستور زیر استفاده کنید.
 
mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 0          |
| Qcache_free_memory      | 0          |
| Qcache_hits             | 0          |
| Qcache_inserts          | 0          |
| Qcache_lowmem_prunes    | 0          |
| Qcache_not_cached       | 0          |
| Qcache_queries_in_cache | 0          |
| Qcache_total_blocks     | 0          |
+-------------------------+------------+
8 rows in set (0.00 sec)

برای فعال سازی حافظه نهان تنظیمات زیر را در my.cnf اضافه کنید.
 
query_cache_size = 32M

به این ترتیب حافظه نهان به اندازه 32MB فعال خواهد شد. همچنین تنظیمات query_cache_type نوع حافظه نهان را تعیین می‌کند. به این ترتیب کنترل حافظه نهان را می‌توان در سطح نرم‌افزار انجام داد.
 
mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 5216       |
| Qcache_free_memory      | 14640664   |
| Qcache_hits             | 2581646882 |
| Qcache_inserts          | 360210964  |
| Qcache_lowmem_prunes    | 281680433  |
| Qcache_not_cached       | 79740667   |
| Qcache_queries_in_cache | 16927      |
| Qcache_total_blocks     | 47042      |
+-------------------------+------------+
8 rows in set (0.00 sec)

تنظیمات زیاد دیگری نیز برای بهینه سازی پایگاه داده باید مورد بررسی قرار گیرد. برای تعیین وضعیت هر پارامتر پایگاه داده باید ابتدا با استفاده از دستورات SHOW STATUS وضعیت فعلی هر پارامتر را بررسی و مقدار مناسبی برای آن تعیین کنیم.
مقدار صحیح هر پارامتر بستگی به نرم‌افزار و تعداد بازدید کننده دارد.
معرفی ابزار
- MySQLTuner یک نرم‌افزار کوچک به زبان Perl است. این ابزار وضعیت فعلی پایگاه داده را بررسی می‌کند و مقدار مناسب برای هر پارامتر پایگاه داده را تعیین می‌کند.
این ابزار را از https://github.com/rackerhacker/MySQLTuner-perl دانلود کنید و mysqltuner.pl را اجرا کنید
- برای مشاهده وضعیت فعلی سیتسم می‌توانید از دستور mytop استفاده کنید.
- ابزار mysqlard نیز وضعیت سیستم را در بازه های زمانی بررسی میکند و تنظیمات پیشنهادی برای بهبود وضعیت را در درازمدت ارائه می‌دهد.
- ابزار Mysqlreport نیز همانند دستور mytop وضعیت سیستم را به صورت هوشمندانه‌تری بررسی می‌کند.
نتیجه گیری
برای بهینه سازی MySQL چند کار کلی انجام دادیم:
- بهینه سازی درخواست‌ها در سطح نرم‌افزار
- کاهش تعداد دسترسی به دیسک با استفاده از حافظه نهان
- اعمال محدودیت‌ها در مورد ارتباط‌های هم زمان روی پایگاه داده به منظور جلوگیری از کمبود منابع سرور

دیدگاه‌ها

تصویر عرفان بناکار

عرفان بناکار (تایید نشده)

عالی بود. چند وقته که به دلیل پروژه ای که داریم روی بهینه سازی سرعت خیلی وقت گذاشتم و اولین نتیجه ای که گرفتم این بود. با MySQL خودتون رو به کشتن میدید. MariaDB یک انتخاب خیلی بهتره. در ضمن mariaDB واقعا متن بازه.
http://www.computerworld.com.au/article/457551/dead_database_walking_mys...

تصویر محمدعلی اکبری

محمدعلی اکبری

مقاله خیلی عالی بود. دست شما درد نکنه

افزودن نظر جدید