تنظیم MySQL اغلب با برخی از تنظیمات اساسی شروع می شود – شاید شما یک تنظیم را تنظیم کنید یا یک شاخص اضافه کنید و یک افزایش سریع را مشاهده کنید. اما این اصلاحات ساده به ندرت طولانی مدت را حفظ می کنند ، مانند زمانی که پایگاه داده شما ترافیک بیشتری پیدا می کند یا داده های شما رشد می کند ، زیرا آنها با مسائل عمیق تر مورد نظر روبرو نمی شوند.
رویکرد سنتی شکاف هایی را که بعداً به مشکلات واقعی تبدیل می شود ، می گذارد. به همین دلیل شما باید به اشتباهات بزرگتر ، مانند اجرای در تنظیمات پیش فرض یا اجازه دادن به اسلاید تعمیر و نگهداری توجه کنید. این مواردی است که شما را کند می کند ، کاربران را ناامید می کنید و شما را در یک تقلا دائمی برای جلب توجه قرار می دهید.
1. نادیده گرفتن بهینه سازی پرس و جو
یک پرس و جو بد می تواند آسیب جدی وارد کند. یک جدول با 10 میلیون ردیف بگیرید – اگر یک پرس و جو در جستجوی یک رکورد بدون شاخص است ، هر ردیف را اسکن می کند. این نه تنها کند است ، بلکه می تواند مصرف CPU را به 90 ٪ سوق دهد و سایر نمایش داده شد. یک بهینه نشده انتخاب کردن می تواند یک عمل 50ms را به یک شعار 5 ثانیه تبدیل کند و کل سیستم را با آن پایین بکشد.
با استفاده از توضیح و تجزیه و تحلیل برای تشخیص و رفع نمایش داده شد
MySQL دو ابزار اصلی برای کمک به شما در درک و رفع سؤالات مشکل خود دارد. شروع کردن توضیح دادنبشر این برنامه MySQL را برای اجرای پرس و جو شما ارائه می دهد. این مانند یک طرح است که چگونه سرور داده های شما را واگذار می کند.
EXPLAIN SELECT name FROM customers WHERE email="john@example.com";
شما یک جدول با ستون هایی مانند ID ، SELECT_TYPE ، جدول، نوع ، کلیدوت ردیفبشر تمرکز کردن نوع وت ردیفبشر اگر نوع می گوید: “همه” ، MySQL در حال انجام اسکن کامل جدول است ، این بدان معنی است که در حال خواندن هر ردیف و بدون میانبر است. ستون ردیف تخمین می زند که چند مورد بررسی می کند ، بنابراین در یک جدول 10 میلیون ردیف ، که بسیار خوب می تواند 10 میلیون باشد. شما همچنین ممکن است ببینید کلید ذکر شده به عنوان تهی، به این معنی که هیچ شاخصی کمک نمی کند.
برای رفع این مشکل ، می توانید یک شاخص را در ستونی که فیلتر می کنید اضافه کنید:
CREATE INDEX idx_email ON customers(email);
توضیح دادن فقط پیش بینی می کند ، در واقع پرس و جو را برای بررسی اجرا نمی کند. در MySQL 8.0.18 و بعد ، می توانید استفاده کنید تجزیه کردن برای اجرای پرس و جو و بازگشت جزئیات واقعی مانند ردیف های اسکن شده ، زمان اجرای و حتی حلقه های حلقه برای پیوستن یا زیرمجموعه ها.
اشتباهات پرس و جو مشترک برای جلوگیری از
در صورت عدم دقت ، برخی از عادت های پرس و جو می توانند عملکرد مخزن را داشته باشند. آنها به راحتی می توانند به راحتی برطرف شوند ، اما وقتی می دانید چه چیزی را جستجو کنید ، آسان است.
-
انتخاب کردن: با استفاده از انتخاب کردن هر ستون در جدول را می کشد ، حتی اگر فقط به یک زن و شوهر احتیاج دارید. این پهنای باند و حافظه را هدر می دهد. بگویید شما یک جدول با 20 ستون دارید ، اما شما فقط می خواهید به آن نگاه کنید نام وت ایمیل ستون ها استفاده کردن نام را انتخاب کنیدبا ایمیل برای هدف قرار دادن آن ستون ها و جلوگیری از جستجوی بقیه.
-
پیوستن به صورت نامشخص: پیوستن به بدون فهرست در زمینه های پیوستن باعث می شود MySQL هر ردیف را اسکن کند – مانند 10،000 ردیف در یک جدول در برابر 100000 در دیگری ، به یک میلیارد مقایسه. در در بند ** (به عنوان مثال ، ** در سفارشات. customer_id = customer.id) مشخص می کند که کدام ستون ها با هم مطابقت دارند. برای جلوگیری از اسکن کامل ، آن ستون ها را فهرست کنید.
-
فیلترهای گمشده: اگر استفاده نمی کنید کجا یا محدود کردن برای فیلتر کردن سؤالات خود ، MySQL هر ردیف موجود در جدول را می خواند ، حتی اگر به همه آنها احتیاج ندارید. این برای جداول بزرگ وحشیانه است. اما وقتی فیلترها را اضافه می کنید نام را از مشتریان انتخاب کنید که در آن فعال = 1 حد 10، سپس شما زمان اجرای را کاهش می دهید زیرا درخواست می کنید که این پرس و جو فقط 10 مشتری فعال را برگرداند.
2. بیش از حد در تنظیمات پیش فرض
خارج از جعبه ، MySQL برای سازگاری و ایمنی تنظیم می شود ، نه سرعت. به عنوان مثال ، innODB_BUFFER_POOL_SIZE از 128 مگابایت شروع می شود – در یک سرور 16 گیگابایتی ، این کمتر از 1 ٪ از رم شما است ، بنابراین با خواندن دیسک ثابت گیر خواهید کرد. پیش فرض ممکن است برای تنظیمات کوچک کار کند اما تمایل دارد با ترافیک واقعی خیلی سریع از بین برود.
تنظیمات شما نیاز به تغییر دارید
-
innODB_BUFFER_POOL_SIZE: درصد زیادی از حافظه سیستم موجود را اختصاص دهید تا داده های CACHED مکرر InnoDB را ذخیره کنید. این دیسک I/O را به حداقل می رساند.
-
max_connections: اتصالات خود را بر اساس ترافیک مورد انتظار و کاربران همزمان مقیاس کنید ، اما استفاده از حافظه کلی را برای گرفتن فشار منابع کنترل کنید.
-
tmp_table_size: تنظیم شده برای قرار دادن مجموعه داده های موقت بزرگ در حافظه. این از مبادله های غیر ضروری دیسک در هنگام نمایش داده های پیچیده جلوگیری می کند.
-
innODB_LOG_FILE_SIZE: اندازه گزارش معاملات برای رسیدگی به فعالیت های نوشتن بالا بدون مشکل و جلوگیری از تنگناها در پردازش معاملات.
-
Table_open_cache: با تعداد جداول مورد استفاده در محیط شما برای کاهش عملیات تکراری پرونده مطابقت داشته باشید.
-
thread_cache_size: اندازه برای بازیافت موضوعات متداول به طور مؤثر و پرش از سربار ایجاد مداوم موارد جدید.
-
innODB_FLUSH_LOG_AT_TRX_COMMIT: بین ایمنی داده های دقیق یا سریعتر نوشتن ، متناسب با تحمل خود برای عملکرد در مقابل خطر بازیابی انتخاب کنید.
-
key_buffer_size: در صورت استفاده از موتورهایی که از حافظه پنهان کلیدی بهره مند می شوند ، به ویژه در محیط های ذخیره سازی مختلط ، حافظه را برای نمایه سازی اختصاص دهید.
-
sort_buffer_size: حافظه کافی را برای هر اتصال برای کارهای مرتب سازی پیچیده اختصاص دهید اما از جابجایی کلی برای نمایش داده های ساده تر برای تعادل سرعت با استفاده از حافظه مسئول خودداری کنید.
تنظیم با Relem آسانتر شد
انجام این کار با دست به معنای آزمایش و حدس زدن زیادی است. REEMEEM به حجم کار واقعی شما نگاه می کند و مقادیر دقیق را پیشنهاد می کند. این برنامه با نظارت بر معیارهای عملکرد ، لکه بینی تنگناها و پیشنهاد بهینه سازی ، کل فرایند تنظیم را خودکار می کند. شما می توانید این توصیه های پیکربندی را به صورت خودکار یا از طریق کار Cron یا به روزرسانی های یک کلیک اعمال کنید ، یا در صورت تمایل کنترل ، ابتدا آنها را در داشبورد مرور کنید.
اطلاعات بیشتر در مورد روند تنظیم Releme را در اینجا بخوانید.
3. استراتژی های نادرست نمایه سازی
ایندکس ها با تجارت همراه هستند. اگر بیش از حد ، مانند 10 فهرست ، در یک جدول واحد اضافه کنید ، هر بار که اجرا می کنید درج کردن یا بروزرسانی، MySQL باید تمام این شاخص ها را به روز کند. این می تواند به چیزی مانند 10 میلی ثانیه در هر نوشتن برسد.
از طرف تلنگر ، اگر شاخص های کافی ندارید ، خوانده های شما رنج می برند. ساده انتخاب کردن روی یک میز با یک میلیون ردیف ممکن است 20 میلی ثانیه با یک شاخص طول بکشد ، اما بدون آن ، این ممکن است به 2 ثانیه یا بیشتر کشیده شود تا یک اسکن کامل جدول انجام شود.
چگونه می توان شاخص هایی را که به آن احتیاج ندارید پیدا کنید
استفاده کردن نمایش شاخص از Table_Name، که تمام فهرست ها را روی یک جدول به همراه ستون هایی که آنها پوشش می دهند لیست می کند. سپس ، بررسی کنید که آیا نمایش داده شدگان شما با اجرای ابزاری مانند PT-Index-Usage (از Percona Toolkit) در برابر ورود به سیستم Querd Query خود از آنها استفاده می کنند.
اگر یک شاخص استفاده را نشان نمی دهد ، به پرس و جوهای شما کمک نمی کند و فقط سربار را به نوشتن اضافه می کند. می توانید آن را با آن حذف کنید Drop Index index_name on table_nameبشر
نگاهی به مقاله مفصلی از Releme بیندازید.
گزینه های فهرست بندی دقیق تر
برای کسب اطلاعات بیشتر از فهرست های خود ، تکنیک هایی مانند آن را در نظر بگیرید پوشش شاخص ها، که تمام ستون ها را که نیاز به پرس و جو در فهرست دارد ، بسته بندی می کند (ایجاد index idx_cover در جدول (ستون 1 ، ستون 2)) بنابراین MySQL می تواند داده های واکشی را از جدول پرش کند.
سپس ، وجود دارد شاخص های جزئی این تمرکز فقط روی زیر مجموعه ای از ردیف ها (IDX_ACTION IDX_ACTION را در کاربران (ID) ایجاد کنید که در آن فعال = 1) – مانند مواردی که با یک شرایط خاص مطابقت دارند – قطع اندازه شاخص و تقویت پرس و جو و نوشتن عملکرد با نادیده گرفتن داده های بی ربط.
و سرانجام ، شاخص های کامپوزیت چندین ستون را در یک ترکیب کنید (ایجاد index idx_multi در جدول (ستون 1 ، ستون 2)) ، اجازه دهید نمایش داده شده هایی که در چندین زمینه فیلتر می شوند با باریک کردن نتایج به جای چندین پاس ، سریعتر انجام می شوند.
4.
هر بار که یک برنامه ارتباط جدیدی با MySQL باز می کند ، برای تنظیم دست بین برنامه و سرور حدود 1-2 میلی ثانیه طول می کشد. اگر برنامه شما به طور مداوم اتصالات را باز می کند ، آن میلی ثانیه می توانند تا 1 تا 2 ثانیه تأخیر اضافه کنند.
به علاوه ، MySQL محدودیتی دارد max_connections، به طور پیش فرض روی 151 تنظیم کنید ، که محدودیت تعداد اتصالات را می توان به یکباره باز کرد. اگر ترافیک سنبله می کند و به آن کلاه ضربه می زنید ، کاربران جدید نمی توانند با خطای “اتصالات خیلی زیاد” وصل شوند و ضربه بزنند.
نحوه باز نگه داشتن اتصالات و کاهش سربار
اتصالات مداوم از آنچه در حال حاضر در آنجاست استفاده کنید و این امر از تأخیر 1-2 ms در باز کردن موارد جدید جلوگیری می کند. بسته به اکوسیستم و نیازهای عملکرد شما ، چند گزینه دارید:
در PHP ، فعال کردن mysqli.Permentired به اتصالات MySQL اجازه می دهد تا درخواست ها ادامه یابد.
در جاوا می توانید استفاده کنید هیکاریک برای حفظ استخر اتصالات آماده برای استفاده ، که برای سناریوهای با بار بالا ایده آل است.
5. نادیده گرفتن نگهداری پایگاه داده
هنگامی که از تعمیر و نگهداری روزمره استفاده می کنید ، جداول مورد غفلت نفخ می شوند. حذف و به روزرسانی ها شکاف هایی را ترک می کند ، که MySQL به طور خودکار باقی نمی ماند. به عنوان مثال ، یک میز 1 گیگابایتی می تواند به 1.5 گیگابایت با 30 ٪ فضای اضافی در بالای سر هدر رود. این ظروف سرباز یا مسافر MySQL را وادار می کند تا سخت تر کار کند ، از طریق داده های تکه تکه شده اسکن شود و با حاشیه قابل توجهی نمایش داده شود.
بهینه سازی و تجزیه و تحلیل
-
دویدن سفارشات جدول را بهینه کنید؛ MySQL را مجبور می کند تا جدول را از ابتدا بازسازی کند ، فقط از داده های فعال کپی کند و شکاف های خالی را بیرون بکشد.
-
پیگیری سفارشات جدول را تجزیه و تحلیل کنید؛ آمار را تازه کنید تا MySQL Optimizer بتواند سریعترین راه را برای اجرای نمایش داده های شما انتخاب کند.
نگهداری خودکار
شما باید به طور مرتب جداول خود را بهینه کنید ، اما نیازی به کودک نگه داشتن این کار ندارید. شما می توانید از برنامه ریز رویداد MySQL استفاده کنید:
CREATE EVENT optimize_orders
ON SCHEDULE EVERY 1 MONTH
DO OPTIMIZE TABLE orders;
6. نظارت بر عملکرد به طور مؤثر
اگر مکانیسم های نظارت مناسب در محل کار ندارید ، مشکلاتی مانند پرس و جو را از دست می دهید که ناگهان 10 ثانیه به جای 1 یا تعویض استخر بافر به دیسک می رسد زیرا خیلی کوچک است – مواردی که کاربران قبل از انجام آن متوجه می شوند.
اگر می خواهید زودتر مشکل را مشاهده کنید ، اطمینان حاصل کنید که معیارهای زیر را پیگیری می کنید:
- تأخیر mysql
- توان
- تعداد نمایش داده شد
- مشتری های سقط شده
- استفاده از CPU
- استفاده از RAM
- مبادله استفاده از مبادله
- عملیات ورودی/خروجی در هر ثانیه (IOPS)
می توانید اطلاعات دقیق تری در مورد این معیارها در اینجا بیابیدبشر
نظارت با Releme
Relem یک انتخاب جامد برای نظارت است زیرا باعث می شود این معیارها نگاه کنید و مانند برخی از ابزارهای قدیمی ، سردرد نیست. رابط کاربری تمیز و کاربر پسند است و راهی ساده برای تجسم بار پردازنده ، دیسک I/O یا نمایش داده های آهسته به شما می دهد تا بتوانید به طور مؤثر مشکلات را برطرف کنید. شما حتی می توانید محدوده زمانی خاص را بررسی کنید تا نحوه عملکرد سرور MySQL خود را با گذشت زمان مشاهده کنید.
در مورد قابلیت های نظارت بر Relem در اینجا بیشتر بدانید.
7. عدم توجه به محدودیت های سخت افزاری
سخت افزار ضعیف می تواند پایگاه داده شما را خفه کند ، هر چقدر هم که آن را تنظیم کنید. آهسته CPU را در نمایش داده شدگان ، Disc Disk I/O و دیسک های لاغر (فکر کنید HDDS در مقابل SSDS) تاخیر در نوشتن را خفه کنید. شما نمی توانید از این مسائل پیشی بگیرید.
عمودی در مقابل مقیاس افقی
اگر سخت افزار شما را عقب نگه می دارد ، می توانید به صورت عمودی یا افقی مقیاس کنید. مقیاس بندی عمودی شامل اضافه کردن رم یا یک پردازنده سریعتر به سرور یک سرور شما است. این یک رفع ساده است ، اما در نهایت شما به سقف ضربه می زنید که چقدر می توانید ارتقا دهید. یا می توانید با مقیاس بندی افقی با اضافه کردن سرورهای اضافی که بار را تقسیم می کنند. این رویکرد کار بیشتری برای تنظیم موارد تکثیر برای مدیریت است ، اما بسیار مقیاس پذیر تر است.
8. مشرف به استراتژی های ذخیره سازی
حافظه نهان داخلی MySQL از نظر تئوری زیبا به نظر می رسد. با ذخیره نتایج ، خواندن های مکرر را سرعت می بخشد ، اما این باعث ایجاد سردرد برای نوشتن می شود. هر جدول ، حافظه نهان را تغییر می دهد ، به همین دلیل به طور پیش فرض در MySQL 8.0 خاموش است.
راههای بهتر برای حافظه پنهان
-
مجدداً: یک ابزار ذخیره فوق العاده سریع و حافظه در حافظه که داده هایی مانند نتایج پرس و جو یا جلسات را در RAM ذخیره می کند و بازدیدهای پایگاه داده را کاهش می دهد. این انعطاف پذیر است ، از انواع پیچیده داده ها پشتیبانی می کند و مقیاس ها را به راحتی برای برنامه های بزرگ پشتیبانی می کند.
-
یادگار: یک گزینه ذخیره ساده و سبک وزن که با ذخیره سازی ارزش کلیدی همه چیز را ساده نگه می دارد. تنظیم سریع است ، عالی برای ذخیره سازی اولیه مانند قطعه صفحه ، و در تنظیمات کوچکتر به خوبی کار می کند.
-
پروکسی: یک پروکسی هوشمند که بین برنامه شما و MySQL قرار دارد ، پرس و جو ذخیره شده در آن لایه نتایج را نشان می دهد. ترفند آسان است ، عملکرد خواندن را بدون تغییر برنامه تقویت می کند و تعادل بار را نیز انجام می دهد.
-
جابجایی: عملکرد حافظه پنهان را ردیابی کنید و تنظیم حافظه پنهان هوشمند را پیشنهاد می کند. این معیارهایی مانند نسبت های ضربه ای و الگوهای پرس و جو را تجزیه و تحلیل می کند و به شما کمک می کند تا برای حجم کاری خاص خود حافظه پنهان را بهینه کنید.
9. انتخاب موتور ذخیره سازی
انتخاب موتور ذخیره سازی مناسب به حجم کار شما بستگی دارد. رفیق بهترین شرط بندی شما برای برنامه های کاربردی بالا و همزمان سنگین است زیرا پشتیبانی قوی برای معاملات اسیدی ، قفل سطح ردیف و کشف تصادف قابل اعتماد ارائه می دهد. مایه برای سناریوهای فشرده مانند گزارش بهتر است زیرا عملکرد پرس و جو سریعتر را با یک معماری ساده تر ارائه می دهد.
mysql را به خواست خود خم کنید
اگر در تله های نمایش داده های بهینه نشده ، تنظیمات پیش فرض لاغر یا نگهداری مورد غفلت قرار بگیرید ، عملکرد MySQL شما می تواند سریع مخزن شود. اما می توانید با یک برنامه جامد از این مشکلات جلوتر باشید. روی تنظیم دقیق تنظیمات خود ، حل ناکارآمدی شاخص و تمیز کردن نمایش داده های خود تمرکز کنید. در بیشتر موارد ، این بزرگترین مجرمان هستند. سپس ، می توانید تا زمانی که کل پایگاه داده شما بهینه سازی شود ، به برخی از مشکلات دیگر بپردازید.
به یاد داشته باشید که کنجکاو بمانید و اتوماسیون را در آغوش بگیرید تا کارها را برای خودتان راحت تر کنید. یادگیری و آزمایش را ادامه دهید و به ابزارهای خود اجازه دهید تا در جایی که می تواند وزنه برداری سنگین را انجام دهد.
