Thread: identifying performance hits: how to ???
Hello All, Anyone know if read performance on a postgres database decreases at an increasing rate, as the number of stored records increase? This is a TCL app, which makes entries into a single, table and from time to time repopulates a grid control. It must rebuild the data in the grid control, because other clients have since written to the same table. It seems as if I'm missing something fundamental... maybe I am... is some kind of database cleanup necessary? With less than ten records, the grid populates very quickly. Beyond that, performance slows to a crawl, until it _seems_ that every new record doubles the time needed to retrieve the records. My quick fix was to cache the data locally in TCL, and only retrieve changed data from the database. But now as client demand increases, as well as the number of clients making changes to the table, I'm reaching the bottleneck again. The client asked me yesterday to start evaluating "more mainstream" databases, which means that they're pissed off. Postgres is fun to work with, but it's hard to learn about, and hard to justify to clients. By the way, I have experimented with populating the exact same grid control on Windows NT, using MS Access (TCL runs just about anywhere). The grid seemed to populate just about instantaneously. So, is the bottleneck in Unix, in Postgres, and does anybody know how to make it faster? Cheers, Rob
> Anyone know if read performance on a postgres database decreases at > an increasing rate, as the number of stored records increase? > > It seems as if I'm missing something fundamental... maybe I am... is > some kind of database cleanup necessary? With less than ten > records, the grid populates very quickly. Beyond that, performance > slows to a crawl, until it _seems_ that every new record doubles the > time needed to retrieve... Are you using indexes? Are you vacuuming? I may have incorrectly inferred table sizes and such, but the behavior you describe seems odd - we typically work with hundreds of thousands of entries in our tables with good results (though things do slow down for the one DB we use with tens of millions of entries). > The client asked me yesterday to start evaluating "more mainstream" > databases, which means that they're pissed off. Postgres is fun to > work with, but it's hard to learn about, and hard to justify to > clients. As for using a 'more mainstream' app, of course there's always that pressure. FWIW, we have done well with the product so far. In about a year of use for important (maybe even 'mission-critical') purposes, we have only had one problem that was not easily solved ourselves. And Postgresql, Inc. solved that one for us. With alot less aggravation than most of our 'mainstream' vendors when we have a problem involving their software. -- Karl DeBisschop <kdebisschop@alert.infoplease.com> 617.832.0332 (Fax: 617.956.2696) Information Please - your source for FREE online reference http://www.infoplease.com - Your Ultimate Fact Finder http://kids.infoplease.com - The Great Homework Helper Netsaint Plugins Development http://netsaintplug.sourceforge.net
Have/do you perform reasonably regular vacuum's of the database?
Do you make use of indices to increase SELECT/UPDATE performance?
Have you checked out your queries using psql+EXPLAIN, to see that said
indices are being used?
What operating system are you using? hardware?
How are you starting up the postmaster?
On Wed, 12 Jan 2000, Robert Wagner wrote:
> Hello All,
>
> Anyone know if read performance on a postgres database decreases at an
> increasing rate, as the number of stored records increase?
>
> This is a TCL app, which makes entries into a single, table and from time
> to time repopulates a grid control. It must rebuild the data in the grid
> control, because other clients have since written to the same table.
>
> It seems as if I'm missing something fundamental... maybe I am... is some
> kind of database cleanup necessary? With less than ten records, the grid
> populates very quickly. Beyond that, performance slows to a crawl, until
> it _seems_ that every new record doubles the time needed to retrieve the
> records. My quick fix was to cache the data locally in TCL, and only
> retrieve changed data from the database. But now as client demand
> increases, as well as the number of clients making changes to the table,
> I'm reaching the bottleneck again.
>
> The client asked me yesterday to start evaluating "more mainstream"
> databases, which means that they're pissed off. Postgres is fun to work
> with, but it's hard to learn about, and hard to justify to clients.
>
> By the way, I have experimented with populating the exact same grid control
> on Windows NT, using MS Access (TCL runs just about anywhere). The grid
> seemed to populate just about instantaneously. So, is the bottleneck in
> Unix, in Postgres, and does anybody know how to make it faster?
>
> Cheers,
> Rob
>
>
>
> ************
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
On Wed, Jan 12, 2000 at 12:23:23PM -0400, The Hermit Hacker wrote:
> Have/do you perform reasonably regular vacuum's of the database?
on my databases, i have only been successful in doing a "VACUUM VERBOSE" on
my tables.
i suspect i've got the syntax wrong or something:
nagoss=> \h vacuum
Command: vacuum
Description: vacuum the database, i.e. cleans out deleted records, updates statistics
Syntax:
VACUUM [VERBOSE] [ANALYZE] [table]
or
VACUUM [VERBOSE] ANALYZE [table [(attr1, ...attrN)]];
nagoss=> vacuum verbose analyse switches;
ERROR: parser: parse error at or near "switches"
nagoss=> vacuum verbose switches;
NOTICE: --Relation switches--
NOTICE: Pages 1: Changed 1, Reapped 0, Empty 0, New 0; Tup 9: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 78,
MaxLen78; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
NOTICE: Index switch_name: Pages 2; Tuples 9. Elapsed 0/0 sec.
VACUUM
what is the correct syntax for doing a vacuum analyse?
--
[ Jim Mercer jim@reptiles.org +1 416 506-0654 ]
[ Reptilian Research -- Longer Life through Colder Blood ]
[ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]
On Wed, 12 Jan 2000, Karl DeBisschop wrote: > > > Anyone know if read performance on a postgres database decreases at > > an increasing rate, as the number of stored records increase? > > > > It seems as if I'm missing something fundamental... maybe I am... is > > some kind of database cleanup necessary? With less than ten > > records, the grid populates very quickly. Beyond that, performance > > slows to a crawl, until it _seems_ that every new record doubles the > > time needed to retrieve... > > Are you using indexes? > > Are you vacuuming? > > I may have incorrectly inferred table sizes and such, but the behavior > you describe seems odd - we typically work with hundreds of thousands > of entries in our tables with good results (though things do slow down > for the one DB we use with tens of millions of entries). An example of a large database that ppl can see in action...the search engine we are using on PostgreSQL, when fully populated, works out to around 6million records... and is reasnably quick...
On Wed, 12 Jan 2000, Jim Mercer wrote:
> On Wed, Jan 12, 2000 at 12:23:23PM -0400, The Hermit Hacker wrote:
> > Have/do you perform reasonably regular vacuum's of the database?
>
> on my databases, i have only been successful in doing a "VACUUM VERBOSE" on
> my tables.
>
> i suspect i've got the syntax wrong or something:
>
> nagoss=> \h vacuum
> Command: vacuum
> Description: vacuum the database, i.e. cleans out deleted records, updates statistics
> Syntax:
> VACUUM [VERBOSE] [ANALYZE] [table]
> or
> VACUUM [VERBOSE] ANALYZE [table [(attr1, ...attrN)]];
>
> nagoss=> vacuum verbose analyse switches;
> ERROR: parser: parse error at or near "switches"
> nagoss=> vacuum verbose switches;
> NOTICE: --Relation switches--
> NOTICE: Pages 1: Changed 1, Reapped 0, Empty 0, New 0; Tup 9: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 78,
MaxLen78; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
> NOTICE: Index switch_name: Pages 2; Tuples 9. Elapsed 0/0 sec.
> VACUUM
>
> what is the correct syntax for doing a vacuum analyse?
I've generally just done a 'vacuum verbose analyze' and do the whole
database ...
But, just tried it on a table and:
udmsearch=> vacuum verbose analyze url;
NOTICE: --Relation url--
NOTICE: Pages 240: Changed 2, Reapped 213, Empty 0, New 0; Tup 2740: Vac 5648, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen
120,MaxLen 616; Re-using: Free/Avail. Space 813836/811912; EndEmpty/Avail. Pages 0/212. Elapsed 0/0 sec.
NOTICE: Index url_next_index_time: Pages 35; Tuples 2740: Deleted 5648. Elapsed 0/0 sec.
NOTICE: Index url_crc: Pages 34; Tuples 2740: Deleted 5648. Elapsed 0/0 sec.
NOTICE: Index url_url: Pages 90; Tuples 2740: Deleted 4973. Elapsed 0/0 sec.
NOTICE: Index url_pkey: Pages 25; Tuples 2740: Deleted 4973. Elapsed 0/0 sec.
NOTICE: Rel url: Pages: 240 --> 142; Tuple(s) moved: 1486. Elapsed 0/0 sec.
NOTICE: Index url_next_index_time: Pages 38; Tuples 2740: Deleted 1486. Elapsed 0/0 sec.
NOTICE: Index url_crc: Pages 46; Tuples 2740: Deleted 1486. Elapsed 0/0 sec.
NOTICE: Index url_url: Pages 90; Tuples 2740: Deleted 1486. Elapsed 0/0 sec.
NOTICE: Index url_pkey: Pages 26; Tuples 2740: Deleted 1486. Elapsed 0/0 sec.
VACUUM
udmsearch=>
What version of PostgreSQL are you using?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
Hi Jim, >>>>> "J" == Jim Mercer <jim@reptiles.org> writes: J> i suspect i've got the syntax wrong or something: Good suspicion. J> nagoss=> \h vacuum J> Command: vacuum J> Description: vacuum the database, i.e. cleans out deleted records, updates statistics J> Syntax: J> VACUUM [VERBOSE] [ANALYZE] [table] J> or J> VACUUM [VERBOSE] ANALYZE [table [(attr1, ...attrN)]]; J> what is the correct syntax for doing a vacuum analyse? The correct syntax as shown in the help message is 'vacuum analyze'. Notice the alternative spelling of analyse with a 'z' not an 's'. Hope you have a very nice day, :-) Tim Ayers (tayers@bridge.com) Norman, Oklahoma
On Wed, Jan 12, 2000 at 01:09:08PM -0500, tayers@bridge.com wrote: > J> nagoss=> \h vacuum > J> Command: vacuum > J> Description: vacuum the database, i.e. cleans out deleted records, updates statistics > J> Syntax: > J> VACUUM [VERBOSE] [ANALYZE] [table] > J> or > J> VACUUM [VERBOSE] ANALYZE [table [(attr1, ...attrN)]]; > > J> what is the correct syntax for doing a vacuum analyse? > > The correct syntax as shown in the help message is > 'vacuum analyze'. Notice the alternative spelling of analyse with a > 'z' not an 's'. > > Hope you have a very nice day, :-) [ picture of jim slapping himself in the forehead ] geez. i know it was right in front of me, but, grrrr. [ in his best homer simpson voice ] Doh! -- [ Jim Mercer jim@reptiles.org +1 416 506-0654 ] [ Reptilian Research -- Longer Life through Colder Blood ] [ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]
FAQ items 3.10 and 4.9 might give you a running start. On 2000-01-12, Robert Wagner mentioned: > Hello All, > > Anyone know if read performance on a postgres database decreases at an > increasing rate, as the number of stored records increase? > > This is a TCL app, which makes entries into a single, table and from time > to time repopulates a grid control. It must rebuild the data in the grid > control, because other clients have since written to the same table. > > It seems as if I'm missing something fundamental... maybe I am... is some > kind of database cleanup necessary? With less than ten records, the grid > populates very quickly. Beyond that, performance slows to a crawl, until > it _seems_ that every new record doubles the time needed to retrieve the > records. My quick fix was to cache the data locally in TCL, and only > retrieve changed data from the database. But now as client demand > increases, as well as the number of clients making changes to the table, > I'm reaching the bottleneck again. > > The client asked me yesterday to start evaluating "more mainstream" > databases, which means that they're pissed off. Postgres is fun to work > with, but it's hard to learn about, and hard to justify to clients. > > By the way, I have experimented with populating the exact same grid control > on Windows NT, using MS Access (TCL runs just about anywhere). The grid > seemed to populate just about instantaneously. So, is the bottleneck in > Unix, in Postgres, and does anybody know how to make it faster? > > Cheers, > Rob > > > > ************ > > -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden