[nycphp-talk] LARGE mysql database performance

Keith Richardson keith at
Wed Feb 1 08:13:55 EST 2006

The camera snapshots are 352x240 @ 96 dpi, averaging about 12,288 bytes on
disk. (This server is a <gasp> windows file system)

So after reading and doing some thinking, and taking into account everyones
thoughts, I think I will go with a file system solution. My cameras table
will have a "ftp folder" and a "storage folder" so that you can traverse the
ftp folder for images, if found, index them and move them to the storage
folder, with a little bit more. I que up file videopos20060130101253.jpg and
know the date and time by the filename, so thats how I will get my
timestamp. When I move it, i will move it into the
$ftpfolder/$year/$month/$day/ folder. I have had this recording files for
about 2 weeks, and have already seen 25-30k images, and had to manually
start splitting them up before I had this front-end app up and running :P

The cameras take their snapshots after motion detection on zones that I set,
and after they are triggered they snapshot every second until it detects no
motion on those zones. -So Far- there are 13 cameras, and we would need
another 30 or so to cover all of our Dorm building enterances (I work at a

The thing for viewing is that you have to authenticate vs windows active
directory (through a ldap script I wrote), and be a member of a certain
usergroup. I will have a showimage.php or something like that authenticate,
and if authenticated, read the file from the filesystem and push out the
image, and if not, I guess I have to make a "You are not authorized to view
this mage" picture to view :)

I will also have to figure out some sort of data removal scheme, as the file
storage is limited, so I have to flush out the old as needed, but thats more
of what the security people want me to do, as some cameras can be purged
more often than others.. Though so far we have 214,778 snapshots recorded
(2,797,568,000 bytes on disk), which is 12 days for 13 cameras, so thats
average 17-18 megs a day per camera, which is a lot better for full motion
video :P

Well I have some work ahead of me, but this stuff is great, since PHP has
been my hobby/"on the side" work, and I one again get to use it towards my
job :)

And in central new york, we finally have snow again, enough with this rain

-Keith Richardson

On 2/1/06, Alberto dos Santos <yournway at> wrote:
> >Keep the images on the filesystem - out of a BLOB record.
> Why?
> >Map the filesystem, meta data and other stuff into the database.
> Isn't there a limit to the number of files that can reside in one
> directory? If so, then how do you handle managing what is mapped
> where?
> tedd
> > Keith Richardson wrote:
> > You should organize the files into some sort of
> > nested directory tree. For ideas, examine the structure of the Postfix
> > (mail server) queue.
> >
> >
> If I was going to organise a camera watch like the one we are discussing I
> would certainly go for the filesystem storage of the pictures, keeping the
> pointers on a database.
> To circumvent filesystem limitations, if any because we are surely talking
> about 640x480x72dpi pics taken every 10 seconds or so, and also to allow me
> to do quick filesystem searches for an event, in case of a never expected
> but always possible application failure, I would organize my folders in a
> cascade like /year/month/day/hour/ kind of thingh, so it would always be
> intuitive for both me and my successors, and easy to tar the folders and
> send them to the PD, if ever necessary, hehe.
> --
> Alberto dos Santos
> Consultor em TI
> IT Consultant
> A internet à sua maneira.
> The Internet your own way.
> _______________________________________________
> New York PHP Community Talk Mailing List
> New York PHP Conference and Expo 2006
> Show Your Participation in New York PHP

Keith Richardson
keithjr at
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <>

More information about the talk mailing list