/var/lib/mysql/asteriskcdrdb/cel.ibd is huge [ solved ]

General Help
1

Hello - after receiving a report about low free storage space we noticed that

/var/lib/mysql/asteriskcdrdb uses 6.3G .

cel.ibd uses 6.2G

Is there a way to limit the space used , or prune the file?

Is it normal for the file to be so big?

PS: In freepbx today one of our users tried to use Call Event Logging . that screen got stuck or was just taking forever to display. so perpahs just running that report populate cel.ibd .
I tried to disable the cel module, however we use it in timeconditions…

2

i always looked at those as logs and as such implemented something akin to below via cron … the first dealing with cdr and the second targeting cel

#!/bin/bash

mysql -D asteriskcdrdb -e "DELETE FROM cdr WHERE calldate < DATE_SUB(NOW(), INTERVAL 365 DAY);"

mysql -D asteriskcdrdb -e "DELETE FROM cel WHERE eventtime < DATE_SUB(NOW(), INTERVAL 90 DAY);"

mysqlcheck --auto-repair --optimize --all-databases

im sure there are some other methods out there but for me cel seems especially chatty for my taste and i rarely have a need to reference it; i would rather have the storage for other things

4

Can you elaborate? I don’t think there’s such a thing.

Also, is the cel db the only large files that is taking up space?

5

at module admin , when i try to remove Call Event Logging this shows up:

Errors with selection:

  • Call Event Logging cannot be removed because the following modules depend on it:
    * sangomacrm
    • timeconditions
     Please disable those modules first then try again.

No actions to perform

Please select at least one action to perform by clicking on the module, and selecting an action on the “Action” tab.

6

I see that cel was referred to in my post… that probably caused the misunderstanding

7

cel is the only large file.

last week i handled /var/lib/mysql/ib* files and posted on forum how that was done.

8

i just noted the 2ND line dealing with cel.

thanks that should fix the issue!

9

trying to run the cel purge line results in an error. i think it is due to forum post formatting ?

mysql -D asteriskcdrdb -e "DELETE FROM cel WHERE eventtime < DATE_SUB(NOW(), INTERVAL 90 DAY);"**

ERROR 1054 (42S22) at line 1: Unknown column ‘lt’ in ‘where clause’

10 
cd /var
du -sh *

What’s the output?

11 
mysql asteriskcdrdb --execute="delete from asteriskcdrdb.cel where datediff(now(), eventtime) > 9" ;

seems to work.

12

du -sh *

4.0K adm
4.0K backups
289M cache
20K db
8.0K empty
8.0K ftp
4.0K games
4.0K gopher
12K kerberos
11G lib
4.0K local
0 lock
2.0G log
0 mail
4.0K nis
4.0K opt
4.0K preserve
0 run
5.7G spool
60K tmp
1.1G www
4.0K yp

13

cel.ibd now uses 212M . Thank you for pointing the direction to solve.

mysql asteriskcdrdb --execute="delete from asteriskcdrdb.cdr where datediff(now(), calldate) > 9" ;  # > /dev/null

mysql asteriskcdrdb --execute="delete from asteriskcdrdb.cel where datediff(now(), eventtime) > 9" ;

mysqlcheck --auto-repair --optimize --all-databases
14

You might also want to take a look at these.

15

lib was the sql

log is normally that for years.

16

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