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" <[email protected]> To: "Said Ramirez" <[email protected]> Cc: "Maria Discuss" <[email protected]> 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 _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
