pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feeddesign 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