X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 158568B9B47 for ; Mon, 31 Jan 2005 12:52:30 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43321-02 for ; Mon, 31 Jan 2005 12:52:16 +0000 (GMT) Received: from www.postgresql.com (unknown [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id 72F998B9B4F for ; Mon, 31 Jan 2005 12:52:17 +0000 (GMT) Received: from rediffmail.com (unknown [203.199.83.39]) by www.postgresql.com (Postfix) with SMTP id 00AFA9A8823 for ; Mon, 31 Jan 2005 09:38:03 +0000 (GMT) Received: (qmail 26417 invoked by uid 510); 31 Jan 2005 09:38:36 -0000 Date: 31 Jan 2005 09:38:36 -0000 Message-ID: <20050131093836.26416.qmail@webmail29.rediffmail.com> Received: from unknown (202.88.231.207) by rediffmail.com via HTTP; 31 jan 2005 09:38:31 -0000 MIME-Version: 1.0 From: "N S" Reply-To: "N S" To: pgsql-performance@postgresql.org Subject: Re: Postgres server getting slow!! Content-type: multipart/alternative; boundary="Next_1107164311---0-203.199.83.39-26226" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.893 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_20_30, HTML_MESSAGE, MSGID_FROM_MTA_HEADER X-Spam-Level: X-Archive-Number: 200501/626 X-Sequence-Number: 10316 This is a multipart mime message --Next_1107164311---0-203.199.83.39-26226 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline =0AThanks joshua=0A=0A i tried running vacuum command,=0Avacuum database= as well as vacuum =0A=0Abut even after that queryin= g the database , the memory shoots up=0Aas i mentioned in the previous mail= and never comes down.=0AAlso the old pids of connections established remai= ns even after the=0Aconnection is closed.=0A=0AWill backing up the complete= database, dropping and recreating can=0Amake any difference.=0A=0A=0AKindl= y suggest=0A=0AThanks in advance=0A=0Aregards=0A=0AN S=0A=0A=0A N S wrote:= =0A=0A> I checked to find out the cause of the problem, ppp is disconnectin= g=0A> properly and the user session is also closed smoothely.=0A> But when = a report query is run on the table containing 32500 records,=0A> the memory= shoots up from 50 MB to 500 MB(Total memory is 512 MB RAM).=0A> After that= the memory usage never comes down .When some 4 or 5 user=0A> connects, the= remaining memory is utilised in a very little way, and=0A> finally the 6th= or 7th user is denied with database access.The server=0A> now becomes slow= .=0A>=0A> Will running vacuum help to solve the problem?=0A>=0ASounds like = you need to run vacuum and analyze. It also sounds like you=0Amay need to r= un vacuum full the first time.=0A=0Avacuum needs to be run regularly as doe= s analyze.=0A=0ASincerely,=0A=0AJoshua D. Drake=0A=0A=0A>=0A> The total dat= abase dump is 50 MB and the /var/lib/pgsql/data contains=0A> 700 MB of data= .=0A>=0A> Which all paramters are required to be increased in postgresq.con= f.=0A>=0A>=0A> Regds=0A>=0A> N S=0A>=0A> On Sun, 30 Jan 2005 N S wrote :=0A= > >=0A> >=0A> >Thanks tom. I checked the client side software. The software= closes=0A> connection when connected locally. But when connected through d= ialup,=0A> >this problem comes. I will check the ppp connection also.=0A> >= Is there any method of killing old pids. And also any performance=0A> tunin= g to be done on postgresql.conf file.=0A> >=0A> >The database now contains = 20K records. Will that cause a problem?=0A> >=0A> >Regds=0A> >=0A> >Naraya= nan=0A> >=0A> >On Sat, 29 Jan 2005 Tom Lane wrote :=0A> > >"Narayanan Subra= maniam Iyer" writes:=0A> > > > 1) When 3 or 4 client= s connect to this server, the pids are=0A> created and=0A> > > > those pids= are not killed even after the client disconnects.=0A> > >=0A> > >In that c= ase your clients are not really disconnecting. Take a closer=0A> > >look a= t your client-side software.=0A> > >=0A> > > regards, tom la= ne=0A> > >=0A> > >---------------------------(end of=0A> broadcast)--------= -------------------=0A> > >TIP 3: if posting/reading through Usenet, please= send an appropriate=0A> > > subscribe-nomail command to majordomo@pos= tgresql.org so that your=0A> > > message can get through to the mailin= g list cleanly=0A>=0A>=0A>=0A> =20 --Next_1107164311---0-203.199.83.39-26226 Content-type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

=0A 
=0AThanks joshua
=0A
=0A  i tried running vacuu= m command,
=0Avacuum database as well as vacuum <indvidual table name= s>
=0A
=0Abut even after that querying the database , the memory s= hoots up
=0Aas i mentioned in the previous mail and never comes down.=0AAlso the old pids of connections established remains even after the
= =0Aconnection is closed.
=0A
=0AWill backing up the complete database= , dropping and recreating can
=0Amake any difference.
=0A
=0A
= =0AKindly suggest
=0A
=0AThanks in advance
=0A
=0Aregards
= =0A
=0AN S
=0A
=0A
=0A     N S wrote:
=0A
= =0A> I checked to find out the cause of the problem, ppp is disconnectin= g
=0A> properly and the user session is also closed smoothely.
=0A= > But when a report query is run on the table containing 32500 records,<= BR>=0A> the memory shoots up from 50 MB to 500 MB(Total memory is 512 MB= RAM).
=0A> After that the memory usage never comes down .When some 4= or 5 user
=0A> connects, the remaining memory is utilised in a very = little way, and
=0A> finally the 6th or 7th user is denied with datab= ase access.The server
=0A> now becomes slow.
=0A>
=0A> Wi= ll running vacuum help to solve the problem?
=0A>
=0ASounds like y= ou need to run vacuum and analyze. It also sounds like you
=0Amay need t= o run vacuum full the first time.
=0A
=0Avacuum needs to be run regul= arly as does analyze.
=0A
=0ASincerely,
=0A
=0AJoshua D. Drake<= BR>=0A
=0A
=0A>
=0A> The total database dump is 50 MB and th= e /var/lib/pgsql/data contains
=0A> 700 MB of data.
=0A>
=0A= > Which all paramters are required to be increased in postgresq.conf.=0A>
=0A>
=0A> Regds
=0A>
=0A> N S
=0A>=0A> On Sun, 30 Jan 2005 N S wrote :
=0A> >
=0A> >=0A> >Thanks tom. I checked the client side software. The software = closes
=0A> connection when connected locally. But when connected thr= ough dialup,
=0A> >this problem comes. I will check the ppp connec= tion also.
=0A> >Is there any method of killing old pids. And also= any performance
=0A> tuning to be done on postgresql.conf file.
= =0A> >
=0A> >The database now contains 20K records. Will tha= t cause  a problem?
=0A> >
=0A> >Regds
=0A> &g= t;
=0A> >Narayanan
=0A> >
=0A> >On Sat, 29 Jan 2= 005 Tom Lane wrote :
=0A> > >"Narayanan Subramaniam Iyer&q= uot; <bunix@rediffmail.com> writes:
=0A> > > > 1) When= 3 or 4 clients connect to this server, the pids are
=0A> created and=
=0A> > > > those pids are not killed even after the client = disconnects.
=0A> > >
=0A> > >In that case your cli= ents are not really disconnecting.  Take a closer
=0A> > >= look at your client-side software.
=0A> > >
=0A> > >= ;                regards, tom lane<= BR>=0A> > >
=0A> > >---------------------------(end of=
=0A> broadcast)---------------------------
=0A> > >TIP 3= : if posting/reading through Usenet, please send an appropriate
=0A> = > >      subscribe-nomail command to majordomo@postgre= sql.org so that your
=0A> > >      message can g= et through to the mailing list cleanly
=0A>
=0A>
=0A>
= =0A> <http://clients.rediff.com/signature/track_sig.asp> =0A

= =0A

=0A=0A --Next_1107164311---0-203.199.83.39-26226--