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
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.
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.
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:
Configure the innodb_file_per_table option: By default, InnoDB stores all tables’ data in the shared
ibdata1file. Enabling the
innodb_file_per_tableoption causes InnoDB to create separate data files for each table. This can help prevent the
ibdata1file from growing excessively.
innodb_file_per_table, you can add the following line to your
my.inifile under the
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
ibdata1file unless you manually rebuild them.
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
For example, you can set a maximum size for the
ibdata1file by adding the following line to your
innodb_data_file_path = ibdata1:10M:autoextend:max:2G
This configures InnoDB to create a single
ibdata1file 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.
Periodically optimize and rebuild tables: Over time, the
ibdata1file 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 TABLEstatement or tools like
Regularly purge unnecessary data: If your databases contain unnecessary or outdated data, removing it can help reduce the size of the
ibdata1file. 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:
Data insertion or updates may fail: If the
ibdata1file 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.
Autoextend fails: By default, InnoDB’s
ibdata1file 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.
System instability or crashes: If the
ibdata1file 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.
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.
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;
Copy the pertinent DBs from MySql from the CLI
mysqldump asteriskcdrdb > asteriskcdrdb.sql;
mysqldump asterisk > asterisk.sql;
then log into myqsl and drop the databases
DROP DATABASE asterisk;
DROP DATABASE asteriskcdrdb;
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
create database asteriskcdrdb;
create database asterisk;
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
show full tables;
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.
This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.