Обсуждение: postgres bogged down beyond tolerance

<p><font size="2">Hi Everybody,<br /><br /> The postgres server I have (on redhat linux with recent<br /> Dell
hardware)is running terribly slow.<br /><br /> A job it should have gotten done in less than 1 hour<br /> took 7.5
hourslast night.  I checked kernel parameter<br /> shmmax and it was set as 33554432.  I "fixed" it as<br /> suggested
bythe manual:<br /><a
href="http://www.postgresql.org/docs/8.2/static/kernel-resources.html">http://www.postgresql.org/docs/8.2/static/kernel-resources.html</a><br
/><br/> Namely, I shutdown the database, issued two commands:<br />   /sbin/sysctl -w kernel.shmmax=134217728<br />  
/sbin/sysctl-w kernel.shmall=2097152<br /> and rebooted the computer.<br /><br /> After it came up, I checked the
shmmaxand it is set<br /> as 33554432.  Which surprised me.  Since I used -w<br /> flag, I thought it should've written
to/etc/sysctl.conf,<br /> but there is no such entry at all and the data of this<br /> file is from 2006.<br /><br />
Cansomebody please give me a tip, insight as to what I<br /> am missing, doing wrong?<br /><br /> Here's tail of
serverlogfile in my data directory:<br /><br /> [2007-11-14 08:53:48.062 PST] <gadb 2007-11-14 08:53:36 PST idle in
transaction>LOG: unexpected EOF on client connection<br /> [2007-11-14 08:53:59.001 PST] <gadb 2007-11-14
08:53:48PST idle in transaction>LOG:  unexpected EOF on client connection<br /> [2007-11-14 08:54:10.782 PST]
<gadb2007-11-14 08:53:59 PST idle in transaction>LOG:  unexpected EOF on client connection<br /> [2007-11-14
08:54:22.557PST] <gadb 2007-11-14 08:54:10 PST idle in transaction>LOG:  unexpected EOF on client connection<br
/>[2007-11-14 08:54:34.282 PST] <gadb 2007-11-14 08:54:22 PST idle in transaction>LOG:  unexpected EOF on client
connection<br/> [2007-11-14 09:13:36.444 PST] <gadb 2007-11-14 09:13:24 PST idle in transaction>LOG:  unexpected
EOFon client connection<br /> [2007-11-14 09:13:43.637 PST] <gadb 2007-11-14 09:13:32 PST idle in
transaction>LOG: unexpected EOF on client connection<br /> [2007-11-14 09:17:16.242 PST] <gadb 2007-11-14
09:17:03PST idle in transaction>LOG:  unexpected EOF on client connection<br /> [2007-11-14 09:39:22.841 PST]
<gjoslyn2007-11-14 09:39:22 PST SELECT>ERROR:  relation "msysconf" does not exist<br /> [2007-11-14 09:39:22.842
PST]<gjoslyn 2007-11-14 09:39:22 PST SELECT>STATEMENT:  SELECT Config, nValue FROM MSysConf<br /><br /> Many
thanksin advance.<br /><br /> Regards,<br /><br /> Tena Sakai<br /> tsakai@gallo.ucsf.edu<br /><br /></font> 

--- On Wed, 11/14/07, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
> The postgres server I have (on redhat linux with recent
> Dell hardware) is running terribly slow.

Hello Tena,

If you do not get a response to your question regarding performance, you might try resending this email to :
pgsql-performance@postgresql.org.
 

This mailing list ( pgsql-sql@postgresql.org ) is really intended for discussions of how to construct SQL queries to
achievedesired results in a PostgreSQL database.
 

Regards,
Richard Broersma Jr.

"Tena Sakai" <tsakai@gallo.ucsf.edu> writes:

> Namely, I shutdown the database, issued two commands:
>   /sbin/sysctl -w kernel.shmmax=134217728
>   /sbin/sysctl -w kernel.shmall=2097152
> and rebooted the computer.
>
> After it came up, I checked the shmmax and it is set
> as 33554432.  Which surprised me.  Since I used -w
> flag, I thought it should've written to /etc/sysctl.conf,
> but there is no such entry at all and the data of this
> file is from 2006.


sysctl changes the values for the running kernel. /etc/sysctl is a file you
edit manually to tell the boot scripts what values to store (using sysctl) so
you don't have to run sysctl every time you reboot.


--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!