Re: [GENERAL] Reindex does not finish 8.2.6
Clodoaldo escribió: The database performance slowly degrades and after two weeks i issue a reindex on the database and the performance gets back to normal. I have been doing this for ages and i don't remember in which version this degradation behavior appeared, perhaps 8.0 I'm not sure. Was this issue solved? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
2008/3/26, Alvaro Herrera [EMAIL PROTECTED]: Clodoaldo escribió: The database performance slowly degrades and after two weeks i issue a reindex on the database and the performance gets back to normal. I have been doing this for ages and i don't remember in which version this degradation behavior appeared, perhaps 8.0 I'm not sure. Was this issue solved? About the reindex, cluster and vacuum full hanging, i had drop the db and reload the backup and now it is back to normal. I could not debug the hanging problem. It was very likely caused by the kernel update. I never had a crash with this database before. It is rock solid, it just gets slower. About the slow degradation of performance nothing has changed. I'm using 8.2.7 and still have to reindex every two weeks or so. Regards, Clodoaldo Pinto Neto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
2008/3/13, Alvaro Herrera [EMAIL PROTECTED]:
Clodoaldo escribió:
2008/3/13, Alvaro Herrera [EMAIL PROTECTED]:
Clodoaldo escribió:
Now what is happening is that reindex does not finish even with a
small 6,500 rows table and after a reboot. In top there is no CPU or
memory usage by postmaster and vmstat shows no disk activity.
Hmm, are you vacuuming the system catalogs appropriately?
If a simple vacuum in instead of a vacuum full is appropriate then yes
the db is vacuumed every three hours after each bulk insert/delete.
As superuser? Take a look at whether relations are skipped during
vacuum.
Try vacuuming pg_class, pg_index, pg_attribute manually and see if that
makes the problem go away. If it doesn't, then my guess is that it's
time to see what the hanging process is doing -- try an strace on it, or
attaching it with gdb and getting a backtrace. (I hope your binaries
have debug symbols).
This is strace started before the reindex table command.
postmaster process:
# strace -f -p 2263
Process 2263 attached - interrupt to quit
select(6, [3 4 5], NULL, NULL, {35, 749000}) = 1 (in [5], left {23, 734000})
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS
RTMIN RT_1], NULL, 8) = 0
accept(5, {sa_family=AF_FILE, [EMAIL PROTECTED], [2]) = 8
getsockname(8, {sa_family=AF_FILE, path=/tmp/.s.PGSQL.5432}, [21]) = 0
clone(Process 16014 attached (waiting for parent)
Process 16014 resumed (parent 2263 ready)
child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD,
child_tidptr=0x2aac72f0) = 16014
[pid 2263] close(8)= 0
[pid 2263] rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
[pid 2263] select(6, [3 4 5], NULL, NULL, {60, 0} unfinished ...
[pid 16014] close(3)= 0
[pid 16014] close(4)= 0
[pid 16014] close(5)= 0
[pid 16014] close(6)= 0
[pid 16014] setsid()= 16014
[pid 16014] rt_sigaction(SIGTERM, {0x581700, [],
SA_RESTORER|SA_RESTART, 0x3e97630f30}, {0x55b5b0, [],
SA_RESTORER|SA_RESTART, 0x3e97630f30}, 8) = 0
[pid 16014] rt_sigaction(SIGQUIT, {0x581700, [],
SA_RESTORER|SA_RESTART, 0x3e97630f30}, {0x55b5b0, [],
SA_RESTORER|SA_RESTART, 0x3e97630f30}, 8) = 0
[pid 16014] rt_sigaction(SIGALRM, {0x581700, [], SA_RESTORER,
0x3e97630f30}, {SIG_IGN}, 8) = 0
[pid 16014] rt_sigprocmask(SIG_SETMASK, ~[QUIT ILL TRAP ABRT BUS FPE
SEGV ALRM TERM CONT SYS RTMIN RT_1], NULL, 8) = 0
[pid 16014] write(2, \0\0_\0\216\0\0tLOG: 0: connection..., 104) = 104
[pid 16014] setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={60,
0}}, NULL) = 0
[pid 16014] recvfrom(8, \0\0\0\20\4\322\26.\0\0\2054\344\367W,
8192, 0, NULL, NULL) = 16
[pid 16014] kill(16005, SIGINT) = 0
[pid 16014] kill(4294951291, SIGINT)= 0
[pid 16014] exit_group(0) = ?
Process 16014 detached
... select resumed ) = ? ERESTARTNOHAND (To be restarted)
--- SIGCHLD (Child exited) @ 0 (0) ---
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS
RTMIN RT_1], NULL, 8) = 0
wait4(-1, [{WIFEXITED(s) WEXITSTATUS(s) == 0}], WNOHANG, NULL) = 16014
wait4(-1, 0x7fff77f82844, WNOHANG, NULL) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
rt_sigreturn(0x2) = -1 EINTR (Interrupted system call)
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS
RTMIN RT_1], NULL, 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
select(6, [3 4 5], NULL, NULL, {60, 0} unfinished ...
Process 2263 detached
Regards, Clodoaldo Pinto Neto
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
On Fri, Mar 14, 2008 at 5:19 AM, Clodoaldo [EMAIL PROTECTED] wrote: Try vacuuming pg_class, pg_index, pg_attribute manually and see if that makes the problem go away. It does not go away. Can it be a case where some other open transaction is holding a lock on the table ? Note that REINDEX would block even some other transaction is inserting/deleting/updating the table. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
2008/3/14, Scott Marlowe [EMAIL PROTECTED]: On Thu, Mar 13, 2008 at 5:49 PM, Clodoaldo [EMAIL PROTECTED] wrote: 2008/3/13, Alvaro Herrera [EMAIL PROTECTED]: Clodoaldo escribió: 2008/3/13, Alvaro Herrera [EMAIL PROTECTED]: Clodoaldo escribió: Now what is happening is that reindex does not finish even with a small 6,500 rows table and after a reboot. In top there is no CPU or memory usage by postmaster and vmstat shows no disk activity. Hmm, are you vacuuming the system catalogs appropriately? If a simple vacuum in instead of a vacuum full is appropriate then yes the db is vacuumed every three hours after each bulk insert/delete. As superuser? Take a look at whether relations are skipped during vacuum. Now I vacuumed with superuser. Try vacuuming pg_class, pg_index, pg_attribute manually and see if that makes the problem go away. It does not go away. Could this be a problem with a bloated table that needs a vacuum full? I'd try that. Vacuum full also hangs. Regards, Clodoaldo Pinto Neto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
2008/3/14, Pavan Deolasee [EMAIL PROTECTED]: On Fri, Mar 14, 2008 at 5:19 AM, Clodoaldo [EMAIL PROTECTED] wrote: Try vacuuming pg_class, pg_index, pg_attribute manually and see if that makes the problem go away. It does not go away. Can it be a case where some other open transaction is holding a lock on the table ? Note that REINDEX would block even some other transaction is inserting/deleting/updating the table. Postgresql was restarted twice, but yes, it is as if the crash left some kind of permanent lock somewhere. Regards, Clodoaldo Pinto Neto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
Clodoaldo escribió: Postgresql was restarted twice, but yes, it is as if the crash left some kind of permanent lock somewhere. A prepared transaction perhaps? SELECT * FROM pg_prepared_xacts; A quick look into pg_locks should tell you if it's blocking. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
2008/3/14, Alvaro Herrera [EMAIL PROTECTED]: Clodoaldo escribió: Postgresql was restarted twice, but yes, it is as if the crash left some kind of permanent lock somewhere. A prepared transaction perhaps? SELECT * FROM pg_prepared_xacts; A quick look into pg_locks should tell you if it's blocking. pg_prepared_xacts is empty and pg_locks has 288 rows: # select locktype, mode, count(*) as total from pg_locks group by locktype, mode; locktype| mode | total ---+--+--- transactionid | ExclusiveLock|30 relation | RowExclusiveLock | 2 relation | AccessShareLock | 256 (3 rows) Regards, Clodoaldo Pinto Neto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
Clodoaldo escribió: 2008/3/14, Alvaro Herrera [EMAIL PROTECTED]: A quick look into pg_locks should tell you if it's blocking. pg_prepared_xacts is empty and pg_locks has 288 rows: # select locktype, mode, count(*) as total from pg_locks group by locktype, mode; locktype| mode | total ---+--+--- transactionid | ExclusiveLock|30 relation | RowExclusiveLock | 2 relation | AccessShareLock | 256 (3 rows) How many of them have granted=false? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
2008/3/14, Alvaro Herrera [EMAIL PROTECTED]: Clodoaldo escribió: 2008/3/14, Alvaro Herrera [EMAIL PROTECTED]: A quick look into pg_locks should tell you if it's blocking. pg_prepared_xacts is empty and pg_locks has 288 rows: # select locktype, mode, count(*) as total from pg_locks group by locktype, mode; locktype| mode | total ---+--+--- transactionid | ExclusiveLock|30 relation | RowExclusiveLock | 2 relation | AccessShareLock | 256 (3 rows) How many of them have granted=false? = select locktype, mode, granted, count(*) as total from pg_locks group by locktype, mode, granted; locktype| mode | granted | total ---+-+-+--- relation | AccessShareLock | t | 112 transactionid | ExclusiveLock | t |17 (2 rows) Regards, Clodoaldo Pinto Neto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
On Thu, 13 Mar 2008, Clodoaldo wrote: I recently had a crash during a bulk insert when i updated to the 2.6.24.3-12.fc8 kernel. I rebooted and Postgresql did its thing to recover and everything was working. I made the previous kernel, 2.6.23.15-137.fc8, the default in grub.conf and rebooted. Have you considered running a Linux distribution that's a bit more stable and production oriented than Fedora 8 on this system? I trust the Fedora kernel to keep data intact about as much as I trust MySQL, and 2.6.24 is waaay more bleeding edge than I'd consider using right now for anything beyond a disposable test environment. I'm not running anything later than 2.6.20 right now and even that I barely trust after six months of pounding on it. I run CentOS on devel machines that can't justify a paid license and where the production system is RedHat Enterprise. I was just forced to use Fedora 8 the other day and was very dissapointed that Fedora remains as buggy as ever. It's the 6th Linux/BSD variant installed on this hardware, the first where I had to pass kernel options just to boot the install CD, and the first where X didn't work after install (the mouse is lost). Solaris 10 gave me less problems on this machine. I know this is kind of off-topic for the question you're asking, but you wouldn't even have to ask it if the underlying platform wasn't problematic. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
Clodoaldo escribió: Now what is happening is that reindex does not finish even with a small 6,500 rows table and after a reboot. In top there is no CPU or memory usage by postmaster and vmstat shows no disk activity. Hmm, are you vacuuming the system catalogs appropriately? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
2008/3/13, Alvaro Herrera [EMAIL PROTECTED]: Clodoaldo escribió: Now what is happening is that reindex does not finish even with a small 6,500 rows table and after a reboot. In top there is no CPU or memory usage by postmaster and vmstat shows no disk activity. Hmm, are you vacuuming the system catalogs appropriately? If a simple vacuum in instead of a vacuum full is appropriate then yes the db is vacuumed every three hours after each bulk insert/delete. Regards, Clodoaldo Pinto Neto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
Clodoaldo escribió: 2008/3/13, Alvaro Herrera [EMAIL PROTECTED]: Clodoaldo escribió: Now what is happening is that reindex does not finish even with a small 6,500 rows table and after a reboot. In top there is no CPU or memory usage by postmaster and vmstat shows no disk activity. Hmm, are you vacuuming the system catalogs appropriately? If a simple vacuum in instead of a vacuum full is appropriate then yes the db is vacuumed every three hours after each bulk insert/delete. As superuser? Take a look at whether relations are skipped during vacuum. Try vacuuming pg_class, pg_index, pg_attribute manually and see if that makes the problem go away. If it doesn't, then my guess is that it's time to see what the hanging process is doing -- try an strace on it, or attaching it with gdb and getting a backtrace. (I hope your binaries have debug symbols). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
2008/3/13, Greg Smith [EMAIL PROTECTED]: On Thu, 13 Mar 2008, Clodoaldo wrote: I recently had a crash during a bulk insert when i updated to the 2.6.24.3-12.fc8 kernel. I rebooted and Postgresql did its thing to recover and everything was working. I made the previous kernel, 2.6.23.15-137.fc8, the default in grub.conf and rebooted. Have you considered running a Linux distribution that's a bit more stable and production oriented than Fedora 8 on this system? Yes, but because I'm so used to Fedora i still didn't change. I run CentOS on devel machines that can't justify a paid license and where the production system is RedHat Enterprise. I guess CentOS is very similar to Fedora. Next OS install I will use it. Regards, Clodoaldo Pinto Neto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
2008/3/13, Alvaro Herrera [EMAIL PROTECTED]: Clodoaldo escribió: 2008/3/13, Alvaro Herrera [EMAIL PROTECTED]: Clodoaldo escribió: Now what is happening is that reindex does not finish even with a small 6,500 rows table and after a reboot. In top there is no CPU or memory usage by postmaster and vmstat shows no disk activity. Hmm, are you vacuuming the system catalogs appropriately? If a simple vacuum in instead of a vacuum full is appropriate then yes the db is vacuumed every three hours after each bulk insert/delete. As superuser? Take a look at whether relations are skipped during vacuum. Now I vacuumed with superuser. Try vacuuming pg_class, pg_index, pg_attribute manually and see if that makes the problem go away. It does not go away. If it doesn't, then my guess is that it's time to see what the hanging process is doing -- try an strace on it, or attaching it with gdb and getting a backtrace. (I hope your binaries have debug symbols). Will try it. Regards, Clodoaldo Pinto Neto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reindex does not finish 8.2.6
On Thu, Mar 13, 2008 at 5:49 PM, Clodoaldo [EMAIL PROTECTED] wrote: 2008/3/13, Alvaro Herrera [EMAIL PROTECTED]: Clodoaldo escribió: 2008/3/13, Alvaro Herrera [EMAIL PROTECTED]: Clodoaldo escribió: Now what is happening is that reindex does not finish even with a small 6,500 rows table and after a reboot. In top there is no CPU or memory usage by postmaster and vmstat shows no disk activity. Hmm, are you vacuuming the system catalogs appropriately? If a simple vacuum in instead of a vacuum full is appropriate then yes the db is vacuumed every three hours after each bulk insert/delete. As superuser? Take a look at whether relations are skipped during vacuum. Now I vacuumed with superuser. Try vacuuming pg_class, pg_index, pg_attribute manually and see if that makes the problem go away. It does not go away. Could this be a problem with a bloated table that needs a vacuum full? I'd try that. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
