Sergei, It doesn't look like it is the long running transactions. For about 15 minutes, we made sure no transaction was running for than 5 minutes. During this time, the size of the index didn't shrink. Does innodb purge out old records from the index on a periodic basis ? If so, when does this happen? Last time we fixed this, we did so by running an optimize table, is there a way to rebuild an index without having to either recreate it or run optimize table ? Thanks, -Said ----- Original Message ----- From: "Sergei Golubchik" <serg@askmonty.org> To: "Said Ramirez" <sramirez@appnexus.com> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> Sent: Monday, March 11, 2013 11:14:11 AM Subject: Re: [Maria-discuss] unexpected index growth Hi, Said! On Mar 11, Said Ramirez wrote:
Sergei, The longest transaction I currently see is ~ 3.5 hours. Which is a long time, and I can look into to that. I haven't seen transactions last days yet. Would having a transaction last hours not cause the old rows from being purged once those transactions have closed? If it were an old or stale transaction keeping the old row versions from being purged, a server restart would fix the issue, no ?
Yes, a long lasting transaction would prevent old row version from being purged even after those transactions have ended. Because a default transaction isolation level is consistent read, which means, that if you've seen some version of a row in a transaction, you should always continue seeing this exactly version of this row until your transaction ends. And InnoDB will have to keep this version and it cannot purge it. Yes, a server restart would fix it.
We also do see a fair amount of deadlocks. Could a thread that is causing the deadlock cause old row versions from being purged even after the this thread is closed?
No. Regards, Sergei