MySQL database cleanup / ibdata growing too large + Reindexing

SuiteCRM Forum - English Language Developer Collaboration
#1

For a customer of us, we have a SuiteCRM in which the ibdata1 file om MySQL (MariaDB) (transactions) is growing and growing. It is now 62GB and recently we cleaned up the aow_xxxxx tabled by truncating them.

total 64643524
drwxr-xr-x 7 mysql mysql 4096 Jun 25 11:55 .
drwxr-xr-x. 34 root root 4096 Jun 26 04:01 …
-rw-rw---- 1 mysql mysql 16384 Jun 23 14:46 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Jun 23 14:46 aria_log_control
-rw-rw---- 1 mysql mysql 5242880 Jun 26 22:39 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jun 26 22:39 ib_logfile1
-rw-rw---- 1 mysql mysql 65819115520 Jun 26 22:39 ibdata1
-rw-rw---- 1 mysql mysql 86043 Jun 23 14:46 customer-portal03-slow.log
-rw-rw---- 1 mysql mysql 723600 Jun 21 12:42 customer-portal03.log
drwx------ 2 mysql mysql 4096 Feb 16 2016 mysql
srwxrwxrwx 1 mysql mysql 0 Jun 23 14:46 mysql.sock
drwx------ 2 mysql mysql 4096 Feb 16 2016 performance_schema
-rwxr-xr-x 1 root root 247 Nov 17 2016 replace.sh
-rwxr-xr-x 1 root root 248 Nov 20 2016 replace.sh.save
-rw-r–r-- 1 root root 183060941 Nov 21 2016 sugardata.sql.gz
drwx------ 2 mysql mysql 8192 Jun 23 09:54 suitecrm
drwx------ 2 mysql mysql 12288 May 2 12:58 suitecrmtest
-rw-r–r-- 1 root root 181396585 Nov 21 2016 suitecrmtest.sql.gz
drwx------ 2 mysql mysql 6 Feb 16 2016 test

But this file is very large and is there a method to clean it up ?

And another issue is that the index is growing fast as well…

du /var/www/html/suitecrm | sort -n

36097084 /var/www/html/suitecrm/modules/AOD_Index/Index
36097084 /var/www/html/suitecrm/modules/AOD_Index/Index/Index

which means 36 gigs for the indexing.

How can we safely clean up this ?

#2

I don’t know if you’ve already done a table-by-table analysis or if you just focused on aow_workflow?


SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;