properties.ibd table more than 25GB

bringha

Hi there,

after a mass deletion activity which was interrupted, the entire Kopano server ran out of disk space because it blow up the properties.ibd and tproperties.ibd tables to >25GB resp. 7 GB each. Google gives examples that this happens when an ALTER command to tables is interrupted.

I took me now long hrs to get our mail server back by adding another disk and bind it to /var/lib/mysql/kopano directory; however I would like to reduce the tables again to reasonable size …

Is there an easy way how to clean up these tables? Can I drop them/rebuild them? Any experiences with such a situation?

Looking forward to your reply

Br br

fbartels

Hi @bringha ,

I think you are searching for something like https://www.percona.com/blog/2013/09/25/how-to-reclaim-space-in-innodb-when-innodb_file_per_table-is-on/

bringha

Thanks Felix,

The article behind the link suggests that I can delete parts of the entries in the table and then optimize the size of the table again. Are the entries in the table properties.ibd and tproperties.ibd not needed anymore? Any risk of inconsistencies?

Br br

fbartels

Hello @bringha ,

I think you misunderstood the article. They delete some data from a test table to provide a test case for their explanation. In reality what you need to perform is an optimize table on the tables in question.

They state in their article that the complete table will be locked. For Kopano that means that you should take your server offline while you are performing this.

bringha

Thanks Felix,

gave it a try - it assumes an amount of 3 days, 18h, 26 min, 9 sec. to carry out the optimization over approximately 65972488 rows…

Is there perhaps a faster way to get this cleaned up?

Thanks

Br br

fbartels

Hello @bringha ,

sounds like your database could use some tuning. https://kb.kopano.io/display/WIKI/Basic+MySQL+tuning should provide a good start.

bringha

Well …

The parameters are already set like this; it also does not clean up the properties table. I executed now an

optimize table properties;

which ran for 3 hrs and reduced the size of properties from 25 G to 17G; at least a start but still too big

Is it possible to drop and recreate this table? what does properties contain?

Br br

fbartels

Hi @bringha ,

what exactly makes you think its “too big”? Couldn’t the data you’re looking for still exist in the deleted items or soft delete? The properties table holds the majority of your mailbox data, so dropping it (specifically if you only drop properties) would mean that you’d have destroyed your mailboxes.

Large ibdata files (the ones with the ibd file extension) also have their upsides. a large concurrent block of data on your disk means that MySQL has a large block on your disk to work with and optimize read and write access on its own.

bringha

Ah ok - so the properties table holds all mails then …

Too big means that I have on this server 5 Postboxes with 11200 Mails. No big attachments. Before the entire properties table was about 6G. With the failed Mass deletion, it out of the sudden rose to 26G over three days. (Clients are MacOS Mail). Obviously the Mac Clients started again and again trying to delete the Mails but did not succeed.

kopano-admin --purge-softdelete hangs for > 1,5 Days now. Size of properties table has not changed since then …

To me it looks like a corruption of this table due to what trigger ever although the server still works (but slower than normal)

Only error messages in mysql error log were
2017-10-16T18:47:46.187756Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 17010ms. The settings might not be optimal. (flushed=5 and evicted=0, during the time.)
2017-10-16T18:53:45.413305Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 16576ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.)
2017-10-16T18:53:57.467104Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8053ms. The settings might not be optimal. (flushed=169 and evicted=0, during the time.)

I such a case like mine and assuming having the opportunity to rebuild single indexes/tables, ibdata files seem to me more appropriate … also it made my life definitely easier as I could provide additional disk space for parts of the database and I did not need to copy the entire database …

Br br

fbartels

Hello @bringha ,

I think the slowdown you still see could still be because of settings that still can be improved (and not corruption). If you have a subscription I would encourage you to reach out to our support for our more comprehensive tuning guide.

Have you logged into the WebApp to check if there are items in the softdelete at all?

bringha

I did, in the Folder ‘Gelöschte Objekte’ (which needs more than 5 min to show in web app), each and every mail of the mass deletion is 11 times there. in Total this folder is 9 GB in size …