RE: Re: DB porting questions... - Mailing list pgsql-sql

Yes, it is being used to store/analyze a high-traffic log.  I have a table
for each day.  I keep about 30-45 days worth of data.  At the end of 45
days, I'm going to dump the data to a directory that gets backed up to tape,
then I simply drop the table.

I had to turn indexing off because I was unable to insert fast enough to
keep up with the data source.  I also had to do a copy into instead of a
insert into.  I still have to option of indexing the older day's tables.  

Querying the entire database is difficult, and very important to me.  I
tried to setup a "view of a union" scheme.  That isn't supported.  Right now
I am using a perl function to rewrite my sql in such a way that it queries
any of the tables I want and coelesces the output.  Aggregate functions even
work with this method.

I'm in the process of expanding from 115Gb to 362Gb of drive space.  This
will help... <grin>

If you have any other questions, please feel free.

Mike Diehl. 

-----Original Message-----
From: Rick Robino
To: Diehl, Jeffrey
Sent: 4/18/2001 1:35 PM
Subject: Re: [SQL] Re: DB porting questions...

Jeffrey,

I was interested in your comment below which says one of your db's gets
5
million inserts a day. I was wondering, are those rows cumulative? I get
this
picture in my head that you have a few tables in this database and that
the
whole thing is growing _alot_ every day.

Or maybe these daily inserts get transferred or concentrated... I ask
because I
have seen many people apparently making databases to take high-traffic
logfiles
as input. I've always thought this interesting, but didn't think that
pgsql (or
any db) would be in very good shape after a "bazillion" rows added up at
the end
of a month, or quarter, etc.

Just curious. Cheers,

--Rick

"Diehl, Jeffrey" wrote:

> Well, I'm glad that my efforts to climb the learning curve are helping
> others... <grin>
>
> I'm not aware of any scripts which will implement sets in psql.  But,
the
> underlying implementation of a set is quite simple and could be done
in a
> pl/sql function or application code.
>
> Conceptually, you assign numeric values to that attributes in the set:
> red=1, white=2, blue=4, plaid=8, etc.  Then you add the values of the
> attributes which are in the set.
>
> Var = red + white = 1 + 2 = 3
>
> Write a function which tests if a given attribute is in the set...
>
> If you need more, lemme know, I'll try to help.
>
> BTW, I have a psql database which gets more than 5 Million inserts a
day.
> Is there anyone with a larger database?  Is there any interest in
comments
> on running such a large database.  No, I can't tell you what it does
in much
> detail.
>
> Take care,
> Mike Diehl.
>
> -----Original Message-----
> From: Vivek Khera
> To: pgsql-sql@postgresql.org
> Sent: 4/17/2001 9:45 AM
> Subject: [SQL] Re: DB porting questions...
>
> >>>>> "JD" == Jeffrey Diehl <jdiehl@sandia.gov> writes:
>
> JD> I'm in the final stages of migrating from mysql to postgres and
have
> a few
> JD> more questions...
>
> I'm just starting, but I've got two questions.  I've found some
> scripts out there that claim to do the conversion of the SQL create
> commands, but none does the right thing it seems.
>
> I've now found out how to handle the timestamp for insert times and
> how to do auto-increment fields.
>
> My unsderstanding of MySQL's enum type is to use something like this
> in postgres:
>
>  owner_status varchar(9) check
>         (owner_status in ('pending','active','suspended'))
>         NOT NULL default 'pending',
>
> But how does one handle the "set" dataype?  The archive for the
> mailing lists is not helping me find out how to deal with that.
>
> Basically, I have a field with a bunch of flags defining the
> attributes of a user, and storing that in a bit-field makes sense.
> Currently in MySQL I have this:
>
>  owner_features set('premium','haveccinfo') default NULL,
>
> for example.  Some other fiels may have about 20 such values, and
> MySQL lets me keep these in 3 bytes as a bit-field behind the scenes.
>
> >From what I see, my choice in Postgres is to store this as a
> comma-separated string and let my application work as before.
>
> Does anyone have a script that actually handles properly doing auto
> increments with the SERIAL type, and does the set/enum conversions?
>
> --
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Vivek Khera, Ph.D.                Khera Communications, Inc.
> Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
> AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster<<Card for Rick Robino>>