This rebuilds/defragments the table data so that data is stored in more compact way, updates the indexes accordingly and then does an ANALYZE on the table. Statistics are collected using some random sample pages from new data in the table This clears the current statistics and new statistics are collected when the table is accessed next time. As per the MySQL documentation, these statistics for a table can be re-estimated explicitly by using the following methods: We found the root cause to be the Outdated Table Statistics. Table Statistics can become outdated if there are a lot of insertions and deletions on the given table (which is true in our case). So, when MySQL optimizer is not picking up the right index in EXPLAIN, it could be caused by outdated table statistics. The Root Causeīefore running any query, MySQL optimiser makes decisions about the execution plan (which can be seen via EXPLAIN), which in turn uses table statistics and conditions in clauses like WHERE, ORDER BY, etc. Then I started reading more about why this can be happening. The same query used the required index on these databases, which I got to know using EXPLAIN command.Īt this point, I was literally clueless about why this is happening and I panicked and rushed towards the coffee dispenser, my usual escape when I am stuck with some problem and had quite a few cups of coffee and regained my consciousness. But to verify, I checked the query on other databases with similar schemas (QA and dev environment) and even on replica database. Since this feature was being used extensively in production from past few months without much issues and no change pertaining to this feature went in last few weeks, there is no way the given query was wrong. When I dug deeper into the issue using the EXPLAIN command, I noticed that this query was not using index which it should be using. I started wondering what happened suddenly that query performance degraded. The table being talked about is a frequently used table, having around 300M rows approximately, with a lot of additions and deletions from the table happening on a regular basis. Pretext to understand the issue: We use MySQL 5.7(InnoDb) as our main database.
I immediately checked our logs and noticed that a particular query was taking much longer than expected for a few users, and even timing out for few others. The Problem At Handįew customers have suddenly started to feel slowness and intermittent failures in a particular feature.
#Mysql optimizer turn off software
It was a normal day, I was working on some new feature additions in the software and suddenly an issue pops up. Following is a sequence of few events which happened with me on a normal work day as a software engineer at ClearTax.
0 kommentar(er)
