potiuk commented on issue #33647: URL: https://github.com/apache/airflow/issues/33647#issuecomment-1717003563
> Just wanted to confirm, what you are suggesting is the query used for triggers need to be updated to solve this, correct? Let me explain how I see the options we have here (I have not done a detailed analysis what is wrong - those are a bit intelligent guesses). I am not sure you can do much more NOW than analysing the tables periodically until the code of Airlfow is updated (but maybe you can also attempt to PR some changes). Likely we need to load at the steps involved and optimise the way DB is used. Running analyse frequently should likely help you (and likely you can even schedule it every day for example) - but I think fundamentally someone (maybe @Taragolis or someone else) needs to optimise the way how we run queries to get rid of the effect you see in case you have huge amount of triggers happening. The root cause is - I believe - that when you add an delete a lot of data, at some point in time the built-in optimiser of MySQL gets confused about what is the fastest execution plan to get the data, and likely produces the plan that is not-at-all optimised - I think the main reason for that is that indexes are never rebuilt and they grow in size when data is often deleted and added and at some point of time the optimizer sees that the index size is so big, that it is better and faster to not use the index at all. This is why "analyze" helps, because it looks at the actual data left and allows the optimizer to find a better and more optimized way to read the data, it rebuilds the index and makes it way smaller, and then the optimizer will start using it again/ I see two ways you could approach it: 1) optimize the code (suggested by @Taragolis ) by running less number of queries but then it has to be done carefully, not using the ever-growing indexes and maybe dropping the indexes altogether. But that would require to change the way how we are using the DB. 2) we could also add hinting to the query, that would always force the plan that we know is optimal (but there we need to figure out which query and what plan is best, and maybe there are cases where it's not really the best one. But fundamentally if the index will be ever growing, this will break as well. 3) we could possibly rebuild the indexes from airflow side (but that's tricky - when? What to do with other tasks running?) Those are a bit guesses - maybe @Taragolis who have done a bit more analysis can also confirm if my thinking is right. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
