SonarQube : How to reduce size of measures_data.ib

问题:

I've been using SonarQube/Sonar for a few years now and the measures_data.idb size has grown to a wopping 114GB, yes gig.

I've got the default house keeping settings set.

I'm using MySql 5.6 (with default per file setting)

I'm using Sonar 4.0.

I've tried exporting the db from mysql and then importing it again. but its the same size. It does state..

note     : Table does not support optimize, doing recreate + analyze instead
status   : OK 

I've deleted a couple of old projects from Sonar's "Bulk Deletion" option, but the file is the same size.

Can anyone give me some advice on what to try next?

Thanks Jeff Porter

回答1:

The solution in the end was simple.

Export from MySQL 5.5. (export file is 114 GB)

Uninstall MySQL 5.5 (remove all data files)

Install MySQL 5.6

Import data

Export from MySQL 5.6 (export file is now 13 GB)

Uninstall MySQL 5.6 (remove all data files)

Install MySQL 5.6

Import data

回答2:

I suppose that your file is much larger than its content and that you have innodb_file_per_table turned off. This was the default setting for mysql server, but with the last version, the default value is on.

The Innodb files grow on need, but do not shrink. In fact, it is not even possible to shrink them.

I had a similar situation some time ago. The solution was to turn on innodb_file_per_table (add 'innodb_file_per_table=1' in my.ini file). Then I exported the whole database in a text file and deleted the Innodb .idb file.
It is not sufficient to delete the database, you also need to delete the file.

When you import the data from the text file, there will be a file per table. With this configuration it will be easier to manage the disk space used.