نحوه تنظیم و بهینه سازی عملکرد MySQL 5.7 در Ubuntu 18.04 VPS یا سرور اختصاصی

مقدمه

قبل از شروع کار ، شما باید عوامل مؤثر بر عملکرد MySQL را بفهمید ، تا بتوانید یاد بگیرید که تنظیم و بهینه سازی سرور MySQL را به روش صحیح انجام دهید و نتایج صحیحی کسب کنید. عوامل اصلی شامل موارد زیر است:


  • حجم داده ها در حال بازیابی است
  • منابع موجود ، یعنی CPU ، Memory (RAM)
  • بار توسط سرور MySQL اجرا می شود>

دلایل عملکرد ضعیف سرور MySQL شامل موارد زیر است:

  • طراحی پایگاه داده ضعیف
  • تنگناهای سخت افزاری
  • کد نویسی ضعیف
  • نمایه سازی ضعیف
  • پیکربندی ناکارآمد

توجه ویژه: همانطور که مشاهده می کنید ، با به روزرسانی به یک سرویس میزبانی وب بهتر ، می توان چندین مورد از این عوامل را بررسی کرد. HostAdvice بهترین ارائه دهندگان میزبانی MySQL را بر اساس بررسی های تخصصی و کاربران به ارمغان می آورد.

نصب mysqltuner و تنظیم تنظیم کننده

mysqltuner را نصب کنید

Mysqltuner یک اسکریپت تنظیم MySQL با کارایی بالا است که به صورت تصویری از وضعیت سلامتی سرور MySQL ارائه می دهد و توصیه هایی را برای بهبود ، افزایش عملکرد ، ثبات و کارآیی ارائه می دهد..

برای نصب mysqltuner دستورات زیر را اجرا کنید

$ cd / tmp
$ sudo wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl –quiet –no-check-گواهینامه –output-document = / usr / local / bin / mysqltuner
$ sudo chmod u + x / usr / local / bin / mysqltuner

تنظیم تنظیم کننده را نصب کنید

Tuning Primer یک اسکریپت پوسته است که اطلاعاتی را از داخلی سرور MySQL بدست می آورد و در مورد تنظیم متغیرهای سرور توصیه هایی را ارائه می دهد..

دستورات زیر را برای نصب تنظیم اولیه تنظیم کنید:

$ sudo apt نصب bc -y
$ sudo wget http://www.day32.com/MySQL/tuning-primer.sh –output-document = / usr / local / bin / تنظیم-آغازگر
$ sudo chmod u + x / usr / local / bin / tuning-Primer

تنظیم و بهینه سازی سرور mysql

تنظیم سرور MySQL بر روی پرونده پیکربندی MySQL انجام می شود.

/etc/mysql/mysql.conf.d/mysqld.cnf

mysqltuner را اجرا کنید.

$ sudo mysqltuner
$ sudo mysqltuner
>> MySQLTuner 1.7.9 – سرگرد هایدن
>> گزارش اشکال ، درخواست ویژگی و بارگیری در http://mysqltuner.com/
>> برای گزینه های اضافی و فیلتر خروجی با “کمک” را اجرا کنید

[-] بررسی نسخه پرش به اسکریپت MySQLTuner
[OK] با استفاده از اعتبارنامه از حساب نگهداری دبیان وارد سیستم شده اید.
[OK] در حال اجرا نسخه MySQL پشتیبانی 5.7.22-0ubuntu18.04.1
[خوب] کار با معماری 64 بیتی

——– توصیه های پرونده ورود به سیستم ——————————————————————
[-] پرونده ورود به سیستم: /var/log/mysql/error.log(18K)
[OK] پرونده ورود /var/log/mysql/error.log موجود است
[OK] پرونده ورود /var/log/mysql/error.log قابل خواندن است.
[OK] پرونده ورود /var/log/mysql/error.log خالی نیست
[OK] پرونده ورود /var/log/mysql/error.log کوچکتر از 32 مگابایت است
[!!] /var/log/mysql/error.log حاوی 14 اخطار.
[!!] /var/log/mysql/error.log شامل 3 خطا است.
[-] 3 شروع (بازدید) در /var/log/mysql/error.log شناسایی شد
[-] 1) 2018-06-06T17: 55: 47.928847Z 0 [توجه] / usr / sbin / mysqld: آماده اتصالات.
[-] 2) 2018-06-06T17: 55: 44.798684Z 0 [توجه] mysqld: آماده اتصالات.
[-] 3) 2018-06-06T17: 55: 41.931105Z 0 [توجه] mysqld: آماده اتصال.
[-] 2 خاموش (بازدید) که در /var/log/mysql/error.log مشاهده شدند
[-] 1) 2018-06-06T17: 55: 46.410548Z 0 [توجه] mysqld: خاموش کردن کامل
[-] 2) 2018-06-06T17: 55: 43.758366Z 0 [توجه] mysqld: خاموش کردن کامل

——– آمار موتور ذخیره سازی —————————————————————–
[-] وضعیت: + ARCHIVE + BLACKHOLE + CSV -FEDERATED + InnoDB + MEMORY + MRG_MYISAM + MyISAM + PERFORMANCE_SCHEMA
[-] داده ها در جداول InnoDB: 16K (جداول: 1)
[OK] تعداد جداول تکه تکه: 0

——– توصیه های امنیتی ——————————————————————
[خوب] هیچ حساب ناشناس برای هیچ یک از کاربران پایگاه داده وجود ندارد
[OK] به کلیه کاربران پایگاه داده دارای کلمه عبور اختصاص داده شده است
[-] اشکال # 80860 MySQL 5.7: از فعال کردن گذرواژه هنگام فعال شدن اعتبار خودداری کنید

——– توصیه های امنیتی CVE ————————————————————–
[-] به دلیل گزینه –cvefile تعریف نشده رد شد

——– معیارهای عملکرد ———————————————————————–
[-] برای: 9m 8s (110 q [0.201 qps] ، 38 conn، TX: 187K، RX: 10K)
[-] می خواند / می نویسد: 98٪ / 2٪
[-] ورود به سیستم دودویی غیرفعال است
[-] حافظه بدنی: 3.6G
[-] حافظه حداکثر MySQL: 352.4M
[-] سایر حافظه پردازش: 175.4M
[-] کل بافرها: 192.0M جهانی + 1.1M در هر موضوع (حداکثر 151 موضوع)
[-] P_S حداکثر استفاده از حافظه: 72B
[-] Galera GCache Max استفاده از حافظه: 0B
[OK] حداکثر استفاده از حافظه رسیده: 194.1M (5.26٪ RAM نصب شده)
[OK] حداکثر استفاده از حافظه ممکن: 352.4M (9.54٪ از حافظه رم نصب شده)
[خوب] استفاده كامل ممكن از حافظه با فرآيند ديگر با حافظه موجود سازگار است
[OK] نمایش داده شدگان آهسته: 0٪ (0/110)
[OK] بیشترین استفاده از اتصالات موجود: 1٪ (2/151)
[OK] اتصالات قطع شده: 0.00٪ (0/38)
وضوح نام [!!] فعال است: وضوح نام معکوس برای هر اتصال جدید ساخته شده است و می تواند عملکرد را کاهش دهد
[!!] حافظه پنهان پرس و جو ممکن است به دلیل پیش بینی mutex به طور پیش فرض غیرفعال شود.
[!!] راندمان حافظه نهان ذخیره سازی: 0.0٪ (0 ذخیره شده / 49 انتخاب)
[خوب] هرس کش پرس و جو در هر روز: 0
[OK] انواع نیاز به جداول موقت: 0٪ (0 نوع دما / 2 نوع)
[خوب] بدون فهرست نمی پیوندد
[OK] جداول موقت ایجاد شده بر روی دیسک: 4٪ (36 در دیسک / 846 کل)
[خوب] میزان ضربه به حافظه نهان نخ: 94٪ (2 ایجاد شده / 38 اتصال)
[خوب] میزان ضربه به حافظه نهان جدول: 95٪ (591 باز / 620 باز)
[OK] محدودیت پرونده باز استفاده شده: 1٪ (50 / 5K)
[OK] قفل های جدول بلافاصله به دست آمده: 100٪ (158 قفل فوری / 158)

——– طرحواره عملکرد ————————————————————————
[-] حافظه مورد استفاده P_S: 72B
[-] طرحواره Sys نصب شده است.

——– معیارهای ThreadPool ————————————————————————
[-] آمار ThreadPool غیرفعال است.

——– معیارهای MyISAM —————————————————————————-
[!!] بافر کلید استفاده شده: 18.3٪ (3M مورد استفاده در حافظه پنهان 16M)
[OK] اندازه بافر / کل شاخص های MyISAM کلیدی: 16.0M / 43.0K
[!!] نرخ ضربه بافر کلید را بخوانید: 94.1٪ (118 ذخیره شده / 7 بار خوانده شده)

——– معیارهای InnoDB —————————————————————————-
[-] InnoDB فعال است.
[-] همزمانی موضوع InnoDB: 0
[OK] پرونده InnoDB در هر جدول فعال می شود
[خوب] استخر بافر InnoDB / اندازه: 128.0M / 16.0K
[!!] نسبت اندازه پرونده ورود InnoDB / اندازه استخر InnoDB بافر (75٪): 48.0M * 2 / 128.0M باید برابر با 25٪ باشد
[خوب] نمونه استخر بافر InnoDB: 1
[-] تعداد پارچه های استخر بافر InnoDB: 1 برای 1 نمونه استخر بافر
[خوب] Innodb_buffer_pool_size با Innodb_buffer_pool_chunk_size مطابقت دارد & Innodb_buffer_pool_inunities
[!!] InnoDB بازده بافر را بخوانید: 88.39٪ (تعداد 1933 بازدید / 2187 کل)
[!!] InnoDB نوشتن راندمان ورود به سیستم: 0٪ (5 بازدید / 0 مجموع)
[OK] گزارش InnoDB منتظر است: 0.00٪ (0 انتظار / 5 می نویسد)

——– معیارهای AriaDB —————————————————————————-
[-] AriaDB غیرفعال است.

——– معیارهای TokuDB —————————————————————————-
[-] TokuDB غیرفعال است.

——– XtraDB Metrics —————————————————————————-
[-] XtraDB غیرفعال است.

——– اندازه گیری های RocksDB —————————————————————————
[-] RocksDB غیرفعال است.

——– اندازه گیری عنکبوت —————————————————————————-
[-] عنکبوت غیرفعال است.

——– متریک ها را وصل کنید —————————————————————————
[-] اتصال غیرفعال است.

——– معیارهای Galera —————————————————————————-
[-] Galera غیرفعال است.

——– معیارهای تکرار ———————————————————————–
[-] همانندسازی همزمان Galera: NO
[-] هیچ بنده (های) تکراری برای این سرور وجود ندارد.
[-] فرمت Binlog: ROW
[-] پشتیبانی XA را فعال کنید: روشن
[-] استاد تکرار نیمه همزمان: فعال نشده است
[-] همانند سازی همزمان همزمان برده: فعال نشده است
[-] این یک سرور مستقل است

——– توصیه ها —————————————————————————
توصیه های کلی:
خط (های) هشدار را در پرونده /var/log/mysql/error.log کنترل کنید
خط (های) خطا را در پرونده /var/log/mysql/error.log کنترل کنید
MySQL در 24 ساعت گذشته شروع شده است – توصیه ها ممکن است نادرست باشد
حساب های خود را فقط با ip یا زیر شبکه ها پیکربندی کنید ، سپس پیکربندی خود را با جستجوی نام-حل = 1 به روز کنید
قبل از تغییر innodb_log_file_size و / یا innodb_log_files_in_group این را بخوانید: http://bit.ly/2wgkDvS
متغیرها برای تنظیم:
query_cache_size (= 0)
query_cache_type (= 0)
query_cache_limit (> 1M یا از مجموعه های نتیجه کوچکتر استفاده کنید)
innodb_log_file_size در صورت امکان باید (= 16M) باشد ، بنابراین اندازه کل پرونده های InnoDB برابر است با 25٪ از اندازه استخر بافر.

در قسمت “توصیه‌ها” روی قسمت آخر خروجی متمرکز شوید و متغیرهای مورد نیاز را افزایش دهید.

از فایل پیکربندی mysql نسخه پشتیبان تهیه کنید و پرونده پیکربندی واقعی را ویرایش کنید.

$ sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

متغیر پرونده را طبق توصیه ها تنظیم کنید. می توانید مقادیر متغیر پیش فرض را اظهار نظر کرده و مقادیر متغیر را به دو برابر مقدار پیش فرض افزایش دهید. در واقع ، ممکن است چند روز طول بکشد تا بهترین ارزش ها برای سرور شما ارائه شود.

پس از ایجاد تغییرات ، سرور mysql را مجدداً راه اندازی کنید.

$ sudo systemctl mysql را مجدداً راه اندازی کنید

توجه ویژه: اگر این برنامه مجدداً راه اندازی مجدد شد ، به عقب برگردید و مقادیر جدید را اظهارنظر کنید و تنظیم را یکی یکی شروع کنید و ببینید کدام تنظیم متغیر باعث عدم موفقیت سرور

اگر سرور با موفقیت بارگذاری مجدد شد ، mysqltuner را دوباره بسازید و ببینید آیا توصیه های بیشتری در مورد تنظیم مقادیر متغیر وجود دارد یا خیر..

روند مشابهی را برای تنظیم پرایمر انجام دهید.

تنظیم اولیه را تنظیم کنید

$ sudo تنظیم-آغازگر
$ sudo تنظیم-آغازگر

— PRIMER تنظیم عملکرد MYSQL —
– توسط: متیو مونتگومری –

MySQL نسخه 5.7.22-0ubuntu18.04.1 x86_64

Uptime = 0 روز 0 ساعت 1 دقیقه 49 ثانیه
میانگین qps = 0
کل سؤالات = 15
موضوعات مرتبط شد = 1

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

برای کسب اطلاعات بیشتر در مورد چگونگی هر یک از این موارد
متغیرهای زمان اجرا اثر بازدیدکننده داشته است:
http://dev.mysql.com/doc/refman/5.7/fa/server-system-variables.html
از http://www.mysql.com/products/enterprise/advisors.html بازدید کنید
برای اطلاعات در مورد سرویس مانیتورینگ و مشاوره سازمانی MySQL

سؤالات آهسته
گزارش جستجوی آهسته فعال نیست.
Long_query_time فعلی = 10.000000 ثانیه.
0 از 36 دارید که بیشتر از 10.000000 ثانیه طول می کشد. برای تکمیل
به نظر می رسد long_query_time شما خوب است

LOG به روز رسانی باینری
ورود به روز رسانی باینری فعال نیست.
شما قادر نخواهید بود به بازیابی زمان اشاره کنید
به http://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html مراجعه کنید

کار می کنند
موضوع فعلی_کست_سایز = 8
موضوعات کنونی = 0 ذخیره شده است
موضوعات فعلی_پر_سک = 0
موضوعات تاریخی_پر_سک = 0
thread_cache_size شما خوب است

پیوندهای اصلی
max_connections فعلی = 151
موضوعات فعلی_کامل = 1
حداکثر تاریخی max_used_connections = 1
تعداد اتصالات استفاده شده 0٪ حداکثر پیکربندی شده است.
شما کمتر از 10٪ از اتصال حداکثر پیکربندی شده خود را استفاده می کنید.
کاهش max_connection می تواند به جلوگیری از اختصاص بیش از حد حافظه کمک کند
دیدن "استفاده از حافظه" برای اطمینان از اینکه بیش از حد تخصیص ندارید ، بخش را انتخاب کنید

هیچ پشتیبانی InnoDB فعال نیست!

استفاده از حافظه
حداکثر حافظه تا کنون اختصاص یافته: 177 م
تنظیمات تنظیمات حداکثر در هر موضوع: 160 M
پیکربندی حداکثر بافر جهانی: 176 M
حداکثر حافظه حداکثر پیکربندی شده: 336 م
حافظه بدنی: 3.60 گرم
حد اکثر حافظه به نظر می رسد در حد هنجارهای قابل قبول باشد

بوفه کلیدی
فضای فهرست فعلی MyISAM = 43 K
key_buffer_size = 16 متر
میزان از دست رفتن کلید کش 1: 2 است
نسبت بدون بافر کلیدی = 81٪
key_buffer_size شما به نظر خوب است

CACY CACHE
حافظه نهان فعال است
query_cache_size = 16 M
query_cache_used = 16 K
query_cache_limit = 1 M
نسبت حافظه پنهان فعلی Query نسبت پر کردن حافظه = 0.10٪
query_cache_min_res_unit = 4 K
به نظر می رسد query_cache_size شما خیلی زیاد است.
شاید بتوانید از این منابع در جای دیگر استفاده کنید
MySQL نتایج پرس و جو را ذخیره نمی کند که از اندازه query_cache_limit بزرگتر باشد

عملکردهای مرتب سازی بر اساس
مرتب سازی بر اساس جریان_buffer_size = 256 K
read_rnd_buffer_size = 256 K
هیچ عملیاتی مرتب سازی انجام نشده است
به نظر می رسد بافر مرتب باشد

بپیوندید
فعلی join_buffer_size = 260.00 K
شما 0 سؤال دارید که در آن پیوستن نمی تواند از یک فهرست به درستی استفاده کند
به نظر می رسد که پیوستنهای شما به درستی از ایندکس استفاده می کند

LIIT FILES FILES
open_files_limit = 5000 پرونده
open_files_limit معمولاً باید حداقل 2x-3x تنظیم شود
اگر از MyISAM سنگین استفاده می کنید ، از Table_cache استفاده کنید.
به نظر می رسد مقدار open_files_limit شما خوب است

جدول CACHE
Table_open_cache = 2000 جدول
جدول فعلی_definition_cache = 1400 جدول
شما در کل 119 جدول دارید
شما 249 جدول باز دارید.
به نظر می رسد مقدار Table_cache خوب است

جداول TEMP
max_heap_table_size = 16 متر
tmp_table_size = 16 متر
از 361 جدول دما ، 6٪ در دیسک ایجاد شده اند
نسبت جداول tmp دیسک ایجاد شده خوب به نظر می رسد

اسکن های جدول
فعلی read_buffer_size = 128 K
نسبت اسکن جدول فعلی = 673: 1
به نظر می رسد read_buffer_size خوب است

قفل جدول
نسبت انتظار انتظار فعلی = 0: 219
قفل جدول شما خوب به نظر می رسد

توجه ویژه: بیشتر روی متن هایلایت شده با رنگ قرمز و زرد تمرکز کنید زیرا در مورد بهینه سازی و بهبود عملکرد mysql توصیه هایی را ارائه می دهد. avkanî

متغیر پرونده را مطابق توصیه ها تنظیم کنید. می توانید مقادیر متغیر پیش فرض را اظهار نظر کرده و مقادیر متغیر را مطابق پیشنهادات تنظیم کنید. avkanî

پس از ایجاد تغییرات ، سرور MySQL را مجدداً راه اندازی کنید.

$ sudo systemctl mysql را مجدداً راه اندازی کنید

توجه ویژه: اگر این برنامه مجدداً راه اندازی مجدد شد ، به عقب برگردید و مقادیر جدید را اظهارنظر کنید و تنظیم را یک به یک شروع کنید و ببینید کدام تنظیم متغیر باعث خرابی سرور می شود..

اگر سرور با موفقیت مجدداً راه اندازی مجدد شد ، تنظیم مجدد را انجام دهید و توصیه های بیشتری در مورد تنظیم مقادیر متغیر مشاهده کنید.

نتیجه

توجه به این نکته بسیار مهم است که ابزارهای تنظیم mysqltuner یا تنظیم کننده تنظیم کننده راه حل هایی برای عملکرد ضعیف سرور MySQL نیستند بلکه اسکریپت هایی هستند که شما را برای تنظیم دقیق سرور MySQL راهنمایی می کنند..

برای بهترین عملکرد,

  • بررسی کاملی از نمایش داده شدگان ارسال شده به سرور و بهینه سازی ساختار / طراحی پایگاه داده و پرس و جو SQL
  • عوامل مؤثر بر عملکرد MySQL را همانطور که در مقاله توضیح داده شده ارزیابی کنید.
  • برای عملکرد بهتر به روزرسانی سخت افزار سرور فیزیکی و بهینه سازی تنظیمات MySQL بر اساس مشخصات سخت افزاری بپردازید.
  • در مورد نحوه تنظیم تنظیمات MySQL بر اساس برنامه هایی که از سرور MySQL استفاده می کنند ، تحقیقات بیشتری انجام دهید.

توصیه می شود پس از راه اندازی مجدد سرور MySQL ، مرتباً ابزار mysqltuner یا tuningprimer را بیش از 48 ساعت انجام دهید ، به منظور دستیابی به آمار استفاده معنادار..

این 3 سرویس برتر میزبانی MySQL را بررسی کنید:

میزبانی A2

قیمت شروع:
$ 3.92


قابلیت اطمینان
9.3


قیمت گذاری
9.0


کاربر پسند
9.3


پشتیبانی
9.3


امکانات
9.3

بررسی ها را بخوانید

از میزبانی A2 دیدن کنید

FastComet

قیمت شروع:
2.95 دلار


قابلیت اطمینان
9.7


قیمت گذاری
9.5


کاربر پسند
9.7


پشتیبانی
9.7


امکانات
9.6

بررسی ها را بخوانید

از FastComet بازدید کنید

میزبان

قیمت شروع:
$ 0.99


قابلیت اطمینان
9.3


قیمت گذاری
9.3


کاربر پسند
9.4


پشتیبانی
9.4


امکانات
9.2

بررسی ها را بخوانید

از هاستینجر بازدید کنید

مقالات مرتبط با نحوه

  • نحوه تنظیم و بهینه سازی عملکرد MySQL 8.0 در سرور CentOS 7
    کارشناس
  • نحوه تهیه نسخه پشتیبان از پایگاه داده MySQL در سرور اختصاصی Ubuntu 18.04 یا اختصاصی
    حد واسط
  • نحوه نصب MYSQL 8.0 و ایجاد بانک اطلاعاتی در Ubuntu 18.04 Linux VPS
    حد واسط
  • نحوه غیر فعال کردن MySQL 5 "حالت سخت" در Ubuntu 18.04 VPS یا سرور اختصاصی
    حد واسط
  • نحوه نصب PhpMyAdmin در Ubuntu 18.04 VPS یا سرور اختصاصی
    حد واسط
Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me