[GENERAL] Postgre "idle" process using 100% CPU
Hi, i am using postgresql version 8.0.1 on Gentoo Linux and from time to time a postgres process that is marked as idle - "postgres: user db IP(34079) idle" - starts using 100% CPU. There is nothing in the logs, so i don't have a clue what could be the problem. Regards, Jernej Kos. -- Jernej Kos <[EMAIL PROTECTED]> JWeb-Network ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Postgre "idle" process using 100% CPU
Well there should be no complex queries executed (there are some huge tables, but the queries aren't huge or specially complicated). I tried attaching to the process via gdb and the process is executing method: HeapTupleSatisfiesSnapshot() The backtrace appears to be useless (too many ??s). Is that of any help ? Regards, Jernej Kos. On Thursday 09 of June 2005 17:19, you wrote: > Jernej Kos <[EMAIL PROTECTED]> writes: > > i am using postgresql version 8.0.1 on Gentoo Linux and from time to time > > a postgres process that is marked as idle - "postgres: user db IP(34079) > > idle" - starts using 100% CPU. There is nothing in the logs, so i don't > > have a clue what could be the problem. > > For a long time? I'd expect it to go busy on receiving a command > somewhat before changing the PS status, because command parsing > happens first (else it can't know what to set the status to ...). > If you are in the habit of sending enormously complex SQL commands > then maybe this state would last long enough to notice. > > If you don't think that's it, maybe you could attach to the busy > backend with gdb and get a stack trace to find out what it's doing? > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Jernej Kos <[EMAIL PROTECTED]> JWeb-Network ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Postgre "idle" process using 100% CPU
Well I can't get any better backtraces (even with --enable-debug). The strange thing is that this just happens once in a while and the process doesn't stop until it is killed (or postgre is restarted). Any suggestions ? Regards, Jernej Kos. On Sunday 12 of June 2005 18:01, you wrote: > Jernej Kos <[EMAIL PROTECTED]> writes: > > Well there should be no complex queries executed (there are some huge > > tables, but the queries aren't huge or specially complicated). I tried > > attaching to the process via gdb and the process is executing method: > > > > HeapTupleSatisfiesSnapshot() > > > > The backtrace appears to be useless (too many ??s). Is that of any help ? > > It's really hard to see how it could get to HeapTupleSatisfiesSnapshot() > without being inside a query --- too bad you can't get a more usable > backtrace. Is it worth recompiling with --enable-debug to investigate > this? > > regards, tom lane -- Jernej Kos <[EMAIL PROTECTED]> JWeb-Network ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Index question
I have a query which orders data like this: ORDER BY a.col1, b.col2 where a and b are different tables. Both tables are really big and so, ordering takes quite a while. Now i would like to create an index to speed things up, but i don't know how to create such an index that would cover both columns in different tables. Regards, Kostko. -- Kostko <[EMAIL PROTECTED]> JWeb-Network ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Multicolumn indexes and ORDER BY
Yes i tried that already - and as you said, it works. But i need to have one column sorted DESC and one ASC. Is there any way this could be done ? Regards, Jernej Kos. On Wednesday 16 of June 2004 08:12, Martijn van Oosterhout wrote: > On Wed, Jun 16, 2004 at 07:12:26AM +0200, Jernej Kos wrote: > > I have a multicolumn index on two columns. If i use the columns in ORDER > > BY like this: > > ORDER BY col1, col2; > > > > The index is used. But, if one column is sorted DESC it is not used: > > ORDER BY col1 DESC, col2; > > > > How can i make this work ? > > Try: > > ORDER BY col1 DESC, col2 desc; > > Hope this helps, -- Jernej Kos <[EMAIL PROTECTED]> JWeb-Network ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
