From: | Manfred Koizar <mkoi-pg(at)aon(dot)at> |
---|---|
To: | David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: how many record versions |
Date: | 2004-05-23 18:11:58 |
Message-ID: | 9ep1b09r9c21dbjcf3au5747h67i7gnahk@email.aon.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, 23 May 2004 23:32:48 +0700, David Garamond
<lists(at)zara(dot)6(dot)isreserved(dot)com> wrote:
>Actually, each record will be incremented probably only thousands of
>times a day. But there are many banners. Each record has a (bannerid,
>campaignid, websiteid, date, countrycode) "dimensions" and (impression,
>click) "measures".
If you need all of bannerid, campaignid, websiteid, date, countrycode to
identify a row, it may be worth the effort to split this up into two
tables:
CREATE TABLE dimensions (
dimensionid int PRIMARY KEY,
bannerid ...,
campaignid ...,
websiteid ...,
date ...,
countrycode ...,
UNIQUE (bannerid, ..., countrycode)
);
CREATE TABLE measures (
dimensionid int PRIMARY KEY REFERENCES dimensions,
impression ...,
click ...
);
Thus you'd only update measures thousands of times and the index would
be much more compact, because the PK is only a four byte integer.
> The table currently has +- 1,5-2 mil records (it's in
>MyISAM MySQL), so I'm not sure if I can use that many sequences which
>Tom suggested. Every impression (banner view) and click will result in a
>SQL statement
Schedule a
VACUUM ANALYSE measures;
for every 100000 updates or so.
>I'm contemplating of moving to Postgres, but am worried with the MVCC
>thing. I've previously tried briefly using InnoDB in MySQL but have to
>revert back to MyISAM because the load increased significantly.
You mean InnoDB cannot handle the load?
Servus
Manfred
From | Date | Subject | |
---|---|---|---|
Next Message | Özgür Çetiner | 2004-05-23 18:58:15 | MySQL Conversion |
Previous Message | David Garamond | 2004-05-23 16:32:48 | Re: how many record versions |