Re: 7.2.1 optimises very badly against 7.2 - Mailing list pgsql-general
From | Nigel J. Andrews |
---|---|
Subject | Re: 7.2.1 optimises very badly against 7.2 |
Date | |
Msg-id | Pine.LNX.4.21.0206281052070.14254-100000@ponder.fairway2k.co.uk Whole thread Raw |
In response to | 7.2.1 optimises very badly against 7.2 ("Sam Liddicott" <sam.liddicott@ananova.com>) |
List | pgsql-general |
On Fri, 28 Jun 2002, Sam Liddicott wrote: > I have a 10GB database which serves up customised tv listings selections at > www.ananova.com/tv <http://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'm thinking that you need to run some queries with explain analyze ... to see what the planner thinks it should do and how it's estimates differ from reality under both 7.2 and 7.2.1. > 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. I know nothing of the filesystem structure but that sounds odd. Like there's something introduced into 7.2.1 that's inherently slow. I assume there's also data loads over those 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). Well you'll be able to see if it's the stats. that are causing this by doing explains and comparing pg_stats for the 7.2 and pg_restored 7.2.1. You could also do the binary transfer from 7.2 to 7.2.1 again and check the explains and pg_stats immediately after and then after it's slowed down. This is probably most useful if there are _no_ data loads in the meantime. > 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] > http://www.ananova.com/tv_listings/tv_mainlisting.html?day=day1 > <http://www.ananova.com/tv_listings/tv_mainlisting.html?day=day1&start=Start > &end=End&tvregion=n_14&i=1&p=0&h=&c=12.25.36.53.54.86.33&S=1> > &start=Start&end=End&tvregion=n_14&i=1&p=0&h=&c=12.25.36.53.54.86.33&S=1 > Interesting that your stuff completes so quick normally. I worked on a large TV listings site that had complex queries and ran with Oracle. It's queries took ages to run mostly. I didn't design the system btw although I did have a hand in some lucky chap winning two or three short breaks one week when we tweaked a slow query and it turned out to be slightly more complex than we thought when we tweaked it. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants
pgsql-general by date: