10 ابزار ضروری برای مدیران سرورهای MySQL

Pt-Query-Digest .1

هیچ‌چیزی عذاب‌آورتر از عملکرد ضعیف و کند MySQL نیست. در بسیاری از مواقع سخت‌افزار سریع‌تر هم نمی‌تواند همه مشکلات را برطرف کند و عملکرد ضعیف می‌تواند ناشی از اجرای کند کوئری‌هایی باشد که جلوی اجرایی شدن کوئری‌های دیگر را سد می‌کنند و در نتیجه باعث کند شدن زمان پاسخ‌دهی می‌شوند. ازآنجاکه بهینه‌سازی کوئری‌ها بسیار کم‌هزینه‌تر از ارتقای سخت‌افزار تمام می‌شود، منطق حکم می‌کند که در نخستین اقدام از بهینه‌سازی MySQL لاگ‌های کوئری تجزیه‌وتحلیل شود. مسئولان پایگاه داده باید دائم لاگ‌های کوئری را تحلیل کنند. اگر تاکنون هرگز این کار را انجام نداده‌اید، اکنون زمان مناسبی برای شروع این کار است. فراموش نکنید حتی اگر شما به یک نرم‌افزار ثالث متکی هستید که ادعا می‌کند کار بهینه‌سازی را انجام می‌دهد، باز هم باید خودتان این کار را به شخصه انجام دهید.
Pt-Query-Digest، یک ابزار عالی برای تجزیه‌وتحلیل کوئری است. این ابزار به شکل فعال توسعه‌یافته و به‌طور کامل آزمایش‌های لازم را پشت سر گذاشته است. باوجود این‌که در خود توزیع‌های MySQL یک تحلیلگر لاگ کوئری به نام Mysqldumpslow گنجانده‌شده، اما این ابزار تاریخ گذشته و آزمايش نشده است و اسناد و راهنمای استفاده از آن نیز کامل نیست. Pt-Query-Digest کوئری‌ها را تجزیه‌وتحلیل کرده و با اطلاعات آماری جمع‌آوری‌شده در مورد زمان‌های اجرا و سایر معیارها یک گزارش تهیه می‌کند. از آنجا که لاگ‌های کوئری معمولا شامل هزاران کوئری است، تجزیه‌وتحلیل آن به یک ابزار نیاز دارد.
Pt-Query-Digest به شما کمک می‌کند تا کوئری‌هایی را که بیشترین زمان را برای اجراشدن در مقايسه با سایر کوئری‌ها صرف کرده‌اند، پیدا کنید. بهینه‌سازی این کوئری‌های کند باعث می‌شود تا با کم شدن میزان تاخیر MySQL سریع‌تر اجرا شود. هر چند فرآیند بهینه‌سازی کوئری پیچیده‌تر از این‌ها است، اما هدف اصلی یکسان است: پیدا کردن کوئری‌های کند، بهینه‌سازی آن‌ها و افزایش زمان پاسخ‌دهی کوئری.
برای دانلود Pt-Query-Digest به لینک زیر مراجعه کنید:

https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html#downloading 

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

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

Mydumper.2

امکان استخراج سریع داده از پایگاه داده MySQL برای پشتیبان‌گیری و تکثیر سرور بسیار حیاتی است. متاسفانه ابزار Mysqldump که در توزیع خود MySQL قرار دارد، تک‌رشته‌ای است و به همین دلیل در محیط‌های پرتراکم بسیار کند عمل می‌کند. اما ابزار پیشرفته Mydumper از چند رشته استفاده کرده و از Mysqldump بسیار سریع‌تر عمل می‌کند. این ابزار که با عنوان MySQL Data Dumper شناخته می‌شود، کار مدیریت پشتیبان‌گیری را برای شما انجام نمی‌دهد و تنها داده‌ها را با سرعت هر چه تمام از MySQL استخراج می‌کند و بعد می‌توانید در زمان‌های کم باری سرور از این داده‌های استخراج‌شده نسخه پشتیبان تهیه کنید.  نکته فنی که باید درباره Mydumper بدانید این است که در زمان کار جدول‌ها را قفل می‌کند، بنابراین نمی‌توان از آن برای پشتیبان‌گیری در ساعت‌های پرکاری استفاده کرد. از این ابزار برای تکثیر سرور نیز می‌توانید استفاده کنید. درحالی‌که سایر ابزارها از کل هارد درايو یک نسخه دوم تهیه می‌کنند، Mydumper تنها داده‌های MySQL مورد نیازتان را تکثیر می‌کند.
برای دانلود Mydumper به لینک زیر مراجعه كنيد:

https://github.com/maxbube/mydumper

3. XtraBackup

اگر پایگاه‌های داده‌تان به‌اندازه‌ای شلوغ و پرکار هستند که هرگز این فرصت را پیدا نمی‌کنید که جدول‌های آن را برای پشتیبان‌گیری قفل کنید، XtraBackup در این زمینه به شما کمک می‌کند. این ابزار منبع باز رایگان بدون مسدود کردن پایگاه داده از آن پشتیبان تهیه می‌کند.
XtraBackup همچنین این امکان را در اختیارتان می‌گذارد تا تنها از داده‌هایی نسخه پشتیبان تهیه کنید که از آخرین زمان پشتیبان‌گیری کامل تغییر کرده‌اند. 
برای دانلود XtraBackup  به لینک زیر مراجعه کنید:

https://www.percona.com/software/mysql-database/percona-xtrabackup

4. Tcprstat

Tcprstat، درخواست‌های TCP را تحت نظر قرار داده و یک فهرست آماری درباره زمان‌های پاسخ‌دهی سطح پایین تهیه می‌کند. آشنایی با زمان‌های پاسخ‌دهی در تعیین وضعیت عملکرد یک پایگاه داده نقش زیادی ایفا می‌کند. به همین دلیل Tcprstat یک ابزار مهم برای مدیران پایگاه داده محسوب می‌شود. اصول کار به این شكل است که یک سرویس (در اینجا MySQL)، یک درخواست (کوئری) را می‌پذیرد، این درخواست را انجام می‌دهد (زمان اجرا) و نتایج را به‌صورت پاسخ ارسال می‌کند. زمان پاسخ‌دهی این سرویس مدت‌زمانی است که طول می‌کشد تا یک درخواست دریافت شده و پاسخ آن ارسال شود. هر چه زمان پاسخ‌دهی کوتاه‌تر باشد، درخواست‌های بیشتری را می‌توان در یک بازه زمانی مشخص پاسخ داد. پردازش موازی و سایر عوامل سطح پایین نقشی قابل‌توجه در این زمینه ایفا می‌کنند. Tcprstat به شما کمک می‌کند زمان‌های پاسخ‌دهی را کاهش دهید.
برای دانلود Tcprstat  به لینک زیر مراجعه کنید: 

https://github.com/Lowercases/tcprstat

5.Pt-Table-Checksum

تغییر تدریجی داده مشکل بزرگی است که معمولا برای محیط‌های پویای MySQL رخ می‌دهد. این مشکل که در آن داده‌های اسلیو از همگام بودن با داده‌های مستر خارج می‌شود، اغلب ناشی از نوشتن داده روی یک اسلیو یا اجرای برخی از کوئری‌های غیرمتمرکز روی مستر است. بدتر از آن این است که این اختلاف بین داده‌ها ممکن است تا زمان از کار افتادن کل سیستم نادیده گرفته شود. Pt-Table-Checksum ابزاری است که محاسبات پیچیده و حساس لازم را برای تایید داده‌ها در دو یا چند جدول انجام می‌دهد. Pt-Table-Checksum به شما اجازه بررسی تمام جزییات اختلاف بین داده‌های مستر و اسلیو را می‌دهد. به ‌غیر از مشکلات مرتبط با سازگاری تکرار، مشکلات دیگری نیز در ارتباط با تاییدیه داده وجود دارد. اندازه جدول یکی از آن‌ها است. فرمان CHECKSUM TABLE در MySQL برای جدول‌های کوچک کفایت می‌کند، اما جدول‌های بزرگ برای جلوگیری از قفل‌شدن‌های طولانی‌مدت و افزایش بار منابع پردازنده و حافظه به محاسبات تکه‌تکه نیاز دارند. Pt-Table-Checksum این کار را برای شما انجام می‌دهد.
برای دانلود pt-table-checksum به لینک زیر مراجعه کنید:

https://www.percona.com/doc/percona-toolkit/LATEST/pt-table-checksum.html#downloading

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

چگونه از طریق جاوا به یک پایگاه داده MySQL متصل شویم

6.Pt-Stalk

برای عیب‌یابی مشکلات به وجود آمده در پایگاه داده MySQL ابتدا باید بخش‌های مختلف آن را تحت نظر داشت و با اطلاعات به‌دست‌آمده از وضعیت سرور مشکلات را برطرف کرد. اصول کار این‌گونه است که خود شما باید یک اسکریپت بنویسید: که یک مشکل را تشخیص داده و بعد شروع به لاگ گرفتن از داده‌های اضافی برای کمک به عیب‌یابی کند. اما مشکل اینجا است که شما نمی‌دانید چه زمانی قرار است مشکلی برای سیستم به وجود آید.  خوشبختانه متخصصانی که می‌دانند چه زمان‌هایی قرار است MySQL کار خود را به‌درستی انجام ندهد و به‌منظور برطرف کردن مشکلات ناشی از آن، ابزاری به نام Pt-Stalk را آماده کرده‌اند. Pt-Stalk منتظر رخ دادن شرایطی خاص می‌ماند، سپس شروع به جمع‌آوری اطلاعات کرده تا به شما در تشخیص مشکل کمک کند. این ابزار نه‌تنها به جمع‌آوری اطلاعات استانداردی که MySQL می‌تواند درباره خودش گزارش دهد، می‌پردازد، بلکه اطلاعات زیاد دیگری در مورد lsof, strace, tcpdump و نظاير آن را هم در اختیار شما می‌گذارد. 
pt-stalk قابل پیکربندی است، بنابراین می‌توان آن را برای شناسایی هر مشکلی به کار گرفت. اولین کاری که باید انجام دهید این است که یک شرط برای ایجاد یک عامل محرکه فعال شدن این ابزار تعیین کنید.
برای دانلود Pt-Stalk به لینک زیر مراجعه کنید:

https://www.percona.com/doc/percona-toolkit/LATEST/pt-stalk.html#downloading

7.Mycheckpoin

همیشه هم نباید منتظر باشید تا اتفاقی رخ دهد و بعد به سراغ رفع آن مشكل بروید. داشبوردها راهکاری را فراهم می‌کنند تا شما بتوانید محیط MySQL خود را برای مشکلات احتمالی قبل از رخ دادن تحت نظر داشته باشید. اپلیکیشن‌های رایگان و تجاری گوناگونی برای تحت نظر داشتن MySQL وجود دارد. Mycheckpoint علاوه بر قابلیت‌های زیادی که در اختیار شما می‌گذارد، رایگان و منبع باز است. اگرچه نویسنده Mycheckpoint توسعه آن را متوقف کرده، اما کدهای منبع و فایل‌های باینری آن همچنان به‌صورت رایگان در اختیار شما است.
مثل خیلی از راهکارهای مانیتورینگ دیگر، Mycheckpoint  یک رابط تحت وب دارد. در شکل 1 مثالی از یک نمودار آن را مشاهده می‌کنید.

Mycheckpoint می‌تواند به شکلی پیکربندی شود تا هم MySQL و هم معیارهای سرور مانند InnoDB Buffer Pool Flushe، جدول‌های موقتی ساخته‌شده، بار کاری سیستم‌عامل، میزان مصرف حافظه و موارد دیگر را تحت نظر بگیرد. اگر علاقه‌ای به نمودارها ندارید، Mycheckpoint می‌تواند گزارش‌های قابل‌خواندن توسط انسان را توليد کند.
برای دانلود Mycheckpoint  به لینک زیر مراجعه کنید:

https://code.google.com/archive/p/mycheckpoint/

8. Shard-Query

با ابزار Shard-Query سرعت کوئری‌گیری از داده‌های پارتیشن‌شده یا تقسیم‌شده بین چند سرور به میزان فراوانی افزایش پیدا می‌کند. کوئری‌هایی که از ساختار زیر تبعیت می‌کنند، می‌توانند از مزایای Shard-Query بهره‌مند شوند:
• Subqueries in the FROM clause
• UNION and UNION ALL
• IN
• BETWEEN
می‌توان از توابع جمع SUM, COUNT, MIN و MAX همراه با این ساختارها استفاده کرد. برای مثال، می‌توان این کوئری را به‌موازات Shard-Query اجرا کرد:

SELECT DayOfWeek, COUNT(*) AS c
FROM ontime_fact

JOIN dim_date USING(date_id)
WHERE Year

BETWEEN 2000 AND 2008
GROUP BY DayOfWeek

ORDER BY c DESC;

نتایج نشان می‌دهد، موازی‌سازی این کوئری زمان پاسخ‌دهی آن را تا نزدیک به 85 درصد و از 21 ثانیه به 3 ثانیه کاهش می‌دهد.
Shard-Query یک ابزار مستقل نیست و به برنامه‌های دیگری مانند Gearman نیاز دارد و تنظیم و پیاده‌سازی آن کار به نسبت دشواری است. 
برای دانلود Shard-Query به لینک زیر مراجعه كنيد:

https://mariadb.com/kb/en/library/shard-query/

9.Pt-Archiver

هر چه جدول‌ها بزرگ‌تر می‌شوند، کوئری گرفتن از آن‌ها کندتر انجام می‌شود. عوامل متعددی هستند که روی زمان پاسخ‌دهی تاثیر می‌گذارند، اما اگر همه‌چیز را بهینه‌سازی کرده باشید، تنها مضمون باقی‌مانده همین بزرگ بودن بیش‌ازحد یک جدول است. آرشیو کردن ردیف‌ها در این جدول می‌تواند مشکل را برطرف کند. شما باید کاملا مراقب حذف ردیف‌ها باشید. آرشیو کردن به‌دقت بالایی نیاز دارد تا باعث از بین رفتن داده‌ها نشود، جدول مربوط بیش از حد قفل باقی نماند و فرآیند آرشیو بیش‌ازحد به MySQL و سرور فشار وارد نکند. Pt-Archiver ابزاری است که این کار را برای شما انجام می‌دهد.
Pt-Archiver به‌طور خودکار مراحل فنی آرشیو کردن را انجام می‌دهد. تنها کاری که باید انجام دهید این است که بگویید کدام جدول باید بایگانی شود، چگونه می‌توان ردیف‌های بایگانی را شناسایی کرد و این ردیف‌ها را کجا باید بایگانی کرد. این ردیف‌ها را می‌توان پاک‌سازی، کپی به جدولی دیگر یا روی یک فایل خروجی برای بازیابی احتمالی در آینده ذخیره کرد.
برای دانلود pt-archiver  به لینک زیر مراجعه كنيد:

https://www.percona.com/doc/percona-toolkit/LATEST/pt-archiver.html#downloading