large image db delemia

[Jason Murry]

I have a camera system (Axis) which stores JPG via FTP 1-10fps. There
is also a motion jpg live stream.

I am trying to store these images either in JPG or in video format so
they can be reviewed at a later date. I would need to be able to pull
a date-time range from the list.

This means 86,400 - 2,160,000 images (around 110Kb a piece) per camera
a day, depending on speed 1fps - 10fps. There will probably be 1-5
cameras typically. Perhaps more in some rare cases.

I have considered storing via FTP process to a file system, then
creating directories by day/hour to group the images. Then store the
filename and timestamp in a searchable (by timestamp) table in mySQL.
This would allow to read the image from disk and have it searchable by
timestamp.

I still am unsure how to display the images, one option is to do meta
refresh 1-10 times a second, but i think this will be a burden on the
client. There should only be 1-2 clients accessing this data and it
will be via local lan most likely.

Concerns I have:

1. Would storing the images in the database directly be fast enough
(by passes the need to worry about file locations and directory
sizes).

2. How many files can you store in a directory before it becomes to
slow to work with (50,000? 1 million?)

3. How do you display these jpgs as a smooth animation without hogging
bandwidth and client cpu to display a slideshow.

4. Was considering storing into a movie format, but how do you search
by time and what method use to store as movie.

Any ideas or insight on this problem would be greatly appreciated.

[Andy Hassall]

Re: large image db delemia

On Tue, 16 Sep 2003 09:39:19 GMT, Jason Murry <me@isp.com> wrote:
[color=blue]
>I have a camera system (Axis) which stores JPG via FTP 1-10fps. There
>is also a motion jpg live stream.
>
>I am trying to store these images either in JPG or in video format so
>they can be reviewed at a later date. I would need to be able to pull
>a date-time range from the list.
>
>This means 86,400 - 2,160,000 images (around 110Kb a piece) per camera
>a day, depending on speed 1fps - 10fps. There will probably be 1-5
>cameras typically. Perhaps more in some rare cases.[/color]

That's quite a bit of data... between ~3 terabytes and ~400 terabytes per
year, between the minimum and maximum numbers you've listed.

Ouch... over a terabyte a day worst case? (I hope I've put a load of zeroes in
the wrong place somewhere!)

2160000 * 110000 * 5 * 365 = 433620000000000
/ 1099511627776 (1Tb)
= 394.37 Tb
[color=blue]
>I have considered storing via FTP process to a file system, then
>creating directories by day/hour to group the images. Then store the
>filename and timestamp in a searchable (by timestamp) table in mySQL.
>This would allow to read the image from disk and have it searchable by
>timestamp.
>
>I still am unsure how to display the images, one option is to do meta
>refresh 1-10 times a second, but i think this will be a burden on the
>client. There should only be 1-2 clients accessing this data and it
>will be via local lan most likely.
>
>Concerns I have:
>
>1. Would storing the images in the database directly be fast enough
>(by passes the need to worry about file locations and directory
>sizes).[/color]

With the data volumes above? You'd need a pretty serious database. Storing
images in the database gives you transactional control over changes, and lets
you back up the database in one go, rather than worrying about matching backups
of the filesystem to the index in the database.

But it does add overhead to inserting and retreiving the data, which could be
reduced by caching the images in the filesystem, at least for repeated reads.
You mention you're using MySQL - forget storing in the database then, for this
sort of volume.
[color=blue]
>2. How many files can you store in a directory before it becomes to
>slow to work with (50,000? 1 million?)[/color]

Depends entirely on the filesystem. Tens of thousands is probably where you
start thinking seriously about splitting it up.

Splitting it by date has the advantage you can just archive a directory or set
of directories for older sections that you don't immediately need, when you
start running out of disk space, rather than having to hunt for old files.
[color=blue]
>3. How do you display these jpgs as a smooth animation without hogging
>bandwidth and client cpu to display a slideshow.[/color]

Send it as video; you mentioned motion jpeg earlier, that would seem to make
sense.
[color=blue]
>4. Was considering storing into a movie format, but how do you search
>by time and what method use to store as movie.[/color]

This could have large advantages in terms of space; since you save a lot on
the compression as it'll be storing differences between frames. Again, motion
jpeg has advantages here, as it can store the video with lossless compression
(lossless compared to the original jpeg frames, anyway). Or you can run it with
lossy compression to save even more space; in which case you could consider
other video compression schemes. If you can stream it in as motion jpeg in the
first place, that could cut down on the processing power you'd need (a lot).

To play from time X to time Y, it'd just be a matter of finding the video
file(s) spanning that period, and extracting the frames. It'll probably have to
seek back a bit to the previous keyframe, to get to the frame at time X if it
doesn't lie exactly on a keyframe.

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Jason Murry]

Re: large image db delemia

Hello, thanks for the response.

[color=blue]
> That's quite a bit of data... between ~3 terabytes and ~400 terabytes per
>year, between the minimum and maximum numbers you've listed.
>
> Ouch... over a terabyte a day worst case? (I hope I've put a load of zeroes in
>the wrong place somewhere!)
>
>2160000 * 110000 * 5 * 365 = 433620000000000
> / 1099511627776 (1Tb)
> = 394.37 Tb[/color]

Here is my math, per camera.

About 9Gb/day per camera, I was thinking 1fps would be about what i
would be recording at.

Per Picture Size 109,000

Seconds in a day 86,400
Seconds in an hour 3,600

Images / day @ 1fps 86,400
Images / day @ 10fps 864,000

Size (MB) / day @ 1fps 8,981.32
Size (MB) / day @ 10fps 89,813.23

[color=blue]
> With the data volumes above? You'd need a pretty serious database. Storing
>images in the database gives you transactional control over changes, and lets
>you back up the database in one go, rather than worrying about matching backups
>of the filesystem to the index in the database.
>[/color]
[color=blue]
> But it does add overhead to inserting and retreiving the data, which could be
>reduced by caching the images in the filesystem, at least for repeated reads.
>You mention you're using MySQL - forget storing in the database then, for this
>sort of volume.[/color]

With mySQL since you do not have transaction support, there is no
logging and thus no transaction overhead. Storing the DB on a raw
petition would remove the file system overhead.

[color=blue]
> Depends entirely on the filesystem. Tens of thousands is probably where you
>start thinking seriously about splitting it up.
>[/color]

I was thinking about the same, some where around 50k I was expecting
to have problems, if I split off by directories I can avoid this
limit, but I would also add an additional process of coping the
images, which would double th bandwith used by the ftp process

[color=blue]
> Send it as video; you mentioned motion jpeg earlier, that would seem to make
>sense.
>[/color]

I would love to know how to do this, I don't know. I was thinking
about using PHP which I have used alot in the past, but I have never
worked with dynamic video formats and such. I did find a java class
in the JMF that takes JPG and makes it quicktime, but i never got it
working correctly. And I am not sure if dynamic video encoding would
be fast enough with java.
[color=blue]
>
> This could have large advantages in terms of space; since you save a lot on
>the compression as it'll be storing differences between frames. Again, motion
>jpeg has advantages here, as it can store the video with lossless compression
>(lossless compared to the original jpeg frames, anyway). Or you can run it with
>lossy compression to save even more space; in which case you could consider
>other video compression schemes. If you can stream it in as motion jpeg in the
>first place, that could cut down on the processing power you'd need (a lot).
>[/color]

The camera has a Motion JPG Stream, but I can't find any way to use
this directly. I tried righting a vb program to open a connection and
try to read the stream but I was left with string of jpgs.

[color=blue]
> To play from time X to time Y, it'd just be a matter of finding the video
>file(s) spanning that period, and extracting the frames. It'll probably have to
>seek back a bit to the previous keyframe, to get to the frame at time X if it
>doesn't lie exactly on a keyframe.[/color]

In motion JPG as I found out, all frames are key frames.

The problem with video, is how much work will it be to do real time
encoding of the jpgs into the video files. If the files coming in
around the clock, and have to move the files into directory structure
and encode a video, this is alot of work. I think it would need to be
C to be fast enough.

If you look at Milestone (a company who works with this problem with
Axis cameras) they claim to use a high performance proprietary db to
store the images. As an Oracle DBA, I know I can do this with Oracle,
but Oracle is way out of the bugdget for this project. Since the
images are not being searched or anything, there is really no reason
to store them in the db.

We trying to find a way to solve this problem so we can offer this
with the cameras for recording functionality.

[Andy Hassall]

Re: large image db delemia

On Tue, 16 Sep 2003 20:36:49 GMT, Jason Murry <me@isp.com> wrote:
[color=blue]
>Hello, thanks for the response.
>[color=green]
>> Ouch... over a terabyte a day worst case? (I hope I've put a load of zeroes in
>>the wrong place somewhere!)[/color]
>
>Here is my math, per camera.
>
>About 9Gb/day per camera, I was thinking 1fps would be about what i
>would be recording at.
>
>Per Picture Size 109,000
>
>Seconds in a day 86,400
>Seconds in an hour 3,600
>
>Images / day @ 1fps 86,400
>Images / day @ 10fps 864,000
>
>Size (MB) / day @ 1fps 8,981.32
>Size (MB) / day @ 10fps 89,813.23[/color]

OK - not sure where the 2,160,000 images came from in the first example - but
you also said 5 cameras; so 45 - 450 Gb a day? Still on the big side - largest
MySQL database I can find mentioned is 1Tb.
[color=blue][color=green]
>> With the data volumes above? You'd need a pretty serious database. Storing
>>images in the database gives you transactional control over changes, and lets
>>you back up the database in one go, rather than worrying about matching backups
>>of the filesystem to the index in the database.
>>
>> But it does add overhead to inserting and retreiving the data, which could be
>>reduced by caching the images in the filesystem, at least for repeated reads.
>>You mention you're using MySQL - forget storing in the database then, for this
>>sort of volume.[/color]
>
>With mySQL since you do not have transaction support, there is no
>logging and thus no transaction overhead.[/color]

MySQL does have transaction support nowadays, can have logging turned on, and
even with those turned off there's still an overhead getting large data in and
out, compared with a filesystem.
[color=blue]
>Storing the DB on a raw petition would remove the file system overhead.[/color]

Didn't know MySQL had support for that.
[color=blue][color=green]
>> Depends entirely on the filesystem. Tens of thousands is probably where you
>>start thinking seriously about splitting it up.
>>[/color]
>I was thinking about the same, some where around 50k I was expecting
>to have problems, if I split off by directories I can avoid this
>limit, but I would also add an additional process of coping the
>images, which would double th bandwith used by the ftp process[/color]

Isn't that just a matter of moving the files, rather than copying?
[color=blue][color=green]
>> Send it as video; you mentioned motion jpeg earlier, that would seem to make
>>sense.[/color]
>
>I would love to know how to do this, I don't know. I was thinking
>about using PHP which I have used alot in the past, but I have never
>worked with dynamic video formats and such. I did find a java class
>in the JMF that takes JPG and makes it quicktime, but i never got it
>working correctly. And I am not sure if dynamic video encoding would
>be fast enough with java.[/color]

If you're doing any processing on this much data, forget Java or PHP, you'll
be needing something a bit quicker, e.g. C or C++.
[color=blue][color=green]
>> This could have large advantages in terms of space; since you save a lot on
>>the compression as it'll be storing differences between frames. Again, motion
>>jpeg has advantages here, as it can store the video with lossless compression
>>(lossless compared to the original jpeg frames, anyway). Or you can run it with
>>lossy compression to save even more space; in which case you could consider
>>other video compression schemes. If you can stream it in as motion jpeg in the
>>first place, that could cut down on the processing power you'd need (a lot).[/color]
>
>The camera has a Motion JPG Stream, but I can't find any way to use
>this directly. I tried righting a vb program to open a connection and
>try to read the stream but I was left with string of jpgs.[/color]

Don't know anything off-the-shelf; you mention VB, so you could be using
DirectShow or the older Video for Windows with a MJPEG codec.

Or it could be as simple as working out whatever protocol it's using to send
the stream (HTTP?) and just saving it straight to disc without worrying too
much about processing it at the time.
[color=blue][color=green]
>> To play from time X to time Y, it'd just be a matter of finding the video
>>file(s) spanning that period, and extracting the frames. It'll probably have to
>>seek back a bit to the previous keyframe, to get to the frame at time X if it
>>doesn't lie exactly on a keyframe.[/color]
>
>In motion JPG as I found out, all frames are key frames.
>
>The problem with video, is how much work will it be to do real time
>encoding of the jpgs into the video files. If the files coming in
>around the clock, and have to move the files into directory structure
>and encode a video, this is alot of work. I think it would need to be
>C to be fast enough.[/color]

Most likely - and then you might be pushing it unless you've got quite a bit
of hardware to throw at it. If the stream's coming in as MJPEG, then the best
way has got to be to just save that stream as it comes in (since you won't have
enough time to process it much), and then possibly decode to frames on demand,
or just stream back the video.

I don't think splitting and storing as individual frames is going to be
practical.

What are these cameras likely to be recording? Lots of movement? If there can
be 'quiet' periods or areas in the picture you're pouring away disk space with
individual frames.

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Randell D.]

Re: large image db delemia


"Jason Murry" <me@isp.com> wrote in message
news:efsemvgmag fa1bebb06ig8k3h s571s6rlh@4ax.c om...[color=blue]
> Hello, thanks for the response.
>
>[color=green]
> > That's quite a bit of data... between ~3 terabytes and ~400 terabytes[/color][/color]
per[color=blue][color=green]
> >year, between the minimum and maximum numbers you've listed.
> >
> > Ouch... over a terabyte a day worst case? (I hope I've put a load of[/color][/color]
zeroes in[color=blue][color=green]
> >the wrong place somewhere!)
> >
> >2160000 * 110000 * 5 * 365 = 433620000000000
> > / 1099511627776 (1Tb)
> > = 394.37 Tb[/color]
>
> Here is my math, per camera.
>
> About 9Gb/day per camera, I was thinking 1fps would be about what i
> would be recording at.
>
> Per Picture Size 109,000
>
> Seconds in a day 86,400
> Seconds in an hour 3,600
>
> Images / day @ 1fps 86,400
> Images / day @ 10fps 864,000
>
> Size (MB) / day @ 1fps 8,981.32
> Size (MB) / day @ 10fps 89,813.23
>
>[color=green]
> > With the data volumes above? You'd need a pretty serious database.[/color][/color]
Storing[color=blue][color=green]
> >images in the database gives you transactional control over changes, and[/color][/color]
lets[color=blue][color=green]
> >you back up the database in one go, rather than worrying about matching[/color][/color]
backups[color=blue][color=green]
> >of the filesystem to the index in the database.
> >[/color]
>[color=green]
> > But it does add overhead to inserting and retreiving the data, which[/color][/color]
could be[color=blue][color=green]
> >reduced by caching the images in the filesystem, at least for repeated[/color][/color]
reads.[color=blue][color=green]
> >You mention you're using MySQL - forget storing in the database then, for[/color][/color]
this[color=blue][color=green]
> >sort of volume.[/color]
>
> With mySQL since you do not have transaction support, there is no
> logging and thus no transaction overhead. Storing the DB on a raw
> petition would remove the file system overhead.
>
>[color=green]
> > Depends entirely on the filesystem. Tens of thousands is probably where[/color][/color]
you[color=blue][color=green]
> >start thinking seriously about splitting it up.
> >[/color]
>
> I was thinking about the same, some where around 50k I was expecting
> to have problems, if I split off by directories I can avoid this
> limit, but I would also add an additional process of coping the
> images, which would double th bandwith used by the ftp process
>
>[color=green]
> > Send it as video; you mentioned motion jpeg earlier, that would seem to[/color][/color]
make[color=blue][color=green]
> >sense.
> >[/color]
>
> I would love to know how to do this, I don't know. I was thinking
> about using PHP which I have used alot in the past, but I have never
> worked with dynamic video formats and such. I did find a java class
> in the JMF that takes JPG and makes it quicktime, but i never got it
> working correctly. And I am not sure if dynamic video encoding would
> be fast enough with java.
>[color=green]
> >
> > This could have large advantages in terms of space; since you save a lot[/color][/color]
on[color=blue][color=green]
> >the compression as it'll be storing differences between frames. Again,[/color][/color]
motion[color=blue][color=green]
> >jpeg has advantages here, as it can store the video with lossless[/color][/color]
compression[color=blue][color=green]
> >(lossless compared to the original jpeg frames, anyway). Or you can run[/color][/color]
it with[color=blue][color=green]
> >lossy compression to save even more space; in which case you could[/color][/color]
consider[color=blue][color=green]
> >other video compression schemes. If you can stream it in as motion jpeg[/color][/color]
in the[color=blue][color=green]
> >first place, that could cut down on the processing power you'd need (a[/color][/color]
lot).[color=blue][color=green]
> >[/color]
>
> The camera has a Motion JPG Stream, but I can't find any way to use
> this directly. I tried righting a vb program to open a connection and
> try to read the stream but I was left with string of jpgs.
>
>[color=green]
> > To play from time X to time Y, it'd just be a matter of finding the[/color][/color]
video[color=blue][color=green]
> >file(s) spanning that period, and extracting the frames. It'll probably[/color][/color]
have to[color=blue][color=green]
> >seek back a bit to the previous keyframe, to get to the frame at time X[/color][/color]
if it[color=blue][color=green]
> >doesn't lie exactly on a keyframe.[/color]
>
> In motion JPG as I found out, all frames are key frames.
>
> The problem with video, is how much work will it be to do real time
> encoding of the jpgs into the video files. If the files coming in
> around the clock, and have to move the files into directory structure
> and encode a video, this is alot of work. I think it would need to be
> C to be fast enough.
>
> If you look at Milestone (a company who works with this problem with
> Axis cameras) they claim to use a high performance proprietary db to
> store the images. As an Oracle DBA, I know I can do this with Oracle,
> but Oracle is way out of the bugdget for this project. Since the
> images are not being searched or anything, there is really no reason
> to store them in the db.
>
> We trying to find a way to solve this problem so we can offer this
> with the cameras for recording functionality.[/color]

Since you're open to the idea of movies, one possibility is to arrange each
video in to five minute slots or something - You can create the jpgs in to a
single directory and then use something like Real Players Helix or whatever
to convert the jpgs in to a streaming format. I believe they will handle
jpgs though I've not tried it - I used the basic version to convert aload of
avi and mpeg movies in to a streaming format (I did the conversion on a
windoze machine but know they supply the package for linux)... The basic
version does not permit batch processing however I was able to create DOS
batch files that permitted me to perform the conversion.

I'd be pretty sure that you could re-create something similar for jpg images
on a linux box with a cron job... and the streaming format of the images
should help save some disk space too...