InnoDB file ibdata1 growing too large

General Help
1

Starting to get space alerts from one system.
When checking into disk usage, the culprit is

[jbusch@pbx ~]$ sudo ls -la /var/lib/mysql/ibdata1 
-rw-rw----. 1 mysql mysql 13054771200 Aug 11 14:48 /var/lib/mysql/ibdata1

An hour later

[jbusch@pbx ~]$ sudo ls -la /var/lib/mysql/ibdata1 
-rw-rw----. 1 mysql mysql 13063159808 Aug 11 15:53 /var/lib/mysql/ibdata1

That is 13 GB in case you did not bother to count.

This PBX only has 29 extensions, and 6700 trunk calls last month.

Just don’t get what is going on here.

2

This is probably CDRs. The ‘ibdata1’ file contains all the data from ALL databases, so you want to be super careful with it. IT also never shrinks in size (well… Almost never. Just assume ‘never’).

There are also various ways to see which tables are taking up a lot of space, and google will be your helper there!

3

Hi xrobau
can I ask what is ment by google will be your helper there??? I assume the search engine but am not sure. Also 13 Gigs per hour is pretty serious.

4

It’s not 13G per hour it’s a total of 13G since you created the innodb databases, you will need to CAREFULLY dump BOTH your asterisk and asteriskcdrdb databases somewhere safe, stop mysql, delete the ibdata1 file, restart mysql and restore the carefully backup up data, that will recreate a new ibdata1 file (that will continue to grow)

To do that carefully, google will return any number of cautions and procedures.

1 Like
5

I’ve been watching this to see what will happen as I am really not liking the clean up methods found.

[jbusch@pbx ~]$ sudo ls -la /var/lib/mysql/ibdata1 
-rw-rw----. 1 mysql mysql 13063159808 Aug 29 12:45 /var/lib/mysql/ibdata1

It has not grown since. But of course this is causing me to get an alert every hour that the PBX is using 85% of the disk.

Alert fatigue officially set in.

6

A full breakdown and various scripts to accomplish it

you need to backup, drop and restore ‘asterisk’ and ‘asteriskcdrdb’

1 Like
7

That is actually the link I have bookmarked if I choose to do this.

8

It’s a catch22, if you don’t do it, you will have to put up with “Alert fatigue” :wink:

9

Yeah, I know.

10

So I did the above a while back and all has been good until a coupel days ago.

Alerts again.

Ok, let’s look at what I have…

Does anyone know what kvblobstore is all about? Most of these files I understand what they relate to.

image
image875×194 22.9 KB

11

The table itself contains json arrays of system status data it looks like.

MariaDB [asterisk]> describe kvblobstore;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| uuid    | char(36) | NO   | PRI | NULL    |       |
| type    | char(32) | YES  |     | NULL    |       |
| content | longblob | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.02 sec)

MariaDB [asterisk]> select count(uuid) from kvblobstore;
+-------------+
| count(uuid) |
+-------------+
|         294 |
+-------------+
1 row in set (2 min 40.39 sec)

MariaDB [asterisk]> select * from kvblobstore limit 1;
+--------------------------------------+----------+-----------
| uuid                                 | type     | content   
+--------------------------------------+----------+-----------
| 00caab1b-71e9-4748-9485-c6bc7b5f3989 | json-arr | {"psi.@att
+--------------------------------------+----------+-----------
1 row in set (0.00 sec)

I snipped the long json and prettified it with VSCode.

{
    "[email protected]:tns": "http://phpsysinfo.sourceforge.net/",
    "[email protected]:xsi": "http://www.w3.org/2001/XMLSchema-instance",
    "[email protected]:schemaLocation": "http://phpsysinfo.sourceforge.net/ phpsysinfo3.xsd",
    "[email protected]": "3.1.11",
    "[email protected]": "c",
    "[email protected]": "auto_binary",
    "[email protected]": "true",
    "[email protected]": "true",
    "[email protected]": "mdstatus",
    "[email protected]": "smart",
    "[email protected]": "ipmiinfo",
    "[email protected]": "pbx.domain.local",
    "[email protected]": "127.0.0.1",
    "[email protected]": "3.10.0-862.9.1.el7.x86_64 (SMP) x86_64",
    "[email protected]": "Sangoma Linux release 7.5.1805 (Core)",
    "[email protected]": "RedHat.png",
    "[email protected]": "0.00 0.01 0.05",
    "[email protected]": "English United States (en_US)",
    "[email protected]": "UTF-8",
    "[email protected]": "Linux",
    "[email protected]": "eth0",
    "[email protected]": "10.1.1.30;00-15-5d-c6-46-01",
    "[email protected]": "lo",
    "[email protected]": "127.0.0.1;::1",
    "[email protected]": "Msft     Virtual Disk     (Direct-Access)",
    "[email protected]": "Msft     Virtual CD/ROM   (CD-ROM)",
    "[email protected]": "Intel(R) Xeon(R) CPU E5-2637 v2 @ 3.50GHz",
    "[email protected]": "hypervisor",
    "[email protected]": "Intel(R) Xeon(R) CPU E5-2637 v2 @ 3.50GHz",
    "[email protected]": "hypervisor",
    "[email protected]": "",
    "[email protected]": "SWAP",
    "[email protected]": "/dev/sda3",
    "[email protected]": "ext4",
    "[email protected]": "/dev/sda2",
    "[email protected]": "rw, relatime, data=ordered",
    "[email protected]": "/",
    "[email protected]": "devtmpfs",
    "[email protected]": "devtmpfs",
    "[email protected]": "rw, nosuid, size=929308k, nr_inodes=232327, mode=755",
    "[email protected]": "/dev",
    "[email protected]": "tmpfs",
    "[email protected]": "tmpfs",
    "[email protected]": "rw",
    "[email protected]": "/dev/shm",
    "[email protected]": "tmpfs",
    "[email protected]": "tmpfs",
    "[email protected]": "rw, nosuid, nodev, mode=755",
    "[email protected]": "/run",
    "[email protected]": "tmpfs",
    "[email protected]": "tmpfs",
    "[email protected]": "ro, nosuid, nodev, noexec, mode=755",
    "[email protected]": "/sys/fs/cgroup",
    "[email protected]": "ext4",
    "[email protected]": "/dev/sda1",
    "[email protected]": "rw, relatime, data=ordered",
    "[email protected]": "/boot",
    "[email protected]": "errorHandlerPsi :",
    "[email protected]": "errorHandlerPsi :",
    "[email protected]": "errorHandlerPsi :",
    "[email protected]": "errorHandlerPsi :",
    "[email protected]": "errorHandlerPsi :",
    "[email protected]": "errorHandlerPsi :",
    "[email protected]": "errorHandlerPsi :",
    "[email protected]": "errorHandlerPsi :",
    "[email protected]": "find_program(ipmitool)",
    "ast.uptime.system": "2 weeks, 6 days, 22 hours, 13 minutes, 45 seconds, ",
    "ast.uptime.reload": "2 weeks, 1 day, 9 hours, 30 minutes, 46 seconds, ",
    "[email protected]": 1542412746,
    "[email protected]": 60000,
    "[email protected]": 90,
    "[email protected]": 1808080.25,
    "[email protected]": 0,
    "[email protected]": 12882441851,
    "[email protected]": 13311868711,
    "[email protected]": 0,
    "[email protected]": 8176,
    "[email protected]": 11707315859,
    "[email protected]": 11707315859,
    "[email protected]": 0,
    "[email protected]": 0,
    "[email protected]": 1,
    "[email protected]": 1,
    "[email protected]": 3499.999,
    "[email protected]": 15728640,
    "[email protected]": 6999.99,
    "[email protected]": 3499.999,
    "[email protected]": 15728640,
    "[email protected]": 6999.99,
    "[email protected]": 232873984,
    "[email protected]": 1686437888,
    "[email protected]": 1919311872,
    "[email protected]": 88,
    "[email protected]": 1120436224,
    "[email protected]": 59,
    "[email protected]": 116359168,
    "[email protected]": 7,
    "[email protected]": 449642496,
    "[email protected]": 24,
    "[email protected]": 434630656,
    "[email protected]": 370671616,
    "[email protected]": 805302272,
    "[email protected]": 47,
    "[email protected]": 1,
    "[email protected]": 434630656,
    "[email protected]": 370671616,
    "[email protected]": 805302272,
    "[email protected]": 47,
    "[email protected]": 1,
    "[email protected]": 6425698304,
    "[email protected]": 22471495680,
    "[email protected]": 30468534272,
    "[email protected]": 74,
    "[email protected]": 15,
    "[email protected]": 2,
    "[email protected]": 951611392,
    "[email protected]": 0,
    "[email protected]": 951611392,
    "[email protected]": 0,
    "[email protected]": 1,
    "[email protected]": 3,
    "[email protected]": 959651840,
    "[email protected]": 4096,
    "[email protected]": 959655936,
    "[email protected]": 1,
    "[email protected]": 1,
    "[email protected]": 4,
    "[email protected]": 858320896,
    "[email protected]": 101335040,
    "[email protected]": 959655936,
    "[email protected]": 11,
    "[email protected]": 1,
    "[email protected]": 5,
    "[email protected]": 959655936,
    "[email protected]": 0,
    "[email protected]": 959655936,
    "[email protected]": 0,
    "[email protected]": 1,
    "[email protected]": 6,
    "[email protected]": 110603264,
    "[email protected]": 165709824,
    "[email protected]": 296236032,
    "[email protected]": 56,
    "[email protected]": 1,
    "timestamp": 1542412746,
    "[email protected]": 0,
    "[email protected]": 0.01,
    "[email protected]": 0.05,
    "ast.chan_totals.external_calls": 0,
    "ast.chan_totals.internal_calls": 0,
    "ast.chan_totals.total_calls": 0,
    "ast.chan_totals.total_channels": 0,
    "ast.connections.sip_users_online": 0,
    "ast.connections.sip_users_offline": 0,
    "ast.connections.sip_users_total": 0,
    "ast.connections.sip_trunks_online": 0,
    "ast.connections.sip_trunks_offline": 0,
    "ast.connections.sip_trunks_total": 0,
    "ast.connections.sip_registrations_online": 0,
    "ast.connections.sip_registrations_offline": 0,
    "ast.connections.sip_registrations_total": 0,
    "ast.connections.iax2_users_online": 0,
    "ast.connections.iax2_users_offline": 0,
    "ast.connections.iax2_users_total": 0,
    "ast.connections.iax2_trunks_online": 1,
    "ast.connections.iax2_trunks_offline": 0,
    "ast.connections.iax2_trunks_total": 1,
    "ast.connections.iax2_registrations_online": 0,
    "ast.connections.iax2_registrations_offline": 0,
    "ast.connections.iax2_registrations_total": 0,
    "ast.connections.pjsip_users_online": 28,
    "ast.connections.pjsip_users_offline": 1,
    "ast.connections.pjsip_users_total": 29,
    "ast.connections.pjsip_trunks_online": 2,
    "ast.connections.pjsip_trunks_offline": 0,
    "ast.connections.pjsip_trunks_total": 2,
    "ast.connections.pjsip_registrations_online": 28,
    "ast.connections.pjsip_registrations_offline": 0,
    "ast.connections.pjsip_registrations_total": 28,
    "ast.connections.users_online": 28,
    "ast.connections.users_offline": 1,
    "ast.connections.users_total": 29,
    "ast.connections.trunks_online": 3,
    "ast.connections.trunks_offline": 0,
    "ast.connections.trunks_total": 3,
    "ast.connections.registrations_online": 28,
    "ast.connections.registrations_offline": 0,
    "ast.connections.registrations_total": 28,
    "ast.uptime.system-seconds": 1808025,
    "ast.uptime.reload-seconds": 1330246,
    "generationlength": 0.20871901512146
}
12

This is pretty normal. It’s what provides stats to dashboard.

13

A second system I just logged into only has 3 records in that table.

I will check a couple systems more later.

14

This is also normal. Both are normal. Depending how long the system has been up and how active it is

15

we fixed this on 2 systems. here are the notes.
innodb-huge-solution.tgz (1.1 KB)

16

really? notes in a tar file?

I took a moment to open that in my disposable VM for the rest of us.

* phonesys mysql issue . 

 - make snapshot in proxmox [ backup up the system ].


 getting alerts of low disk space. <<< change made see below <<
   ** keep track of changes, perhaps file maxed out?
   $ sudo ssh sip  "ls -l /var/lib/mysql/ibdata1"
  -rw-rw----. 1 mysql mysql 6520045568 Jul 29 15:01 /var/lib/mysql/ibdata1
  -rw-rw----. 1 mysql mysql 6528434176 Jul 29 17:14 /var/lib/mysql/ibdata1
  -rw-rw----. 1 mysql mysql 6528434176 Jul 30 09:04 /var/lib/mysql/ibdata1
       
https://vdachev.net/2007/02/22/mysql-reducing-ibdata1/   at Dump the Whole Database


mysqldump --extended-insert --all-databases --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers > '/tmp/all-databases.sql'


# trying to elim 'Firewall was unable to connect to MySQL after 30 seconds.'
systemctl stop crond
systemctl stop sangoma-pnpd.service
systemctl stop incrond.service

systemctl stop mariadb.service

# Note we kept phone system on duiring the long process.  prob only affected logging.

#kill 
 # ps -ef | grep firew | grep -v grep
root      8039     1  0 Jul20 ?        00:08:30 php /var/www/html/admin/modules/firewall/hooks/voipfirewalld
root      8580  8039  0 Jul20 ?        00:50:16 voipfirewalld (Monitor thread)

 
#-----------------------------------------------------------------------------

nano /etc/my.cnf  
# set these:
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=250M
innodb_buffer_pool_size=1G
 
#-----------------------------------------------------------------------------

mv  /var/lib/mysql /var/lib/mysql.ori

# May need to rm -fr /var/lib/mysql.ori if space low.   check with df 

mkdir /var/lib/mysql
chown mysql:mysql /var/lib/mysql

sudo -u mysql mysql_install_db

systemctl start  mariadb.service

mysql
SET FOREIGN_KEY_CHECKS=0;
SOURCE /tmp/all-databases.sql;
SET FOREIGN_KEY_CHECKS=1;

systemctl restart mariadb.service

systemctl start crond
systemctl start sangoma-pnpd.service
systemctl start incrond.service

I rebooted to make sure firewall was back on. TBD how to start it.


rm -f /tmp/all-databases.sql
1 Like
17

For the record, I decided it was an insane amount of storage for “dashboard stats” and simply dropped and recreated the kvblobstore table.

I used mysqldump to create a backup.sql and then took the table creation part of kvblobstore out of it and left it as a .sql file to run.

mysql -uroot asterisk < ~/kvblobstore.sql

Save the below as kvblobstore.sql

-- MySQL dump 10.14  Distrib 5.5.56-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: asterisk
-- ------------------------------------------------------
-- Server version	5.5.56-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `kvblobstore`
--

DROP TABLE IF EXISTS `kvblobstore`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `kvblobstore` (
  `uuid` char(36) COLLATE utf8_unicode_ci NOT NULL,
  `type` char(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` longblob,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `kvblobstore`
--

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-12-04 17:28:27
18

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.