Re: Performance Question

I'm wondering if long running transacations might be the cause (you'll
likely want to do this while perfomance is suffering).

Have a look at pg_stat_activity and see if there are any long running
transacations (or any idle transactions).

I'd also be curious to see the output of the following:

VACUUM ANALYZE VERBOSE pg_listener;

--
Brad Nicholson
Database Administrator, Afilias Canada Corp.

Thomas F.O'Connell wrote:

> It doesn't make all that much more sense. I'd keep posting to the
> lists to let other people continue to take a crack at it. Is the
> system dedicated entirely to postgres? Are you VACUUMing? What kinds
> of operations are being performed and how much data is in the database?
>
> -tfo
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005
>
> On Mar 14, 2005, at 10:03 AM, Werner vd Merwe wrote:
>
>> Hi Thomas,
>>
>> Thank you for your response.
>>
>> Performance does not pick up after a service restart, needs to be a
>> system
>> restart.
>>
>> If we do not do that restart, then things are 'broken bad', as the
>> system
>> becomes incredibly slow. Not broken after the vacuum, it is a gradual
>> decline in performance.
>>
>> Hope that makes more sense.
>>
>> Many thanks
>>
>>
>> -----Original Message-----
>> From: Thomas F. O'Connell [mailto:tfo(at)sitening(dot)com]
>> Sent: 14 March 2005 05:59 PM
>> To: Werner vd Merwe
>> Cc: pgsql-admin(at)postgresql(dot)org
>> Subject: Re: [ADMIN] Performance Question
>>
>> I think you need to provide more information to get any help with your
>> setup.
>>
>> For one thing, why are you "restarting"? Are you restarting the server?
>> Postgres? In general, there should be no need to restart either.
>>
>> Next, what do you mean by "broken bad" after a full vacuum?
>>
>> -tfo
>>
>> --
>> Thomas F. O'Connell
>> Co-Founder, Information Architect
>> Sitening, LLC
>> http://www.sitening.com/
>> 110 30th Avenue North, Suite 6
>> Nashville, TN 37203-6320
>> 615-260-0005
>>
>> On Mar 14, 2005, at 1:08 AM, Werner vd Merwe wrote:
>>
>>> Hi guys,
>>>
>>>
>>>
>>> I have been browsing around and reading up on PostgreSQL performance
>>> to try and tweak our system at the office, as its performance is not
>>> that great.
>>>
>>>
>>>
>>> Many people say that PG is a great DB, and I know that our problems
>>> are purely a setup issue.
>>>
>>>
>>>
>>> After a complete server restart, the system is ok, not fast, but
>>> workable, the problem are increased by the fact that the PG slows
>>> down, for example, we run a full vacuum every night, and after a
>>> restart, it takes about an hour and half, which increases to about 3
>>> hours in two weeks. At that stage everything is broken bad, and we are
>>> forced to do a restart again.
>>>
>>>
>>>
>>> I have tried to follow as much of the documents and HOWTO’s on the
>>> web, but still have some issues.
>>>
>>>
>>>
>>> Here is some info (did a restart last night, so this is best
>>> performance atm)
>>>
>>>
>>>
>>> System:
>>>
>>> Dual XEON 2.4GHz
>>>
>>> 3Gb RAM
>>>
>>>
>>>
>>> Dedicated to PG
>>>
>>>
>>>
>>> Type of apps:
>>>
>>> Mostly JDBC queries running via Tomcat.
>>>
>>>
>>>
>>>
>>>
>>> ----------------------------------------------------------------------
>>> -
>>> --------------------------------------------------------
>>>
>>> ------ Shared Memory Attach/Detach/Change Times --------
>>>
>>> shmid owner attached detached
>>> changed
>>>
>>>
>>>
>>> 131072 postgres Mar 14 09:02:43 Mar 14 09:02:23 Mar
>>> 12 15:14:49
>>>
>>>
>>>
>>>
>>>
>>> ------ Shared Memory Operation/Change Times --------
>>>
>>> shmid owner last-op last-changed
>>>
>>> 786432 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49
>>> 2005
>>>
>>> 819201 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49
>>> 2005
>>>
>>> 851970 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49
>>> 2005
>>>
>>> 884739 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49
>>> 2005
>>>
>>> 917508 postgres Sat Mar 12 15:14:49 2005 Sat Mar 12 15:14:49
>>> 2005
>>>
>>> 950277 postgres Mon Mar 14 09:02:44 2005 Mon Mar 14 09:02:43
>>> 2005
>>>
>>> 983046 postgres Mon Mar 14 09:02:43 2005 Mon Mar 14 09:02:23
>>> 2005
>>>
>>> 1015815 postgres Mon Mar 14 09:02:44 2005 Mon Mar 14 09:02:43
>>> 2005
>>>
>>>
>>>
>>> ------ Message Queues Send/Recv/Change Times --------
>>>
>>> msqid owner send recv change
>>>
>>>
>>>
>>>
>>>
>>> ------ Shared Memory Status --------
>>>
>>> segments allocated 1
>>>
>>> pages allocated 266324
>>>
>>> pages resident 257206
>>>
>>> pages swapped 8619
>>>
>>> Swap performance: 0 attempts 0 successes
>>>
>>>
>>>
>>> ----------------------------------------------------------------------
>>> -
>>> --------------------------------------------------------
>>>
>>>
>>>
>>> 09:03:48 up 2 days, 10:12, 3 users, load average: 0.15, 0.36, 0.31
>>>
>>> 64 processes: 63 sleeping, 1 running, 0 zombie, 0 stopped
>>>
>>> CPU0 states: 2.2% user 1.4% system 0.0% nice 0.0% iowait
>>> 95.4% idle
>>>
>>> CPU1 states: 1.3% user 0.3% system 0.0% nice 0.0% iowait
>>> 97.4% idle
>>>
>>> CPU2 states: 2.3% user 0.1% system 0.0% nice 0.0% iowait
>>> 97.1% idle
>>>
>>> CPU3 states: 0.0% user 0.3% system 0.0% nice 0.0% iowait
>>> 99.2% idle
>>>
>>> Mem: 2063932k av, 2017520k used, 46412k free, 0k shrd,
>>> 79388k buff
>>>
>>> 1434408k actv, 232k in_d, 46268k in_c
>>>
>>> Swap: 2040244k av, 63676k used, 1976568k free
>>> 1678480k cached
>>>
>>>
>>>
>>> PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU
>>> COMMAND
>>>
>>> 15985 postgres 15 0 88796 86M 86192 S 3.4 4.2 0:00 0
>>> postmaster
>>>
>>> 16108 postgres 20 0 21252 20M 19236 S 1.6 1.0 0:00 1
>>> postmaster
>>>
>>> 16094 postgres 18 0 12188 11M 10292 S 0.4 0.5 0:00 3
>>> postmaster
>>>
>>> 24846 postgres 15 0 432 228 120 S 0.0 0.0 0:04 2
>>> postmaster
>>>
>>> 24851 postgres 15 0 1320 1044 24 S 0.0 0.0 0:06 2
>>> postmaster
>>>
>>> 24852 postgres 15 0 628 400 128 S 0.0 0.0 0:18 2
>>> postmaster
>>>
>>> 11207 postgres 20 0 11536 10M 9700 S 0.0 0.5 0:00 2
>>> postmaster
>>>
>>> 15113 postgres 20 0 20908 20M 18796 S 0.0 0.9 0:00 2
>>> postmaster
>>>
>>> 15114 postgres 20 0 12732 11M 10792 S 0.0 0.5 0:00 2
>>> postmaster
>>>
>>> 15606 postgres 20 0 12672 11M 10764 S 0.0 0.5 0:00 3
>>> postmaster
>>>
>>> 15917 postgres 15 0 17172 16M 15220 S 0.0 0.8 0:00 1
>>> postmaster
>>>
>>>
>>>
>>> ----------------------------------------------------------------------
>>> -
>>> --------------------------------------------------------
>>>
>>>
>>>
>>> Postgresql.conf extract
>>>
>>>
>>>
>>> max_connections = 120
>>>
>>> shared_buffers = 131072
>>>
>>> sort_mem = 16384
>>>
>>> vacuum_mem = 8192
>>>
>>> effective_cache_size = 65536
>>>
>>>
>>>
>>> ----------------------------------------------------------------------
>>> -
>>> --------------------------------------------------------
>>>
>>>
>>>
>>> Any ideas will be greatly appreciated.
>>>
>>>
>>>
>>> Kind regards
>>>
>>> Werner vd Merwe
>>>
>>>
>>>
>>>
>>>
>>> --
>>> No virus found in this outgoing message.
>>> Checked by AVG Anti-Virus.
>>> Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11
>>>
>>
>> --
>> No virus found in this incoming message.
>> Checked by AVG Anti-Virus.
>> Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11
>>
>>
>> --
>> No virus found in this outgoing message.
>> Checked by AVG Anti-Virus.
>> Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 2005/03/11
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings