Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 7CF49658E76 for ; Wed, 13 Jan 2010 13:54:49 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 03635-06 for ; Wed, 13 Jan 2010 17:54:39 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from ey-out-2122.google.com (ey-out-2122.google.com [74.125.78.27]) by mail.postgresql.org (Postfix) with ESMTP id 741F2641FD5 for ; Wed, 13 Jan 2010 13:54:38 -0400 (AST) Received: by ey-out-2122.google.com with SMTP id 22so4104670eye.33 for ; Wed, 13 Jan 2010 09:54:37 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=i+E4+yTEu2DxxxFpiUATciqt0+0dXjO96bRx7ONUay8=; b=d7zLKgaRmcp+G+wux5KTTjjmbLQrdlkSHwWFBdsq49KteY7+dR9QsJdLQmnmwsZLUx KwUmprWhiTA6MpbNveoK9a0sgrbQcHVlaYcOGdAs/3QR/PSEhL2qTE0bBPsVguEQ67RG c+aCVTrLZxD6FW7qGriKS+7as5spMINWDHQNI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=iq7DMe+do5PZr5DBjFRwPtDyO2bSkRxMC3rjbs6VjUIMNLyAAsAs7v9FFIk6apv2Jl 9w4qCRINot1hSEpqUUS+Mw+l88XTWlj59EeuX6fJLmXcZQBr7gcov+XxHodhGEPTdWEO 6wkb840uhj2uVRpg7DsG5d+iAm3I9t5kPXmAo= MIME-Version: 1.0 Received: by 10.213.109.86 with SMTP id i22mr1400740ebp.58.1263405276933; Wed, 13 Jan 2010 09:54:36 -0800 (PST) In-Reply-To: References: Date: Wed, 13 Jan 2010 14:54:36 -0300 Message-ID: Subject: Re: a heavy duty operation on an "unused" table kills my server From: Eduardo Piombino To: Scott Marlowe Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000e0cdf6b74bd2de1047d0f76b8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.59 tagged_above=-10 required=5 tests=BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201001/259 X-Sequence-Number: 37139 --000e0cdf6b74bd2de1047d0f76b8 Content-Type: text/plain; charset=ISO-8859-1 > OK, I'm not entirely sure this table is not still locking something > else. If you make a copy by doing something like: > > select * into test_table from a; > > and then alter test_table do you still get the same problems? If so, > then it is an IO issue, most likely. If not, then there is some > client connection still referencing this table or something and that > could cause this type of behaviour as well. > I can guarantee you that the table is not being referenced by any other thread, table or process, and that it is totally unrelated to everything else in the system. Its just a plain table, with 1.8 million records, that no thread knows it exists. It has no foreign keys that would allow thinking of a possible "lock" on the parent table, nor it is being referenced by any other table in the model. It has no triggers associated, and no indexes. It could very well even be on another database on the same physical server, and still do the same damage. I did not try this, but I'm pretty sure of the outcome. I would'nt like to bring the server down just to prove this, but I will do it if I find it necessary. The only things that are common to this table and other tables in the system, as I see are: RAM, IO, and CPU, at a very low level. One of these is being stressed out by the thread executing the ALTER, and the other threads (not just pgsql application threads, but system processes in general) suffer from the lack of this resource. All the previous discussions tend to induce that the resource we are talking about is IO. The fact that the Task Manager does not come up, would also not be explained by a lock in a client thread. Besides all that, all the client queries are NO WAIT, thus any lock would just return immediately, and no retry would be done until the response gets back to the user and the user confirms it. In that case, all the errors presented to the final users would be "The element is being processed some other place", as my default handler to pgsql error code "55P03", instead of the horrible "Operation timed out", that is what final users got during the huge slowdown/downtime. -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --000e0cdf6b74bd2de1047d0f76b8 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
OK, I'm not entirely sure this table is not still locking something
else. =A0If you make a copy by doing something like:

select * into test_table from a;

and then alter test_table do you still get the same problems? =A0If so,
then it is an IO issue, most likely. =A0If not, then there is some
client connection still referencing this table or something and that
could cause this type of behaviour as well.

I can = guarantee you that the table is not being referenced by any other thread, t= able or process, and that it is totally unrelated to everything else in the= system.

Its just a plain table, with 1.8 million records, that no thread knows = it exists. It has no foreign keys that would allow thinking of a possible &= quot;lock" on the parent table, nor it is being referenced by any othe= r table in the model. It has no triggers associated, and no indexes. It cou= ld very well even be on another database on the same physical server, and s= till do the same damage. I did not try this, but I'm pretty sure of the= outcome. I would'nt like to bring the server down just to prove this, = but I will do it if I find it necessary.

The only things that are common to this table and other tables in the s= ystem, as I see are:
RAM, IO, and CPU, at a very low level. One of these= is being stressed out by the thread executing the ALTER, and the other thr= eads (not just pgsql application threads, but system processes in general) = suffer from the lack of this resource. All the previous discussions tend to= induce that the resource we are talking about is IO.

The fact that the Task Manager does not come up, would also not be expl= ained by a lock in a client thread.
Besides all that, all the client qu= eries are NO WAIT, thus any lock would just return immediately, and no retr= y would be done until the response gets back to the user and the user confi= rms it. In that case, all the errors presented to the final users would be = "The element is being processed some other place", as my default = handler to pgsql error code "55P03", instead of the horrible &quo= t;Operation timed out", that is what final users got during the huge s= lowdown/downtime.





--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--000e0cdf6b74bd2de1047d0f76b8--