pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed some info on Postgresql
9+ messages / 2 participants
[nested] [flat]
* Need some info on Postgresql
@ 2008-01-03 11:33 Suresh Gupta VG <suresh.g@zensar.com>
0 siblings, 1 reply; 9+ messages in thread
From: Suresh Gupta VG @ 2008-01-03 11:33 UTC (permalink / raw)
To: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin
Hi Team,
I am using Postgresql 7.4.2 version on Solaris. There are number of
tables say about 30+ tables in our database. I started to "reindex" the
tables individually. "reindex table <<table name>>". All the queries
executed normally with less than 1 minute of duration. But one table is
not responding any thing even after 10 minutes. The details of the table
are ...
1) it had 5 column, out of it, one is primary key.
2) There are only 5 rows in it.
3) see this entry for index on that table
Schema | Name | Type |
Owner | Table
------------+------------------------------+---------+----------+-------
---------------
public | currency_pkey | index |
pgsql | currency
Can you please tell me why it not executing this command on the
particular table and I am the owner of the DB? Can you please suggest
some thing. We found these days, the database is taking more time to
execute any query. We are doing "vacuum Verbose analyze" regularly at
low traffic time.
Regards,
G. V. Suresh Gupta
------------------------------------------------------------------------
---------------------------------------------------------
Innovative Technology Solutions(ITS), Zensar Technologies
Zensar Knowledge Park, Plot#5, MIDC IT Tower,
Kharadi, Off Nagar Road, Pune - 411014
Landline : +91-20-66453471 | +91-9890898688
Email : suresh.g@zensar.com | website: www.zensar.com
<http://www.zensar.com/;
________________________________
From: Peter Koczan [mailto:pjkoczan@gmail.com]
Sent: Sunday, November 04, 2007 5:09 AM
To: Suresh Gupta VG
Cc: pgsql-admin@postgresql.org
Subject: Re: Postgresql pg_dumpall
DISCLAIMER:
This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails.
Attachments:
image001.jpg (image/jpeg, 3.1K) download
view image
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need some info on Postgresql
@ 2008-01-04 02:23 Scott Marlowe <scott.marlowe@gmail.com>
parent: Suresh Gupta VG <suresh.g@zensar.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2008-01-04 02:23 UTC (permalink / raw)
To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin
On Jan 3, 2008 5:33 AM, Suresh Gupta VG <suresh.g@zensar.com> wrote:
>
> Hi Team,
>
> I am using Postgresql 7.4.2 version on Solaris.
You need to update to 7.4.18 or whatever the last version was. 7.4.2
has known data eating bugs, and if you value your data even a little,
you should update. this is a relatively painless update not requiring
a dump / restore.
> There are number of tables
> say about 30+ tables in our database. I started to "reindex" the tables
> individually. "reindex table <<table name>>". All the queries executed
> normally with less than 1 minute of duration. But one table is not
> responding any thing even after 10 minutes. The details of the table are …
I've had large tables take well over 10 minutes on 7.4 to reindex.
That's not necessarily a sign of a problem. How many rows does this
table have? Do you get any kind of error message or the database just
never returns. Have you tried vacuum full on this table instead? Do
the logs say anything about the reindex operation getting an error
etc???
> Can you please tell me why it not executing this command on the particular
> table and I am the owner of the DB? Can you please suggest some thing.
Is it really not executing? Is it simply returning without a notice,
or hanging?
> We
> found these days, the database is taking more time to execute any query. We
> are doing "vacuum Verbose analyze" regularly at low traffic time.
You would do well to consider a migration to 8.2.5 or 8.3 when it
comes out of beta / RC status. 7.4.x is getting rather old, and the
8.x series (8.0, 8.1, 8.2, and now 8.3) have each had major
improvements in speed.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need some info on Postgresql
@ 2008-01-04 06:41 Suresh Gupta VG <suresh.g@zensar.com>
0 siblings, 1 reply; 9+ messages in thread
From: Suresh Gupta VG @ 2008-01-04 06:41 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin
Hi Scott,
Thanks for your reply and pls see my inline comments in red color.
Regards,
G. V. Suresh Gupta
------------------------------------------------------------------------
---------------------------------------------------------
Innovative Technology Solutions(ITS), Zensar Technologies
Zensar Knowledge Park, Plot#5, MIDC IT Tower,
Kharadi, Off Nagar Road, Pune - 411014
Landline : +91-20-66453471 | +91-9890898688
Email : suresh.g@zensar.com | website: www.zensar.com
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Friday, January 04, 2008 7:53 AM
To: Suresh Gupta VG
Cc: Peter Koczan; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Need some info on Postgresql
On Jan 3, 2008 5:33 AM, Suresh Gupta VG <suresh.g@zensar.com> wrote:
>
> Hi Team,
>
> I am using Postgresql 7.4.2 version on Solaris.
You need to update to 7.4.18 or whatever the last version was. 7.4.2
has known data eating bugs, and if you value your data even a little,
you should update. this is a relatively painless update not requiring
a dump / restore.
Suresh : Can you pls tell is 7.4.2 is latest or 7.4.18 is latest? We
will plan to upgrade to 8.2.5.
> There are number of tables
> say about 30+ tables in our database. I started to "reindex" the
tables
> individually. "reindex table <<table name>>". All the queries executed
> normally with less than 1 minute of duration. But one table is not
> responding any thing even after 10 minutes. The details of the table
are ...
I've had large tables take well over 10 minutes on 7.4 to reindex.
That's not necessarily a sign of a problem. How many rows does this
table have? Do you get any kind of error message or the database just
never returns. Have you tried vacuum full on this table instead? Do
the logs say anything about the reindex operation getting an error
etc???
Suresh : This table contains only 5 columns and 5 rows of data. I don't
get any error message instead it remains un answered, I am using "ctrl
C" to cancel the command. I also had done the vacuum on this table, pls
see the result below. Pls give the general path of log files available
in the database. I don't have idea of this logs files of PGSQL.
commercelink=# vacuum verbose analyze currency;
INFO: vacuuming "public.currency"
INFO: index "currency_pkey" now contains 5 row versions in 2 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "currency": found 0 removable, 5 nonremovable row versions in 1
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 3 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.currency"
INFO: "currency": 1 pages, 5 rows sampled, 5 estimated total rows
VACUUM
commercelink=# reindex table currency;
^CCancel request sent
ERROR: canceling query due to user request
commercelink=#
> Can you please tell me why it not executing this command on the
particular
> table and I am the owner of the DB? Can you please suggest some thing.
Is it really not executing? Is it simply returning without a notice,
or hanging?
Suresh : It is not executing at all. It is not hanging, I am canceling
the query by giving "ctrl c". Then it is giving me the database prompt
as usual.
> We
> found these days, the database is taking more time to execute any
query. We
> are doing "vacuum Verbose analyze" regularly at low traffic time.
You would do well to consider a migration to 8.2.5 or 8.3 when it
comes out of beta / RC status. 7.4.x is getting rather old, and the
8.x series (8.0, 8.1, 8.2, and now 8.3) have each had major
improvements in speed.
Suresh : We will plan to upgrade to 8.X. What is this beta / RC status,
can you brief me pls?
DISCLAIMER:
This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need some info on Postgresql
@ 2008-01-04 10:40 Scott Marlowe <scott.marlowe@gmail.com>
parent: Suresh Gupta VG <suresh.g@zensar.com>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Marlowe @ 2008-01-04 10:40 UTC (permalink / raw)
To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin
On Jan 4, 2008 12:41 AM, Suresh Gupta VG <suresh.g@zensar.com> wrote:
>
> Hi Scott,
>
> Thanks for your reply and pls see my inline comments in red color.
> I am using Postgresql 7.4.2 version on Solaris.
>
>
>
> You need to update to 7.4.18 or whatever the last version was. 7.4.2
>
> has known data eating bugs, and if you value your data even a little,
>
> you should update. this is a relatively painless update not requiring
>
> a dump / restore.
>
>
>
> Suresh : Can you pls tell is 7.4.2 is latest or 7.4.18 is latest? We will
> plan to upgrade to 8.2.5.
http://www.postgresql.org/ says the latest minor update to the 7.4
branch is 7.4.18. You are missing over two years of bug fixes by
running 7.4.2. updating to 7.4.2 will take minutes.
Migrating to 8.2.5 requires you to test your applications against
8.2.5 then dump and restore your database. Update 7.4 first. Then
plan a migration.
> > There are number of tables
> > say about 30+ tables in our database. I started to "reindex" the tables
> > individually. "reindex table <<table name>>". All the queries executed
> > normally with less than 1 minute of duration. But one table is not
> > responding any thing even after 10 minutes. The details of the table are …
>
> I've had large tables take well over 10 minutes on 7.4 to reindex.
> That's not necessarily a sign of a problem. How many rows does this
> table have? Do you get any kind of error message or the database just
> never returns. Have you tried vacuum full on this table instead? Do
> the logs say anything about the reindex operation getting an error
> etc???
>
>
> Suresh : This table contains only 5 columns and 5 rows of data. I don't get
> any error message instead it remains un answered, I am using "ctrl C" to
> cancel the command. I also had done the vacuum on this table, pls see the
> result below. Pls give the general path of log files available in the
> database. I don't have idea of this logs files of PGSQL.
If it's hanging with only 5 rows then you've likely that reindex can't
get some kind of lock it needs. I'm not sure if 7.4 has the same
views as 8.2 so I'm not sure if pg_locks has the answer or not. while
your reindex is trying to run, in another shell, in psql to your db,
type in
select * from pg_locks;
No idea where your log files are, they change based on how pgsql was installed.
> You would do well to consider a migration to 8.2.5 or 8.3 when it
> comes out of beta / RC status. 7.4.x is getting rather old, and the
> 8.x series (8.0, 8.1, 8.2, and now 8.3) have each had major
> improvements in speed.
>
> Suresh : We will plan to upgrade to 8.X. What is this beta / RC status, can
> you brief me pls?
It's due out this year, most likely before spring.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need some info on Postgresql
@ 2008-01-04 10:49 Scott Marlowe <scott.marlowe@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2008-01-04 10:49 UTC (permalink / raw)
To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin
On Jan 4, 2008 4:40 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> http://www.postgresql.org/ says the latest minor update to the 7.4
> branch is 7.4.18. You are missing over two years of bug fixes by
> running 7.4.2. updating to 7.4.2 will take minutes.
Correction, should be "updating to 7.4.18 will take minutes."
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need some info on Postgresql
@ 2008-01-04 16:33 Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2008-01-04 16:33 UTC (permalink / raw)
To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin
On Jan 4, 2008 5:14 AM, Suresh Gupta VG <suresh.g@zensar.com> wrote:
>
> Hi Scott,
>
> Thanks for your reply.
>
> I got the result for the query select * from pg_locks; as below. Can you
> explain me the output please.
>
> relation | database | transaction | pid | mode | granted
>
> ----------+----------+-------------+-------+-----------------+---------
> | | 7784829 | 21222 | ExclusiveLock | t
> 87441 | 87343 | | 21173 | AccessShareLock | t
> 87441 | 87343 | | 21044 | AccessShareLock | t
> 87441 | 87343 | | 21209 | AccessShareLock | t
> | | 7782935 | 20942 | ExclusiveLock | t
> | | 7784034 | 21106 | ExclusiveLock | t
> | | 7784091 | 21112 | ExclusiveLock | t
> 87441 | 87343 | | 21197 | AccessShareLock | t
> | | 7783246 | 20989 | ExclusiveLock | t
> 87441 | 87343 | | 21081 | AccessShareLock | t
> | | 7783542 | 21034 | ExclusiveLock | t
> | | 7782957 | 20944 | ExclusiveLock | t
> | | 7785128 | 21268 | ExclusiveLock | t
> 87441 | 87343 | | 21228 | AccessShareLock | t
> 87441 | 87343 | | 20971 | AccessShareLock | t
> | | 7783524 | 21032 | ExclusiveLock | t
> | | 7784237 | 21136 | ExclusiveLock | t
> 87441 | 87343 | | 21063 | AccessShareLock | t
> 87441 | 87343 | | 20952 | AccessShareLock | t
> | | 7784975 | 21247 | ExclusiveLock | t
> 87441 | 87343 | | 21222 | AccessShareLock | t
> | | 7783940 | 21092 | ExclusiveLock | t
> | | 7783596 | 21044 | ExclusiveLock | t
> | | 7783670 | 21052 | ExclusiveLock | t
> | | 7784165 | 21124 | ExclusiveLock | t
> 87441 | 87343 | | 21046 | AccessShareLock | t
> 87441 | 87343 | | 21164 | AccessShareLock | t
> | | 7784793 | 21218 | ExclusiveLock | t
> 87441 | 87343 | | 21247 | AccessShareLock | t
> 87441 | 87343 | | 21278 | AccessShareLock | t
> | | 7784393 | 21158 | ExclusiveLock | t
> | | 7783363 | 21008 | ExclusiveLock | t
> 87441 | 87343 | | 21249 | AccessShareLock | t
> 87441 | 87343 | | 21280 | AccessShareLock | t
> | | 7785374 | 21303 | ExclusiveLock | t
> | | 7783707 | 21061 | ExclusiveLock | t
> 87441 | 87343 | | 21147 | AccessShareLock | t
> 87441 | 87343 | | 21304 | AccessShareLock | t
Look up those relation numbers as oid against pg_class. Note that I'm
running 8.2 and haven't run 7.4 seriously in over two years, so some
of this might not match up. A query like this works in 8.2:
select pgc.relname, pgl2.relation from pg_locks pgl join pg_locks pgl2
on (pgl.relation is null AND pgl2.relation is not null and
pgl.pid=pgl2.pid) join pg_class pgc on (pgl2.relation=pgc.oid);
Like I said, no guarantees that will run on 7.4
What you're looking for is something holding an exclusive lock on the
table you're trying to reindex. But it doesn't sound as if that table
is bloated to me, so I'm guessing something else is wrong.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need some info on Postgresql
@ 2008-01-07 16:46 Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2008-01-07 16:46 UTC (permalink / raw)
To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin
On Jan 7, 2008 3:41 AM, Suresh Gupta VG <suresh.g@zensar.com> wrote:
>
> Hi Scott,
>
> Thanks for your reply. Pls find the attached file for the output of the
> command given by you. The reindex problem is coming for the table
> "currency". Pls look into the result and advice me what to do.
Well, looks like something is holding locks on currency. What
processes are using that table and what kind of explicit locks, if
any, are they taking?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need some info on Postgresql
@ 2008-01-08 07:39 Suresh Gupta VG <suresh.g@zensar.com>
0 siblings, 1 reply; 9+ messages in thread
From: Suresh Gupta VG @ 2008-01-08 07:39 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin
Thanks Scott for your reply.
Actually, our application will extract all the data from currency table
and never uses that table later at all. I don't know which process is
locking that table. Pls find the attached file for the list of locks
available on our Pgsql. Our database is restarted recently last 2 days
back. No fruitful result has come.
Pls advice us why it is happening. And our database is taking more than
5 minutes to execute a simple query directly on the database at the peak
timings say (9:00 Am to 11:00 AM).
Pls advice and suggest us what to do.
Regards,
G. V. Suresh Gupta
------------------------------------------------------------------------
---------------------------------------------------------
Innovative Technology Solutions(ITS), Zensar Technologies
Zensar Knowledge Park, Plot#5, MIDC IT Tower,
Kharadi, Off Nagar Road, Pune - 411014
Landline : +91-20-66453471 | +91-9890898688
Email : suresh.g@zensar.com | website: www.zensar.com
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Monday, January 07, 2008 10:17 PM
To: Suresh Gupta VG
Cc: Peter Koczan; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Need some info on Postgresql
On Jan 7, 2008 3:41 AM, Suresh Gupta VG <suresh.g@zensar.com> wrote:
>
> Hi Scott,
>
> Thanks for your reply. Pls find the attached file for the output of
the
> command given by you. The reindex problem is coming for the table
> "currency". Pls look into the result and advice me what to do.
Well, looks like something is holding locks on currency. What
processes are using that table and what kind of explicit locks, if
any, are they taking?
DISCLAIMER:
This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails.
Attachments:
localtest_gg (application/octet-stream, 6.3K) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Need some info on Postgresql
@ 2008-01-08 15:25 Scott Marlowe <scott.marlowe@gmail.com>
parent: Suresh Gupta VG <suresh.g@zensar.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2008-01-08 15:25 UTC (permalink / raw)
To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin
On Jan 8, 2008 1:39 AM, Suresh Gupta VG <suresh.g@zensar.com> wrote:
>
> Thanks Scott for your reply.
>
> Actually, our application will extract all the data from currency table and
> never uses that table later at all. I don't know which process is locking
> that table. Pls find the attached file for the list of locks available on
> our Pgsql. Our database is restarted recently last 2 days back. No fruitful
> result has come.
Then whatever is causing your problems is happening right after you
turn it back on.
> Pls advice us why it is happening. And our database is taking more than 5
> minutes to execute a simple query directly on the database at the peak
> timings say (9:00 Am to 11:00 AM).
It's your database it's happening in, so the why is hidden away in
there. All I can do is help you find it, as I'm not looking at it
myself... :)
> Pls advice and suggest us what to do.
Try this query:
select pgc.relname, pgl2.relation, pgl.mode from pg_locks pgl join
pg_locks pgl2
on (pgl.relation is null AND pgl2.relation is not null and
pgl.pid=pgl2.pid) join pg_class pgc on (pgl2.relation=pgc.oid);
and look for modes that are ExclusiveLock and the like. Shared stuff isn't bad.
So, I'm guessing that something you're doing with your database is
taking out these locks. DDL in a long running transaction, that kind
of thing.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2008-01-08 15:25 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-03 11:33 Need some info on Postgresql Suresh Gupta VG <suresh.g@zensar.com>
2008-01-04 02:23 ` Re: Need some info on Postgresql Scott Marlowe <scott.marlowe@gmail.com>
2008-01-04 06:41 Re: Need some info on Postgresql Suresh Gupta VG <suresh.g@zensar.com>
2008-01-04 10:40 ` Re: Need some info on Postgresql Scott Marlowe <scott.marlowe@gmail.com>
2008-01-04 10:49 ` Re: Need some info on Postgresql Scott Marlowe <scott.marlowe@gmail.com>
2008-01-04 16:33 Re: Need some info on Postgresql Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 16:46 Re: Need some info on Postgresql Scott Marlowe <scott.marlowe@gmail.com>
2008-01-08 07:39 Re: Need some info on Postgresql Suresh Gupta VG <suresh.g@zensar.com>
2008-01-08 15:25 ` Re: Need some info on Postgresql Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox