Comment 1 for bug 1825477

Hi there -

SQLAlchemy maintainer here. We are seeing extremely slow performance in this same area, such that the MySQL query times out and the service fails. Additionally, it pushed the CPU use of MySQL to 100% per cpu which is something I've never seen before. If the MySQL backend is recommended for use then I would think this should be high priority for this backend as it is a blocking issue when it causes MySQL itself to lose its ability to respond to other queries.

The reason seems to be that the query is too complicated and needs to be rewritten, most likely as a series of smaller and simpler queries. It has lots of deep nesting, subquery correlation, and UNIONs, enough so that it can cause MySQL to grind to a halt, making this more of a blocker. The code at https://github.com/openstack/panko/blob/7fe341001cb3132389d62c99d5bc8a790c366958/panko/storage/impl_sqlalchemy.py#L345 is explicit in it's request for this level of nesting and complexity so this is the area that would need to be reworked somehow.

Here's a full dump of the SQL we've observed, which I've formatted for readability:

SELECT
 anon_1.trait_datetime_event_id AS anon_1_trait_datetime_event_id, anon_1.trait_datetime_key AS anon_1_trait_datetime_key,
 anon_1.trait_datetime_value AS anon_1_trait_datetime_value, anon_1.anon_2 AS anon_1_anon_2,
 anon_1.anon_3 AS anon_1_anon_3, anon_1.anon_4 AS anon_1_anon_4
FROM (
 SELECT trait_datetime.event_id AS trait_datetime_event_id, trait_datetime.`key` AS trait_datetime_key,
 trait_datetime.value AS trait_datetime_value,
 CAST(NULL AS SIGNED INTEGER) AS anon_2, NULL AS anon_3, CAST(NULL AS CHAR(255)) AS anon_4 FROM trait_datetime
 WHERE EXISTS (
  SELECT * FROM (
   SELECT event.id AS id FROM event INNER JOIN event_type ON event_type.id = event.event_type_id
   WHERE (
    EXISTS (
     SELECT * FROM (
      SELECT anon_7.trait_text_event_id AS trait_text_event_id
      FROM (
       SELECT trait_text.event_id AS trait_text_event_id FROM trait_text
       WHERE NOT (
        EXISTS (
         SELECT * FROM (
          SELECT trait_text.event_id AS event_id FROM trait_text
          WHERE trait_text.`key` = %(key_1)s
         ) AS anon_8
        WHERE trait_text.event_id = anon_8.event_id
       )
      )
      UNION
      SELECT trait_text.event_id AS trait_text_event_id
      FROM trait_text, event
      WHERE trait_text.`key` = %(key_2)s AND trait_text.value = %(value_1)s AND event.id = trait_text.event_id
     ) AS anon_7
    ) AS anon_6
    WHERE event.id = anon_6.trait_text_event_id)
   ) AND event.`generated` >= %(generated_1)s
   ORDER BY event.`generated` ASC, event.message_id ASC
    LIMIT %(param_1)s
   ) AS anon_5
  WHERE trait_datetime.event_id = anon_5.id
 )
 UNION ALL
 SELECT
  trait_int.event_id AS trait_int_event_id, trait_int.`key` AS trait_int_key,
  NULL AS anon_9, trait_int.value AS trait_int_value,
  NULL AS anon_10, NULL AS anon_11 FROM trait_int
  WHERE EXISTS (
   SELECT * FROM (
    SELECT event.id AS id FROM event
    INNER JOIN event_type ON event_type.id = event.event_type_id
    WHERE (
     EXISTS (
      SELECT * FROM (
       SELECT anon_7.trait_text_event_id AS trait_text_event_id
       FROM (
        SELECT trait_text.event_id AS trait_text_event_id
        FROM trait_text
        WHERE NOT (
         EXISTS (
          SELECT * FROM (
           SELECT trait_text.event_id AS event_id FROM trait_text
           WHERE trait_text.`key` = %(key_1)s
          ) AS anon_8
         WHERE trait_text.event_id = anon_8.event_id
        )
       )
       UNION
       SELECT trait_text.event_id AS trait_text_event_id
       FROM trait_text, event
       WHERE trait_text.`key` = %(key_2)s AND trait_text.value = %(value_1)s
       AND event.id = trait_text.event_id
      ) AS anon_7
     ) AS anon_6 WHERE event.id = anon_6.trait_text_event_id
    )
   ) AND event.`generated` >= %(generated_1)s
   ORDER BY event.`generated` ASC, event.message_id ASC
    LIMIT %(param_2)s
   ) AS anon_12
  WHERE trait_int.event_id = anon_12.id
 )

 UNION ALL
 SELECT trait_float.event_id AS trait_float_event_id, trait_float.`key` AS trait_float_key,
 NULL AS anon_13, NULL AS anon_14,
 trait_float.value AS trait_float_value, NULL AS anon_15
 FROM trait_float
 WHERE EXISTS (
  SELECT * FROM (
   SELECT event.id AS id FROM event INNER JOIN event_type ON event_type.id = event.event_type_id
   WHERE (
    EXISTS (
     SELECT * FROM (
      SELECT anon_7.trait_text_event_id AS trait_text_event_id
      FROM (
       SELECT trait_text.event_id AS trait_text_event_id
       FROM trait_text
       WHERE NOT (
        EXISTS (
         SELECT * FROM (
          SELECT trait_text.event_id AS event_id FROM trait_text
          WHERE trait_text.`key` = %(key_1)s
         ) AS anon_8 WHERE trait_text.event_id = anon_8.event_id
        )
       )
       UNION SELECT trait_text.event_id AS trait_text_event_id
       FROM trait_text, event
       WHERE trait_text.`key` = %(key_2)s AND trait_text.value = %(value_1)s AND event.id = trait_text.event_id
      ) AS anon_7
     ) AS anon_6
     WHERE event.id = anon_6.trait_text_event_id
    )
   ) AND event.`generated` >= %(generated_1)s ORDER BY event.`generated` ASC, event.message_id ASC
    LIMIT %(param_3)s
   ) AS anon_16
  WHERE trait_float.event_id = anon_16.id
 )

 UNION ALL

 SELECT trait_text.event_id AS trait_text_event_id, trait_text.`key` AS trait_text_key,
  NULL AS anon_17, NULL AS anon_18, NULL AS anon_19, trait_text.value AS trait_text_value
 FROM trait_text
 WHERE EXISTS (
  SELECT * FROM (
   SELECT event.id AS id FROM event INNER JOIN event_type ON event_type.id = event.event_type_id
   WHERE (
    EXISTS (
     SELECT * FROM (
      SELECT anon_7.trait_text_event_id AS trait_text_event_id
      FROM (
       SELECT trait_text.event_id AS trait_text_event_id FROM trait_text
       WHERE NOT (
        EXISTS (
         SELECT * FROM (
          SELECT trait_text.event_id AS event_id FROM trait_text
          WHERE trait_text.`key` = %(key_1)s
         ) AS anon_8 WHERE trait_text.event_id = anon_8.event_id
        )
       )
       UNION SELECT trait_text.event_id AS trait_text_event_id FROM trait_text, event
       WHERE trait_text.`key` = %(key_2)s AND trait_text.value = %(value_1)s
       AND event.id = trait_text.event_id
      ) AS anon_7
     ) AS anon_6 WHERE event.id = anon_6.trait_text_event_id
    )
   ) AND event.`generated` >= %(generated_1)s ORDER BY event.`generated` ASC, event.message_id ASC
    LIMIT %(param_4)s
   ) AS anon_20
  WHERE trait_text.event_id = anon_20.id
 )
) AS anon_1 ORDER BY anon_1.trait_datetime_key