XF 2.1 Typical size of xf_data_registry?

[Joe Link]

Investigating why our database is 17.8 GB, I noticed our largest table is xf_data_registry 5 GB on disk, yet reported as 20.1 MB in phpMyAdmin. Is this typical, or an indication that we have an issue?

 

[MattW]

You only have a few records in that table as well

MariaDB [****]> SELECT COUNT(*) FROM xf_data_registry;
+----------+
| COUNT(*) |
+----------+
|       68 |
+----------+
1 row in set (0.000 sec)

 

[MattW]

https://www.percona.com/blog/2013/0...e-in-innodb-when-innodb_file_per_table-is-on/

The table space isn't reclaimed when using individual files for tables, so they keep growing. You can reclaim it by running the ALTER TABLE {TABLENAME} ENGINE=INNODB command against the table

From my own server

ls -hl xf_mail_queue*
-rw-rw---- 1 mysql mysql 2.1K Jul 16 13:25 xf_mail_queue_failed.frm
-rw-rw---- 1 mysql mysql 128K Jul 16 13:26 xf_mail_queue_failed.ibd
-rw-rw---- 1 mysql mysql 1.6K Jul 16 13:25 xf_mail_queue.frm
-rw-rw---- 1 mysql mysql  36M Sep 11 16:22 xf_mail_queue.ibd

MariaDB [forum]> ALTER TABLE xf_mail_queue ENGINE=INNODB;
Query OK, 0 rows affected (0.013 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [forum]> exit;
Bye
ls -hl xf_mail_queue*
-rw-rw---- 1 mysql mysql 2.1K Jul 16 13:25 xf_mail_queue_failed.frm
-rw-rw---- 1 mysql mysql 128K Jul 16 13:26 xf_mail_queue_failed.ibd
-rw-rw---- 1 mysql mysql 1.6K Sep 11 16:31 xf_mail_queue.frm
-rw-rw---- 1 mysql mysql 112K Sep 11 16:31 xf_mail_queue.ibd

 

[Joe Link]

https://www.percona.com/blog/2013/0...e-in-innodb-when-innodb_file_per_table-is-on/

The table space isn't reclaimed when using individual files for tables, so they keep growing. You can reclaim it by running the ALTER TABLE {TABLENAME} ENGINE=INNODB command against the table

From my own server

ls -hl xf_mail_queue*
-rw-rw---- 1 mysql mysql 2.1K Jul 16 13:25 xf_mail_queue_failed.frm
-rw-rw---- 1 mysql mysql 128K Jul 16 13:26 xf_mail_queue_failed.ibd
-rw-rw---- 1 mysql mysql 1.6K Jul 16 13:25 xf_mail_queue.frm
-rw-rw---- 1 mysql mysql  36M Sep 11 16:22 xf_mail_queue.ibd

MariaDB [forum]> ALTER TABLE xf_mail_queue ENGINE=INNODB;
Query OK, 0 rows affected (0.013 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [forum]> exit;
Bye
ls -hl xf_mail_queue*
-rw-rw---- 1 mysql mysql 2.1K Jul 16 13:25 xf_mail_queue_failed.frm
-rw-rw---- 1 mysql mysql 128K Jul 16 13:26 xf_mail_queue_failed.ibd
-rw-rw---- 1 mysql mysql 1.6K Sep 11 16:31 xf_mail_queue.frm
-rw-rw---- 1 mysql mysql 112K Sep 11 16:31 xf_mail_queue.ibd

xf_mail_queue is back up to 6.4GB and doesn't appear to have any entries.

Are there any changes we can make so we don't have to alter the table periodically?

 

[Joe Link]

Just checked this again and it's up to 15GB now!

Where do we start our investigation to figure out how to fix this?

 

[faker.king]

https://www.percona.com/blog/2013/0...e-in-innodb-when-innodb_file_per_table-is-on/

The table space isn't reclaimed when using individual files for tables, so they keep growing. You can reclaim it by running the ALTER TABLE {TABLENAME} ENGINE=INNODB command against the table

From my own server

ls -hl xf_mail_queue*
-rw-rw---- 1 mysql mysql 2.1K Jul 16 13:25 xf_mail_queue_failed.frm
-rw-rw---- 1 mysql mysql 128K Jul 16 13:26 xf_mail_queue_failed.ibd
-rw-rw---- 1 mysql mysql 1.6K Jul 16 13:25 xf_mail_queue.frm
-rw-rw---- 1 mysql mysql  36M Sep 11 16:22 xf_mail_queue.ibd

MariaDB [forum]> ALTER TABLE xf_mail_queue ENGINE=INNODB;
Query OK, 0 rows affected (0.013 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [forum]> exit;
Bye
ls -hl xf_mail_queue*
-rw-rw---- 1 mysql mysql 2.1K Jul 16 13:25 xf_mail_queue_failed.frm
-rw-rw---- 1 mysql mysql 128K Jul 16 13:26 xf_mail_queue_failed.ibd
-rw-rw---- 1 mysql mysql 1.6K Sep 11 16:31 xf_mail_queue.frm
-rw-rw---- 1 mysql mysql 112K Sep 11 16:31 xf_mail_queue.ibd

Is there any permanent solution for this rather than running running this command every now and then