База падает, множественные запросы

Всем привет! в MySQL. Накапливаются вот такие запросы и база соответственно падает, как это вылечить кто сталкивался?

SELECT p.*,p.id,(p.count > 0 || p.count IS NULL) AS in_stock,GROUP_CONCAT(DISTINCT ps1.id) AS sku_ids FROM shop_product p        JOIN shop_product_skus ps1                ON ps1.product_id = p.id        JOIN shop_product_features pf1                ON p.id = pf1.product_id AND pf1.feature_id = 6        JOIN shop_product_features pf2                ON p.id = pf2.product_id AND pf2.feature_id = 16        JOIN shop_product_features pf3                ON p.id = pf3.product_id AND pf3.feature_id = 83        JOIN shop_product_features pf4                ON p.id = pf4.product_id AND pf4.feature_id = 68        JOIN shop_product_features pf5                ON p.id = pf5.product_id AND pf5.feature_id = 160        JOIN shop_product_features pf6                ON p.id = pf6.product_id AND pf6.feature_id = 74        JOIN shop_product_features pf7                ON p.id = pf7.product_id AND pf7.feature_id = 146        JOIN shop_product_features pf8                ON p.id = pf8.product_id AND pf8.feature_id = 13        JOIN shop_product_features pf9                ON p.id = pf9.product_id AND pf9.feature_id = 44        JOIN shop_product_features pf10                ON p.id = pf10.product_id AND pf10.feature_id = 45        JOIN shop_category_products cp1                ON p.id = cp1.product_id WHERE ps1.available > 0 AND ps1.status > 0        AND (ps1.primary_price) >= 0        AND (ps1.primary_price) <= 1        AND pf1.feature_value_id IN (881) AND (pf1.sku_id IS NULL OR pf1.sku_id = ps1.id)        AND pf2.feature_value_id IN (123) AND (pf2.sku_id IS NULL OR pf2.sku_id = ps1.id)        AND pf3.feature_value_id IN (972) AND (pf3.sku_id IS NULL OR pf3.sku_id = ps1.id)        AND pf4.feature_value_id IN (861) AND (pf4.sku_id IS NULL OR pf4.sku_id = ps1.id)        AND pf5.feature_value_id IN (1662) AND (pf5.sku_id IS NULL OR pf5.sku_id = ps1.id)        AND pf6.feature_value_id IN (911) AND (pf6.sku_id IS NULL OR pf6.sku_id = ps1.id)        AND pf7.feature_value_id IN (1534) AND (pf7.sku_id IS NULL OR pf7.sku_id = ps1.id)        AND pf8.feature_value_id IN (120) AND (pf8.sku_id IS NULL OR pf8.sku_id = ps1.id)        AND pf9.feature_value_id IN (719) AND (pf9.sku_id IS NULL OR pf9.sku_id = ps1.id)        AND pf10.feature_value_id IN (720) AND (pf10.sku_id IS NULL OR pf10.sku_id = ps1.id)        AND p.status = 1        AND cp1.category_id IN(4076,615,4060,616,617,619,623,624,625,626,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,4063,663,4061,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,933,942,957,1001,1005,1038,1041,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,1103,1163,1164,1216,1232,1328,1340,1362,1429,1576,1614,1733,1927,2322,2366,2373,2708,2786,3055,3162,3191,3334,3335,3500,3590,3619,3635,3636,3663,3702,3703,3740,3813,3921,3978,4018,4019,4020,4021,4022,4023,4073) GROUP BY p.id ORDER BY in_stock DESC,p.name ASC, p.id LIMIT 30 

6 ответов

  • 1
    DEV 8 апреля 2021 09:29 #

    Что неужели никто не поможет ?

    Обновления никакие не прилетали в базе виснут такие вот запросы как выше, 

    slow-log выглядит так

    IMIT 30;
    # Time: 210408  8:30:28
    # User@Host: c26325_next_******_com[c26325_next_******_com] @ localhost []
    # Thread_id: 906  Schema: c26325_next_******_com_next  QC_hit: No
    # Query_time: 53934.508297  Lock_time: 0.000502  Rows_sent: 0  Rows_examined: 6241
    # Rows_affected: 0  Bytes_sent: 0
    # Tmp_tables: 2  Tmp_disk_tables: 1  Tmp_table_sizes: 16384
    # Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes
    # Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
    SET timestamp=1617859828;
    SELECT p.*,p.id,(p.count > 0 || p.count IS NULL) AS in_stock,GROUP_CONCAT(DISTINCT ps1.id) AS sku_ids
    FROM shop_product p
            JOIN shop_product_skus ps1
                    ON ps1.product_id = p.id
            JOIN shop_product_features pf1
                    ON p.id = pf1.product_id AND pf1.feature_id = 15
            JOIN shop_product_features pf2
                    ON p.id = pf2.product_id AND pf2.feature_id = 16
            JOIN shop_product_features pf3
                    ON p.id = pf3.product_id AND pf3.feature_id = 83
            JOIN shop_product_features pf4
                    ON p.id = pf4.product_id AND pf4.feature_id = 68
            JOIN shop_product_features pf5
                    ON p.id = pf5.product_id AND pf5.feature_id = 160
            JOIN shop_product_features pf6
                    ON p.id = pf6.product_id AND pf6.feature_id = 74
            JOIN shop_product_features pf7
                    ON p.id = pf7.product_id AND pf7.feature_id = 146
            JOIN shop_product_features pf8
                    ON p.id = pf8.product_id AND pf8.feature_id = 13
            JOIN shop_product_features pf9
                    ON p.id = pf9.product_id AND pf9.feature_id = 44
            JOIN shop_product_features pf10
                    ON p.id = pf10.product_id AND pf10.feature_id = 45
            JOIN shop_category_products cp1
                    ON p.id = cp1.product_id
    WHERE ps1.available > 0 AND ps1.status > 0
            AND (ps1.primary_price) >= 1
            AND (ps1.primary_price) <= 1
            AND pf1.feature_value_id IN (122) AND (pf1.sku_id IS NULL OR pf1.sku_id = ps1.id)
            AND pf2.feature_value_id IN (123) AND (pf2.sku_id IS NULL OR pf2.sku_id = ps1.id)
            AND pf3.feature_value_id IN (972) AND (pf3.sku_id IS NULL OR pf3.sku_id = ps1.id)
            AND pf4.feature_value_id IN (861) AND (pf4.sku_id IS NULL OR pf4.sku_id = ps1.id)
            AND pf5.feature_value_id IN (1662) AND (pf5.sku_id IS NULL OR pf5.sku_id = ps1.id)
            AND pf6.feature_value_id IN (911) AND (pf6.sku_id IS NULL OR pf6.sku_id = ps1.id)
            AND pf7.feature_value_id IN (0) AND (pf7.sku_id IS NULL OR pf7.sku_id = ps1.id)
            AND pf8.feature_value_id IN (120) AND (pf8.sku_id IS NULL OR pf8.sku_id = ps1.id)
            AND pf9.feature_value_id IN (719) AND (pf9.sku_id IS NULL OR pf9.sku_id = ps1.id)
            AND pf10.feature_value_id IN (720) AND (pf10.sku_id IS NULL OR pf10.sku_id = ps1.id)
            AND p.status = 1
            AND cp1.category_id IN(4076,615,4060,616,617,619,623,624,625,626,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,4063,663,4061,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,933,942,957,1001,1005,1038,1041,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,1103,1163,1164,1216,1232,1328,1340,1362,1429,1576,1614,1733,1927,2322,2366,2373,2708,2786,3055,3162,3191,3334,3335,3500,3590,3619,3635,3636,3663,3702,3703,3740,3813,3921,3978,4018,4019,4020,4021,4022,4023,4073)
    GROUP BY p.id
    ORDER BY in_stock DESC,p.name ASC, p.id
    LIMIT 30;

    error-log

    2020-12-12 12:45:26 255620 [Warning] Access denied for user 'root'@'localhost' (using password: YES)

    Рестарт вмваре помогает но ненадолго 

  • 1

    Ну фильтры какие-то. Или динамические категории. И что?

  • 1
    info 9 апреля 2021 12:11 #

    чем тут поможешь то? понятное дело что кучу join'ов нужно заменить на один, но на практике это потребует как минимум переопределение класса, а в SS подобные фиксы долго не живут)

  • 1
    Nikolai 9 апреля 2021 15:04 #

    Возможно вот этот плагин поможет:

    https://www.webasyst.ru/store/...

    (но это не точно)

  • 1
    DEV 12 апреля 2021 13:12 #

    Проблема решилась конвертацией в innodb и увеличением ресурсов - посмотрим

  • 1
    marsianin 13 апреля 2021 10:45 #

    можно еще memcached включить, тогда запросов к бд должно стать меньше, т к часть данных будет доставаться из кеша. Это может существенно ускорить работу сайта

Добавить ответ

Чтобы добавить комментарий, зарегистрируйтесь или войдите