כוונון מאגר InnoDB עבור 1C-Bitrix: האץ את MySQL

מדוע מאגר של 128 MB מאט את Bitrix?

MySQL מבזבז שנייה שלמה על שאילתה שאמורה לרוץ ב-10 מילישניות. SHOW STATUS LIKE 'Innodb_buffer_pool_reads' מחזיר עשרות אלפי קריאות פיזיות לדקה — נתונים נקראים כל הזמן מהדיסק. הסיבה כמעט תמיד זהה: innodb_buffer_pool_size מוגדר ל-128 MB כברירת מחדל, בעוד שקבוצת הנתונים העובדת של אתר Bitrix כבר מזמן עברה ג'יגה.

נתקלנו פעמים רבות במצבים שבהם בעלי חנויות מקוונות על 1C-Bitrix מתלוננים על האטות בעומס שיא, ושורש הבעיה הוא בדיוק מאגר הבuffer. לדוגמה, לאחרונה בפרויקט עם 50,000 מוצרים, hit_ratio היה 87% — כל קריאה שביעית הלכה לדיסק. לאחר כוונון המאגר ל-4 GB, hit_ratio עלה ל-99.8%, זמן השאילתה הממוצע ירד מ-350 ל-20 ms. במשך 5 שנים, כיוונו InnoDB במאות פרויקטים ואנחנו מבטיחים לפחות האצה של פי 2-3 בשאילתות לאחר הגדרה נכונה.

מהו InnoDB Buffer Pool ומדוע גודלו קריטי

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

אתר Bitrix טיפוסי בגודל בינוני:

• b_iblock_element_property — 500 MB – 3 GB
• b_iblock_element — 50–300 MB
• b_catalog_price, b_catalog_product — 100–500 MB
• אינדקסים לטבלאות אלה — בערך אותו דבר

קבוצת העבודה הכוללת — 1–8 GB. ברירת המחדל של 128 MB מכסה פחות מ-10%.

אבחון וחישוב גודל Buffer Pool

כיצד לאבחן חוסר?

הרץ את השאילתות:

SELECT (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS hit_ratio
FROM (
    SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r,
(
    SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_flushed';
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';

אם SELECT (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS hit_ratio FROM ( SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads' ) r, ( SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests' ) rr; SHOW STATUS LIKE 'Innodb_buffer_pool_pages_flushed'; SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free'; נמוך מ-99% — מאגר ה-buffer בהחלט קטן מדי. אם hit_ratio אינו אפס — מחסור קריטי: פעולות כתיבה ממתינות לדפים פנויים.

כיצד לחשב את הגודל האופטימלי?

מצא את הגודל האמיתי של נתונים ואינדקסים:

SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 0) AS total_mb,
       ROUND(SUM(data_length) / 1024 / 1024, 0) AS data_mb,
       ROUND(SUM(index_length) / 1024 / 1024, 0) AS index_mb
FROM information_schema.TABLES
WHERE table_schema = 'ваша_база';

כלל: buffer pool = 70–80% מגודל קבוצת הנתונים העובדת, אך לא יותר מ-70–80% מסך זיכרון ה-RAM של השרת. על שרת עם 8 GB RAM ומסד נתונים של 4 GB — הגדר 4–5 GB.

הגדרת Buffer Pool ב-my.cnf ושינוי מקוון

[mysqld] innodb_buffer_pool_size = 4G innodb_buffer_pool_instances = 4 innodb_buffer_pool_chunk_size = 128M 

Innodb_buffer_pool_wait_free — מספר המאגרים העצמאיים. מפחית תחרות בין תהליכים על mutexes. כלל: מופע אחד לכל 1 GB של מאגר, מקסימום 64. עבור מאגר של 4 GB — 4 מופעים.

SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 0) AS total_mb, ROUND(SUM(data_length) / 1024 / 1024, 0) AS data_mb, ROUND(SUM(index_length) / 1024 / 1024, 0) AS index_mb FROM information_schema.TABLES WHERE table_schema = 'ваша_база'; חייב להיות מחלק של הסכום הכולל: [mysqld] innodb_buffer_pool_size = 4G innodb_buffer_pool_instances = 4 innodb_buffer_pool_chunk_size = 128M . בדוגמה: innodb_buffer_pool_instances — נכון.

MySQL 5.7.5+ תומך בשינוי גודל buffer pool מקוון:

SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4 ГБ в байтах 

תהליך השינוי הוא אסינכרוני. עקוב אחר ההתקדמות:

SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status'; 

במהלך השינוי, ייתכן ירידה זמנית בביצועים. בצע שינוי בחלון עומס נמוך.

אילו פרמטרים של InnoDB קריטיים עבור Bitrix?

מלבד buffer pool, חשוב להגדיר:

innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

innodb_buffer_pool_chunk_size הוא קריטי על Linux: בלעדיו, נתונים נשמרים במטמון פעמיים — במאגר InnoDB ובמטמון הדפים של הליבה. זה מבזבז זיכרון RAM.

ניטור ותוצאות

ניטור לאחר שינויים

לאחר 30–60 דקות תחת עומס:

SELECT ROUND(Innodb_buffer_pool_bytes_data / 1024 / 1024 / 1024, 2) AS cached_gb
FROM (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_bytes_data
      FROM information_schema.GLOBAL_STATUS
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_bytes_data') t;

SELECT ROUND(
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data')
    / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total')
    * 100, 1
) AS pool_used_pct;

אם innodb_buffer_pool_size = chunk_size * instances * N הוא בעקביות 95–100% — המאגר מלא; שקול להגדיל. אם 60–70% — לגודל הנוכחי יש מרווח.

השוואת ביצועים לפני ואחרי כוונון

טעויות נפוצות בהגדרה

• הגדרת גודל גדול יותר מזיכרון ה-RAM הזמין (גורם ל-swap).
• התעלמות מ-4G = 128M * 4 * 8 על Linux.
• שינוי גודל מקוון ללא ניטור התקדמות.
• בחירת ערכים שאינם כפולות של SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4 ГБ в байтах (שגיאת עיגול).

מה כלול בכוונון Buffer Pool במפתח פתוח

• אבחון המצב הנוכחי: hit_ratio, גודל נתונים, הגדרות.
• חישוב גודל אופטימלי בהתחשב ב-RAM ובעומס.
• שינוי פרמטרים (buffer pool, log, שיטת flush) עם יישום מקוון.
• ניטור לאחר שינוי ודוח עם המלצות.
• ייעוץ לגבי צווארי בקבוק אחרים (אינדקסים, מטמון Bitrix).
• אחריות על תוצאה: hit_ratio > 99% לאחר חימום המאגר.

צור קשר לאבחון ראשוני. הזמן כוונון buffer pool וקבל האצת מסד נתונים היום. נעזור לך לבחור את ההגדרות לפרויקט שלך ולהבטיח ביצועים יציבים בעומס שיא.