سرور مجازی لهستان با ترافیک نامحدود
پشتیبانی آنلاین ناحیه کاربری

آموزش بهینه سازی MySQL با استفاده از Query Cache

بهینه سازی دیتابیس با استفاده از کش Query در دیتابیس MySQL

یکی از امکانات برجسته دیتابیس MySQL برای افزایش سرعت دریافت اطلاعات، Query Cache میباشد. برای انجام این فرایند MySQL نتیجه دستور SELECT را در حافطه نگهداری کرده و در صورت درخواست Client بدون اجرای مجدد این دستور و درگیر کردن MySQL و منابع، نتیجه را با سرعت بیشتری به Client ارسال میکند. با مقایسه ساده میتواند فهمید سرعت خواندن اطلاعات حافظه RAM بسیار بالا تر از هارد بوده و به همین دلیل استفاده از Query Cache که برای کش از RAM استفاده میکند باعث افزایش محسوس سرعت سرویس خواهد شد.

برای مثال یک سایت وردپرس یا جوملا در صورت داشتن بازدید بالا و یا درخواست های بالای تغییر در جداول دیتابیس میتواند با استفاده از امکان Query Cache افزایش چشم گیری در سرعت داشته باشد. در این مقاله ابتدا یک دیتابیس ایجاد و در شرایط غیر فعال بودن Query Cache بازدهی دیتابیس برسی سپس Query Cache فعال شده و مجددا میزان بازدهی برسی میشود تا تفاوت را در یک محیط عملیاتی مشاهده نمایید.

پیش نیاز ها:

همانند همیشه برای انجام هر عملیات نیاز به برخی ملزومات نرم افزاری و سخت افزاری خواهید داشت. قبل از مطالعه ادامه مقاله ابتدا موارد ذکر شده را انجام دهید سپس ادامه مقال را مطالعه نمایید:

  1. یک سرور مجازی که بر روی ان لینوکس CentOS یا Ubuntu نصب شده باشد
  2. بر روی لینوکس خود MySQL نصب نمایید و برای یوز root دیتابیس نیز کلمه عبور قرار ندهید. (راهنمای نصب MySQL)

 

مرحله اول – برسی وضعیت Query Cache بر روی سرور

در این مرحله ابتدا باید برسی شود که دیتابیس mysql از قابلیت Query Cache پشتیبانی میکند. ابتدا با استفاده از ssh وارد محیط command line سرور لینوکس شده سپس دستور زیر را وارد نمایید.

mysql -u root -p

با وارد کردن دستور بالا وارد و وارد کردن کلمه عبور root دیتابیس وارد محیط CLI دیتابیس mysql خواهید شد. در این محیط میتوانید دستورات لازم را برای دیتابیس وارد نمایید. برای مشاهده وضعیت Query Cache دستور زیر را وارد نمایید:

show variables like ‘have_query_cache’;

خروجی »

+——————+——-+
| Variable_name | Value |
+——————+——-+
| have_query_cache | YES |
+——————+——-+
1 row in set (0.01 sec)

اگر متغیر have_query_cache با YES مقدار دهی شده است امکان فعال سازی امکان Query Cache در دیتابیس شما وجود داشته و میتوانید این امکان را فعال سازی نمایید. حالا که از پشتیبانی دیتابیس خود اطمینان حاصل کردید میتوانید با ادامه مقاله با ما همراه باشید.

مرحله دوم – برسی متغیر مربوط به Query Cache

برای فعال سازی Query Cache نیاز است تا با متغییر های ان نیز اشنا شوید تا بتوانید بهترین تنظمات را برای ان انجام دهید. برای مشاهده متغییر های مربوط به Query Cache دستور زیر را در محیط mysql که قبلا وارد شده اید وارد نمایید:

show variables like ‘query_cache_%’ ;

خروجی »

+——————————+———-+
| Variable_name | Value |
+——————————+———-+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+——————————+———-+
5 rows in set (0.00 sec)

معرفی متغییر های Query Cache :

  • query_cache_limit : مقدار این متغییر مشخص کننده فضای مورد نظر برای نتیجه یا result یک query میباشد. میزان پیشفرض ان 1,048,576 بایت است که برابر با یک مگابایت میباشد.
  • query_cache_min_res_unit : دیتابیس mysql اطلاعات را در یک فضای بزرگ نگهداری نمیکند. اطلاعات در بلاک های کوچکتر نگهداری میشود که با متغییر query_cache_min_res_unit مقدار ان مشخص میشود. میزان پیشفرض این متغییر 4096 بایت بوده که برابر با 4 کیلوبایت میباشد.
  • query_cache_size : توسط این متغییر میتوانید میزان مجموع فضای اختصاص داده شده به Query Cache را کنترل نمایید. در صورتی که مقدار ان 0 باشد Query Cache غیر فعال خواهد بود. مقدار پیشفرض این گزینه 16,777,216 بایت است که برابر با حدودا 16 مگابایت است. باید توجه داشته باشید برای پیاده سازی ساختار یا structures مربوط به Query Cache باید حدقل 40 کیلوبایت به ان فضا اختصاص داده شود.
  • query_cache_type : نوع query هایی که باید cache شود توسط متغییر query_cache_type تشخیص داده میشود. در صورتی که مقدار ان 1 تنظیم شود تمامی Query ها به جز query هایی که SELECT SQL_NO_CACHE  باشد کش می شود. در صورتی که مقدار این متغییر 2 باشد فقط Query هایی که SELECT SQL_CACHE باشد کش خواهد شد. اگر مقدار ان 0 باشد نیز از کش query ها خودداری خواهد شد.
  • query_cache_wlock_invalidate : این متغییر مشخص میکند query هایی که lock شده است کش شود یا خیر که بصورت پیشفرض OFF میباشد.

 

حالا که متغییر های مربوط به امکان Query Cache معرفی شد ابتدا میزان بازدهی mysql بدون استفاده از این امکان Query Cache برسی خواهد شد.

مرحله سوم – تست بازدهی MySQL بدون استفاده از امکان Query Cache

هدف این اموزش افزایش بازدهی دیتابیس MySQL با استفاده از Query Cache است. قبل از فعال سازی این امکان باید تفاوت بازدهی قبل و بعد استفاده از Query Cache برسی شود. برای مشاهده بازدهی باید یک دیتابیس، جدول و اطلاعاتی وارد ان شود. در ادامه اموزش با استفاده یک دیتابیس با نام sample_db ایجاد و در ان یک جدول با نام customers و دو فیلد customer_id و customer_name ایجاد خواهیم کرد.

برای ایجاد دیتابیس sample_db دستور زیر را وارد نمایید:

Create database sample_db;

خروجی »

Query OK, 1 row affected (0.00 sec)

پس از ایجاد دیتابیس باید دیتابیس فعال را تغییر دهید تا تغییرات در ان انجام شود. برای این مورد دستور زیر را وارد نمایید:

Use sample_db;

خروجی »

Database changed

با استفاده از دستور زیر یک جدول customers و دو فیلد customer_id و customer_name ایجاد میکنیم:

Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;

خروجی »

Query OK, 0 rows affected (0.01 sec)

پس از ایجاد جدول و فیلد میتوانید با استفاده از دستورات زیر اطلاعاتی که بصورت تست تهیه شده است را در ان وارد نمایید تا در مراحل مربوط به تست استفاده شود (دستورات زیر را بصورت خط به خط اجرا نمایید):

Insert into customers(customer_id, customer_name) values (‘1’, ‘JANE DOE’);
Insert into customers(customer_id, customer_name) values (‘2’, ‘JANIE DOE’);
Insert into customers(customer_id, customer_name) values (‘3’, ‘JOHN ROE’);
Insert into customers(customer_id, customer_name) values (‘4’, ‘MARY ROE’);
Insert into customers(customer_id, customer_name) values (‘5’, ‘RICHARD ROE’);
Insert into customers(customer_id, customer_name) values (‘6’, ‘JOHNNY DOE’);
Insert into customers(customer_id, customer_name) values (‘7’, ‘JOHN SMITH’);
Insert into customers(customer_id, customer_name) values (‘8’, ‘JOE BLOGGS’);
Insert into customers(customer_id, customer_name) values (‘9’, ‘JANE POE’);
Insert into customers(customer_id, customer_name) values (’10’, ‘MARK MOE’);

خروجی »

Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
…

پس از ایجاد و ورود اطلاعات باید امکان Profiler برای برسی و مانیتورینگ Query ها فعال شود. برای فعال سازی باید مقدار profiling را برابر با 1 قرار دهید. برای انجام این مورد دستور زیر را وارد نمایید:

SET profiling = 1;

خروجی »

Query OK, 0 rows affected, 1 warning (0.00 sec)

حالا query زیر را وارد کنید تا اطلاعات مربوط به جدول customer را دریافت نمایید:

Select * from customers;

خروجی »

+————-+—————+
| customer_id | customer_name |
+————-+—————+
| 1 | JANE DOE |
| 2 | JANIE DOE |
| 3 | JOHN ROE |
| 4 | MARY ROE |
| 5 | RICHARD ROE |
| 6 | JOHNNY DOE |
| 7 | JOHN SMITH |
| 8 | JOE BLOGGS |
| 9 | JANE POE |
| 10 | MARK MOE |
+————-+—————+
10 rows in set (0.00 sec)

حالا دستور SHOW PROFILES را اجرا نمایید تا میزان بازدهی مربوط به query اجرا شده را مشاهده نمایید:

SHOW PROFILES;

خروجی »

+———-+————+————————-+
| Query_ID | Duration | Query |
+———-+————+————————-+
| 1 | 0.00044075 | Select * from customers |
+———-+————+————————-+
1 row in set, 1 warning (0.00 sec)

ممکن است زمان اجرای دستور در سرور های مختلف به دلیل سخت افزار و دیگر موارد متفاوت باشد. زمان نمایش داده در خروجی بالا مربوط به اجرای این دستور بوده که میتوانید این زمان را با زمان اجرای دستور پس از فعال سازی Query Cache مقایسه نمایید.

حالا برای فعال سازی Query Cache از محیط کامند لاین دیتابیس MySQL خارج شوید. برای خروج از دستور زیر استفاده نمایید.

quit;

مرحله چهارم – فعال سازی Query Cache

در مراحل قبل برای برسی و تست عملکرد Query Cache یک سری اطلاعات تست در دیتابیس ایجاد شد. در ادامه نیاز است تا فایل تنظیمات MySQL ویرایش شود. فایل تنظیمات با نام my.cnf است که در ادرس /etc/ قرار دارد. برای ویرایش کافیست در محیط command line لینوکس دستور زیر را وارد نمایید:

vi /etc/mysql/my.cnf

پس از وارد شدن به این فایل متن زیر را در ان اضافه نمایید:

[mysqld]
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256K

در متن بالا با مقدار دهی متغیر query_cache_type با 1 امکان Query Cache فعال سازی خواهد شد. در ادامه نیز برای 10 مگابایت از فضای رم به Query Cache اختصاص داده شد. پس از ویرایش فایل و اضافه کردن متن بالا تغییرات را ذخیری نمایید و از ویرایشگر خارج شوید. سپس سرویس MySQL را ریستارت نمایید.

systemctl restart mysql

مرحله چهارم – تست بازدهی سرور پس از فعال سازی Query Cache

برای برسی وضعیت و بازدهی سرویس دیتابیس پس از تغییرات نیاز است تا مجددا وارد محیط دیتابیس شوید. با استفاده از دستور زیر وارد محیط دیتابیس شوید:

mysql -u root -p

پس از وارد شدن به محیط command line دیتابیس دستور زیر را وارد نمایید تا از فعال سازی Query Cache و متغییر های اختصاص یافته شده اطمینان حاصل کنید. برای این منظور دستور زیر را در محیط Command Line دیتابیس وارد نمایید:

show variables like ‘query_cache_%’ ;

خروجی »

+——————————+———-+
| Variable_name | Value |
+——————————+———-+
| query_cache_limit | 262144 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 10485760 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+——————————+———-+
5 rows in set (0.01 sec)

همانطور که مشاهده میکنید متغییر query_cache_type با ON مقدار دهی شده است که به معنی فعال بودن Query Cache میباشد. برای تست وضعیت سرور ابتدا دیتابیس sample_db را انتخاب نمایید:

Use sample_db;

سپس Profiler را نیز فعال میکنیم:

SET profiling = 1;

پس از فعال سازی Profiler باید Query نمایش اطلاعات Customer را دو بار وارد و اجرا نمایید تا Query Cache برای ان فعال شود (Query بر روی حافظه کش شود). توجه داشته باشید اگر این دستور را یکبار وارد نمایید پس از نمایش Profiler بازدهی دستور بدون استفاده از Query Cache نمایش داده خواهد شد.

Select * from customers;
Select * from customers;

سپس دستور مربوط به نمایش Profiler را وارد نمایید تا بازدهی و میزان زمان اجرای مورد نیاز اخرین دستورات اجرا شده را مشاهده نمایید.

خروجی »

 

+———-+————+————————-+
| Query_ID | Duration | Query |
+———-+————+————————-+
| 1 | 0.00049250 | Select * from customers |
| 2 | 0.00026000 | Select * from customers |
+———-+————+————————-+
2 rows in set, 1 warning (0.00 sec)

همانطور که مشاهده میکنید دستور در اولین اجرا با زمان 0.00049250 اجرا و در مرحله دوم پس از کش شدن با زمان 0.00026000 اجرا شد.

 

با استفاده از این امکان میتوانید میزان بازدهی سرور را افزایش دهید. شما میتوانید از این آموزش برای افزایش بازدهی دیتابیس MySQL استفاده نمایید. فعال سازی Query Cache میتواند تقریبا سرعت را به دو برابر افزایش دهد. مقدار متغیر های ان را نیز میتوانید با توجه به نیاز خود و تست دیتابیس تغییر دهید.

 

موفق و پیروز باشید.

منابع:

WikiConfig.ir

Digital Ocean

MySql