שיפור ביצועים של אלפי inserts בשניה בMYSQL

[nigun]

היום היה לי עומסים גדולים יחסית על המסד נתונים
עם 1100 משתמשים בו זמנית (כולם מבצעים insert בתוך כמה שניות)
וזה גרם לאיטיות בשאילתות (לפחות בSELECT, לא יודע כמה דיליי היה בinsert).

בדקתי בהיסטוריה בפאנל ניהול שלי (הכל מופיע בממוצע של 5 דקות)
מופיע לי שהיה 234% שימוש במעבד (מתוך 6 ליבות)
49.57 (ops/sec (write
load 22.28
יש משהו שיכול לשפר את הבקשות?
האם יעזור להוסיף מעבדים?

עכשיו מצאתי שיש לי לוג מיחוד לMYSQL
הוא כותב שהיה ממוצע של .15.07 insert בשניה ו11.01 SELECT שניה
וממוצע של Slow Queries 0.37
כל זה בממצוע של חמש דקות בפועל אני יודע שכל הבקשות נעשות בחלון זמן הרבה יותר קטן.

מצאתי ריפו קטן שמאחד את כל הinsert ל batch-insert אחד גדול.
האם זה ישפר את הביצועים? האם זה גישה נכונה?

נ.ב. לבנתיים זה משהו שאפשר לחיות איתו
אני פשוט חושש מה יקרה אם יהיה יותר עומסים
ואז זה יהיה מציק הרבה יותר.

[nigun]

עכשיו דיבגתי בזמן אמת
וראיתי שהבעיה היא פשוט תפוסה של 100% של המעבד (יותר נכון 600%)
load 208
mysqld io 40 M
בקיצור צריך להגדיל שרת
ופשוט לזכור שאי אפשר לדבג עם ממוצעים.

[aaron]

אני חושב שיש פרמטר בתפוסה של המעבד בהקשר של המתנה לקריאות/כתיבות לדיסק.. (נראלי ראיתי פעם בnetdata)
40M/s בהקשר הזה לא מוכיח כלום כי זה פעולות מאוד קטנות, מה שאומר שהI/Ops גבוה מאוד בכל מקרה.
(בhtop אפשר להוסיף עמודות עם הנתונים האלה. אולי זה יעזור.)

בכל מקרה, לפני שאתה רץ לשדרג שרת, הייתי חושב שוב איפה הצווארי בקבוק.
יתכן שכדאי להפריד את הנתונים של mysql אל דיסק נפרד.
(או שבכלל הmysql על שרת אחר?)

[nigun]

@aaron
הmysql נמצא על אותו שרת של האפליקציה (שרת TCP +שרת HTTP במקרה הזה)
אבל האפליקציה לא תופסת כמעט משאבים.
כך שהעברה לשרת נפרד לא כל כך תועיל.

על פניו נראה שהצוואר בקבוק הוא פשוט מאות או אלפי inserts בשניה
אולי יש הגדרה מיוחדת בmysql שתשפר את הביצועים?
או לקבץ את כל השאילתות לבקשה אחת גדולה?
או שפשוט לשדרג שרת?
אני יחכה לערב לשדרוג(זה אמנם לוקח דקה וחצי, אבל צריך להפעיל מחדש את השרת)
ומחר אני יראה האם יש שיפור.

[nigun]

@aaron
הגדלתי ל16 ליבות
זה מגיע לכמה שניות לתפוסה מלאה
אבל לא מורגש בפועל ללקוחות.

(הload מגיע ל25
עם 106 IO.
לא נראה לי שהם קשורים כל כך
כי גם בשהIO עומד על 682
הload נשאר על 0.96)

[nigun]

אתמול פתאום השרת קפא
ולא הצלחתי להתחבר אליו בשום אופן ( אפילו סשן פעיל של SSH קפא)
בסוף לא היה לי ברירה והפעלתי מחדש דרך הממשק של החברה
בפאנל ניהול הופיע לי בדקות לפני הנפילה ממוצע של 100 load ו17 inserts בשניה.
יש מצב שזה בגלל זה?
מה הפתרון?

[dovid]

כמה שהטבלה יותר פשוטה, הINSERT זול יותר,
ולעומת זאת ככל שיש יותר אינדקסים וכו' אז זה יקר יותר.
99% מהנתונים לא יודעים לי, אז קשה להביע דעה, אבל התופעה משונה. 1000 בכמה שניות זה בכלל לא הרבה.

[nigun]

אולי זה יעזור?
זה המבנה של הטבלה

CREATE TABLE  `answers` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`room` INT(11) NULL DEFAULT NULL,
`questionId` INT(11) NULL DEFAULT NULL, 
`answerId` INT(11) NULL DEFAULT NULL,
`answerText` TEXT NULL DEFAULT NULL,
       PRIMARY KEY (`id`));

הסטטוס של הטבלה

mysql> show table status WHERE Name   =  'answers';
+---------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+---------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| answers | InnoDB |      10 | Compact    | 45984 |             99 |     4554752 |               0 |            0 |   4194304 |          96598 | 2020-04-26 06:17:37 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
+---------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

[OdedDvir]

@nigun הטבלה נראית בסדר וכמו ש @dovid אמר, אפילו 1000 INSERTs בשניה\שתים הן דבר של מה בכך אפילו באקסס ...
הייתי בודק:

1. האם פקודת INSERT מכילה JOIN?
2. האם לכל INSERT מתלווה חישוב מורכב בקוד?
   אולי תשתף גם מה פקודת ה INSERT.

[nigun]

@OdedDvir

"INSERT INTO `answers`  (`room`,`questionId`,`answerId`,`answerText`) VALUES ( "12345", "2", "1", "מה פרשת השבוע?")"

[dovid]

אין לי הסבר.
זה קרה לי גם בעבר, ואני לא מספיק חקרתי אז.

[nigun]

חשבתי לקחת מסד נתונים מנוהל בdigitalocean
זה יעזור לי?

[nigun]

עכשיו שמתי לב שטעיתי
מדובר בטבלה אחרת

 CREATE TABLE `points` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`room` INT(11) NULL DEFAULT NULL,
`user` VARCHAR(20) NULL DEFAULT NULL,
`questionId` INT(11) NULL DEFAULT NULL,
`points` INT(11) NULL DEFAULT NULL,
`time` INT(64) NULL DEFAULT NULL,  
`grup` VARCHAR(64) NULL DEFAULT NULL,
`answerId` INT(11) NULL DEFAULT NULL,   
        PRIMARY KEY (`id`)"

הסטטוס שלה הוא

mysql> show table status WHERE Name   =  'points';
+--------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+--------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| points | InnoDB |      10 | Compact    | 553311 |             82 |    45678592 |               0 |            0 |   4194304 |        1599099 | 2020-08-11 21:53:07 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
+--------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

והשאילתא נראת כך

 INSERT INTO `points`  (`room`,`user`,`questionId`,`points`,`time`,`grup`,`answerId`) VALUES (?,?,?,?,?,?,? )", room, user, questionId, points, time, grup, answerId

קצת יותר גדול
אבל לא נראה לי שאמור להפריע כל כך

[OdedDvir]

@nigun אני גם לא חושב שזה אמור כל כך להפריע, אבל לפי התאור הראשוני גם מבוצעות שאילתות SELECT ו-INSERT בו זמנית על טבלה זו, ועם חצי מליון שורות אני חושב שכדאי מאוד היה לנרמל את הנתונים עוד יותר, למשל

1. כדאי להפוך את כל השדות של ה-VARCHAR ל-INT.
   user => UserId
   grup => groupId
   וליצור טבלאות עבור Groups ו- Users.
2. לחשוב טוב האם כל השדות האלו הכרחיים לשמירה עבור כל תשובה?
3. אם time הוא רק חותמת זמן, הייתי משאיר זאת לבסיס הנתונים לקבוע ערך ברירת מחדל עבור השדה (=לזמן הנוכחי) ומסיר אותו מה-INSERT.
4. כמה שתקטין יותר את הטבלה המהירות תהיה כנראה טובה יותר. יש לך 4 גורמים שאחראים על המהירות הכללית: מעבד, זיכרון, דיסק ורשת. תבדוק את כולם. אם למשל כל הנתונים לא נטענים לזיכרון בבת אחת, הDB יבצע קאשינג והמהירות תרד פלאים בשעות העומס...
5. אני נזכר שכבר היתה לך איזו בעיה כזו בעבר, וה Execution Plan של ה-DB לא היתה אופטימלית. לפי זכרוני השתמשת בגירסא מיושנת של MySQL, ואולי כדאי לבצע שדרוג.

[שואף]

@nigun איך אתה רואה הסטטוס של הMYSQL ? עם NETDATA ?

[nigun]

@שואף
בפאנל ניהול של linode
אפשר לראות היסטוריה של מעבד,זיכרון,פעולת בMYSQL וכדו'
לא יודע כמה הוא אמין לצורך דיבוג
כי אני משער שהוא לא דוגם כל חצי שניה.

[nigun]

לבנתיים פתחתי מסד נתונים בDO
אולי זה יתן ביצועים יותר טובים
לבנתיים אני רואה שיש ממשק נחמד עם סטיסטיקות מורחבות

[nigun]

עכשיו היה לי כ650 משתמשים על המערכת
וזה אולי נותן קצת יותר תמונה (נראה לי שהנתונים של DO יותר מדוייקים)
כפי שרואים בתמונה אין כל כל הרבה inserts בו זמנית, אלא fetchs
מה זה fetch? ולמה יש לי כל כך הרבה בשניה?
עריכה: אני מבין שזה כתוב השורות במסד נתונים מדפיס בSELECT
אז כנראה שזה לא בעיה.
בכל אופן נראה שהמסד נתונים של DO מתמודד עם זה יפה (הCPU ממש נמוך)
אולי בגלל שהוא משתמש בגירסה 8.0?

[OdedDvir]

@nigun אמר בשיפור ביצועים של אלפי inserts בשניה בMYSQL:

מה זה fetch?

אני לא מכיר את DO אבל לכאורה fetch זו קריאת שורה מטבלה (SELECT)

ולמה יש לי כל כך הרבה בשניה?

כי יש הרבה קריאות.

[nigun]

@OdedDvir
מסתבר שכן
כי בלוג מככבים לי הרבה שאילתות של SELECT (שלפעמים לקחו 217 ms)
(ועכשיו אני נזכר שיש איזה קטע קוד שהשתוללתי שם עם השאילתות)