15 ترفند مفید برای Tune کرد (بهینه سازی و بهبود عملکرد) MySQL/MariaDB

[Habili]

در این مقاله ما به شما یک سری ترفندهای اصلی و مفید درباره نحوه بهینه سازی و افزایش سرعت دیتابیس MySQL/MariaDB نمایش خواهیم داد. لطفا دقت کنید که در این مقاله فرض بر این است که شما از قبل پایگاه داده MySQL یا MariaDB را روی سیستم تان نصب دارید. به هر حال اگر نمیدانید چگونه آنها را روی سیستم تان نصب کنید میتوانید از راهنما های مفصل زیر استفاده کنید.

همچنین توصیه میشود مطالعه کنید.

آموزش نصب MariaDB 10 در CentOS 7/8 - RedHat 7/8 - Debian 8/9/10 - Ubuntu 16/18/19

آموزش دستورات پایه ای و مقدماتی MySQL/MariaDB - بخش 1

آموزش mysql پیشرفته

آموزش تصویری کار با MySQL

نحوه بک آپ گیری از MySQL/MariaDB و ریستور کردن دیتابیس

نحوه پیدا کردن مسیر my.cnf - http.conf / apache2.conf - php.ini در لینوکس (Apache, PHP و MySQL)

ترفندهای مفید برای افزایش سرعت و بهینه سازی دیتابیس MySQL/MariaDB

نحوه Replication کردن MySQL (یا Master-Slave Replication)



MySQL یک سیستم قوی و open source برای مدیریت نسبی دیتابیس و یا به عبارت کوتاه تر RDBMS میباشد. این سیستم حدود 20 سال قبل در سال 1995 منتشر شد. در این سیستم از Structured Query Language (یا SQL) استفاده شده است که یکی از معروف ترین انتخاب ها برای مدیریت محتوا درون یک دیتابیس میباشد. آخرین ورژن مای اسکیول 5.6.25 میباشد که در 29 May سال 2015 منتشر شد.

حقیقت جالب درباره MySQL این است که نام آن از نام دختر Michael Widenius که My میباشد نشآت گرفته است. اگر چه دنیایی از حقایق جالب درباره MySQL وجود دارد اما در این مقاله ما قصد داریم که یکسری تمرین های مفید را به شما نمایش دهیم که با کمک آنها شما میتوانید سرور مای اسکیول تان را مدیریت کنید.


در April سال 2009 پروژه MySQL توسط Oracle خریداری شد. در نتیجه این اتفاق شاخه ای مجتمع از مای اسکیول با نام MariaDB ایجاد گردید. هدف اصلی از ایجاد این شاخه این بود که پروژه آزاد از تحت لایسنس General Public License بودن باشد.

امروز MySQL و MariaDB یکی از رایج ترین RDBMS های مورد استفاده برای اپلیکیشن های وب مانند WordPress, Joomla, Magento و دیگر اپلیکیشن ها میباشد.


همچنین توصیه میشود مطالعه کنید.

آموزش نصب LAMP در RHEL/CentOS 7

آموزش نصب LAMP در Ubuntu 15.04


توجه : قبل از شروع، دقت کنید که تنظیمات مای اسکیول unique است و قبل از هرگونه تغییری باید به دقت بررسی شود.

مواردی که باید بدانید.

• فایل کانفیگ MariaDB در مسیر /etc/my.cnf و MySQL در مسیر /etc/mysql/my.cnf قرار گرفته، هر زمان که این فایل را تغییر دهید نیاز هست که سرویس mysql را به منظور اعمال تغییرات restart کنید.
• در این مقاله ما از MySQL ورژن 5.6 به عنوان نمونه استفاده میکنیم.

1 - فعال کردن InnoDB file-per-table

در ابتدا مهم است که توضیح دهیم InnoDB یک موتور storage یا (storage engine) است. MySQL/MariaDB به عنوان موتور storage، به صورت پیش فرض از InnoDB استفاده میکند. در گذشته MySQL برای نگهداری از جداول (tables) و index های دیتابیس از یک سیستم tablespace استفاده میکرد. این شیوه برای سرورهایی که تنها هدف آنها پروسس دیتابیس بود و دیسک storage آنها برای هیچ هدف دیگری استفاده نمیشد، معنا داشت.

InnoDB شیوه منعطف تری را فراهم آورده و اطلاعات هر دیتابیس در یک فایل دیتای .ibd ذخیره میشود. هر فایل .ibd یک tablespace از خودش را ارائه میدهد. در اصل tablespace هنگام حذف دیتا باعث ایجاد فضای خالی بین سکتورها شده (یعنی Data Stream Contagency از بین خواهد رفت) ولی با داشتن .idb جداگانه برای table ها این fragmentation فقط برای جداولی که در آن idb هستند رخ خواهد داد. به این شیوه، عملکردهای دیتابیس مانند "TRUNCATE" (برای حذف کردن دیتای یک جدول بدون اینکه تغییری در ساختار جداول ایجاد شود) میتوانند سریعتر انجام شوند و همچنین شما میتوانید فضای استفاده نشده را به هنگام drop یا truncate کردن یک جدول دیتابیس، اصلاح کنید.

فایده دیگر این تنظیمات در حقیقت این است که شما میتوانید یکسری از جداول دیتابیس را در storage مجزا قرار دهید و این امر باعث بهبود بسیار عالی در لود I/O روی دیسک شما شود.

innodb_file_per_table به صورت پیش فرض در مای اسکیول 5.6 و بالاتر فعال است. شما میتوانید آنرا در فایل /etc/my.cnf اضافه کنید. که چیزی شبیه به زیر میباشد.

innodb_file_per_table=1 

2 - ذخیره دیتای دیتابیس MySQL روی پارتیشن مجزا

نکته : این تنظیمات تنها روی MySQL کار میکند و روی MariaDB کار نمیکند.

گاهی اوقات read/write سیستم عامل میتواند performace سرور mysql شما را کند سازد. مخصوصا اگر روی hard drive یکسانی با سیستم عامل قرار گرفته باشد. به جای این مورد من توصیه میکنم که از یک هارد درایو مجزا (ترجیحا SSD) برای سرویس مای اسکیول استفاده کنید.

برای انجام این مورد شما نیاز خواهید داشت که درایو جدیدی را به computer/server خود متصل کنید، در این مقاله من فرض میکنم که درایو جدید تحت /dev/sdb خواهد بود.

مرحله بعد آماده سازی درایو جدید است.

# fdisk /dev/sdb 

اکنون برای ایجاد پارتیشن جدید "n" را فشار دهید سپس برای اینکه پارتیشن جدید را primary کنید، "p" را فشار دهید. بعد از آن شماره پارتیشن را از 1 تا 4 انتخاب کنید، سپس سایز پارتیشن را انتخاب و Enter کنید. در مرحله بعدی نیاز خواهید داشت که سایز پارتیشن را کانفیگ کنید.


همچنین توصیه میشود مطالعه کنید.

آموزش پارتیشن بندی LVM در Linux


اگر شما میخواهید کل دیسک را استفاده کنید Enter را یکبار دیگر فشار دهید در غیر اینصورت شما میتوانید به صورت دستی سایز پارتیشن جدید را تعریف کنید. بعد از اتمام برای write کردن تغییرات "w" را تایپ کنید. سپس شما نیاز خواهید داشت برای پارتیشن جدید فایل سیستم ایجاد کنید. این کار به آسانی همانند زیر انجام میشود.

# mkfs.ext4 /dev/sdb1 

اکنون ما پارتیشن جدیدمان را در یک فولدر mount میکنیم. من نام folder خود را SSD انتخاب کردم و آنرا در دایرکتوری root ساختم.

# mkdir /ssd/ 

اکنون آماده mount کردن پارتیشن جدیدی هستیم که آنرا در فولدر جدید ایجاد کردیم.

# mount /dev/sdb1  /ssd/ 

ما میتوانیم mount کردن دایرکتوری را به هنگام startup سیستم و با اضافه کردن خط زیر درون فایل /etc/fstab اجرا کنیم.

/dev/sdb1 /ssd ext3 defaults 0 0 

اکنون شما آماده انتقال MySQL به دیسک جدید هستید. ابتدا سرویس مای اسکیول را با دستور زیر stop کنید.

# service mysqld stop 

من به شما توصیه میکنم که برای جلوگیری از هر گونه تلاش برای write کردن دیتا در دیتابیس ها سرویس های Apache یا Nginx را نیز stop کنید.

# service httpd stop
# service nginx stop 

اکنون کل دایرکتوری MySQL را در درایو جدید copy کنید.

# cp /var/lib/mysql /ssd/ -Rp 

این مرحله با توجه به دیتابیس های MySQL شما ممکن است طول بکشد. بعد از تمام شدن آن دایرکتوری mysql را تغییر نام دهید.

# mv /var/lib/mysql /var/lib/mysql-backup 

سپس یک symlink برای دایرکتوری mysql ایجاد خواهیم کرد.

# ln -s /ssd/mysql /var/lib/mysql 

اکنون شما آماده برای start کردن mysql و web سرویس خود هستید.

# service mysqld start
# service httpd start
# service nginx start 

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



3 - بهینه سازی InnoDB buffer pool Usage

موتور InnoDB یک buffer pool برای cache دیتا و index ها در memory دارد. این مورد البته که میتواند کمک کند که query های maysql/mariadb به طور قابل توجهی سریعتر اجرا شوند. در اینجا انتخاب سایز مناسب نیازمند این است که شما یکسری تصمیمات مهم و دانش خوبی را درباره مصرف مموری سیستم تان داشته باشید.

مواردی که شما نیاز هست بررسی کنید :

• برای پروسس های دیگر چه مقدار مموری نیاز دارید. برای این منظور شما باید processes های سیستم page tables, socket buffers را بدانید.
• اینکه این سرور آیا اختصاصی برای mysql است و یا سرویس های دیگری روی آن در حال اجرا هستند که مموری مصرف میکنند.

روی یک سرور اختصاصی شما ممکن است بخواهید که حدود 60/70 درصد از مموری خود را به innodb_buffer_pool_size اختصاص دهید. اگر شما روی سرور سرویس های بیشتری در حال اجرا دارید شما باید میزان مموری که به innodb_buffer_pool_size اختصاص میدهید را مجدد بررسی کنید.

مقداری که شما باید در my.cnf ویرایش کنید :

innodb_buffer_pool_size 

4 - جلوگیری از Swap شدن در MySQL

swapping پروسه ایست که زمانیکه سیستم بخشی از مموری را به قسمت خاصی از فضای خالی هارد دیسک شما (که به آن "swap" میگوییم) انتقال میدهد. این اتفاق معمولا زمانی رخ میدهد که سیستم شما مموری فیزیکی کم می آورد و به جای خالی کردن RAM سیستم اطلاعات را درون دیسک قرار میدهد. حتی با وجود اینکه دیسک خیلی کندتر از RAM شما باشد.

به صورت پیش فرض این آپشنی فعال است.

# sysctl vm.swappiness
vm.swappiness = 60 

برای غیر فعال کردن swapping دستور زیر را اجرا کنید :

# sysctl -w vm.swappiness=0 

5 - تعریف حداکثر Connection های MySQL

قسمت max_connections مشخص میکند که در سرور شما چه مقدار connection های همزمان مجاز است. سرور MySQL/MariaDB برای کاربر با سطوح دسترسی supper admin مقدار یکی بیشتر از max_connections را مجاز میکند. (max_connections + 1) کانکشن تنها برای زماینکه کوئری مای اسکیول در حال اجراست باز است، بعد از آن بسته میشود و connection های جدید میتوانند رخ دهند.

در ذهن داشته باشید که کانکشن های بسیار زیاد میتوان میزان استفاده بالایی از ram را به همراه داشته باشند و منجر به قفل شدن سرور mysql شما شوند. معمولا سایت های کوچک نیاز به کانکشن ها بین 100 تا 200 هستند در حالیکه سایت های بزرگتر ممکن است نیاز به 500 - 800 یا حتی بیشتر داشته باشند. مقداری که در اینجا قرار میدهیم شدیدا به میزان استفاده از MySQL/MariaDB شما دارد.

شما میتوانید به صورت متغییر میزان max_connections را بدون نیاز به reset کردن سرویس mysql را با اجرای دستور زیر تغییر دهید.

# mysql -u root -p
mysql> set global max_connections := 300; 

6 - کانفیگ thread_cache_size در MySQL

بخش thread_cache_size میزان thread هایی که سرور شما باید cach کند را تعریف میکند. زمانیکه کلاینت disconnect میشود thread های او اگر کمتر از thread_cache_size باشند، در cache قرار داده میشود. در خواست های بعدی با استفاده از thread های ذخیره شده در cache کامل میشوند.

برای بهبود بخشیدن به performance خود شما میتوانید thread_cache_size را به یک عدد نسبتا بزرگ تعریف کنید. در cache hit rate آخرین کوئری های اجرا شده را در فضای کوچکی از مموری cache کرده و همین امر باعث لود سریعتر query ها میشود. برای پیدا کردن thread cache hit rate شما میتوانید تکنیک های زیر را استفاده کنید.

mysql> show status like 'Threads_created';
mysql> show status like 'Connections'; 

اکنون فرمول زیر را برای محاسبه درصد thread cache hit rate استفاده کنید.

100 - ((Threads_created / Connections) * 100) 

اگر شما عدد کمی را دریافت کردید این بدان معناست که اکثر کانکشن های جدید MySQL در حال شروع کردن thread جدید به جای load کردن آنها از cache هستند. در چنین مواردی شما مطمئنا میخواهید thread_cache_size را افزایش دهید.


نکته جالب در اینجا این است که thread_cache_size را میتوان به صورت متغیر بدون اینکه نیاز به restart کردن سرویس mysql داشته باشید، تغییر داد که برای این منظور میتوانید دستور زیر را اجرا کنید.

mysql> set global thread_cache_size = 16; 

7 - غیر فعال کردن Reverse DNS Lookups در MySQL

به صورت پیش فرض MySQL/MariaDB یک dns lookup از ip address/Hostname کاربرانی که از آنها کانکشن دریافت میکند، اجرا میکند. برای هر یک از client connection ها، ip آدرس ها برای یک host name توسط resolve چک میشود. بعد از آن hostname به یک آی پی resolve میشود تا بررسی شود که هر دو با هم match شوند.

متاسفانه این مورد ممکن است در موارد کانفیگ بد DNS یا مشکلات سرور DNS باعث تاخیر شود، و این دلیلی است بر اینکه شما میتوانید revers dns lookup را با اضافه کردن موارد زیر به فایل کانفیگ تان غیر فعال کنید.

[mysqld]
# Skip reverse DNS lookup of clients
skip-name-resolve 

بعد از اعمال تغییرات بالا شما باید سرویس مای اسکیول تان را restart کنید.



8 - کانفیگ query_cache_size در MySQL

اگر شما تعداد زیادی کوئری تکراری دارید و data اغلب تغییر نمیکند از query cache استفاده کنید. افراد اغلب مفهوم پشت query_cache_size را درک نمیکنند و این مقدار را به گیگا بایت (GB) تعریف میکنند که میتواند در حقیقت باعث کاهش کارایی بشود.

علت آن، این حقیقت است که thread ها نیاز دارند که cache را در طول آپدیت ها ببندند (مسدود کنند). معمولا مقدار 200 - 300 MB حتی بیشتر از حد، کافی میباشد. اگر وب سایت شما نسبتا کوچک باشد شما میتوانید مقدار 64 MB را امتحان کنید و به مرور آنرا افزایش دهید.

شما باید تنظیمات زیر را به فایل کانفیگ MySQL اضافه کنید.

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M 

9 - تنظیم tmp_table_size و max_heap_table_size

هر دو بخش باید سایز یکسانی داشته باشند و به شما کمک خواهند کرد که از رایت روی دیسک جلوگیری شود. tmp_table_size حداکثر مقدار سایز جدول های داخلی و in-memory میباشد. MyISAM موقع update یا insert تا زمان تمام شدن transaction کل table را lock میکند، ولی innodb میتواند فقط یک raw را lock یا تغییر میدهد. همزمان با اینکه دیگر raw ها توسط دیگر query ها میتوانند اجرا و lock بشوند، برای unlock شدن table با کوئری های مختلف مرتب آپدیت شده و همین امر باعث عملکرد بهتر innodb میشود، ولی MyISAM نصبت به innodb مکانیزم های بهتری برای ریکاوری table ها دارد.

و این مورد روی performance دیتابیس تاثیر خواهد گذاشت. admin های سیستم معمولا توصیه میکنند که به ازای هر گیگ از ram روی سرور برای هر دوی مقادیر زیر، مقدار 64M را بدهند.

[mysqld]
tmp_table_size= 64M
max_heap_table_size= 64M 

10 - فعال کردن Slow query Logs در MySQL

log کردن query های slow میتواند به شما در تشخیص مشکلات دیتابیس و دیباگ کردن آنها کمک کند. این مورد به آسانی با اضافه کردن مقدارهای زیر به فایل کانفیگ مای اسکیول شما فعال میشود.

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1 

بخش اول، log کوئری های slow را فعال میکند در حالیکه بخش دوم به مای اسکیول امکان ذخیره سازی log فایل واقعی را اعلام میکند. از long_query_time برای تعریف مقدار زمانیکه یک کوئری مای اسکیول طول میکشد تا اجرا شود استفاده کنید. در حقیقت اگر کوئری بیشتر از این زمان مشخص شده طول بکشید برای سیستم به منزله یک کوئری slow تلقی میشود.



11 - بررسی برای Connection های آزاد MySQL

کانکشن های آزاد (idle) منابع سیستم را مصرف میکنند و باید متوقف یا در صورت ممکن refresh شوند. این چنین کانکشن هایی در وضعیت sleep به سر میبرند و معمولا برای مدت زمانی طولانی در همان حالت باقی میماند. برای پیدا کردن connection های آزاد میتوانید دستور زیر را اجرا کنید.

# mysqladmin processlist -u root -p | grep “Sleep” 

این دستور به شما لیستی از پروسس هایی که در وضعیت sleep هستند را نمایش خواهد داد. این event زمانیکه یک کد از ارتباط دائمی به دیتابیس استفاده میکند نمایان میشود. مثلا هنگامیکه php با استفاده از mysql_pconnect یک کانکشنی را به دیتابیس باز میکند بعد از اینکه کوئری هایی را اجرا کرد و کانکشن را همانطور باز رها کرد این event نمایان میشود. این مورد باعث میشود که هر buffer به ازای هر thread در memory نگه داشته شود تا زمانیکه آن thread از بین برود.

اولین چیزی که شما در اینجا باید انجام دهید، چک کردن code و اصلاح آن میباشد. اگر به code بیرون سیستم دسترسی ندارید، میتوانید بخش wait_timeout را تغییر دهید. مقدار پیش فرض 28800 ثانیه است که شما میتوانید آنرا با خیال راحت به چیزی شبیه به 60 کاهش دهید.

wait_timeout=60 

12 - انتخاب فایل سیستم مناسب برای MySQL

انتخاب فایل سیستم درست برای دیتابیس شما حیاتی است. مهم ترین چیزی که نیاز دارید بررسی کنید در اینجا یکپارچگی، کارایی و راحتی در مدیریت است.

با توجه به توصیه های MariaDB بهترین فایل سیستم ها XFS, Ext4 و Btrfs هستند. همه آنها فایل سیستم های enterprise ی هستند که میتوانند با فایل های خیلی بزرگ و مقدارهای خیلی بزرگ برای ذخیره مورد استفاده قرار گیرند.

در زیر شما میتوانید یکسری اطلاعات مفید درباره این سه فایل سیستم مشاهده کنید.

Btrfs	Ext4	XFS	Filesystems
16EB	1EB	8EB	Maximum filesystem size
16EB	16TB	8EB	Maximum file size

مزایا و معایب فایل سیستم های لینوکسی به صورت مفصل در مقاله زیر توضیح داده شده است.

فایل سیستم لینوکس : Boot Loading, Disk Partitioning, BIOS, UEFI و File System Types (به زودی)



13 - تعریف max_allowed_packet در MySQL

مای اسکیول دیتا را به پکت ها تقسیم میکند. معمولا یک پکت مجزا در حقیقت یک ردیف (row) است که به سمت یک کلاینت ارسال میشود. بخش max_allowed_packet حداکثر سایز پکتی که میتواند ارسال شود را مشخص و تعریف میکند.

تعریف خیلی کم این مقدار میتواند باعث شود که یک query متوقف شود و شما یک error در error log مای اسکیول دریافت کنید. توصیه میشود که این مقدار را به سایز بزرگترین پکت خود تعریف کنید.



14 - بررسی کردن تنظیمات Performance در MySQL

اندازه گیری performance در MySQL/MariaDB موردی است که شما باید در کارهای اصلی که مرتبا بررسی میکنید قرار دهید. این مورد به شما کمک میکند که اگر چیزی در میزان مصرف منابع تغییر کرد یا نیاز به بهبود داشت آنرا بررسی کنید.

تنوعی از ابزارها برای معیار سنجی وجود دارند، اما ما قصد داریم به شما یکی از آسانترین و ساده ترین آنها را به شما پیشنهاد بدهیم. این ابزار mysqltuner نامیده میشود.

برای دانلود و اجرای آن دستورهای زیر را استفاده کنید.

# wget https://github.com/major/MySQLTuner-perl/tarball/master
# tar xf master
# cd major-MySQLTuner-perl-993bc18/
# ./mysqltuner.pl 

شما یک گزارش با جزئیات درباره میزان مصرف mysql خود و همچنین ترفندهایی برای آن دریافت خواهید کرد. در اینجا یک نمونه خروجی از یک سرویس نصب شده و پیش فرض mariadb را مشاهده میکنید.

15 - بهینه سازی و تعمیر جداول MySQL

گاهی اوقات جدول های MySQL/MariaDB کاملا به سادگی crashe میکند، مخصوصا زمانیکه سرور به طور غیر منتظره ای خاموش شود و یا فایل سیستم ناگهان هنگ کند و یا حتی در طول عملیات copy، هنگامی که دیتابیس هنوز در دسترس است ممکن است جدول ها crash کند. خوشبختانه یک ابزار open source رایگان به نام "mysqlcheck" وجود دارد که به صورت اتوماتیک دیتابیس همه جداول در لینوکس را بررسی، تعمیر و بهینه سازی میکند.

# mysqlcheck -u root -p --check --all-databases
# mysqlcheck -u root -p --auto-repair --optimize --all-databases

# mysqlcheck -u root -p --check databasename
# mysqlcheck -u root -p --auto-repair --check --optimize databasename 

امیدوارم مقاله بالا برای شما مفید بوده باشد و به شما در بهینه سازی و بالا بردن کارایی سرور mysql تان کمک کرده باشد. مثل همیشه اگر هر گونه سوال نظر یا ترفندی داشتید آنرا با ما به اشتراک بگذارید.

همچنین توصیه میشود مقالات زیر را هم مطالعه فرمایید.

20 دستور MySQL برای ادمین های Database

4 ابزار مفید برای مانیتور کردن MySQL (به زودی)

دستورات Backup و Restore در MySQL

نحوه وصل شدن به MySQL بدون وارد کردن پسورد Root در ترمینال

آموزش نحوه استفاده از Function های متعدد در MySQL و MariaDB - بخش 2

نحوه تست ارتباط دیتابیس MySQL با استفاده از اسکریپت PHP

دستورات Select در MySQL

چطور از Command Line لینوکس Querie های MySQL/MariaDB را مستقیما اجرا کنیم؟

آموزش نحوه تغییر پسورد root در MySQL / MariaDB در لینوکس



با سپاس از تمام دوستانی که ما را در تهیه این مقاله یاری کردند.

موفق باشید.