Mysql DB huge (grew > 26GB)
Posted: Mon Jul 22, 2019 12:30 pm
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
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.
===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 | | |
+-----------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
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.