StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql Update performance suddenly abysmal
text
Body
copied!<p><em>MySQL 5.1, Ubuntu 10.10 64bit, Linode virtual machine.</em></p> <p>All tables are <em>InnoDB</em>.</p> <p>One of our production machines uses a MySQL database containing 31 related tables. In one table, there is a field containing display values that may change several times per day, depending on conditions.</p> <p>These changes to the display values are applied lazily throughout the day during usage hours. A script periodically runs and checks a few inexpensive conditions that may cause a change, and updates the display value if a condition is met. However, this lazy method doesn't catch all posible scenarios in which the display value should be updated, in order to keep background process load to a minimum during working hours.</p> <p>Once per night, a script purges all display values stored in the table and recalculates them all, thereby catching all possible changes. This is a much more expensive operation.</p> <p>This has all been running consistently for about 6 months. Suddenly, 3 days ago, the run time of the nightly script went from an average of 40 seconds to 11 minutes.</p> <p>The overall proportions on the stored data have not changed in a significant way.</p> <p>I have investigated as best I can, and the part of the script that is suddenly running slower is the last update statement that writes the new display values. It is executed once per row, given the (INT(11)) id of the row and the new display value (also an INT).</p> <pre><code>update `table` set `display_value` = ? where `id` = ? </code></pre> <p>The funny thing is, that the purge of all the previous values is executed as:</p> <pre><code>update `table` set `display_value` = null </code></pre> <p>And this statement still runs at the same speed as always.</p> <p>The <code>display_value</code> field is not indexed. <code>id</code> is the primary key. There are 4 other foreign keys in <code>table</code> that are not modified at any point during execution.</p> <p>And the final curve ball: If I dump this schema to a test VM, and execute the same script it runs in 40 seconds not 11 minutes. I have not attempted to rebuild the schema on the production machine, as that's simply not a long term solution and I want to understand what's happening here.</p> <p>Is something off with my indexes? Do they get cruft in them after thousands of updates on the same rows?</p> <hr> <p><strong>Update</strong></p> <p>I was able to completely resolve this problem by running optimize on the schema. Since InnoDB doesn't support optimize, this forced a rebuild, and resolved the issue. Perhaps I had a corrupted index?</p> <pre><code>mysqlcheck -A -o -u <user> -p </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload