Manfred Koizar wrote:
>>begin;
>>update t set val=val+1; -- 1000 times
>>commit;
>>
>>How many record versions does it create? 1 or 1000?
>
> 1000
>
>>I'm implementing a
>>banner counter which is incremented at least 2-3 millions a day.
>
> How many rows? I would VACUUM that table after every few hundred
> updates or whenever 10% to 20% of the rows have been updated, whichever
> is greater.
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". 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 (= a "transaction" in MyISAM, since MyISAM doesn't support
BEGIN + COMMIT).
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.
--
dave