Обсуждение: \d very, very, very slow
I'm seeing very, very slow response to \d <tablename> - around 15-20 seconds on an otherwise idle Netra. PostgreSQL 7.2.1 on sparc-sun-solaris2.8, compiled by cc -xarch=v9 -Xa Running psql -E shows that \d is issuing 4 queries. Picking one at random at running it through explain analyze shows three nested seqscans and a few joins, taking 6500ms. (Running the same query on a Linux box running Postgresql 7.2 with the same schema and similar postgresql.conf shows the same query plan, taking 80ms.) So is 15-20 seconds for \d unusual? If so, where should I start looking to see what's broken? Any thoughts appreciated. Cheers, Steve
Steve Atkins <steve@blighty.com> writes:
> So is 15-20 seconds for \d unusual?
Very.
> If so, where should I start looking to see what's broken?
Well, what query plans are used for the queries issued by \d on each
of your two boxes? Have you done ANALYZE or VACUUM ANALYZE lately?
(Out-of-date stats about the system tables might lead the planner to
make a stupid choice of query plan.)
regards, tom lane
On Sun, Aug 04, 2002 at 01:51:55AM -0400, Tom Lane wrote:
> Steve Atkins <steve@blighty.com> writes:
> > So is 15-20 seconds for \d unusual?
>
> Very.
>
> > If so, where should I start looking to see what's broken?
>
> Well, what query plans are used for the queries issued by \d on each
> of your two boxes? Have you done ANALYZE or VACUUM ANALYZE lately?
> (Out-of-date stats about the system tables might lead the planner to
> make a stupid choice of query plan.)
That was the problem. I'd been happily running analyze - but not as
the postgres user, so it was looking only at the user tables, not the
system tables.
The query times went from 6.5s to 1.2ms. Noticably better...
Thanks. Y'know, the help and support I've seen from Tom Lane on this
list alone exceeds everything I've ever seen from Oracle.
I'm using postgres for a long-running application[1], where I'd really
rather the application daemons don't have any access as the postgres
user. As the schema doesn't change is it safe to analyze the system
tables as the postgres user once at installation time or should I
be doing that as part of the normal cronjobbed maintenance?
Cheers,
Steve
[1] Commercial, with pretty high data rates. I'm describing it as "based
on the industry standard PostgreSQL database" to customers and'll be
adding it to the list of PostrgreSQL apps once the press releases
go out.
Steve Atkins <steve@blighty.com> writes:
> I'm using postgres for a long-running application[1], where I'd really
> rather the application daemons don't have any access as the postgres
> user. As the schema doesn't change is it safe to analyze the system
> tables as the postgres user once at installation time or should I
> be doing that as part of the normal cronjobbed maintenance?
If you're not doing any schema changes then the ANALYZE stats for the
system tables shouldn't need updates, probably. On the other hand,
I'm not sure what it buys you to avoid it. Running "vacuum analyze"
as the postgres user from a cron job is standard practice in my book.
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: 04 August 2002 06:52 > To: Steve Atkins > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] \d very, very, very slow > > > Steve Atkins <steve@blighty.com> writes: > > So is 15-20 seconds for \d unusual? > > Very. > > > If so, where should I start looking to see what's broken? > > Well, what query plans are used for the queries issued by \d on each > of your two boxes? Have you done ANALYZE or VACUUM ANALYZE lately? > (Out-of-date stats about the system tables might lead the planner to > make a stupid choice of query plan.) We find that sometimes pgadmin takes this long to show us DB details when we double click on a DB and we vacuum analyse every night. This ispostgres 7.2.1 Sam
Howdy, I would like to evaluate postgresql. I want to install the RPM version on a redhat 7.1 server. I looked at the files at http://planetmirror.com/pub/postgresql/binary/v7.2.1/RPMS/ but I can't tell which files I need. And if they are for an intel linux why do they have the name sparc appended at the end? Thanks for your help, Jeff
I'd like to direct you to rpmfind.net, here's a link directly to postgres: http://rpmfind.net//linux/RPM/PLD/dists/ra/PLD/i386/PLD/RPMS/postgresql-7.2.1-9.i386.html I am not much up to date on RPMs any more (switched to debian a while back). So the only advice I can give you is install that package, and if RPM complains about missing packages, download the missing ones and install those too. After it's all set up, use the command-line utilities like createdb and psql. Have fun! Regards, Jeff Davis On Monday 05 August 2002 01:07 am, jeff wrote: > Howdy, > > I would like to evaluate postgresql. I want to install the RPM version > on a redhat 7.1 server. I looked at the files at > http://planetmirror.com/pub/postgresql/binary/v7.2.1/RPMS/ but I can't > tell which files I need. And if they are for an intel linux why do they > have the name sparc appended at the end? > > > Thanks for your help, > > Jeff > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Monday 05 August 2002 04:07 am, jeff wrote: > I would like to evaluate postgresql. I want to install the RPM version > on a redhat 7.1 server. I looked at the files at > http://planetmirror.com/pub/postgresql/binary/v7.2.1/RPMS/ but I can't > tell which files I need. You will have to rebuild from the source RPM for Red Hat 7.1 for Intel. Grab the .src.rpm out of SRPMs. You will then need to 'rpm --rebuild' (as root) that src.rpm. If you get errors and it won't build, you need to make sure that you have a sufficient development system installed -- IIRC, with Red Hat 7.1 you will need to manually install the python-devel package. You must have X development installed as well. You really need to read the README.rpm-dist file -- you can see it my doing 'rpm -i postgresql-7.2.1-1PGDG.src.rpm' and navigating to /usr/src/redhat/SOURCES. > And if they are for an intel linux why do they > have the name sparc appended at the end? Because they are for a _SPARC_ Linux, not Intel. The only machines I still have running anything older than Red Hat 7.3 is a pair of SPARC's running Red Hat 6.2, and since I needed to test the buildability of the source RPM on a Red Hat 6.2 machine of any flavor, I built the SPARC set. But whatever you do don't try to install the RPM that came with Red Hat 7.3 (which is what you will find off of rpmfind.net), unless you want to do an equivalent of upgrading to Red Hat 7.3 ahead of time. Although the frequency of this request has gotten me to thinking about new directions in my RPM maintenance. Red Hat is doing a great job of keeping their latest distribution populated with the latest version of PostgreSQL, but older dists are getting left out. I will investigate what it will take to support older versions in terms of disk space, and will see what I can do to alleviate the situation. I will have to acquire some more hard disks, however. If anyone wants to donate a 4-5 GB IDE hard drive to the cause (or a similar-sized SCSI drive, wide, narrow, or ultra) I have a machine to build it upon. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
"Lamar Owen" <lamar.owen@wgcr.org> wrote in message news:200208051058.53834.lamar.owen@wgcr.org... > On Monday 05 August 2002 04:07 am, jeff wrote: > > I would like to evaluate postgresql. I want to install the RPM version > > on a redhat 7.1 server. I looked at the files at > > http://planetmirror.com/pub/postgresql/binary/v7.2.1/RPMS/ but I can't > > tell which files I need. > > You will have to rebuild from the source RPM for Red Hat 7.1 for Intel. Grab > the .src.rpm out of SRPMs. You will then need to 'rpm --rebuild' (as root) > that src.rpm. If you get errors and it won't build, you need to make sure > that you have a sufficient development system installed -- IIRC, with Red Hat > 7.1 you will need to manually install the python-devel package. You must > have X development installed as well. > > You really need to read the README.rpm-dist file -- you can see it my doing > 'rpm -i postgresql-7.2.1-1PGDG.src.rpm' and navigating to > /usr/src/redhat/SOURCES. > > > And if they are for an intel linux why do they > > have the name sparc appended at the end? > > Because they are for a _SPARC_ Linux, not Intel. The only machines I still > have running anything older than Red Hat 7.3 is a pair of SPARC's running Red > Hat 6.2, and since I needed to test the buildability of the source RPM on a > Red Hat 6.2 machine of any flavor, I built the SPARC set. > > But whatever you do don't try to install the RPM that came with Red Hat 7.3 > (which is what you will find off of rpmfind.net), unless you want to do an > equivalent of upgrading to Red Hat 7.3 ahead of time. > > Although the frequency of this request has gotten me to thinking about new > directions in my RPM maintenance. Red Hat is doing a great job of keeping > their latest distribution populated with the latest version of PostgreSQL, > but older dists are getting left out. I will investigate what it will take > to support older versions in terms of disk space, and will see what I can do > to alleviate the situation. I will have to acquire some more hard disks, > however. If anyone wants to donate a 4-5 GB IDE hard drive to the cause (or > a similar-sized SCSI drive, wide, narrow, or ultra) I have a machine to build > it upon. > -- > Lamar Owen > WGCR Internet Radio > 1 Peter 4:11 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org Would it be possible to put build instructions on the PostgreSQL web site for those of us with older releases? My first experience with rebuilding sources with with my upgrade to 7.2 on RedHat 7.1 and while it wasn't difficult, finding a simple set of instructions was. Rob