pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
design question: general db performance
5+ messages / 5 participants
[nested] [flat]

* design question: general db performance
@ 2003-11-25 18:42  shane hill <shill@adobe.com>
  0 siblings, 4 replies; 5+ messages in thread

From: shane hill @ 2003-11-25 18:42 UTC (permalink / raw)
  To: pgsql-performance

Hi folks,

Disclaimer:  I am relatively new to RDBMSs, so please do not laugh at me 
too loudly,  you can laugh, just not too loudly and please do not point. :)

I am working on an Automated Installer Testing System for Adobe Systems 
and I am doing a DB redesign of the current postgres db:

1. We are testing a matrix of over 900 Acrobat installer configurations 
and we are tracking every file and registry entry that is affected by an 
installation.

2. a single file or registry entry that is affected by any test is 
stored in the db as a record.

3. a typical record is about 12 columns of string data. the data is all 
information about a file (mac or windows) or windows registry entry [ 
file or regkey name, file size,  modification date, checksum, 
permissions, owner, group, and in the case of a mac, we are getting all 
the hfs atts as well].

4. A typical test produces anywhere from 2000 - 5000 records.


Our db is getting to be a respectable size (about 10GB right now) and is 
growing slower and slower. I have been charged with making it faster and 
with a smaller footprint while retaining all of the current 
functionality.  here is one of my ideas.  Please tell me if I am crazy:

The strings that we are storing (mentioned in 3 above) are extremely 
repetitive. for example, there are a limited number of permissions for 
the files in the acrobat installer and we are storing this information 
over and over again in the tables. The same goes for filenames, registry 
key names and almost all of the data we are storing.  So it seems to me 
that to create a smaller and faster database we could assign an integer 
to each string and just store the integer representation of the string 
rather than the string itself.  Then we would just store the strings in 
a separate table one time and do join queries against the tables that 
are holding the strings and the main data tables.  for example,

a table that would hold unique permissions strings would look like

table:  perms_strs

string             |  id
---------------------
'drwxr-xr-x'   |   1
'-rw-------'    |   2
'drwxrwxr-x'  |   3
'-rw-r--r--'     |   4

then in my data I would just store 1,2,3 or 4 instead of the whole 
permissions string.

it seems to me that we would save lots of space and over time not see 
the same performance degradation.

anyways,  please tell me if this makes sense and make any other 
suggestions that you can think of.  I am just now starting this analysis 
so I cannot give specifics as to where we are seeing poor performance 
just yet.  just tell me if my concepts are correct.

thanks for your time and for suffering this email.

chao,

-Shane




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: design question: general db performance
@ 2003-11-25 18:58  Jord Tanner <jord@indygecko.com>
  parent: shane hill <shill@adobe.com>
  3 siblings, 0 replies; 5+ messages in thread

From: Jord Tanner @ 2003-11-25 18:58 UTC (permalink / raw)
  To: shane hill <shill@adobe.com>; +Cc: pgsql-performance

[small chuckle]

By George, I think he's got it!

You are on the right track. Have a look at this link on database
normalization for more info:

http://databases.about.com/library/weekly/aa080501a.htm 



On Tue, 2003-11-25 at 10:42, shane hill wrote:
> Hi folks,
> 
> Disclaimer:  I am relatively new to RDBMSs, so please do not laugh at me 
> too loudly,  you can laugh, just not too loudly and please do not point. :)
> 

[snip]

-- 
Jord Tanner <jord@indygecko.com>




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: design question: general db performance
@ 2003-11-25 19:12  Josh Berkus <josh@agliodbs.com>
  parent: shane hill <shill@adobe.com>
  3 siblings, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2003-11-25 19:12 UTC (permalink / raw)
  To: shane hill <shill@adobe.com>; pgsql-performance

Shane,

> Disclaimer:  I am relatively new to RDBMSs, so please do not laugh at me 
> too loudly,  you can laugh, just not too loudly and please do not point. :)

Hey, we all started somewhere.  Nobody was born knowing databases.  Except 
maybe Neil Conway.

> I am working on an Automated Installer Testing System for Adobe Systems 
> and I am doing a DB redesign of the current postgres db:

Cool!    We're going to want to talk to you about a case study later, if you 
can get your boss to authorize it ....

> Our db is getting to be a respectable size (about 10GB right now) and is 
> growing slower and slower. 

Slower and slower?   Hmmm ... what's your VACUUM. ANALYZE & REINDEX schedule?  
What PG version?  What are your postgresql.conf settings?   Progressive 
performance loss may indicate a problem with one or more of these things ...

> then in my data I would just store 1,2,3 or 4 instead of the whole 
> permissions string.
> 
> it seems to me that we would save lots of space and over time not see 
> the same performance degradation.

Yes, this is a good idea.   Abstracting other repetitive data is good too.  
Also keep in mind that the permissions themselves can be represented as octal 
numbers instead of strings, which takes less space.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: design question: general db performance
@ 2003-11-25 19:23  Jeff <threshar@torgo.978.org>
  parent: shane hill <shill@adobe.com>
  3 siblings, 0 replies; 5+ messages in thread

From: Jeff @ 2003-11-25 19:23 UTC (permalink / raw)
  To: shane hill <shill@adobe.com>; +Cc: pgsql-performance

On Tue, 25 Nov 2003 10:42:47 -0800
shane hill <shill@adobe.com> wrote:

> Our db is getting to be a respectable size (about 10GB right now) and
> is growing slower and slower. I have been charged with making it
> faster and with a smaller footprint while retaining all of the current
> functionality.  here is one of my ideas.  Please tell me if I am
> crazy:
> 

What exactly is it getting slower doing?

Have you run through the usual gamut of things to check - shared
buffers, vacuum analyzig, etc. etc.

What ver of PG?

What OS?

Can you post any schema/queries?

Normalizing can help. But I don't think it is going to be a magical
bullet that will make the DB instantly fast.  It will reduce the size of
it though.

-- 
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: design question: general db performance
@ 2003-11-25 19:27  Richard Huxton <dev@archonet.com>
  parent: shane hill <shill@adobe.com>
  3 siblings, 0 replies; 5+ messages in thread

From: Richard Huxton @ 2003-11-25 19:27 UTC (permalink / raw)
  To: shane hill <shill@adobe.com>; pgsql-performance

On Tuesday 25 November 2003 18:42, shane hill wrote:
>
> Our db is getting to be a respectable size (about 10GB right now) and is
> growing slower and slower. I have been charged with making it faster and
> with a smaller footprint while retaining all of the current
> functionality.  here is one of my ideas.  Please tell me if I am crazy:

Your idea of using an integer makes sense - that's how it is stored on unix 
anyway.

Are you familiar with VACUUM/VACUUM FULL/REINDEX and when you should use them? 
If not, that's a good place to start. Try a VACUUM FULL on frequently updated 
tables and see if that reduces your disk size.

You'll probably want to check the performance notes too: 
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php



-- 
  Richard Huxton
  Archonet Ltd



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2003-11-25 19:27 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-25 18:42 design question: general db performance shane hill <shill@adobe.com>
2003-11-25 18:58 ` Jord Tanner <jord@indygecko.com>
2003-11-25 19:12 ` Josh Berkus <josh@agliodbs.com>
2003-11-25 19:23 ` Jeff <threshar@torgo.978.org>
2003-11-25 19:27 ` Richard Huxton <dev@archonet.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox