مشکل مصرف شدید cpu و بهینه سازی mysql

با سلام

ما یک سرور سی پنل داریم با رم 24 گیگ و سی پیو 8 هسته ای
سرور اختصاصی دست خودمون هست اما با vm مجازی سازی کردیم و سرور فوق بدون لیمیت می باشد
بر روی این سرور کلادلینوکس نصب هست همراه با لایت اسپید

مشکلی که داریم مصرف شدید CPU سرور هست که سی پیو بصورت 100% پر است !

چک کردیم مصرف سی پیو Mysql روی 300 و 400 درصد بود و گاهی حتی بیشتر !

شدیدا سی پیو مصرف میکند

ورژن Mysql هست : 5.5.47-cll

ما میخواهیم mysql بهینه شود تا مصرف سی پیو کاهش و سرعت سایت ها افزایش یابد

نکته مهم : روی این سرور تعداد زیادی سایت وردپرسی جمع آوری مطالب هست که البته پلاگین های آن ها مصرفی ندارند و مشکلی که هست عموما دیتابیس هایشان کم و بیش مشکل دارد و حتی هنگام Rapier ارور میدهند و ...

تنها مصرف این سرور از Mysql و دیتابیس هاست
لود سرور گاهی تا 9 هم میرود و اصولا کمتر از 3.5 نمی شود

مطمئنم این مشکل راهی دارد

امیدوارم شما بزرگواران راهنمایی بفرمایید.

چطور Mysql را بهینه سازی کنم ؟ با توجه به ورژن Mysql - منابع و نوع استفاده ای که خدمتتون عرض کردم
بهترین راه برای بهینه سازی جداول دیتابیس وردپرس چیست ؟

با تشکر از تمامی شما عزیزان

سلام
در ابتدا سعی کنید mysql رو بهینه کنید.
با استفاده از mysqltuner
آگر نیاز به کمک داشتید من شما رو راهنمایی کنم.
در کل سایت هایی که سیستم جمع اوری دارند. به دلیل درخواست زیاد معمولا مصرف mysql زیادی دارند.
آگر سایت ها بازدید مناسبی دارند حتما افزونه کش برای ورد پرس نصب کنید

نقل قول:

---

نوشته اصلی توسط j4b3r

سلام
در ابتدا سعی کنید mysql رو بهینه کنید.
با استفاده از mysqltuner
آگر نیاز به کمک داشتید من شما رو راهنمایی کنم.
در کل سایت هایی که سیستم جمع اوری دارند. به دلیل درخواست زیاد معمولا مصرف mysql زیادی دارند.
آگر سایت ها بازدید مناسبی دارند حتما افزونه کش برای ورد پرس نصب کنید

---

ممنون از شما جناب

شما همیشه به ما لطف داشتید

استفاده از mysqltuner به چه صورت هست ؟

بازدیدشون هم بله بالاست

نقل قول:

---

نوشته اصلی توسط mandana32

ممنون از شما جناب

شما همیشه به ما لطف داشتید

استفاده از mysqltuner به چه صورت هست ؟

بازدیدشون هم بله بالاست

---

س
wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.pl

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

نقل قول:

---

نوشته اصلی توسط 007

س
wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.pl

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

---

سلام مهندس

فقط همین 2 دستور را وارد کنم ؟!

- - - Updated - - -

ممنون از شما بزرگوار

دستورات را دادم به سرور

نتیجه :

کد PHP:

---

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 5G (Tables: 1100)
[--] Data in InnoDB tables: 3G (Tables: 1709)
[--] Data in MEMORY tables: 0B (Tables: 10)
[!!] Total fragmented tables: 256

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations  -----------------------------------------                                                                             --
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 55m 26s (33M q [385.947 qps], 448K conn, TX: 537B, RX: 5B)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Total buffers: 912.0M global + 2.8M per thread (1000 max threads)
[OK] Maximum reached memory usage: 1.2G (5.06% of installed RAM)
[OK] Maximum possible memory usage: 3.6G (15.27% of installed RAM)
[OK] Slow queries: 0% (2K/33M)
[OK] Highest usage of available connections: 11% (110/1000)
[OK] Aborted connections: 0.00%  (18/448503)
[OK] Query cache efficiency: 76.5% (23M cached / 30M selects)
[!!] Query cache prunes per day: 1522129
[OK] Sorts requiring temporary tables: 0% (18K temp sorts / 2M sorts)
[!!] Joins performed without indexes: 1976
[!!] Temporary tables created on disk: 59% (620K on disk / 1M total)
[OK] Thread cache hit rate: 99% (110 created / 448K connections)
[!!] Table cache hit rate: 0% (100 open / 386K opened)
[OK] Open file limit used: 0% (99/10K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 28.1% (75M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/743.3M
[OK] Read Key buffer hit rate: 99.9% (20B cached / 13M reads)
[OK] Write Key buffer hit rate: 99.6% (387M cached / 1M writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/3.2G
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB Used buffer: 100.00% (8191 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 96.95% (1057351888 hits/ 1090619505 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 220039 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (10000) variable
    should be greater than table_open_cache ( 100)
Variables to adjust:
    query_cache_size (> 256M) [see warning above]
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_open_cache (> 100)
    innodb_buffer_pool_size (>= 3G) if possible. 


---

س
خوب الان باید مقدار رو برحسب چیزی که پیشنهاد کرده در فایل my.confتغییرداده و شرایط بهتری رو تجربه کنید
برای مثال query_cache_size رو باید بیشتراز 256Mاختصاص بدین و ...
ارادت

نقل قول:

---

نوشته اصلی توسط 007

س
خوب الان باید مقدار رو برحسب چیزی که پیشنهاد کرده در فایل my.confتغییرداده و شرایط بهتری رو تجربه کنید
برای مثال query_cache_size رو باید بیشتراز 256Mاختصاص بدین و ...
ارادت

---

ممنونم مهندس

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

join_buffer_size (> 128.0K, or always use indexes with joins)
innodb_buffer_pool_size (>= 3G) if possible.

مقادیر اون 2 گزینه رو هم 512 و 300 گذاشتم کافیه یا بیشتر بذارم؟
مشخصات سرور رو خدمتتون عرض کردم
رم 24 گیگ ، سی پیو 4 هسته فیزیکی و 4 هسته مجازی ، هارد 1 ترابایت - کلادلینوکس - لایت اسپید

یکی از موارد که گاهی اثر برعکس میزاره، افزونها و ماژول های کش هستند
واسه من هم پیش اومد
کش رو چک کنید

نقل قول:

---

نوشته اصلی توسط bia2dj

یکی از موارد که گاهی اثر برعکس میزاره، افزونها و ماژول های کش هستند واسه من هم پیش اومد کش رو چک کنید

---

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

لاگ slow query فعال هست؟!
پیشنهادم اینه از برنامه mytop برای مشاهده کوئری های دزحال انجام استفاده کنید (مدت زمان هر کوئری رو میده ) سپس گلوگاه رو پیدا کنید و مشکل رو رفع کنید

هر چیزی که mysql tuner پیشنهاد کرد مناسب نیست!

query cache size 256MB?!!?! مگر چقدر رم دارید ؟!

نقل قول:

---

نوشته اصلی توسط Yas-Host

هر چیزی که mysql tuner پیشنهاد کرد مناسب نیست!

query cache size 256MB?!!?! مگر چقدر رم دارید ؟!

---

طبق همان نتیجه mysqltuner ایشون 24 گیگ رم دارند. اما باز هم هیچ جا بیشتر از 200 توصیه نشده.

ببخشید الان لود سرورم رفته روی 37 !!!

خیلی بدتر شد

چکار کنم الان ؟

- - - Updated - - -

نقل قول:

---

نوشته اصلی توسط Yas-Host

هر چیزی که mysql tuner پیشنهاد کرد مناسب نیست!

query cache size 256MB?!!?! مگر چقدر رم دارید ؟!

---

خسته نباشید جناب سنجری

اتفاقا این سرور رو شما کانفینگ کردید و زحمتش ر کشیدید چی پیشنهاد میکنید ؟

چکارش کنم ؟

- - - Updated - - -

نقل قول:

---

نوشته اصلی توسط shivahost

طبق همان نتیجه mysqltuner ایشون 24 گیگ رم دارند. اما باز هم هیچ جا بیشتر از 200 توصیه نشده.

---

بله 24 گیگ رم دارم

سرورم رم کم مصرف میکنه اما سی پیو میخوره
حالا با این تغییرات شدیدا بدتر شده

- - - Updated - - -

نقل قول:

---

نوشته اصلی توسط rezaonline.net

لاگ slow query فعال هست؟!
پیشنهادم اینه از برنامه mytop برای مشاهده کوئری های دزحال انجام استفاده کنید (مدت زمان هر کوئری رو میده ) سپس گلوگاه رو پیدا کنید و مشکل رو رفع کنید

---

امکانش هست بیشتر توضیح بدید مهندس ؟

نقل قول:

---

امکانش هست بیشتر توضیح بدید مهندس ؟

---

mytop رو روی سرور نصب میکنن روی اوبونتو apt-get install mytop
بگید جناب سنجری نصب کنن
سپس دستور زیر رو توی سرور میزنید

mytop --prompt

کوئری های درحال اجرا و تایمشون رو نشون میده ، خب کوئری هایی که لود بیشتر از یک ثانیه دارند کند هستن باید بررسی کنید سمت سیستم
اسکریپت سایتتون چیه ؟

- - - Updated - - -

نقل قول:

---

امکانش هست بیشتر توضیح بدید مهندس ؟

---

mytop رو روی سرور نصب میکنن روی اوبونتو apt-get install mytop
بگید جناب سنجری نصب کنن
سپس دستور زیر رو توی سرور میزنید

mytop --prompt

کوئری های درحال اجرا و تایمشون رو نشون میده ، خب کوئری هایی که لود بیشتر از یک ثانیه دارند کند هستن باید بررسی کنید سمت سیستم
اسکریپت سایتتون چیه ؟

دوستان کسی نبود دقیقا بگه چکار کنم ؟

لطفا راهنمایی کنید متاسفانه بدتر شد که بهتر نشد

- - - Updated - - -

نقل قول:

---

نوشته اصلی توسط rezaonline.net

mytop رو روی سرور نصب میکنن روی اوبونتو apt-get install mytop
بگید جناب سنجری نصب کنن
سپس دستور زیر رو توی سرور میزنید

mytop --prompt

کوئری های درحال اجرا و تایمشون رو نشون میده ، خب کوئری هایی که لود بیشتر از یک ثانیه دارند کند هستن باید بررسی کنید سمت سیستم
اسکریپت سایتتون چیه ؟

- - - Updated - - -


mytop رو روی سرور نصب میکنن روی اوبونتو apt-get install mytop
بگید جناب سنجری نصب کنن
سپس دستور زیر رو توی سرور میزنید

mytop --prompt

کوئری های درحال اجرا و تایمشون رو نشون میده ، خب کوئری هایی که لود بیشتر از یک ثانیه دارند کند هستن باید بررسی کنید سمت سیستم
اسکریپت سایتتون چیه ؟

---

آقای سنجری سرشون شلوغه لطفا دستوراتش رو بفرمایید تا خودم بزنم
CentsOS 6.6 هست البته الان شده کلاد لینوکس

https://www.digitalocean.com/communi...ql-performance

یا از این هم میتونید استفاده کنید .
فرض کنیم یوزر دیتابیستون اینه dbuser

این دستور رو توی ssh بزنید و سپس پسورد دیتابیس رو وارد کنید
(توی این دستور عدد یک نشان دهنده رفرش کردن اطلاعت در یک ثانیه است میتونید تغییرش بدید)

mysqladmin -u dbuser -p -i 1 processlist

چک کنید کدوم کوئری هاتون کند هستن و چند ثانیه تایم میبره تا اجرا بشه