Ibdata1 file size

[edlentz]

We have been having a few hosted systems with this file growing, so much it fills the hard drive. I have read here : InnoDB and MySQL ibdata1: Things You Need to Know. That the file size can be limited in the my.cnf. To the more seasoned techs here, has anyone dealt with limiting this file size? One customer , this file is over 2G Seems excessive. Anyway what is to be done? I went into the system and removes the Asteriskdb and asteriskcdrdb and reloaded a backup and restarted Maria. The file beforehand was almost 10G and it dropped to just over 2GB

[dicko]

You can to export all your tables, remove them all then re-import the data, you need to do this while any mysql users (asterisk) are stopped.

[david55]

I would assume that limiting the filesize would cause everything to break if the limit was reached. I any case it is a matter for the database manager.

[jfinstrom (James Finstrom)]

If you are experiencing issues with the size of the InnoDB data file (ibdata1) in MySQL/MariaDB, there are a few approaches you can take to limit its size. Here are some suggestions:

1. Configure the innodb_file_per_table option: By default, InnoDB stores all tables’ data in the shared ibdata1 file. Enabling the innodb_file_per_table option causes InnoDB to create separate data files for each table. This can help prevent the ibdata1 file from growing excessively.

   To enable innodb_file_per_table, you can add the following line to your my.cnf or my.ini file under the [mysqld] section:

   innodb_file_per_table=1
   

   After making this change, you may need to restart the MariaDB service.

   Note that enabling this option will only affect newly created tables. Existing tables will still be stored in the shared ibdata1 file unless you manually rebuild them.

2. Adjust the innodb_data_file_path option: This option specifies the size and number of InnoDB data files. By configuring this option, you can limit the size of the ibdata1 file.

   For example, you can set a maximum size for the ibdata1 file by adding the following line to your my.cnf or my.ini file:

   innodb_data_file_path = ibdata1:10M:autoextend:max:2G
   

   This configures InnoDB to create a single ibdata1 file with an initial size of 10MB that can auto-extend up to a maximum size of 2GB.

   Adjust the values according to your requirements. After modifying the configuration, restart the MariaDB service for the changes to take effect.

3. Periodically optimize and rebuild tables: Over time, the ibdata1 file can become fragmented, leading to increased file size. You can optimize and rebuild your tables to reduce the size of the file. One way to achieve this is by using the OPTIMIZE TABLE statement or tools like pt-online-schema-change or mysqldump.

4. Regularly purge unnecessary data: If your databases contain unnecessary or outdated data, removing it can help reduce the size of the ibdata1 file. Identify and remove any unused or irrelevant data to reclaim disk space.

Remember to take proper backups of your databases before making any significant changes to the configuration or performing maintenance operations.

When the InnoDB data file (ibdata1) reaches its maximum size, which in the above case is set to 2GB, the behavior depends on the configuration of your MariaDB server. Here are a few scenarios that may occur:

1. Data insertion or updates may fail: If the ibdata1 file reaches its maximum size and there is no more space available for new data or updates, MariaDB will be unable to write any further data. Any attempts to insert or update records may result in errors indicating that the disk is full or that there is no space left.

2. Autoextend fails: By default, InnoDB’s ibdata1 file is set to autoextend, meaning it can grow in size as needed. However, if the maximum size limit of 2GB is reached and the file cannot be extended due to disk space limitations or other factors, the autoextend process will fail.

3. System instability or crashes: If the ibdata1 file reaches its maximum size and MariaDB is unable to write or extend the file, it can lead to system instability or crashes. In extreme cases, the database server may become unresponsive or fail altogether.

To avoid these issues, it is important to monitor the size of the ibdata1 file regularly and ensure that it has sufficient space to accommodate your database growth. By implementing the suggestions mentioned earlier to limit the file size or optimize your database, you can prevent the ibdata1 file from reaching its maximum capacity and causing disruptions in your system.

[billsimon]

Thanks JamesGPTbot!

[edlentz]

Thanks James and everyone else for the comments. I might just leave this location to fend for itself and check in periodically to see if that file is growing. Just for grins here is what I ended up doing:

Remove call records older than 12 months from the cli and optimize the tables.

mysql
use asteriskcdrdb;
delete from cdr where calldate < DATE_SUB(NOW(), INTERVAL 12 MONTH);
delete from cel where eventtime < DATE_SUB(NOW(), INTERVAL 12 MONTH);
optimize table cdr;
optimize table cel;
exit;

Copy the pertinent DBs from MySql from the CLI
cd /var/lib/mysql
mysql;
mysqldump asteriskcdrdb > asteriskcdrdb.sql;
mysqldump asterisk > asterisk.sql;
then log into myqsl and drop the databases

mysql
DROP DATABASE asterisk;
DROP DATABASE asteriskcdrdb;
exit

Then stop mysql, the command is service mariadb stop

Delete the ibdata1 and the two log files from /var/lib/mysql

Restart mariadb using service mariadb restart , this may take awhile.

Create the databases you removed
mysql
create database asteriskcdrdb;
create database asterisk;
exit

Then in the CLI (not logged into mysql) load the tables you backed up
mysql asteriskcdrdb < asteriskcdrdb.sql
mysql asterisk < asterisk.sql

check to see if the tables are restored
mysql
use asterisk;
show full tables;
use asteriskcdrdb;
show full tables;

Check to see if the phones reconnected and the system appears OK
Check the ibdata1 files in /var/lib/mysql There should be two log files and a ibdata1 file.