0

Hello SonarQube community. I am running SonarQube 4.5.7 with MySql 5.5 For the past couple of years the database has been quite manageable in size and it currently has 381 projects in it. Monthly I delete any projects that have not been analyzed in the past three months. In the last two months though the database appears to have grown very large. The mysql ibdata1 file is now at 53294923776 characters in size. Of that 50+ gigs of size the project_measures table take up over 40 giga bytes and has 233 million rows in it. Is this normal? If so then I may need to add more space to the file system that it is on. If this is not normal for this table then what can I do to reduce the size of the table while not loosing data that would be pertinent to the projects that are currently being worked on?

Thanks for any help you may be able to provide on this issue. Doug

G. Ann - SonarSource Team
  • 22,346
  • 4
  • 40
  • 76
Doug Beattie
  • 41
  • 6

2 Answers2

0

You should contact your DBA and ask him to reorganize this table and compact the wasted space (don't forget to rebuild the indexes). Eric

Eric D. - SonarSource Team
  • 31
  • 1
  • I have spoken with our DBAs who are certified for Oracle and do a lot with Postgres as well. They are not sure what can/should be done on this table in a MySql DB. How would they go about reorganizing and compacting the wasted space in the project_measures table without loosing pertinent data? – Doug Beattie Aug 16 '16 at 14:07
  • id,value,metric_id,snapshot_id,rule_id,rules_category_id,text_value,tendency,measure_date,project_id,alert_status,alert_text,url,description,rule_priority,characteristic_id,variation_value_1,variation_value_2,variation_value_3,variation_value_4,variation_value_5,person_id,measure_data 1295192,342.00000000000000000000,4,46356,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL 1295193,888.00000000000000000000,2,46356,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL – Doug Beattie Aug 16 '16 at 14:18
0

If your database is set with the feature innodb_file_per_table , you can do it easily. If it is not the case, you have to consider to rebuild the whole database (backup/restore).It's not possible to loose data if the procedure is done carefully.

Eric D. - SonarSource Team
  • 31
  • 1
  • Thanks Eric. I'll take this information to the DBAs. BTW - They are not the ones that set up nor maintain this DB. I was the one to install SonarQube and set it up to use MySql. The DB was created using SonarQube. I'll see if they can help me out. – Doug Beattie Aug 16 '16 at 14:48
  • Eric and or others. One of the DBAs reviewed these thoughts and said the DB was not created with innodb_file_per_table configured. When a mysqldump is done its size is 46079299653 bytes. They do not see how a restore would result in a smaller size after indexes are built. Executing the statement "SELECT COUNT(*) FROM sonar.project_measures;" it returns 47879754. Does this look reasonable where we have 381 projects being analyzed? – Doug Beattie Aug 16 '16 at 15:42
  • When I use the GUI delete function to clean up stale projects do it clean up all tables or may some information still be in the project_measures table? We are not sure if this can be cleaned up. Sorry to keep asking but I am still wondering how to get the DB smaller. – Doug Beattie Aug 16 '16 at 15:42