Very high disc write from MySQL destroying SSD.

[kibbling]

Hello,

I have two discs.
SDA is the virtual system disc from Proxmox - a Samsung 1 TB 600 TBW nvme SSD
SDB is a spinning HDD for the videos.

I write all Videos with "Record" to the sdb HDD. This take about 700 KB/s (seen in iostat).

But also the system disk has 500KB/s - for no obvious reason.
I see that this is mostly related to the MySQL process (seen with iotop).
But I can not figure out, why ZM is writing nearly as much as the video streams to the Database.

Any tips, how to reduce the Writing on the mysql?
With such values, the SSD will not last long and is broken very soon.
There are lots of other VM's on this proxmox, and no one (not even dedicated Database servers) have near this IO load as ZM.
How to disable all this MySQL writing? What is it used for?

[kibbling]

I already reduced Logging to Error.
Increasing the Event Frame Intervall from 100 to 1000 does not change the write amount - like suggested in some threads.

[bbunge]

Use Mariadb instead of MySQL.
Don't run a production zoneminder on a VM.
Don't record jpegs if possible. Video with camera pass through should use fewer database writes.

And you Samsung SSD should last you a long time. I ran a Kingston SSD on a 16 cam system, for OS and database, for a couple of years with no issues. It would still be working today but I stopped volunteering my services and my replacement installed a commercial video recording system (which is not as good as Zoneminder was).

[Andyrh]

According to the math, 600TBW divided by 500KB/s = 1,200,000,000 seconds of life or 38 years. Unless I screwed up the math I think you will be fine.

Scaled to KBs
600,000,000,000 ÷ 500 = 1,200,000,000

[kibbling]

I dont write jpgs, only the videos.
the Video hdd is fine - the writes there are as expected and "normal".
But the writes on the system disk are not expected and are more as all the full-HD video streams on the video hdd.
Your Math is correct - but this is not good, its very bad.

It needs to be a VM - because it need to be High available.
I dont want to care about failing hardware.
So I run Proxmox as a Cluster. When a node fail, the VM is directly executed by another host without downtime.

ZM alone reduces the livetime of a node by the Numbers you named.
But because there are ~20 VMs in this cluster, the SSD livetime is currenlty down to 5 Years.
And this is bad.
ZM has a mayor impact on the live time of the whole cluster and by far the worst writing compared to all other services.

Also:
The "Meta Data" writing is more than all Full-HD Video streams in a summ.
That sounds like a bug.
Writing more meta-data on the system drive as the Videostreams on the video volume....

I think all Videos get written to the System disk at first, and then copied to the Video disk, instead of writing it directly to the video disk?

[iconnor]

You can turn off writing of motion detection statistics Options->Logging->RECORD_EVENT_STATS. You can turn off all logging to the db. This will reduce db writing a bit.

The rest is going to be a matter of db tuning. ZoneMinder has a need to store information in the db. Your disks will just have to handle it.

[apber]

HDD's rated for video or NAS grade should handle whatever the DMBS throws at them in terms of IO. My db has been on Samsung Pro SSD's on RAID5 for two years and have seen no issues.

[kibbling]

I found the root cause.

With 4 Cammeras, ZoneMinder is executing 150 Querys per second in avarage.


Instead of 1 insert into the Frame Table, they fire a complete bunch of Querys:

Code: Select all

2022-10-27T08:50:34.007875Z      2673 Query     INSERT INTO `Frames` (`EventId`, `FrameId`, `Type`, `TimeStamp`, `Delta`, `Score`) VALUES
( 10328, 4800, 'Bulk', from_unixtime( 1666860633 ), 397.80, 0 )
2022-10-27T08:50:34.008211Z      2673 Query     UPDATE Events SET Length = 397.80, Frames = 4800, AlarmFrames = 0, TotScore = 0, AvgScore = 0, MaxScore = 0 WHERE Id = 10328
2022-10-27T08:50:34.008816Z      2673 Query     UPDATE Events_Hour SET DiskSpace=NEW.DiskSpace WHERE EventId=NEW.Id
2022-10-27T08:50:34.008914Z      2673 Query     UPDATE Events_Day SET DiskSpace=NEW.DiskSpace WHERE EventId=NEW.Id
2022-10-27T08:50:34.008975Z      2673 Query     UPDATE Events_Week SET DiskSpace=NEW.DiskSpace WHERE EventId=NEW.Id
2022-10-27T08:50:34.009063Z      2673 Query     UPDATE Events_Month SET DiskSpace=NEW.DiskSpace WHERE EventId=NEW.Id
2022-10-27T08:50:34.129055Z      2677 Query     UPDATE LOW_PRIORITY Monitor_Status SET CaptureFPS = 11.99, CaptureBandwidth=99089, AnalysisFPS = 0.00 WHERE MonitorId=7

The INSERT and the UPDATE is then triggering Stored Procedures "event_update_trigger" and "event_insert_trigger".
The event_update_trigger alone fires 13 more updates - each Trigger.

1 Frame insert (each 100 Camera Frames) trigers 6 Update Querys from ZM and they fire 6 * 13 Update Querys to Mysql = 78 Update Querys... for each Frame. As far As I can see - 48 Querys out of this is to update the field "DiskSpace" for Day, Hour, Week And Month.

Each of this 78 Updates per Frame Entry is executed in a separate Transaction in the Mode: "ISOLATION LEVEL READ COMMITTED", what is the slowest mode in innoDB, forcing each single update to first go to the disc 2 Times, before the next Query can be executed.


Maybe someone can point the Developers to this - thats really crazy amount of Querys for each single Frame.
It would be enought to:
- Have 1 Insert per Frame
- Update the Disk usage once

Wihtout any Drawback this would prolongen the livetime of the SSD by a factor of 50.

[kibbling]

Samsung Pro SSD's on RAID5 for two years and have seen no issues.

I Also use a Samsung Pro SSD with a livetime of 600 TBW.
Zoneminder is using 60 TB/Year for the MySQL only when having 4 Cameras.
So expected Livetime by only MySQL usage is 10 Years to consume all 600 TBW without video writing to the SSD.

It is less Video Data written as MySQL Data, even if you record all Videos 100% of the time.
As in the post above - 78 Updates to the Database per Frame are not needed at all. There are even 48 Querys doing exactly the same - updating the Disc usage 48 times to the same value....

[iconnor]

Are you saying that mysql writes non-changes to disk? Wow.. my respect for mysql plummets even further.

I suppose we could change some of our SQL queries to check for change of value... I can't think of any that those would be though..

Good thing we have someone working on switching to postgres...

[kibbling]

Any chance to get this fixed for ZM/MySQL current installations?

I have no idea how many Installations of ZM are out there.
But if you have 10.000 Installations and each can save a few years of SSD lifetime, this saves tons of waste and literally million(s) of dollars for broken SSDs.

[geraldbrandt]

Are you saying that mysql writes non-changes to disk? Wow.. my respect for mysql plummets even further.

I suppose we could change some of our SQL queries to check for change of value... I can't think of any that those would be though..

Good thing we have someone working on switching to postgres...

I would switch to Postgres!

[apber]

I would switch to Postgres!

Wouldn't the db on Postgress also contain the same update and insert triggers?

[Andyrh]

The math on my SSD and the one earlier in this tread shows the drive is likely to last longer than I will want to use it. While I do like efficient code, I am not stressed on this subject.

[iconnor]

The SSDs that I run my ZM on are now.. at least 10 years old.. and have 94% life left?

Look if you want to investigate what queries can be optimised to write less, please do. I'm happy to help, but I won't be spending a lot of time on it.

Otherwise you are just talking about db tuning. I know you can commit to disk less often, and at the OS level can likely do even more.

I can't think of a single query that updates data that doesn't need updating. In fact over the last few versions I added a ton of code to queue up inserts so that they happen around 1/second instead of 30/second etc in order to make mysql scale higher, which should also have the effect of improving SSD lifetime.

However the people who are paying me? They are talking about injecting MORE data, from AI/ML pipelines, and environment sensors etc. And they want it at 30-40fps. So that's where my attention is.

Yeah can't wait for postgres. I've always been so much more impressed with it. From what I read, it is starting to really take over in the free relational db space finally.