FreePBX | Register | Issues | Wiki | Portal | Support

InnoDB file ibdata1 growing too large


(Jared Busch) #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.


(Rob Thomas) #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!


(Jimbronson) #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.


(Jared Busch) #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’


(Jared Busch) #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:


(Jared Busch) #9

Yeah, I know.


(Jared Busch) #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.


(Jared Busch) #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.

{
    "psi.@attributes.xmlns:tns": "http://phpsysinfo.sourceforge.net/",
    "psi.@attributes.xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
    "psi.@attributes.xsi:schemaLocation": "http://phpsysinfo.sourceforge.net/ phpsysinfo3.xsd",
    "psi.Generation.@attributes.version": "3.1.11",
    "psi.Options.@attributes.tempFormat": "c",
    "psi.Options.@attributes.byteFormat": "auto_binary",
    "psi.Options.@attributes.showPickListTemplate": "true",
    "psi.Options.@attributes.showPickListLang": "true",
    "psi.UsedPlugins.Plugin.0.@attributes.name": "mdstatus",
    "psi.UsedPlugins.Plugin.1.@attributes.name": "smart",
    "psi.UsedPlugins.Plugin.2.@attributes.name": "ipmiinfo",
    "psi.Vitals.@attributes.Hostname": "pbx.domain.local",
    "psi.Vitals.@attributes.IPAddr": "127.0.0.1",
    "psi.Vitals.@attributes.Kernel": "3.10.0-862.9.1.el7.x86_64 (SMP) x86_64",
    "psi.Vitals.@attributes.Distro": "Sangoma Linux release 7.5.1805 (Core)",
    "psi.Vitals.@attributes.Distroicon": "RedHat.png",
    "psi.Vitals.@attributes.LoadAvg": "0.00 0.01 0.05",
    "psi.Vitals.@attributes.SysLang": "English United States (en_US)",
    "psi.Vitals.@attributes.CodePage": "UTF-8",
    "psi.Vitals.@attributes.OS": "Linux",
    "psi.Network.NetDevice.0.@attributes.Name": "eth0",
    "psi.Network.NetDevice.0.@attributes.Info": "10.1.1.30;00-15-5d-c6-46-01",
    "psi.Network.NetDevice.1.@attributes.Name": "lo",
    "psi.Network.NetDevice.1.@attributes.Info": "127.0.0.1;::1",
    "psi.Hardware.SCSI.Device.0.@attributes.Name": "Msft     Virtual Disk     (Direct-Access)",
    "psi.Hardware.SCSI.Device.1.@attributes.Name": "Msft     Virtual CD/ROM   (CD-ROM)",
    "psi.Hardware.CPU.CpuCore.0.@attributes.Model": "Intel(R) Xeon(R) CPU E5-2637 v2 @ 3.50GHz",
    "psi.Hardware.CPU.CpuCore.0.@attributes.Virt": "hypervisor",
    "psi.Hardware.CPU.CpuCore.1.@attributes.Model": "Intel(R) Xeon(R) CPU E5-2637 v2 @ 3.50GHz",
    "psi.Hardware.CPU.CpuCore.1.@attributes.Virt": "hypervisor",
    "psi.Memory.Swap.Mount.@attributes.FSType": "",
    "psi.Memory.Swap.Mount.@attributes.Name": "SWAP",
    "psi.Memory.Swap.Mount.@attributes.MountPoint": "/dev/sda3",
    "psi.FileSystem.Mount.0.@attributes.FSType": "ext4",
    "psi.FileSystem.Mount.0.@attributes.Name": "/dev/sda2",
    "psi.FileSystem.Mount.0.@attributes.MountOptions": "rw, relatime, data=ordered",
    "psi.FileSystem.Mount.0.@attributes.MountPoint": "/",
    "psi.FileSystem.Mount.1.@attributes.FSType": "devtmpfs",
    "psi.FileSystem.Mount.1.@attributes.Name": "devtmpfs",
    "psi.FileSystem.Mount.1.@attributes.MountOptions": "rw, nosuid, size=929308k, nr_inodes=232327, mode=755",
    "psi.FileSystem.Mount.1.@attributes.MountPoint": "/dev",
    "psi.FileSystem.Mount.2.@attributes.FSType": "tmpfs",
    "psi.FileSystem.Mount.2.@attributes.Name": "tmpfs",
    "psi.FileSystem.Mount.2.@attributes.MountOptions": "rw",
    "psi.FileSystem.Mount.2.@attributes.MountPoint": "/dev/shm",
    "psi.FileSystem.Mount.3.@attributes.FSType": "tmpfs",
    "psi.FileSystem.Mount.3.@attributes.Name": "tmpfs",
    "psi.FileSystem.Mount.3.@attributes.MountOptions": "rw, nosuid, nodev, mode=755",
    "psi.FileSystem.Mount.3.@attributes.MountPoint": "/run",
    "psi.FileSystem.Mount.4.@attributes.FSType": "tmpfs",
    "psi.FileSystem.Mount.4.@attributes.Name": "tmpfs",
    "psi.FileSystem.Mount.4.@attributes.MountOptions": "ro, nosuid, nodev, noexec, mode=755",
    "psi.FileSystem.Mount.4.@attributes.MountPoint": "/sys/fs/cgroup",
    "psi.FileSystem.Mount.5.@attributes.FSType": "ext4",
    "psi.FileSystem.Mount.5.@attributes.Name": "/dev/sda1",
    "psi.FileSystem.Mount.5.@attributes.MountOptions": "rw, relatime, data=ordered",
    "psi.FileSystem.Mount.5.@attributes.MountPoint": "/boot",
    "psi.Errors.Error.0.@attributes.Function": "errorHandlerPsi :",
    "psi.Errors.Error.1.@attributes.Function": "errorHandlerPsi :",
    "psi.Errors.Error.2.@attributes.Function": "errorHandlerPsi :",
    "psi.Errors.Error.3.@attributes.Function": "errorHandlerPsi :",
    "psi.Errors.Error.4.@attributes.Function": "errorHandlerPsi :",
    "psi.Errors.Error.5.@attributes.Function": "errorHandlerPsi :",
    "psi.Errors.Error.6.@attributes.Function": "errorHandlerPsi :",
    "psi.Errors.Error.7.@attributes.Function": "errorHandlerPsi :",
    "psi.Errors.Error.8.@attributes.Function": "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, ",
    "psi.Generation.@attributes.timestamp": 1542412746,
    "psi.Options.@attributes.refresh": 60000,
    "psi.Options.@attributes.threshold": 90,
    "psi.Vitals.@attributes.Uptime": 1808080.25,
    "psi.Vitals.@attributes.Users": 0,
    "psi.Network.NetDevice.0.@attributes.RxBytes": 12882441851,
    "psi.Network.NetDevice.0.@attributes.TxBytes": 13311868711,
    "psi.Network.NetDevice.0.@attributes.Err": 0,
    "psi.Network.NetDevice.0.@attributes.Drops": 8176,
    "psi.Network.NetDevice.1.@attributes.RxBytes": 11707315859,
    "psi.Network.NetDevice.1.@attributes.TxBytes": 11707315859,
    "psi.Network.NetDevice.1.@attributes.Err": 0,
    "psi.Network.NetDevice.1.@attributes.Drops": 0,
    "psi.Hardware.SCSI.Device.0.@attributes.Count": 1,
    "psi.Hardware.SCSI.Device.1.@attributes.Count": 1,
    "psi.Hardware.CPU.CpuCore.0.@attributes.CpuSpeed": 3499.999,
    "psi.Hardware.CPU.CpuCore.0.@attributes.Cache": 15728640,
    "psi.Hardware.CPU.CpuCore.0.@attributes.Bogomips": 6999.99,
    "psi.Hardware.CPU.CpuCore.1.@attributes.CpuSpeed": 3499.999,
    "psi.Hardware.CPU.CpuCore.1.@attributes.Cache": 15728640,
    "psi.Hardware.CPU.CpuCore.1.@attributes.Bogomips": 6999.99,
    "psi.Memory.@attributes.Free": 232873984,
    "psi.Memory.@attributes.Used": 1686437888,
    "psi.Memory.@attributes.Total": 1919311872,
    "psi.Memory.@attributes.Percent": 88,
    "psi.Memory.Details.@attributes.App": 1120436224,
    "psi.Memory.Details.@attributes.AppPercent": 59,
    "psi.Memory.Details.@attributes.Buffers": 116359168,
    "psi.Memory.Details.@attributes.BuffersPercent": 7,
    "psi.Memory.Details.@attributes.Cached": 449642496,
    "psi.Memory.Details.@attributes.CachedPercent": 24,
    "psi.Memory.Swap.@attributes.Free": 434630656,
    "psi.Memory.Swap.@attributes.Used": 370671616,
    "psi.Memory.Swap.@attributes.Total": 805302272,
    "psi.Memory.Swap.@attributes.Percent": 47,
    "psi.Memory.Swap.Mount.@attributes.MountPointID": 1,
    "psi.Memory.Swap.Mount.@attributes.Free": 434630656,
    "psi.Memory.Swap.Mount.@attributes.Used": 370671616,
    "psi.Memory.Swap.Mount.@attributes.Total": 805302272,
    "psi.Memory.Swap.Mount.@attributes.Percent": 47,
    "psi.FileSystem.Mount.0.@attributes.MountPointID": 1,
    "psi.FileSystem.Mount.0.@attributes.Free": 6425698304,
    "psi.FileSystem.Mount.0.@attributes.Used": 22471495680,
    "psi.FileSystem.Mount.0.@attributes.Total": 30468534272,
    "psi.FileSystem.Mount.0.@attributes.Percent": 74,
    "psi.FileSystem.Mount.0.@attributes.Inodes": 15,
    "psi.FileSystem.Mount.1.@attributes.MountPointID": 2,
    "psi.FileSystem.Mount.1.@attributes.Free": 951611392,
    "psi.FileSystem.Mount.1.@attributes.Used": 0,
    "psi.FileSystem.Mount.1.@attributes.Total": 951611392,
    "psi.FileSystem.Mount.1.@attributes.Percent": 0,
    "psi.FileSystem.Mount.1.@attributes.Inodes": 1,
    "psi.FileSystem.Mount.2.@attributes.MountPointID": 3,
    "psi.FileSystem.Mount.2.@attributes.Free": 959651840,
    "psi.FileSystem.Mount.2.@attributes.Used": 4096,
    "psi.FileSystem.Mount.2.@attributes.Total": 959655936,
    "psi.FileSystem.Mount.2.@attributes.Percent": 1,
    "psi.FileSystem.Mount.2.@attributes.Inodes": 1,
    "psi.FileSystem.Mount.3.@attributes.MountPointID": 4,
    "psi.FileSystem.Mount.3.@attributes.Free": 858320896,
    "psi.FileSystem.Mount.3.@attributes.Used": 101335040,
    "psi.FileSystem.Mount.3.@attributes.Total": 959655936,
    "psi.FileSystem.Mount.3.@attributes.Percent": 11,
    "psi.FileSystem.Mount.3.@attributes.Inodes": 1,
    "psi.FileSystem.Mount.4.@attributes.MountPointID": 5,
    "psi.FileSystem.Mount.4.@attributes.Free": 959655936,
    "psi.FileSystem.Mount.4.@attributes.Used": 0,
    "psi.FileSystem.Mount.4.@attributes.Total": 959655936,
    "psi.FileSystem.Mount.4.@attributes.Percent": 0,
    "psi.FileSystem.Mount.4.@attributes.Inodes": 1,
    "psi.FileSystem.Mount.5.@attributes.MountPointID": 6,
    "psi.FileSystem.Mount.5.@attributes.Free": 110603264,
    "psi.FileSystem.Mount.5.@attributes.Used": 165709824,
    "psi.FileSystem.Mount.5.@attributes.Total": 296236032,
    "psi.FileSystem.Mount.5.@attributes.Percent": 56,
    "psi.FileSystem.Mount.5.@attributes.Inodes": 1,
    "timestamp": 1542412746,
    "psi.Vitals.@attributes.LoadAvg.five": 0,
    "psi.Vitals.@attributes.LoadAvg.ten": 0.01,
    "psi.Vitals.@attributes.LoadAvg.fifteen": 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
}

(Andrew Nagy) #12

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


(Jared Busch) #13

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

I will check a couple systems more later.


(Andrew Nagy) #14

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