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 E78838B9CAC for ; Mon, 31 Jan 2005 12:20:25 +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 38233-04 for ; Mon, 31 Jan 2005 12:20:12 +0000 (GMT) Received: from www.postgresql.com (unknown [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id 767F48B9BC5 for ; Mon, 31 Jan 2005 12:19:01 +0000 (GMT) Received: from rediffmail.com (unknown [203.199.83.31]) by www.postgresql.com (Postfix) with SMTP id 808C79A866E for ; Mon, 31 Jan 2005 05:47:31 +0000 (GMT) Received: (qmail 21553 invoked by uid 510); 31 Jan 2005 05:47:37 -0000 Date: 31 Jan 2005 05:47:37 -0000 Message-ID: <20050131054737.21552.qmail@webmail46.rediffmail.com> Received: from unknown (202.88.231.207) by rediffmail.com via HTTP; 31 jan 2005 05:47:24 -0000 MIME-Version: 1.0 From: "N S" Reply-To: "N S" To: "Joshua D.Drake" Cc: pgsql-performance@postgresql.org Subject: Re: Postgres server getting slow!! Content-type: multipart/alternative; boundary="Next_1107150444---0-203.199.83.31-20838" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.806 tagged_above=0 required=5 tests=AWL, HTML_20_30, HTML_MESSAGE, MSGID_FROM_MTA_HEADER X-Spam-Level: X-Archive-Number: 200501/625 X-Sequence-Number: 10315 This is a multipart mime message --Next_1107150444---0-203.199.83.31-20838 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Thanks joshua=0A=0A i tried running vacuum command, =0Avacuum database as= well as vacuum =0A=0Abut even after that querying t= he database , the memory shoots up=0Aas i mentioned in the previous mail an= d never comes down.=0AAlso the old pids of connections established remains = even after the=0Aconnection is closed.=0A=0AWill backing up the complete da= tabase, dropping and recreating can=0Amake any difference. =0A=0A=0AKindly = suggest=0A=0AThanks in advance=0A=0Aregards=0A=0AN S=0A=0A=0AOn Mon, 31 Jan= 2005 Joshua D.Drake wrote :=0A>N S wrote:=0A>=0A>>I checked to find out th= e cause of the problem, ppp is disconnecting properly and the user session = is also closed smoothely.=0A>>But when a report query is run on the table c= ontaining 32500 records,=0A>>the memory shoots up from 50 MB to 500 MB(Tota= l 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 v= ery little way, and finally the 6th or 7th user is denied with database acc= ess.The server now becomes slow.=0A>>=0A>>Will running vacuum help to solve= the problem?=0A>>=0A>Sounds like you need to run vacuum and analyze. It al= so sounds like you=0A>may need to run vacuum full the first time.=0A>=0A>va= cuum needs to be run regularly as does analyze.=0A>=0A>Sincerely,=0A>=0A>Jo= shua D. Drake=0A>=0A>=0A>>=0A>>The total database dump is 50 MB and the /va= r/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 t= om. I checked the client side software. The software closes connection when= connected locally. But when connected through dialup,=0A>> >this problem c= omes. I will check the ppp connection also.=0A>> >Is there any method of ki= lling old pids. And also any performance tuning to be done on postgresql.co= nf file.=0A>> >=0A>> >The database now contains 20K records. Will that caus= e a problem?=0A>> >=0A>> >Regds=0A>> >=0A>> >Narayanan=0A>> >=0A>> >On Sat= , 29 Jan 2005 Tom Lane wrote :=0A>> > >"Narayanan Subramaniam Iyer" writes:=0A>> > > > 1) When 3 or 4 clients connect to this s= erver, the pids are created and=0A>> > > > those pids are not killed even a= fter the client disconnects.=0A>> > >=0A>> > >In that case your clients are= not really disconnecting. Take a closer=0A>> > >look at your client-side = software.=0A>> > >=0A>> > > regards, tom lane=0A>> > >=0A>> = > >---------------------------(end of broadcast)---------------------------= =0A>> > >TIP 3: if posting/reading through Usenet, please send an appropria= te=0A>> > > subscribe-nomail command to majordomo@postgresql.org so th= at your=0A>> > > message can get through to the mailing list cleanly= =0A>>=0A>>=0A>>=0A>>=0A>= =0A>=0A>=0A>-- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC an= d S/JDBC=0A>Postgresql support, programming shared hosting and dedicated ho= sting.=0A>+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt= .com=0A>PostgreSQL Replicator -- production quality replication for Postgre= SQL=0A>=0A --Next_1107150444---0-203.199.83.31-20838 Content-type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

=0A Thanks joshua
=0A
=0A  i tried running vacuum command, =0Avacuum database as well as vacuum <indvidual table names>
=0A=
=0Abut even after that querying the database , the memory shoots up
= =0Aas i mentioned in the previous mail and never comes down.
=0AAlso the= old pids of connections established remains even after the
=0Aconnectio= n is closed.
=0A
=0AWill backing up the complete database, dropping a= nd recreating can
=0Amake any difference.
=0A
=0A
=0AKindly su= ggest
=0A
=0AThanks in advance
=0A
=0Aregards
=0A
=0AN S<= BR>=0A
=0A
=0AOn Mon, 31 Jan 2005 Joshua D.Drake wrote :
=0A>N = S wrote:
=0A>
=0A>>I checked to find out the cause of the pr= oblem, ppp is disconnecting properly and the user session is also closed sm= oothely.
=0A>>But when a report query is run on the table containi= ng 32500 records,
=0A>>the memory shoots up from 50 MB to 500 MB(T= otal memory is 512 MB RAM).
=0A>>After that the memory usage never= comes down .When some 4 or 5 user
=0A>>connects, the remaining me= mory is utilised in a very little way, and finally the 6th or 7th user is d= enied with database access.The server now becomes slow.
=0A>>
= =0A>>Will running vacuum help to solve the problem?
=0A>>=0A>Sounds like you need to run vacuum and analyze. It also sounds like= you
=0A>may need to run vacuum full the first time.
=0A>
= =0A>vacuum needs to be run regularly as does analyze.
=0A>
=0A&= gt;Sincerely,
=0A>
=0A>Joshua D. Drake
=0A>
=0A>=0A>>
=0A>>The total database dump is 50 MB and the /var/li= b/pgsql/data contains
=0A>>700 MB of data.
=0A>>
=0A&g= t;>Which all paramters are required to be increased in postgresq.conf.=0A>>
=0A>>
=0A>>Regds
=0A>>
=0A>&= gt;N S
=0A>>
=0A>>On Sun, 30 Jan 2005 N S wrote :
=0A&= gt;> >
=0A>> >
=0A>> >Thanks tom. I checked t= he client side software. The software closes connection when connected loca= lly. But when connected through dialup,
=0A>> >this problem com= es. I will check the ppp connection also.
=0A>> >Is there any m= ethod of killing old pids. And also any performance tuning to be done on po= stgresql.conf file.
=0A>> >
=0A>> >The database now= contains 20K records. Will that cause  a problem?
=0A>> >=
=0A>> >Regds
=0A>> >
=0A>> >Narayanan<= BR>=0A>> >
=0A>> >On Sat, 29 Jan 2005 Tom Lane wrote :=
=0A>> > >"Narayanan Subramaniam Iyer" <bunix@r= ediffmail.com> writes:
=0A>> > > > 1) When 3 or 4 clie= nts connect to this server, the pids are created and
=0A>> > &g= t; > those pids are not killed even after the client disconnects.
=0A= >> > >
=0A>> > >In that case your clients are no= t really disconnecting.  Take a closer
=0A>> > >look at= your client-side software.
=0A>> > >
=0A>> > &g= t;                regards, tom lane=
=0A>> > >
=0A>> > >-------------------------= --(end of broadcast)---------------------------
=0A>> > >TIP= 3: if posting/reading through Usenet, please send an appropriate
=0A>= ;> > >      subscribe-nomail command to majordomo@p= ostgresql.org so that your
=0A>> > >      mes= sage can get through to the mailing list cleanly
=0A>>
=0A>&= gt;
=0A>>
=0A>><http://clients.rediff.com/signature/tr= ack_sig.asp>
=0A>
=0A>
=0A>
=0A>-- Command Promp= t, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
=0A>Postgresq= l support, programming shared hosting and dedicated hosting.
=0A>+1-5= 03-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
=0A>= ;PostgreSQL Replicator -- production quality replication for PostgreSQL
= =0A>
=0A=0A

=0A

=0A=0A --Next_1107150444---0-203.199.83.31-20838--