search

txxxxc / isucon11-qualify

0 stars 0 forks source link

コンディションを期間でソートするのめっちゃ遅いなぁ #30

Closed txxxxc closed 2 months ago

txxxxc commented 3 months ago

cacheはしても無理そう

❯ bat --theme=Nord logs/mysql/slow-query.log | grep "AND `timestamp`" | wc -l
    9660
[I] ~/projects/github.com/txxxxc/isucon11-qualify (feature/#29-pprotein)
❯ bat --theme=Nord logs/mysql/slow-query.log | grep "AND `timestamp`" | sort | uniq -c | wc -l
    9585
txxxxc commented 3 months ago

今までのメモ

now
3103 rows in set (0.013 sec)

analyze SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = '31b59925-95f0-4c3c-ad53-8d2eb70e83e9' AND `timestamp` < '2021-08-27 15:00:00' AND '2021-08-27 14
:00:00' <= `timestamp` ORDER BY `timestamp` DESC\G;
→0.008

analyze SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = '31b59925-95f0-4c3c-ad53-8d2eb70e83e9' AND `timestamp` BETWEEN '2021-08-27 14:00:00' AND '2021-08
-27 15:00:00' ORDER BY `timestamp` DESC\G;
→0.002

まじでbetweenにしたら早くなるっぽい

explain SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = '31b59925-95f0-4c3c-ad53-8d2eb70e83e9' AND `timestamp` BETWEEN CAST('2021-08-10 01:20:10' AS DATE
TIME) AND CAST('2021-08-24 19:41:14' AS DATETIME) ORDER BY `timestamp` DESC\G;
1 row in set (0.002 sec)

cast as datetimeはあんまりでした。

一旦betweenはdatetimeでキャストする
whereは別にキャストしなくてもOK
txxxxc commented 3 months ago

これもしかして逆にしたら早い？

txxxxc commented 3 months ago

ていうかメッセージを基準に取ってきたら全然早くなる説あります。 現に8通りしかなかった(3つ状態があってtrue, falseの2通りなので、2^3乗)

+--------+--------------------------------------+---------------------+------------+----------------------------------------------------+-----------------------------------------+----------------------------+
| id     | jia_isu_uuid                         | timestamp           | is_sitting | condition                                          | message                                 | created_at                 |
+--------+--------------------------------------+---------------------+------------+----------------------------------------------------+-----------------------------------------+----------------------------+
| 181492 | f7eab45c-4417-4b2d-80bb-47fe219f2e1c | 2021-08-27 09:02:26 |          1 | is_dirty=true,is_overweight=true,is_broken=true    | 改善を希望します。                      | 2024-08-06 01:07:06.120385 |
| 176414 | f7eab45c-4417-4b2d-80bb-47fe219f2e1c | 2021-08-26 21:22:20 |          0 | is_dirty=true,is_overweight=false,is_broken=true   | 汚れているし壊れています。              | 2024-08-06 01:07:04.721657 |
| 125899 | f7eab45c-4417-4b2d-80bb-47fe219f2e1c | 2021-08-22 00:02:27 |          1 | is_dirty=false,is_overweight=true,is_broken=true   | 重たいし壊れています。                  | 2024-08-06 01:06:50.646555 |
| 119823 | f7eab45c-4417-4b2d-80bb-47fe219f2e1c | 2021-08-21 10:22:21 |          1 | is_dirty=true,is_overweight=true,is_broken=false   | 汚れているし重たいです。                | 2024-08-06 01:06:49.013101 |
|  19825 | f7eab45c-4417-4b2d-80bb-47fe219f2e1c | 2021-08-11 19:22:20 |          1 | is_dirty=false,is_overweight=true,is_broken=false  | 重たいです。                            | 2024-08-06 01:06:21.291985 |
|  17503 | f7eab45c-4417-4b2d-80bb-47fe219f2e1c | 2021-08-11 13:22:21 |          0 | is_dirty=true,is_overweight=false,is_broken=false  | 汚れています。                          | 2024-08-06 01:06:20.566087 |
|  14866 | f7eab45c-4417-4b2d-80bb-47fe219f2e1c | 2021-08-11 07:22:24 |          1 | is_dirty=false,is_overweight=false,is_broken=true  | 壊れています。                          | 2024-08-06 01:06:19.896997 |
|    669 | f7eab45c-4417-4b2d-80bb-47fe219f2e1c | 2021-08-09 22:22:22 |          0 | is_dirty=false,is_overweight=false,is_broken=false | いい感じです！                          | 2024-08-06 01:06:15.874496 |
+--------+--------------------------------------+---------------------+------------+----------------------------------------------------+-----------------------------------------+----------------------------+
txxxxc commented 3 months ago

is_dirty=true,is_overweight=true,is_broken=true を文字列にパースする処理が必要っぽい

txxxxc commented 3 months ago

conditionはwarning,criticalとかがあるっぽいです info,warning,critical

txxxxc commented 3 months ago 
+----------------------------------------------------+
| condition                                          |
+----------------------------------------------------+
| is_dirty=false,is_overweight=false,is_broken=false |
| is_dirty=false,is_overweight=false,is_broken=true  |
| is_dirty=false,is_overweight=true,is_broken=false  |
| is_dirty=false,is_overweight=true,is_broken=true   |
| is_dirty=true,is_overweight=false,is_broken=false  |
| is_dirty=true,is_overweight=false,is_broken=true   |
| is_dirty=true,is_overweight=true,is_broken=false   |
| is_dirty=true,is_overweight=true,is_broken=true    |
+----------------------------------------------------+
txxxxc commented 3 months ago

結構めんどいかもしれん

func calculateConditionLevel(condition string) (string, error) {
    var conditionLevel string

    warnCount := strings.Count(condition, "=true")
    switch warnCount {
    case 0:
        conditionLevel = conditionLevelInfo
    case 1, 2:
        conditionLevel = conditionLevelWarning
    case 3:
        conditionLevel = conditionLevelCritical
    default:
        return "", fmt.Errorf("unexpected warn count")
    }

    return conditionLevel, nil
}
txxxxc commented 3 months ago

全然無理かもしれん

txxxxc commented 3 months ago

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0150

txxxxc commented 3 months ago

これ使ったらいい感じに絞れたりする？

+----------------------------------------------------+--------------------------------------------+
| condition                                          | message                                    |
+----------------------------------------------------+--------------------------------------------+
| is_dirty=false,is_overweight=false,is_broken=false | わ、わるくないね！                         |
| is_dirty=false,is_overweight=false,is_broken=true  | い、痛くないから！                         |
| is_dirty=false,is_overweight=true,is_broken=false  | 重くないってば！                           |
| is_dirty=false,is_overweight=true,is_broken=true   | …流石に結構痛いかも                        |
| is_dirty=true,is_overweight=false,is_broken=false  | この程度の汚れヘーキだって！               |
| is_dirty=true,is_overweight=false,is_broken=true   | …痛いしベタつく                            |
| is_dirty=true,is_overweight=true,is_broken=false   | ベタつくし重いし…                          |
| is_dirty=true,is_overweight=true,is_broken=true    | もう無理…                                  |
+----------------------------------------------------+--------------------------------------------+
txxxxc commented 3 months ago

critical,warning,infoでindex貼れるのが最善な気はする

txxxxc commented 3 months ago

stored columnで一回やってみるか

txxxxc commented 3 months ago

mariadbではgenerated columnとnot nullは一気に追加できないらしい https://stackoverflow.com/questions/68008727/error-when-importing-virtual-column-from-mysql-to-mariadb

txxxxc commented 3 months ago

INSERT INTOisu_conditionVALUES実行するときに、valuesの値をstored columnの数も含めて実行しないとエラー吐くのしょうもなすぎる

ERROR 1906 (HY000): The value specified for generated column 'condition_level' in table 'isu_condition' has been ignored

指定したら指定したで無視されそう

txxxxc commented 3 months ago

init.sqlでやるのではなくて、後でalter tableやるのが丸い説あります

txxxxc commented 3 months ago

betweenは境界値を含むらしい；；

txxxxc commented 3 months ago

いい感じに速度上がったっぽい