Mysql DB huge (grew > 26GB)

Forum for questions and support relating to the 1.32.x releases only.
Forum rules
Post Reply
sroot
Posts: 2
Joined: Sat Nov 12, 2016 3:24 pm

Mysql DB huge (grew > 26GB)

Post by sroot »

Hi,
===EDIT===

Warning - read all posts and replies, I later discovered all events were deleted after truncating the database! bbunge suggests a command that may be a lot more effective!

===END EDIT===

Version= v1.32.3

tl;dr = reduced database size on disk using mysql + truncate table (Frames, Events, Logs). Also, truncate needs disk space (remove files or add disk so it can work)

I've solved but I'm posting details in case this affects other people, there are two many variables for me to think this is anything other than my installation (so, most likely this is not a bug).

Situation: ZM wasn't running, gave a database error
History: Long power cut took down all servers. I *think* it was running before but I can't tell for sure. my ZM is two virtual machines, one for Zoneminder (called myZM from here on) and another on a different server with big disks shared via NFS (called VideoTape). Events/recordings are stored on VideoTape but the mysql database is on myZM. The shutdown was not graceful!
There are 5 HD sources in total but only 4 work (1 has been down for months)

I realised myZM had a full disk. I had assumed that myZM started before VideoTape and started filling it's small disk with recordings.
What actually happened was the mysql database had grown to 26GB and filled the disk. Clearing apt cache gave me enough space to start a mysql client. Checking table status gave me this output

Code: Select all

+-----------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name            | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free   | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Config          | InnoDB |      10 | Dynamic    |      223 |            955 |      212992 |               0 |            0 |           0 |           NULL | 2019-04-30 06:07:57 | 2019-07-22 10:06:13 | NULL
  | latin1_swedish_ci |     NULL |                |         |
| ControlPresets  | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |            0 |           0 |           NULL | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Controls        | InnoDB |      10 | Dynamic    |       38 |            431 |       16384 |               0 |            0 |           0 |             39 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Devices         | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |            0 |           0 |              1 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Events          | InnoDB |      10 | Dynamic    |    15412 |            307 |     4734976 |               0 |      2621440 |     5242880 |          81770 | 2019-04-30 06:07:57 | 2019-07-22 10:41:19 | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Events_Archived | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |        16384 |           0 |           NULL | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Events_Day      | InnoDB |      10 | Dynamic    |      151 |            108 |       16384 |               0 |        32768 |           0 |           NULL | 2019-04-30 06:07:57 | 2019-07-22 10:40:42 | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Events_Hour     | InnoDB |      10 | Dynamic    |       28 |            585 |       16384 |               0 |        32768 |           0 |           NULL | 2019-04-30 06:07:57 | 2019-07-22 10:40:14 | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Events_Month    | InnoDB |      10 | Dynamic    |     1393 |            764 |     1064960 |               0 |       114688 |     5242880 |           NULL | 2019-04-30 06:07:57 | 2019-07-22 10:41:17 | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Events_Week     | InnoDB |      10 | Dynamic    |      484 |            101 |       49152 |               0 |        32768 |     4194304 |           NULL | 2019-04-30 06:07:57 | 2019-07-22 10:41:17 | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Filters         | InnoDB |      10 | Dynamic    |        2 |           8192 |       16384 |               0 |        16384 |           0 |              3 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Frames          | InnoDB |      10 | Dynamic    | 37106925 |             54 |  2011136000 |               0 |   2353987584 |    14680064 |      118864867 | 2019-04-30 06:07:57 | 2019-07-22 10:41:19 | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Groups          | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |            0 |           0 |              1 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Groups_Monitors | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |            0 |           0 |              1 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Logs            | InnoDB |      10 | Dynamic    |  5346345 |            164 |   880820224 |               0 |    331399168 | 12743344128 |           NULL | 2019-04-30 06:07:57 | 2019-07-22 10:41:20 | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Manufacturers   | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |        16384 |           0 |              1 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Maps            | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |            0 |           0 |              1 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Models          | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |        16384 |           0 |              1 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| MonitorPresets  | InnoDB |      10 | Dynamic    |       72 |            227 |       16384 |               0 |            0 |           0 |             73 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Monitor_Status  | MEMORY |      10 | Fixed      |        5 |             18 |      126984 |         6291450 |       126984 |          18 |           NULL | 2019-07-22 10:06:11 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Monitors        | InnoDB |      10 | Dynamic    |        5 |           3276 |       16384 |               0 |        16384 |           0 |              6 | 2019-04-30 06:07:57 | 2019-07-22 10:41:19 | NULL
  | latin1_swedish_ci |     NULL |                |         |
| MontageLayouts  | InnoDB |      10 | Dynamic    |        5 |           3276 |       16384 |               0 |            0 |           0 |              6 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Servers         | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |            0 |           0 |              1 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| States          | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |        16384 |           0 |              2 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Stats           | InnoDB |      10 | Dynamic    | 27259239 |             89 |  2432679936 |               0 |   1631535104 |     3145728 |           NULL | 2019-04-30 06:07:57 | 2019-07-22 10:41:19 | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Storage         | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |            0 |           0 |              2 | 2019-04-30 06:07:57 | 2019-07-22 10:41:19 | NULL
  | latin1_swedish_ci |     NULL |                |         |
| TriggersX10     | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |            0 |           0 |           NULL | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Users           | InnoDB |      10 | Dynamic    |        0 |              0 |       16384 |               0 |        16384 |           0 |              2 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| ZonePresets     | InnoDB |      10 | Dynamic    |        7 |           2340 |       16384 |               0 |            0 |           0 |              8 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
| Zones           | InnoDB |      10 | Dynamic    |        5 |           3276 |       16384 |               0 |        16384 |           0 |              7 | 2019-04-30 06:07:57 | NULL                | NULL
  | latin1_swedish_ci |     NULL |                |         |
+-----------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
The lines of interest are the table Frames, Logs and Stats.
Frames also related to this post I found on the forum: viewtopic.php?t=26970

The solution to this (at least, it's working for now and I don't think I broke anything else...) was to truncate the tables,
https://tableplus.io/blog/2018/08/mysql ... ables.html had simple instructions to do this.

Once that's done, disk usage dropped to 4GB and as one example the Frames table data_length became 49152 rather than 2011136000 shown in the output above (I'm assuming this is bytes).

Truncating tables needs disk space though, so I had to expand the LVM to do this. I think other online sources spoke about backing up database, truncating elsewhere, then restoring.
Last edited by sroot on Mon Jul 22, 2019 2:56 pm, edited 1 time in total.
Top
bbunge
Posts: 2934
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: Mysql DB huge (grew > 26GB)

Post by bbunge »

Try stopping Zoneminder and running zmaudit.pl from a command prompt. Might take a while. My database grew large due to the log file not deleting on schedule. If this works turn off opt fast delete and zmaudit.pl in options and schedule with a cron to run zmaudit.pl once a week.
Top
sroot
Posts: 2
Joined: Sat Nov 12, 2016 3:24 pm

Re: Mysql DB huge (grew > 26GB)

Post by sroot »

thanks bbunge, that's useful to know.

Also to anyone considering my first method - be warned that later in the day ALL events were deleted from the disk. I know when I first ran it they were there as I checked a recording to see if I could see our power pole go bang (I didn't but saw people going to look at it afterwards though).

I guess truncate was not just reclaiming unused rows but removing lots which then triggered a delete by a housekeeping script. At least I have plenty of disk space now :lol:
Top
jwarfin
Posts: 41
Joined: Mon Jul 23, 2018 4:36 am

Re: Mysql DB huge (grew > 26GB)

Post by jwarfin »

We have 10 cams, set for modect and we often get well over 1000 events per day.

On my system we have OPT_FAST_DELETE set and AUDIT_CHECK_INTERVAL set to 3600 (1 hour), rather than the default 900 (15min). So far, the 3600 setting allows way more than enough time to zm_audit to do its thing.

In conjunction with the above, a standard practice we employ for Zoneminder is to manually run a filter at the end of the day that clears all current events.

Note that we have zoneminder setup such that upon an event, a video is automatically generated for the event & promptly replicated to remote storage. Thus if we need to review an old event, we can browse archived events in the remote storage. So, for us, keeping days/weeks/months of events in zoneminder's db isn't necessary.
Top
jrd
Posts: 11
Joined: Wed Jan 04, 2017 7:32 pm

Re: Mysql DB huge (grew > 26GB)

Post by jrd »

I'm having a similar problem.

My DB log if left unattended just keeps growing. I have my log retention setting set to "1 day". zmaudit.pl is running regularly, but it never seems to get around to deleting old log entries. There are no errors in the (file) log.

Hints?
Top
jrd
Posts: 11
Joined: Wed Jan 04, 2017 7:32 pm

Re: Mysql DB huge (grew > 26GB)

Post by jrd »

Bump

Any wisdom? What are the "right" config options to get the DB log to automatically stay pruned?

I'm running a cron job which goes straight to the DB and deletes old events from that deletes old rows from Logs, but it seems like there ought to be a better way.
Top
Post Reply

Return to “ZoneMinder 1.32.x”