I have a 10GB database which serves up customised tv listings selections at www.ananova.com/tv (see "Personalise Listings" in the left column)
We had it running very well under postgres 7.2 on a 4xPentium 700mhz with 8GB RAM
For a personalised selection from "start" to "end" of 7 channels [url1 below]
takes a fraction of a second to do the query and if I'm lucky enough to spot it on "top" it uses low CPU percentage.
Under 7.2.1 it takes 99% CPU for between 5-9 seconds.
Rolling back to 7.2 is also very slow unless we vacuum analyse after rolling back, then it is very fast again.
[We normally vacuum analyse every 24 hours]
We have max connections=128, 4520 shared buffers 91268 sortmem and collect row level and block level stats.
I also have this anecodotal information;
we installed 7.2.1 on a fresh box and then restored a pg_dump of the tv database and found it always very slow, stopping postgres and transferring the binary DB files and restarting was very fast but degraded slowly over a few days.
So I imagine there is something about stats gathering and use changed with 7.2.1 (I hear it has a new optimiser).
The query we do is complex and really does need a good optimiser (why we don't use mysql) as it has to join programmes against broadcasts (restricted by time) to channels (restricted to interested channels). It has to be careful not to initially start with all broadcasts of a interested channel as well as not all broadcasts on the interested channels.
[url1, 7 channels from midnight to about 6:00am day after]
Samuel Liddicott
Support Consultant
sam@ananova.com
Direct Dial: +44 (0)113 367 4523
Fax: +44 (0)113 367 4680
Switchboard: +44 (0)113 367 4600Ananova Limited
Marshall Mill
Marshall Street
Leeds
LS11 9YJhttp://www.ananova.com
Registered Office:
St James Court
Great Park Road
Almondsbury Park
Bradley Stoke
Bristol BS32 4QJ
Registered in England No.2858918
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer.
