0

I have been running Zenoss 4.2 and the filesystem has never exceeded 20gb. In the past 3 days it has shot up to 90gb and I am almost out of disk space. It appears the used space is all in /var/lib/mysql/ibdata1. First, how can I tell what is causing the system to grow so quickly, and how can I cleanup the space?

Mike C
  • 3
  • 1

2 Answers2

0

1.) Identify the largest tables

Find out the largest tables on your DB MySQL Server - run this query as DB "root":

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

2.) Find the reason/solution of the largest tables

It depends on the largest table. Probably the largest table will be zep_events*. In this case you should to tune your Zenoss events settings.

Note 1: You have a size problem with the InnoDB tables, so it wont be easy to release InnoDB space. You can delete rows from the table, but space won't be released. See Deleting & Reclaiming space from InnoDB table

Note 2: I recommend you to use http://www.zenoss.org/forum or IRC #zenoss for Zenoss specific questions

Community
  • 1
Jan Garaj
  • 136
  • 3
  • Looking at my top tables, the biggest is 26.5GB, the next is 1.7GB, and only a handful are under 1GB, the rest are 0. My ibdata1 file is is 48GB, where would the rest of the space be? – Mike C Dec 15 '14 at 13:41
  • Your ibdata1 file contains more than data/indexes. There are also pages that contain undo segments for example. It's helpful to understand that MySQL will not shrink the data files when data is removed. It becomes space within the file and can be reused by innodb until the next time innodb needs to grow the table space. – eroomydna Dec 15 '14 at 22:56
0

One method to address this issue is to use a logical backup tool, such as mysqldump to backup the database.

Refer to your my.cnf (usually under /etc/my.cnf) and then ensure you have the setting

innodb_file_per_table = 1

This will ensure that MySQL will use a file per table to store data and therefore easier to recover space moving forward.

Drop all the data and restore logically from the backup file. This will populate each table per file and future tables are going to be created in their own file.

eroomydna
  • 1,031
  • 5
  • 5