maria-discuss team mailing list archive

[Said Ramirez <sramirez@xxxxxxxxxxxx>]

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@xxxxxxxxxxxx>
To: "Said Ramirez" <sramirez@xxxxxxxxxxxx>
Cc: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
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