Re: how to vacuum thru psycopg

On Fri, 2009-06-12 at 11:58 -0700, Tim Roberts wrote:
> Al Niessner wrote:
> > You all are probably going to hate me for this, but I need to give you a
> > picture to help clarify what I am talking about.
> 
> That's a very revealing graph.  I know you came in hoping to solve a
> very focused problem, and I hope you will not be upset if I veer off to
> explore your database layout a bit.  I'm still not 100% clear on your
> schema, but I have a suspicion that your trouble is coming from the schema.
> 

Ask and suggest away. It is how we all learn and improve.

> > If you do not know, EXIF data is a set of name-value pairs. For brevity,
> > EXIF uses a 16 bit int for its name and then some variable length number
> > of bytes for the value. Of the images being scanned, it is normal for
> > the EXIF data to be 40 or 44 pairs in size, but there are some that are
> > zero. Of these 44 or so pairs, only about 10 of them change. So, what I
> > do is scan the table to see if the pair exists. If it does, then I leave
> > it be and just point the image at it (another table with 2 foreign keys
> > relating the picture to the pair). So, pair table should never really
> > have that many entries. The table that relates the picture to the pair
> > is huge (number of pictures * 44) but it is just a primary key and two
> > foreign keys, which should be very fast.
> >   
> 
> OK, so you have a table with EXIF key, EXIF value, and a unique ID. 
> Then, you have another table with picture number, EXIF key, and the
> unique ID from the EXIF table.  Is that right?
> 

Kind of. I have a picture table that contains a primary key, the MD5 sum
of the original image, and two blobs (mini and thumb). I have a table
that contains grouped named-value paired meta data. One group is the
EXIF. The group table contains a primary key and group name of which one
group is called EXIF. I then have a pair name table which contains a
primary key the group the name belongs too (that way you can the same
name in two groups) and the name itself. There is then a table for each
unique value that has a primary key and the value. Finally, there is an
index table which contains a primary key, pair name key, pair value key,
and picture key. Told you it was a bit more complicated. I tried to make
it for fast reads and ignored writes. Oh, and this is my first DB work
so I am sure there is tons room for improvement.

> How are you determining whether the pair exists?  Are you actually doing
> a SELECT for each pair?  That is, you're doing 44 SELECTs for every
> picture before doing the INSERT?  Since each one of those is a server
> round trip, doesn't it seem obvious that this is where your performance
> is being eaten up?
> 

Yes, I do agree that if I bundled them up it would speed things up.
However, since there are only a fixed number of unique values and that
set is smaller than 10000 and is probably less than 1000, I do not think
that is responsible for the linear time growth.

> You're doing these extra lookups just to save disk space, right?  I
> would assert that this is a false optimization.  If it were me, I'd just
> have a table with picture number, EXIF key, and EXIF value, and store
> each image's keys in there unconditionally.  You'll have the same number
> of records as in your relation table, but the records will be a bit
> larger.  You're talking about, what, maybe 400 bytes of EXIF data per
> image?  That's well under 10 megabytes total.
> 

I would pretend that I did it for space allocation, but that is not
really true. If I were doing that, I would do as you suggest and just
lump the EXIF into a quick table because it is as small as it gets
already. Mostly, I was just trying to minimize elements so that read
back would be faster. Some extra time at load is fine because it is done
once and read a zillion times. However, this linear time growth is just
a bit much even for me.

I would agree that it may still be a false optimization. Not sure how to
measure it though since it is really related back to read times.

> I think you are making a lot of extra work for yourself with no tangible
> benefit.
> 

This too is probably true. However, it is how I learn the best.

> > So, I have this huge time sink that is strictly database related and my
> > best suggestion so far was to vacuum once in a while. I am welcome to
> > entertain other suggestions.
> >   
> 
> Never let it be said that I hesitated in giving unwanted advice.
> 

So far, none of it is unwanted and I really appreciate your help.

-- 
Al Niessner

I have never found the companion that was so companionable as solitude.
- From Walden by Henry David Thoreau

The universe is indifferent, and life is brutal; however, it is man's
choice of behavior that makes them malevolent rather than benevolent.

Some will fall in love with life and drink it from a fountain
That is pouring like an avalanche coming down the mountain.
- From the song Pepper by the Butthole Surfers