Navigation

    Kopano
    • Register
    • Login
    • Search
    • Categories
    • Get Official Kopano Support
    • Recent
    Statement regarding the closure of the Kopano community forum and the end of the community edition

    properties.ibd table more than 25GB

    Kopano Groupware Core
    2
    11
    2232
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • fbartels
      fbartels Kopano @bringha last edited by

      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/

      Regards Felix

      Resources:
      https://kopano.com/blog/how-to-get-kopano/
      https://documentation.kopano.io/
      https://kb.kopano.io/

      Support overview:
      https://kopano.com/support/

      1 Reply Last reply Reply Quote 0
      • bringha
        bringha last edited by

        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 1 Reply Last reply Reply Quote 0
        • fbartels
          fbartels Kopano @bringha last edited by

          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.

          Regards Felix

          Resources:
          https://kopano.com/blog/how-to-get-kopano/
          https://documentation.kopano.io/
          https://kb.kopano.io/

          Support overview:
          https://kopano.com/support/

          1 Reply Last reply Reply Quote 0
          • bringha
            bringha last edited by

            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 1 Reply Last reply Reply Quote 0
            • fbartels
              fbartels Kopano @bringha last edited by

              Hello @bringha ,

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

              Regards Felix

              Resources:
              https://kopano.com/blog/how-to-get-kopano/
              https://documentation.kopano.io/
              https://kb.kopano.io/

              Support overview:
              https://kopano.com/support/

              1 Reply Last reply Reply Quote 0
              • bringha
                bringha last edited by

                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 1 Reply Last reply Reply Quote 0
                • fbartels
                  fbartels Kopano @bringha last edited by

                  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.

                  Regards Felix

                  Resources:
                  https://kopano.com/blog/how-to-get-kopano/
                  https://documentation.kopano.io/
                  https://kb.kopano.io/

                  Support overview:
                  https://kopano.com/support/

                  1 Reply Last reply Reply Quote 0
                  • bringha
                    bringha last edited by

                    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 1 Reply Last reply Reply Quote 0
                    • fbartels
                      fbartels Kopano @bringha last edited by

                      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?

                      Regards Felix

                      Resources:
                      https://kopano.com/blog/how-to-get-kopano/
                      https://documentation.kopano.io/
                      https://kb.kopano.io/

                      Support overview:
                      https://kopano.com/support/

                      1 Reply Last reply Reply Quote 0
                      • bringha
                        bringha last edited by

                        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 …

                        1 Reply Last reply Reply Quote 0
                        • First post
                          Last post