אופטימיזציה של MySQL ו-MariaDB עבור Bitrix: ללא צורך בשדרוג

אופטימיזציה של MySQL ו-MariaDB עבור Bitrix: אין צורך בשדרוג

לעתים קרובות אנו נתקלים במצב: שרת עם 32 GB RAM, אבל MySQL משתמש רק ב-2 GB. ברירת המחדל של innodb_buffer_pool_size היא 128 MB או אפילו 8 MB. בקטלוג עם 500,000 פריטים, מערך העבודה הוא 4-8 GB. ללא מאגר מתאים, כל בקשה לדפים שאינם במטמון פוגעת בדיסק: 5-10 אלפיות השנייה במקום 0.1 אלפיות השנייה מהזיכרון. התוצאה: דפים נטענים תוך 10-15 שניות, מנהלים מתלוננים, לקוחות עוזבים. כוונון MySQL ו-MariaDB עבור Bitrix הוא משימה שאנו פותרים באופן קבוע. חיסכון בחומרה באמצעות תצורה נכונה מסתכם בעד 30% מתקציב האירוח.

מדוע כוונון MySQL הוא קריטי עבור Bitrix

Bitrix עושה שימוש נרחב ב-InnoDB לאחסון בלוקי מידע, קטלוגים מסחריים, מאפיינים ואירועים. עם תצורת ברירת מחדל, מסד הנתונים הופך לצוואר בקבוק, גם אם משאבי שרת אחרים בשפע. אנו ביצענו אופטימיזציה של MySQL עבור עשרות פרויקטים של Bitrix ויודעים אילו פרמטרים מניבים רווחים מקסימליים. הפחתת עומס הדיסק גם מקצצת בעלויות תמיכה וחידוש.

תצורה נכונה של InnoDB Buffer Pool

גודל המאגר הוא הפרמטר החשוב ביותר. הגדר אותו ל-60-70% מה-RAM עבור שרת DB ייעודי. עבור 32 GB RAM, זה 20 GB. הוסף מספר מופעי buffer pool כדי להפחית תחרות mutex: innodb_buffer_pool_instances = 8. אם יש לך 64 GB RAM, ניתן להגדיר 40-45 GB עם 8-16 מופעים. בשרתים עם עומס גבוה, מספר המאגרים צריך להיות שווה בערך למספר ליבות המעבד. זה נותן שיפור של עד 20% בעומסי עבודה מרובי-תהליכים.

פרמטרים של InnoDB: קובץ /etc/mysql/conf.d/bitrix.cnf:

[mysqld]
# ===== InnoDB Buffer Pool =====
# 60-70% от RAM для выделенного DB-сервера
innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 8
# ~1 instance per 1-2GB
# ===== InnoDB I/O =====
innodb_io_capacity = 2000
# для SSD: 2000-4000
innodb_io_capacity_max = 4000
innodb_flush_method = O_DIRECT
# обход OS page cache
innodb_flush_log_at_trx_commit = 2
# не fsync на каждую транзакцию
# ===== Redo Log =====
# MySQL 8.0+: управляется автоматически
# MariaDB / MySQL 5.7:
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
# ===== Connections =====
max_connections = 500
thread_cache_size = 50
wait_timeout = 300
interactive_timeout = 300
# ===== Query Cache =====
# MySQL 8.0: Query Cache удалён
# MariaDB / MySQL 5.7: отключаем (лучше использовать Memcached/Redis)
query_cache_type = 0
query_cache_size = 0
# ===== Temp Tables =====
tmp_table_size = 256M
max_heap_table_size = 256M
# ===== Slow Log =====
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

[mysqld] # ===== InnoDB Buffer Pool ===== # 60-70% от RAM для выделенного DB-сервера innodb_buffer_pool_size = 20G innodb_buffer_pool_instances = 8 # ~1 instance per 1-2GB # ===== InnoDB I/O ===== innodb_io_capacity = 2000 # для SSD: 2000-4000 innodb_io_capacity_max = 4000 innodb_flush_method = O_DIRECT # обход OS page cache innodb_flush_log_at_trx_commit = 2 # не fsync на каждую транзакцию # ===== Redo Log ===== # MySQL 8.0+: управляется автоматически # MariaDB / MySQL 5.7: innodb_log_file_size = 1G innodb_log_buffer_size = 64M # ===== Connections ===== max_connections = 500 thread_cache_size = 50 wait_timeout = 300 interactive_timeout = 300 # ===== Query Cache ===== # MySQL 8.0: Query Cache удалён # MariaDB / MySQL 5.7: отключаем (лучше использовать Memcached/Redis) query_cache_type = 0 query_cache_size = 0 # ===== Temp Tables ===== tmp_table_size = 256M max_heap_table_size = 256M # ===== Slow Log ===== slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = 1 — יומן העסקאות נכתב לדיסק פעם בשנייה, לא בכל COMMIT. הסיכון לאובדן שנייה אחת של עסקאות בקריסה מקובל עבור רוב חנויות המקוונות. נותן האצת כתיבה פי 3-5. innodb_flush_log_at_trx_commit = 2 — MySQL כותב ישירות להתקן הבלוקים, תוך עקיפת מטמון הדפים של מערכת ההפעלה. מבטל שמירה כפולה במטמון.

מה נותן כוונון עבור NVMe SSD

בכונני NVMe מודרניים ניתן להיות אגרסיביים יותר:

innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 innodb_read_io_threads = 8 innodb_write_io_threads = 8 

זה מגדיל את התפוקה בעד 30% בהשוואה ל-SSD רגיל. לדוגמה, בפרויקט אחרון עם קטלוג של 200,000 מוצרים, זמן יצירת הדף ירד מ-12 ל-2 שניות לאחר הכוונון.

השוואת ביצועים בין תצורות:

מאפייני טבלאות Bitrix

innodb_flush_method = O_DIRECT — אינדקס טקסט מלא. הטבלה גדלה ל-2-5 GB באתרים גדולים. אם נעשה שימוש ב-Elasticsearch, ניתן לקצץ טבלה זו ולבטל את האינדוקס המובנה.

innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 innodb_read_io_threads = 8 innodb_write_io_threads = 8 — מאפיינים מרובים. עם יותר ממיליון שורות וללא אינדקסים, הפילטר החכם מאט. אנו מוסיפים אינדקסים על b_search_content, b_iblock_element_prop_m*.

IBLOCK_ELEMENT_ID — יומן אירועי מערכת. באתרים פעילים הוא גדל ב-10-50 MB ליום. נקה באמצעות agent או cron:

DELETE FROM b_event WHERE DATE_COLUMN < DATE_SUB(NOW(), INTERVAL 90 DAY); 

כמו כן, שים לב ל-IBLOCK_PROPERTY_ID ו-b_event — ללא אינדקסים הם מאטים מאוד את העגלה ורשימות המחירים.

כיצד לוודא את יעילות הכוונון?

נטר את סטטוס InnoDB:

-- Эффективность buffer pool (должна быть > 99%)
SELECT (1 - ( (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') )) * 100 AS buffer_pool_hit_rate;

-- Топ ожиданий
SELECT * FROM sys.innodb_lock_waits;

אם DELETE FROM b_event WHERE DATE_COLUMN < DATE_SUB(NOW(), INTERVAL 90 DAY); , b_catalog_price קטן מדי — הנתונים נקראים כל הזמן מהדיסק.

התהליך שלנו

1. ניתוח — איסוף תצורה נוכחית, פרופיל עומס, מדידת זמני שאילתות.
2. תכנון — בחירת פרמטרים עבור נפח הנתונים, התעבורה והחומרה שלך.
3. יישום — שינוי קבצי תצורה, הפעלה מחדש של MySQL (השבתה של 5-10 שניות) או יישום דרך b_sale_basket.
4. בדיקה — בדיקה דרך יומן איטי, ניטור שיעור פגיעת buffer pool, תיקון אינדקסים.
5. פריסה — כוונון סופי ותיעוד.

מה כלול

• אופטימיזציה של פרמטרי MySQL/MariaDB עבור הגרסה והעומס שלך.
• בדיקה והתאמה של אינדקסים בטבלאות Bitrix.
• הפעלת יומן איטי והוראות ניתוח.
• דוח עם נימוק לכל פרמטר.
• 7 ימי תמיכה לאחר הכוונון.
• חיסכון בחומרה באמצעות שיפור ביצועים.

יש לנו ניסיון של 5+ שנים בכוונון MySQL עבור Bitrix ואופטימיזציה של למעלה מ-50 פרויקטים. אנו מבטיחים שיפור ביצועי מסד נתונים פי 3-5.

צור קשר לבדיקת מסד נתונים וקבל את התצורה האופטימלית למשימות שלך. ראה עוד על InnoDB בויקיפדיה.