pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance question
21+ messages / 11 participants
[nested] [flat]
* Performance question
@ 2002-02-26 09:39 Brian McCane <bmccane@mccons.net>
0 siblings, 1 reply; 21+ messages in thread
From: Brian McCane @ 2002-02-26 09:39 UTC (permalink / raw)
To: pgsql-admin
First of all, I have somewhat fixed my locking problems from before, at
least to the point that the app is mostly usable. I will tweak it more
later, when I have some more free time to myself :)
Now:
I use an english alphabet exclusively in an app I am working on
for another customer. If I need to put non-english characters, I use the
HTML encoding functions from HTML::Entities in Perl. This causes me to
store strings like:
You’re antique vase cost ¥5.000.000
Okay, you get the picture any way.
Since I am using a strict alphabet like this, would it be faster to build
PostgreSQL without Multibyte support? Or has the indexing, etc. been
optimized so much for multibyte that it is actually faster than single
byte stuff?
- brian
Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance question
@ 2002-02-26 15:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brian McCane <bmccane@mccons.net>
0 siblings, 0 replies; 21+ messages in thread
From: Tom Lane @ 2002-02-26 15:15 UTC (permalink / raw)
To: Brian McCane <bmccane@mccons.net>; +Cc: pgsql-admin
Brian McCane <bmccane@mccons.net> writes:
> Since I am using a strict alphabet like this, would it be faster to build
> PostgreSQL without Multibyte support? Or has the indexing, etc. been
> optimized so much for multibyte that it is actually faster than single
> byte stuff?
A non-multibyte build will certainly be faster than a multibyte build.
How much is hard to say, especially without any specific operation mix
in mind. We'd be interested to hear what sort of results you get ...
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Performance question
@ 2003-03-05 18:11 Bob Smith <bsmith@h-e.com>
0 siblings, 0 replies; 21+ messages in thread
From: Bob Smith @ 2003-03-05 18:11 UTC (permalink / raw)
To: pgsql-admin
When I execute a query on a new connection, the performance is many
times slower than if the query is repeated. In other words, if I start
psql, execute the query, then repeat it immediately, the second time it
takes only about 20% as long to execute. Now here's the confusing
part, if I exit psql then start it up again, the same thing will occur
on the new connection as well, the first execution takes 5x as long
again. I don't understand this, it would make sense to me that the
second execution being faster is due to disk caching on the server, but
then why is it slower again on every new connection? Disk caching
should benefit all current and new connections until the cache is
flushed, which on this server shouldn't happen for a long time, the
load is light and it has lots of RAM. Is Postgres doing some kind of
caching itself that lasts only for the life of one backend process? If
so, is there any way to make this caching persistent across backends?
Server particulars:
Postgres 7.2.1, Mac OS X Server 10.1.5, dual 1GHz CPUs, 1.5GB memory
Thanks!
Bob Smith
Hammett & Edison, Inc.
bsmith@h-e.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Performance Question
@ 2005-03-14 07:08 Werner vd Merwe <werner@saicom.co.za>
0 siblings, 2 replies; 21+ messages in thread
From: Werner vd Merwe @ 2005-03-14 07:08 UTC (permalink / raw)
To: pgsql-admin
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 HOWTOs 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-14 15:58 Thomas F.O'Connell <tfo@sitening.com>
parent: Werner vd Merwe <werner@saicom.co.za>
1 sibling, 0 replies; 21+ messages in thread
From: Thomas F.O'Connell @ 2005-03-14 15:58 UTC (permalink / raw)
To: Werner vd Merwe <werner@saicom.co.za>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-14 16:10 Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 21+ messages in thread
From: Thomas F.O'Connell @ 2005-03-14 16:10 UTC (permalink / raw)
To: Werner vd Merwe <werner@saicom.co.za>; +Cc: pgsql-admin
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@sitening.com]
> Sent: 14 March 2005 05:59 PM
> To: Werner vd Merwe
> Cc: pgsql-admin@postgresql.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
>
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-14 16:36 Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 0 replies; 21+ messages in thread
From: Thomas F.O'Connell @ 2005-03-14 16:36 UTC (permalink / raw)
To: Werner vd Merwe <werner@saicom.co.za>; +Cc: pgsql-admin
Well, there's always the dbsize module in contrib to check actual size
on disk. I was thinking more in terms of approximate numbers of tables
and rows in those tables.
-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:21 AM, Werner vd Merwe wrote:
> Yeah - only postgres running on the server, VACUUM happens every
> night, with
> intermitted ANALYSE on selected tables during the day.
>
> Not sure how much data in the DB, not sure how to check that?
> Operations is all via JDBC, mostly standard queries, with quite a bit
> if
> inner joins and inline selects.
>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas
> F.O'Connell
> Sent: 14 March 2005 06:10 PM
> To: Werner vd Merwe
> Cc: PgSQL Admin
> Subject: Re: [ADMIN] Performance Question
>
> 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@sitening.com]
>> Sent: 14 March 2005 05:59 PM
>> To: Werner vd Merwe
>> Cc: pgsql-admin@postgresql.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
>
> --
> 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
>
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-14 17:10 Brad Nicholson <bnichols@ca.afilias.info>
parent: Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 0 replies; 21+ messages in thread
From: Brad Nicholson @ 2005-03-14 17:10 UTC (permalink / raw)
To: pgsql-admin
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@sitening.com]
>> Sent: 14 March 2005 05:59 PM
>> To: Werner vd Merwe
>> Cc: pgsql-admin@postgresql.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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-15 06:33 Werner vd Merwe <werner@saicom.co.za>
0 siblings, 1 reply; 21+ messages in thread
From: Werner vd Merwe @ 2005-03-15 06:33 UTC (permalink / raw)
To: 'Thomas F.O'Connell' <tfo@sitening.com>
Hi Thomas,
The DB has 134 tables, sizes vary, one of the most used and thus biggest
problems currently have around 3,000,000 records in, with 15 fields,
around
15000 records per day added.
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas
F.O'Connell
Sent: 14 March 2005 06:37 PM
To: Werner vd Merwe
Cc: PgSQL Admin
Subject: Re: [ADMIN] Performance Question
Well, there's always the dbsize module in contrib to check actual size
on disk. I was thinking more in terms of approximate numbers of tables
and rows in those tables.
-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:21 AM, Werner vd Merwe wrote:
> Yeah - only postgres running on the server, VACUUM happens every
> night, with
> intermitted ANALYSE on selected tables during the day.
>
> Not sure how much data in the DB, not sure how to check that?
> Operations is all via JDBC, mostly standard queries, with quite a bit
> if
> inner joins and inline selects.
>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas
> F.O'Connell
> Sent: 14 March 2005 06:10 PM
> To: Werner vd Merwe
> Cc: PgSQL Admin
> Subject: Re: [ADMIN] Performance Question
>
> 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@sitening.com]
>> Sent: 14 March 2005 05:59 PM
>> To: Werner vd Merwe
>> Cc: pgsql-admin@postgresql.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
>
> --
> 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 4: Don't 'kill -9' the postmaster
--
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-15 15:32 Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 1 reply; 21+ messages in thread
From: Brad Nicholson @ 2005-03-15 15:32 UTC (permalink / raw)
To: pgsql-admin
Werner vd Merwe wrote:
>Output of VACUUM ANALYSE VERBOSE pg_listener:
>
>Query OK, 0 rows affected (0.06 sec)
>INFO: vacuuming "pg_catalog.pg_listener"
>INFO: "pg_listener": found 0 removable, 0 nonremovable row versions in 0
>pages
>INFO: analyzing "pg_catalog.pg_listener"
>INFO: "pg_listener": 0 pages, 0 rows sampled, 0 estimated total rows
>
>
>
No problems there.
>At any given time we have between 10 and 20 IDLE connections.
>
>
>
How long have the transactions been IDLE for? Do the IDLE transactions
have any locks on anything (check pg_locks)?
I suspect that (gently) killing the IDLE transactions that are sitting
around doing nothing will prevent you from having to restart the postmaster.
--
Brad Nicholson
Database Administrator, Afilias Canada Corp.
>-----Original Message-----
>From: pgsql-admin-owner@postgresql.org
>[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brad Nicholson
>Sent: 14 March 2005 07:10 PM
>To: PgSQL Admin
>Subject: Re: [ADMIN] 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;
>
>
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-15 15:54 weiping <laser@tacacs.zhengmai.net.cn>
parent: Werner vd Merwe <werner@saicom.co.za>
1 sibling, 1 reply; 21+ messages in thread
From: weiping @ 2005-03-15 15:54 UTC (permalink / raw)
To: Werner vd Merwe <werner@saicom.co.za>; +Cc: pgsql-admin
what' your JDBC version?
if it's pretty old, then upgrade to newest one is a bet.
Don't know if it could solve the problem, but old
version of JDBC did have some problem in transaction
handling, we've experienced such problem not so long
before.
regards laser
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-16 06:28 Werner vd Merwe <werner@saicom.co.za>
parent: Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 0 replies; 21+ messages in thread
From: Werner vd Merwe @ 2005-03-16 06:28 UTC (permalink / raw)
To: pgsql-admin
Hi Brad,
Just before I carry on - I am not sure if top-posting is 'allowed' on this
list, if not, please let me know and I'll stop :)
Will gently be level 15?
There are a couple of locks, both exclusive and shared...
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brad Nicholson
Sent: 15 March 2005 05:33 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Performance Question
Werner vd Merwe wrote:
>Output of VACUUM ANALYSE VERBOSE pg_listener:
>
>Query OK, 0 rows affected (0.06 sec)
>INFO: vacuuming "pg_catalog.pg_listener"
>INFO: "pg_listener": found 0 removable, 0 nonremovable row versions in 0
>pages
>INFO: analyzing "pg_catalog.pg_listener"
>INFO: "pg_listener": 0 pages, 0 rows sampled, 0 estimated total rows
>
>
>
No problems there.
>At any given time we have between 10 and 20 IDLE connections.
>
>
>
How long have the transactions been IDLE for? Do the IDLE transactions
have any locks on anything (check pg_locks)?
I suspect that (gently) killing the IDLE transactions that are sitting
around doing nothing will prevent you from having to restart the postmaster.
--
Brad Nicholson
Database Administrator, Afilias Canada Corp.
>-----Original Message-----
>From: pgsql-admin-owner@postgresql.org
>[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brad Nicholson
>Sent: 14 March 2005 07:10 PM
>To: PgSQL Admin
>Subject: Re: [ADMIN] 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;
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 2005/03/15
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 2005/03/15
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-16 06:45 Werner vd Merwe <werner@saicom.co.za>
parent: weiping <laser@tacacs.zhengmai.net.cn>
0 siblings, 1 reply; 21+ messages in thread
From: Werner vd Merwe @ 2005-03-16 06:45 UTC (permalink / raw)
To: pgsql-admin
-----Original Message-----
From: weiping [mailto:laser@tacacs.zhengmai.net.cn]
Sent: 15 March 2005 05:55 PM
To: Werner vd Merwe
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Performance Question
what' your JDBC version?
if it's pretty old, then upgrade to newest one is a bet.
Don't know if it could solve the problem, but old
version of JDBC did have some problem in transaction
handling, we've experienced such problem not so long
before.
regards laser
--
Hi,
We are using JDK1.4.2_06
Regards
Werner
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 2005/03/15
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-19 18:09 Thomas F.O'Connell <tfo@sitening.com>
parent: Werner vd Merwe <werner@saicom.co.za>
0 siblings, 1 reply; 21+ messages in thread
From: Thomas F.O'Connell @ 2005-03-19 18:09 UTC (permalink / raw)
To: Werner vd Merwe <werner@saicom.co.za>; +Cc: pgsql-admin
The long and short of it is that you should never need to restart
either the main server or postgres in order to achieve better
performance. If the issue is that you are not vacuuming frequently
enough, then you might consider pg_autovacuum, which is located in
contrib.
You could also post more information about your system, including
platform, postgres version, and salient features of your
postgresql.conf file.
The more information you're able to provide, the more help people on
this list will be able to give you.
-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 15, 2005, at 12:33 AM, Werner vd Merwe wrote:
> Hi Thomas,
>
> The DB has 134 tables, sizes vary, one of the most used and thus
> biggest
> problems currently have around 3,000,000 records in, with 15 fields,
> around
> 15000 records per day added.
>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas
> F.O'Connell
> Sent: 14 March 2005 06:37 PM
> To: Werner vd Merwe
> Cc: PgSQL Admin
> Subject: Re: [ADMIN] Performance Question
>
> Well, there's always the dbsize module in contrib to check actual size
> on disk. I was thinking more in terms of approximate numbers of tables
> and rows in those tables.
>
> -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:21 AM, Werner vd Merwe wrote:
>
>> Yeah - only postgres running on the server, VACUUM happens every
>> night, with
>> intermitted ANALYSE on selected tables during the day.
>>
>> Not sure how much data in the DB, not sure how to check that?
>> Operations is all via JDBC, mostly standard queries, with quite a bit
>> if
>> inner joins and inline selects.
>>
>>
>> -----Original Message-----
>> From: pgsql-admin-owner@postgresql.org
>> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas
>> F.O'Connell
>> Sent: 14 March 2005 06:10 PM
>> To: Werner vd Merwe
>> Cc: PgSQL Admin
>> Subject: Re: [ADMIN] Performance Question
>>
>> 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@sitening.com]
>>> Sent: 14 March 2005 05:59 PM
>>> To: Werner vd Merwe
>>> Cc: pgsql-admin@postgresql.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
>>
>> --
>> 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 4: Don't 'kill -9' the postmaster
>
> --
> 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 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-21 03:40 Weiping <laser@qmail.zhengmai.net.cn>
parent: Werner vd Merwe <werner@saicom.co.za>
0 siblings, 0 replies; 21+ messages in thread
From: Weiping @ 2005-03-21 03:40 UTC (permalink / raw)
To: Werner vd Merwe <werner@saicom.co.za>; +Cc: pgsql-admin
No, I mean JDBC version, not JDK version.
go to jdbc.postgresql.org and download newest version of JDBC to
have a try.
regards laser
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-22 06:31 Werner vd Merwe <werner@saicom.co.za>
parent: Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 21+ messages in thread
From: Werner vd Merwe @ 2005-03-22 06:31 UTC (permalink / raw)
To: 'Thomas F. O'Connell' <tfo@sitening.com>; +Cc: pgsql-admin
Hi Thomas,
We perform a full VACUUM and ANALYSE every night, and a ANALYSE on selected
tables throughout the day.
Platform is Redhat 9.0 and PG 7.4
Snippet of postgresql.conf:
Shared_buffers = 32768
Effective_cache_size = 131072
Hardware:
Dual Xeon 2.4
2Gb RAM
Raid 5 SCSI (3 X 73Gb)
If any more info will be helpful, please let me know!
Many thanks
-----Original Message-----
From: Thomas F. O'Connell [mailto:tfo@sitening.com]
Sent: 19 March 2005 08:09 PM
To: Werner vd Merwe
Cc: PgSQL Admin
Subject: Re: [ADMIN] Performance Question
The long and short of it is that you should never need to restart
either the main server or postgres in order to achieve better
performance. If the issue is that you are not vacuuming frequently
enough, then you might consider pg_autovacuum, which is located in
contrib.
You could also post more information about your system, including
platform, postgres version, and salient features of your
postgresql.conf file.
The more information you're able to provide, the more help people on
this list will be able to give you.
-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 15, 2005, at 12:33 AM, Werner vd Merwe wrote:
> Hi Thomas,
>
> The DB has 134 tables, sizes vary, one of the most used and thus
> biggest
> problems currently have around 3,000,000 records in, with 15 fields,
> around
> 15000 records per day added.
>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas
> F.O'Connell
> Sent: 14 March 2005 06:37 PM
> To: Werner vd Merwe
> Cc: PgSQL Admin
> Subject: Re: [ADMIN] Performance Question
>
> Well, there's always the dbsize module in contrib to check actual size
> on disk. I was thinking more in terms of approximate numbers of tables
> and rows in those tables.
>
> -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:21 AM, Werner vd Merwe wrote:
>
>> Yeah - only postgres running on the server, VACUUM happens every
>> night, with
>> intermitted ANALYSE on selected tables during the day.
>>
>> Not sure how much data in the DB, not sure how to check that?
>> Operations is all via JDBC, mostly standard queries, with quite a bit
>> if
>> inner joins and inline selects.
>>
>>
>> -----Original Message-----
>> From: pgsql-admin-owner@postgresql.org
>> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas
>> F.O'Connell
>> Sent: 14 March 2005 06:10 PM
>> To: Werner vd Merwe
>> Cc: PgSQL Admin
>> Subject: Re: [ADMIN] Performance Question
>>
>> 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@sitening.com]
>>> Sent: 14 March 2005 05:59 PM
>>> To: Werner vd Merwe
>>> Cc: pgsql-admin@postgresql.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
>>
>> --
>> 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 4: Don't 'kill -9' the postmaster
>
> --
> 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 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 2005/03/18
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 2005/03/21
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance Question
@ 2005-03-22 15:55 Thomas F.O'Connell <tfo@sitening.com>
parent: Werner vd Merwe <werner@saicom.co.za>
0 siblings, 0 replies; 21+ messages in thread
From: Thomas F.O'Connell @ 2005-03-22 15:55 UTC (permalink / raw)
To: Werner vd Merwe <werner@saicom.co.za>; +Cc: pgsql-admin
Have you considered trying pg_autovacuum, which is in contrib? It
actually sets and monitors thresholds to try to determine dynamically
when tables need vacuuming.
-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 22, 2005, at 12:31 AM, Werner vd Merwe wrote:
> Hi Thomas,
>
> We perform a full VACUUM and ANALYSE every night, and a ANALYSE on
> selected
> tables throughout the day.
>
> Platform is Redhat 9.0 and PG 7.4
>
> Snippet of postgresql.conf:
> Shared_buffers = 32768
> Effective_cache_size = 131072
>
> Hardware:
> Dual Xeon 2.4
> 2Gb RAM
> Raid 5 SCSI (3 X 73Gb)
>
> If any more info will be helpful, please let me know!
>
> Many thanks
>
> -----Original Message-----
> From: Thomas F. O'Connell [mailto:tfo@sitening.com]
> Sent: 19 March 2005 08:09 PM
> To: Werner vd Merwe
> Cc: PgSQL Admin
> Subject: Re: [ADMIN] Performance Question
>
> The long and short of it is that you should never need to restart
> either the main server or postgres in order to achieve better
> performance. If the issue is that you are not vacuuming frequently
> enough, then you might consider pg_autovacuum, which is located in
> contrib.
>
> You could also post more information about your system, including
> platform, postgres version, and salient features of your
> postgresql.conf file.
>
> The more information you're able to provide, the more help people on
> this list will be able to give you.
>
> -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 15, 2005, at 12:33 AM, Werner vd Merwe wrote:
>
>> Hi Thomas,
>>
>> The DB has 134 tables, sizes vary, one of the most used and thus
>> biggest
>> problems currently have around 3,000,000 records in, with 15 fields,
>> around
>> 15000 records per day added.
>>
>>
>> -----Original Message-----
>> From: pgsql-admin-owner@postgresql.org
>> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas
>> F.O'Connell
>> Sent: 14 March 2005 06:37 PM
>> To: Werner vd Merwe
>> Cc: PgSQL Admin
>> Subject: Re: [ADMIN] Performance Question
>>
>> Well, there's always the dbsize module in contrib to check actual size
>> on disk. I was thinking more in terms of approximate numbers of tables
>> and rows in those tables.
>>
>> -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:21 AM, Werner vd Merwe wrote:
>>
>>> Yeah - only postgres running on the server, VACUUM happens every
>>> night, with
>>> intermitted ANALYSE on selected tables during the day.
>>>
>>> Not sure how much data in the DB, not sure how to check that?
>>> Operations is all via JDBC, mostly standard queries, with quite a bit
>>> if
>>> inner joins and inline selects.
>>>
>>>
>>> -----Original Message-----
>>> From: pgsql-admin-owner@postgresql.org
>>> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas
>>> F.O'Connell
>>> Sent: 14 March 2005 06:10 PM
>>> To: Werner vd Merwe
>>> Cc: PgSQL Admin
>>> Subject: Re: [ADMIN] Performance Question
>>>
>>> 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@sitening.com]
>>>> Sent: 14 March 2005 05:59 PM
>>>> To: Werner vd Merwe
>>>> Cc: pgsql-admin@postgresql.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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Performance question
@ 2006-12-02 05:18 Benjamin Krajmalnik <kraj@illumen.com>
0 siblings, 1 reply; 21+ messages in thread
From: Benjamin Krajmalnik @ 2006-12-02 05:18 UTC (permalink / raw)
To: pgsql-admin
I am battling a performance issue and was wondering if someone could
help. PostgreSQL 8.1.5, FreeBSD.
I have a very intense stored procedure which performs real time
aggregation of data.
I captured the stored procedure calls from a production system and
pumped them through psql, logging duration.
The stored procedure ran at about 30ms per stored procedure call - which
was significantly faster than the previous production system. Based on
these results, I was extremely optimistic and migrated to the new
server.
Now that we turned up the new server, the stored procedure is taking
over 250ms.
These are the execution times as recorded by enable "all" logging.
The only difference is that when I pumped the data in the test
environment, I pumped it by reading the script via psql, whereas the
current system is calling the stored procedure via ODBC through a
100Mbit ethernet connection.
I cannot explain the difference between the 2 scenarios. Does the
duration include the overhead of the ODBC layer?
Now, the second part that was troubling relates to the execution
planner.
Two of the tables in which data is being entered are partitioned tables.
Data is being routed to the partition by using rules. Each partition
has a check constraint on a date range. Constraint exclusion is on.
The first partitioned table is very simple, and the stored procedure
simply inserts a record for each test which is executed. These are
later used by a procedure running in the background which calculates
statistical data.
My problem is with the second partitioned table. As part of my
troubleshooting, I omitted the code from the stored procedure which
accesses this data. In this table, I attempt to retrieve the daily
record for a specific test. If it is not found, after aggregating data
I insert it. If the record already exists, data is aggregated and the
record is updated.
The key is composed of a date field (monthdate) and an int4 field
(kstestsysid).
The check constraints are of the form of:
ALTER TABLE tblkssnapshot12
ADD CONSTRAINT tblkssnapshot12_chk CHECK (monthdate >= '2006-12-01
00:00:00'::timestamp without time zone AND monthdate < '2007-01-01
00:00:00'::timestamp without time zone);
I ran a sample query which would be issued by the stored procedure with
the explain option.
The query was:
select monthdate, kstestssysid from tblkssnapshotdaily where monthdate =
'2006-12-01' and kstestssysid = 3143
The explain is as follows:
select monthdate, kstestssysid from tblkssnapshotdaily where monthdate =
'2006-12-01' and kstestssysid = 3143
Result (cost=0.00..122.49 rows=25 width=8)
-> Append (cost=0.00..122.49 rows=25 width=8)
-> Index Scan using tblkssnapshotdaily_idx_monthtest on
tblkssnapshotdaily (cost=0.00..3.52 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot01_idx_monthtest on
tblkssnapshot01 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot02_idx_monthtest on
tblkssnapshot02 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot03_idx_monthtest on
tblkssnapshot03 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot04_idx_monthtest on
tblkssnapshot04 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot05_idx_monthtest on
tblkssnapshot05 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot06_idx_monthtest on
tblkssnapshot06 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot07_idx_monthtest on
tblkssnapshot07 tblkssnapshotdaily (cost=0.00..5.94 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot08_idx_monthtest on
tblkssnapshot08 tblkssnapshotdaily (cost=0.00..4.73 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot09_idx_monthtest on
tblkssnapshot09 tblkssnapshotdaily (cost=0.00..5.56 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot10_idx_monthtest on
tblkssnapshot10 tblkssnapshotdaily (cost=0.00..5.75 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot11_idx_monthtest on
tblkssnapshot11 tblkssnapshotdaily (cost=0.00..5.81 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot12_idx_monthtest on
tblkssnapshot12 tblkssnapshotdaily (cost=0.00..4.33 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot13_idx_monthtest on
tblkssnapshot13 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot14_idx_monthtest on
tblkssnapshot14 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot15_idx_monthtest on
tblkssnapshot15 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot16_idx_monthtest on
tblkssnapshot16 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot17_idx_monthtest on
tblkssnapshot17 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot18_idx_monthtest on
tblkssnapshot18 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot19_idx_monthtest on
tblkssnapshot19 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot20_idx_monthtest on
tblkssnapshot20 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot21_idx_monthtest on
tblkssnapshot21 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot22_idx_monthtest on
tblkssnapshot22 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot23_idx_monthtest on
tblkssnapshot23 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
-> Index Scan using tblkssnapshot24_idx_monthtest on
tblkssnapshot24 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
Index Cond: ((monthdate = '2006-12-01'::date) AND
(kstestssysid = 3143))
If constraint exclusion is on, why are index scans taking place on all
of the tables and not only on the only partitioned table for which the
constraint exists?
I went to a partitioned table thinking this would improve performance
from the previous schema in which all of the data was in the parent
table, but the end result was significantly slower performance (by
orders of magnitude).
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance question
@ 2006-12-05 00:53 Jim Nasby <decibel@decibel.org>
parent: Benjamin Krajmalnik <kraj@illumen.com>
0 siblings, 0 replies; 21+ messages in thread
From: Jim Nasby @ 2006-12-05 00:53 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@illumen.com>; +Cc: pgsql-admin
Are you sure you're doing an apples-apples comparison? Is the load on
both machines the same, or does production have extra stuff running?
Have you tried your original test again in the same setup without
ODBC to eliminate that possibility? (or redone your original test
with ODBC).
As for the constraint exclusion, try changing the query so that
'2006-12-01' is cast to a timestamp (or change the constraints so
that you're constraining dates... the check constraints should really
match the type of the underlying table).
On Dec 1, 2006, at 9:18 PM, Benjamin Krajmalnik wrote:
> I am battling a performance issue and was wondering if someone could
> help. PostgreSQL 8.1.5, FreeBSD.
>
> I have a very intense stored procedure which performs real time
> aggregation of data.
>
> I captured the stored procedure calls from a production system and
> pumped them through psql, logging duration.
> The stored procedure ran at about 30ms per stored procedure call -
> which
> was significantly faster than the previous production system.
> Based on
> these results, I was extremely optimistic and migrated to the new
> server.
>
> Now that we turned up the new server, the stored procedure is taking
> over 250ms.
> These are the execution times as recorded by enable "all" logging.
>
> The only difference is that when I pumped the data in the test
> environment, I pumped it by reading the script via psql, whereas the
> current system is calling the stored procedure via ODBC through a
> 100Mbit ethernet connection.
>
> I cannot explain the difference between the 2 scenarios. Does the
> duration include the overhead of the ODBC layer?
>
> Now, the second part that was troubling relates to the execution
> planner.
>
>
> Two of the tables in which data is being entered are partitioned
> tables.
> Data is being routed to the partition by using rules. Each partition
> has a check constraint on a date range. Constraint exclusion is on.
>
> The first partitioned table is very simple, and the stored procedure
> simply inserts a record for each test which is executed. These are
> later used by a procedure running in the background which calculates
> statistical data.
>
> My problem is with the second partitioned table. As part of my
> troubleshooting, I omitted the code from the stored procedure which
> accesses this data. In this table, I attempt to retrieve the daily
> record for a specific test. If it is not found, after aggregating
> data
> I insert it. If the record already exists, data is aggregated and the
> record is updated.
>
> The key is composed of a date field (monthdate) and an int4 field
> (kstestsysid).
> The check constraints are of the form of:
>
> ALTER TABLE tblkssnapshot12
> ADD CONSTRAINT tblkssnapshot12_chk CHECK (monthdate >= '2006-12-01
> 00:00:00'::timestamp without time zone AND monthdate < '2007-01-01
> 00:00:00'::timestamp without time zone);
>
> I ran a sample query which would be issued by the stored procedure
> with
> the explain option.
> The query was:
>
> select monthdate, kstestssysid from tblkssnapshotdaily where
> monthdate =
> '2006-12-01' and kstestssysid = 3143
>
> The explain is as follows:
>
> select monthdate, kstestssysid from tblkssnapshotdaily where
> monthdate =
> '2006-12-01' and kstestssysid = 3143
> Result (cost=0.00..122.49 rows=25 width=8)
> -> Append (cost=0.00..122.49 rows=25 width=8)
> -> Index Scan using tblkssnapshotdaily_idx_monthtest on
> tblkssnapshotdaily (cost=0.00..3.52 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot01_idx_monthtest on
> tblkssnapshot01 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot02_idx_monthtest on
> tblkssnapshot02 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot03_idx_monthtest on
> tblkssnapshot03 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot04_idx_monthtest on
> tblkssnapshot04 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot05_idx_monthtest on
> tblkssnapshot05 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot06_idx_monthtest on
> tblkssnapshot06 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot07_idx_monthtest on
> tblkssnapshot07 tblkssnapshotdaily (cost=0.00..5.94 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot08_idx_monthtest on
> tblkssnapshot08 tblkssnapshotdaily (cost=0.00..4.73 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot09_idx_monthtest on
> tblkssnapshot09 tblkssnapshotdaily (cost=0.00..5.56 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot10_idx_monthtest on
> tblkssnapshot10 tblkssnapshotdaily (cost=0.00..5.75 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot11_idx_monthtest on
> tblkssnapshot11 tblkssnapshotdaily (cost=0.00..5.81 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot12_idx_monthtest on
> tblkssnapshot12 tblkssnapshotdaily (cost=0.00..4.33 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot13_idx_monthtest on
> tblkssnapshot13 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot14_idx_monthtest on
> tblkssnapshot14 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot15_idx_monthtest on
> tblkssnapshot15 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot16_idx_monthtest on
> tblkssnapshot16 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot17_idx_monthtest on
> tblkssnapshot17 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot18_idx_monthtest on
> tblkssnapshot18 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot19_idx_monthtest on
> tblkssnapshot19 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot20_idx_monthtest on
> tblkssnapshot20 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot21_idx_monthtest on
> tblkssnapshot21 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot22_idx_monthtest on
> tblkssnapshot22 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot23_idx_monthtest on
> tblkssnapshot23 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
> -> Index Scan using tblkssnapshot24_idx_monthtest on
> tblkssnapshot24 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> Index Cond: ((monthdate = '2006-12-01'::date) AND
> (kstestssysid = 3143))
>
> If constraint exclusion is on, why are index scans taking place on all
> of the tables and not only on the only partitioned table for which the
> constraint exists?
>
> I went to a partitioned table thinking this would improve performance
> from the previous schema in which all of the data was in the parent
> table, but the end result was significantly slower performance (by
> orders of magnitude).
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance question
@ 2006-12-05 03:25 Jim C. Nasby <decibel@decibel.org>
0 siblings, 0 replies; 21+ messages in thread
From: Jim C. Nasby @ 2006-12-05 03:25 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@illumen.com>; +Cc: pgsql-admin
Please cc the list so others can learn and help.
Yes, if ODBC is tearing the connection down after every call,
performance *WILL* suck. Setup some kind of persistent connection.
Better yet, don't make lots of tiny calls to the database if you can
avoid it.
On Mon, Dec 04, 2006 at 06:28:03PM -0700, Benjamin Krajmalnik wrote:
> Hi Jim,
>
> Apples to apples (as best as I can tell).
> The test procedure was as follows:
>
> I captured the SQL statements which call the stored procedures from our
> monitoring box to a file.
> I copied the file to the FreeBSD data server.
> Executed psql, and pumped the file to it through \i filename.
> Execution speed for the code in question was sub 10 seconds.
> Running the query directly via ODBC is taking about 60 ms.
>
> The numbers I just stated above are after I removed the code which was
> accessing the one partitioned table where the constraint exclusion was
> not taking place (more on that later). This code happened to be
> updating a daily aggregate - so each SP call was selecting the test
> record for the day and aggregating data. I ended up adding a few more
> fields to a records which keeps some data in arrays for quick access to
> generate our graphs, and now I run a scheduled task at midnight which
> takes the previous days aggregates and creates our snapshot records
> (which we use for historical trending of up to 24 months). This process
> now takes about 5 seconds, whereas running it through the stored
> procedure it was adding almost 200 miliseconds to the execution time).
> I assume this was caused by the fact that there was a select - and the
> execution planner was not isolating to the single partitioned table,
> followed by either an insert or another update.
> The new code is much more efficient and loads the db much less, so it
> will be the preferred method.
> Right now the datra path between the monitoring appliacne and the data
> server is 100Mbit. We are moving it to Gig tonight - so at least I hope
> I will realize some speed advantages. I have been monitoring the
> switch, and the actual throughput is only 150kbit/sec, so I do not have
> a data throughput bpttlenecg, but rather I suspect latencies in the ODBC
> connection.
>
> The item I found interesting is that the statement duration was much
> longer.
> What I am thinking is that when running through psql, it is using a
> local pipe.
> When running through ODBC, each stored procedure call is creating a
> connection and then disconnecting, so it has added a huge overhead. I
> think the duration is including the time it is taking to
> connect/authenticate disconnect/teardown.
>
> Thanks for the tip on the constraint exclusion.
> I need to go back and see how we are querying it for the reports, and
> will adjust it accordingly.
> Does it make a difference if it is timestamp or timestamp without
> timezone?
>
>
> > -----Original Message-----
> > From: Jim Nasby [mailto:decibel@decibel.org]
> > Sent: Monday, December 04, 2006 5:53 PM
> > To: Benjamin Krajmalnik
> > Cc: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] Performance question
> >
> > Are you sure you're doing an apples-apples comparison? Is the
> > load on both machines the same, or does production have extra
> > stuff running?
> > Have you tried your original test again in the same setup
> > without ODBC to eliminate that possibility? (or redone your
> > original test with ODBC).
> >
> > As for the constraint exclusion, try changing the query so
> > that '2006-12-01' is cast to a timestamp (or change the
> > constraints so that you're constraining dates... the check
> > constraints should really match the type of the underlying table).
> >
> > On Dec 1, 2006, at 9:18 PM, Benjamin Krajmalnik wrote:
> >
> > > I am battling a performance issue and was wondering if
> > someone could
> > > help. PostgreSQL 8.1.5, FreeBSD.
> > >
> > > I have a very intense stored procedure which performs real time
> > > aggregation of data.
> > >
> > > I captured the stored procedure calls from a production system and
> > > pumped them through psql, logging duration.
> > > The stored procedure ran at about 30ms per stored procedure call -
> > > which
> > > was significantly faster than the previous production system.
> > > Based on
> > > these results, I was extremely optimistic and migrated to the new
> > > server.
> > >
> > > Now that we turned up the new server, the stored procedure
> > is taking
> > > over 250ms.
> > > These are the execution times as recorded by enable "all" logging.
> > >
> > > The only difference is that when I pumped the data in the test
> > > environment, I pumped it by reading the script via psql,
> > whereas the
> > > current system is calling the stored procedure via ODBC through a
> > > 100Mbit ethernet connection.
> > >
> > > I cannot explain the difference between the 2 scenarios. Does the
> > > duration include the overhead of the ODBC layer?
> > >
> > > Now, the second part that was troubling relates to the execution
> > > planner.
> > >
> > >
> > > Two of the tables in which data is being entered are partitioned
> > > tables.
> > > Data is being routed to the partition by using rules. Each
> > partition
> > > has a check constraint on a date range. Constraint exclusion is on.
> > >
> > > The first partitioned table is very simple, and the stored
> > procedure
> > > simply inserts a record for each test which is executed. These are
> > > later used by a procedure running in the background which
> > calculates
> > > statistical data.
> > >
> > > My problem is with the second partitioned table. As part of my
> > > troubleshooting, I omitted the code from the stored procedure which
> > > accesses this data. In this table, I attempt to retrieve the daily
> > > record for a specific test. If it is not found, after aggregating
> > > data I insert it. If the record already exists, data is aggregated
> > > and the record is updated.
> > >
> > > The key is composed of a date field (monthdate) and an int4 field
> > > (kstestsysid).
> > > The check constraints are of the form of:
> > >
> > > ALTER TABLE tblkssnapshot12
> > > ADD CONSTRAINT tblkssnapshot12_chk CHECK (monthdate >=
> > '2006-12-01
> > > 00:00:00'::timestamp without time zone AND monthdate < '2007-01-01
> > > 00:00:00'::timestamp without time zone);
> > >
> > > I ran a sample query which would be issued by the stored procedure
> > > with the explain option.
> > > The query was:
> > >
> > > select monthdate, kstestssysid from tblkssnapshotdaily
> > where monthdate
> > > = '2006-12-01' and kstestssysid = 3143
> > >
> > > The explain is as follows:
> > >
> > > select monthdate, kstestssysid from tblkssnapshotdaily
> > where monthdate
> > > = '2006-12-01' and kstestssysid = 3143 Result (cost=0.00..122.49
> > > rows=25 width=8)
> > > -> Append (cost=0.00..122.49 rows=25 width=8)
> > > -> Index Scan using tblkssnapshotdaily_idx_monthtest on
> > > tblkssnapshotdaily (cost=0.00..3.52 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot01_idx_monthtest on
> > > tblkssnapshot01 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot02_idx_monthtest on
> > > tblkssnapshot02 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot03_idx_monthtest on
> > > tblkssnapshot03 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot04_idx_monthtest on
> > > tblkssnapshot04 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot05_idx_monthtest on
> > > tblkssnapshot05 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot06_idx_monthtest on
> > > tblkssnapshot06 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot07_idx_monthtest on
> > > tblkssnapshot07 tblkssnapshotdaily (cost=0.00..5.94 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot08_idx_monthtest on
> > > tblkssnapshot08 tblkssnapshotdaily (cost=0.00..4.73 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot09_idx_monthtest on
> > > tblkssnapshot09 tblkssnapshotdaily (cost=0.00..5.56 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot10_idx_monthtest on
> > > tblkssnapshot10 tblkssnapshotdaily (cost=0.00..5.75 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot11_idx_monthtest on
> > > tblkssnapshot11 tblkssnapshotdaily (cost=0.00..5.81 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot12_idx_monthtest on
> > > tblkssnapshot12 tblkssnapshotdaily (cost=0.00..4.33 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot13_idx_monthtest on
> > > tblkssnapshot13 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot14_idx_monthtest on
> > > tblkssnapshot14 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot15_idx_monthtest on
> > > tblkssnapshot15 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot16_idx_monthtest on
> > > tblkssnapshot16 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot17_idx_monthtest on
> > > tblkssnapshot17 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot18_idx_monthtest on
> > > tblkssnapshot18 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot19_idx_monthtest on
> > > tblkssnapshot19 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot20_idx_monthtest on
> > > tblkssnapshot20 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot21_idx_monthtest on
> > > tblkssnapshot21 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot22_idx_monthtest on
> > > tblkssnapshot22 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot23_idx_monthtest on
> > > tblkssnapshot23 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > > -> Index Scan using tblkssnapshot24_idx_monthtest on
> > > tblkssnapshot24 tblkssnapshotdaily (cost=0.00..4.83 rows=1 width=8)
> > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >
> > > If constraint exclusion is on, why are index scans taking
> > place on all
> > > of the tables and not only on the only partitioned table
> > for which the
> > > constraint exists?
> > >
> > > I went to a partitioned table thinking this would improve
> > performance
> > > from the previous schema in which all of the data was in the parent
> > > table, but the end result was significantly slower performance (by
> > > orders of magnitude).
> > >
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 5: don't forget to increase your free space map settings
> > >
> >
> > --
> > Jim Nasby jim@nasby.net
> > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
> >
> >
> >
>
--
Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Performance question
@ 2006-12-05 03:40 Benjamin Krajmalnik <kraj@illumen.com>
0 siblings, 0 replies; 21+ messages in thread
From: Benjamin Krajmalnik @ 2006-12-05 03:40 UTC (permalink / raw)
To: Jim C. Nasby <decibel@decibel.org>; +Cc: pgsql-admin
Unfortunately, the application doing the logging is not written by me,
so I have to play with the deck of cards which I am dealt.
The developer stated that he uses connection pooling, but apparently the
pg odbc driver does not support it, since I can see a new connection
being made for each statement execution.
I have asked him to see if he can create one connection and maintain it
- we'll see.
I have another scenario to try out, whereby instead of making a stored
procedure call via ODBC I will insert the parameters of the SP call into
a table via simple insert statements. If I see significant improvements
(I am not holding my breath), I will use that route and run a background
peocess on the server to issue the SP calls off of that recordset.
> -----Original Message-----
> From: Jim C. Nasby [mailto:decibel@decibel.org]
> Sent: Monday, December 04, 2006 8:25 PM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Performance question
>
> Please cc the list so others can learn and help.
>
> Yes, if ODBC is tearing the connection down after every call,
> performance *WILL* suck. Setup some kind of persistent connection.
> Better yet, don't make lots of tiny calls to the database if
> you can avoid it.
>
> On Mon, Dec 04, 2006 at 06:28:03PM -0700, Benjamin Krajmalnik wrote:
> > Hi Jim,
> >
> > Apples to apples (as best as I can tell).
> > The test procedure was as follows:
> >
> > I captured the SQL statements which call the stored procedures from
> > our monitoring box to a file.
> > I copied the file to the FreeBSD data server.
> > Executed psql, and pumped the file to it through \i filename.
> > Execution speed for the code in question was sub 10 seconds.
> > Running the query directly via ODBC is taking about 60 ms.
> >
> > The numbers I just stated above are after I removed the
> code which was
> > accessing the one partitioned table where the constraint
> exclusion was
> > not taking place (more on that later). This code happened to be
> > updating a daily aggregate - so each SP call was selecting the test
> > record for the day and aggregating data. I ended up adding
> a few more
> > fields to a records which keeps some data in arrays for
> quick access
> > to generate our graphs, and now I run a scheduled task at midnight
> > which takes the previous days aggregates and creates our snapshot
> > records (which we use for historical trending of up to 24 months).
> > This process now takes about 5 seconds, whereas running it
> through the
> > stored procedure it was adding almost 200 miliseconds to
> the execution time).
> > I assume this was caused by the fact that there was a
> select - and the
> > execution planner was not isolating to the single
> partitioned table,
> > followed by either an insert or another update.
> > The new code is much more efficient and loads the db much
> less, so it
> > will be the preferred method.
> > Right now the datra path between the monitoring appliacne
> and the data
> > server is 100Mbit. We are moving it to Gig tonight - so at least I
> > hope I will realize some speed advantages. I have been
> monitoring the
> > switch, and the actual throughput is only 150kbit/sec, so I do not
> > have a data throughput bpttlenecg, but rather I suspect
> latencies in
> > the ODBC connection.
> >
> > The item I found interesting is that the statement duration
> was much
> > longer.
> > What I am thinking is that when running through psql, it is using a
> > local pipe.
> > When running through ODBC, each stored procedure call is creating a
> > connection and then disconnecting, so it has added a huge
> overhead. I
> > think the duration is including the time it is taking to
> > connect/authenticate disconnect/teardown.
> >
> > Thanks for the tip on the constraint exclusion.
> > I need to go back and see how we are querying it for the
> reports, and
> > will adjust it accordingly.
> > Does it make a difference if it is timestamp or timestamp without
> > timezone?
> >
> >
> > > -----Original Message-----
> > > From: Jim Nasby [mailto:decibel@decibel.org]
> > > Sent: Monday, December 04, 2006 5:53 PM
> > > To: Benjamin Krajmalnik
> > > Cc: pgsql-admin@postgresql.org
> > > Subject: Re: [ADMIN] Performance question
> > >
> > > Are you sure you're doing an apples-apples comparison? Is
> the load
> > > on both machines the same, or does production have extra stuff
> > > running?
> > > Have you tried your original test again in the same setup without
> > > ODBC to eliminate that possibility? (or redone your original test
> > > with ODBC).
> > >
> > > As for the constraint exclusion, try changing the query so that
> > > '2006-12-01' is cast to a timestamp (or change the constraints so
> > > that you're constraining dates... the check constraints should
> > > really match the type of the underlying table).
> > >
> > > On Dec 1, 2006, at 9:18 PM, Benjamin Krajmalnik wrote:
> > >
> > > > I am battling a performance issue and was wondering if
> > > someone could
> > > > help. PostgreSQL 8.1.5, FreeBSD.
> > > >
> > > > I have a very intense stored procedure which performs real time
> > > > aggregation of data.
> > > >
> > > > I captured the stored procedure calls from a production
> system and
> > > > pumped them through psql, logging duration.
> > > > The stored procedure ran at about 30ms per stored
> procedure call -
> > > > which
> > > > was significantly faster than the previous production system.
> > > > Based on
> > > > these results, I was extremely optimistic and migrated
> to the new
> > > > server.
> > > >
> > > > Now that we turned up the new server, the stored procedure
> > > is taking
> > > > over 250ms.
> > > > These are the execution times as recorded by enable
> "all" logging.
> > > >
> > > > The only difference is that when I pumped the data in the test
> > > > environment, I pumped it by reading the script via psql,
> > > whereas the
> > > > current system is calling the stored procedure via ODBC
> through a
> > > > 100Mbit ethernet connection.
> > > >
> > > > I cannot explain the difference between the 2
> scenarios. Does the
> > > > duration include the overhead of the ODBC layer?
> > > >
> > > > Now, the second part that was troubling relates to the
> execution
> > > > planner.
> > > >
> > > >
> > > > Two of the tables in which data is being entered are
> partitioned
> > > > tables.
> > > > Data is being routed to the partition by using rules. Each
> > > partition
> > > > has a check constraint on a date range. Constraint
> exclusion is on.
> > > >
> > > > The first partitioned table is very simple, and the stored
> > > procedure
> > > > simply inserts a record for each test which is executed. These
> > > > are later used by a procedure running in the background which
> > > calculates
> > > > statistical data.
> > > >
> > > > My problem is with the second partitioned table. As part of my
> > > > troubleshooting, I omitted the code from the stored procedure
> > > > which accesses this data. In this table, I attempt to retrieve
> > > > the daily record for a specific test. If it is not
> found, after
> > > > aggregating data I insert it. If the record already
> exists, data
> > > > is aggregated and the record is updated.
> > > >
> > > > The key is composed of a date field (monthdate) and an
> int4 field
> > > > (kstestsysid).
> > > > The check constraints are of the form of:
> > > >
> > > > ALTER TABLE tblkssnapshot12
> > > > ADD CONSTRAINT tblkssnapshot12_chk CHECK (monthdate >=
> > > '2006-12-01
> > > > 00:00:00'::timestamp without time zone AND monthdate <
> '2007-01-01
> > > > 00:00:00'::timestamp without time zone);
> > > >
> > > > I ran a sample query which would be issued by the
> stored procedure
> > > > with the explain option.
> > > > The query was:
> > > >
> > > > select monthdate, kstestssysid from tblkssnapshotdaily
> > > where monthdate
> > > > = '2006-12-01' and kstestssysid = 3143
> > > >
> > > > The explain is as follows:
> > > >
> > > > select monthdate, kstestssysid from tblkssnapshotdaily
> > > where monthdate
> > > > = '2006-12-01' and kstestssysid = 3143 Result
> (cost=0.00..122.49
> > > > rows=25 width=8)
> > > > -> Append (cost=0.00..122.49 rows=25 width=8)
> > > > -> Index Scan using
> tblkssnapshotdaily_idx_monthtest on
> > > > tblkssnapshotdaily (cost=0.00..3.52 rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot01_idx_monthtest on
> > > > tblkssnapshot01 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot02_idx_monthtest on
> > > > tblkssnapshot02 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot03_idx_monthtest on
> > > > tblkssnapshot03 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot04_idx_monthtest on
> > > > tblkssnapshot04 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot05_idx_monthtest on
> > > > tblkssnapshot05 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot06_idx_monthtest on
> > > > tblkssnapshot06 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot07_idx_monthtest on
> > > > tblkssnapshot07 tblkssnapshotdaily (cost=0.00..5.94
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot08_idx_monthtest on
> > > > tblkssnapshot08 tblkssnapshotdaily (cost=0.00..4.73
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot09_idx_monthtest on
> > > > tblkssnapshot09 tblkssnapshotdaily (cost=0.00..5.56
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot10_idx_monthtest on
> > > > tblkssnapshot10 tblkssnapshotdaily (cost=0.00..5.75
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot11_idx_monthtest on
> > > > tblkssnapshot11 tblkssnapshotdaily (cost=0.00..5.81
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot12_idx_monthtest on
> > > > tblkssnapshot12 tblkssnapshotdaily (cost=0.00..4.33
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot13_idx_monthtest on
> > > > tblkssnapshot13 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot14_idx_monthtest on
> > > > tblkssnapshot14 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot15_idx_monthtest on
> > > > tblkssnapshot15 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot16_idx_monthtest on
> > > > tblkssnapshot16 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot17_idx_monthtest on
> > > > tblkssnapshot17 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot18_idx_monthtest on
> > > > tblkssnapshot18 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot19_idx_monthtest on
> > > > tblkssnapshot19 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot20_idx_monthtest on
> > > > tblkssnapshot20 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot21_idx_monthtest on
> > > > tblkssnapshot21 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot22_idx_monthtest on
> > > > tblkssnapshot22 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot23_idx_monthtest on
> > > > tblkssnapshot23 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > > -> Index Scan using tblkssnapshot24_idx_monthtest on
> > > > tblkssnapshot24 tblkssnapshotdaily (cost=0.00..4.83
> rows=1 width=8)
> > > > Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > > (kstestssysid = 3143))
> > > >
> > > > If constraint exclusion is on, why are index scans taking
> > > place on all
> > > > of the tables and not only on the only partitioned table
> > > for which the
> > > > constraint exists?
> > > >
> > > > I went to a partitioned table thinking this would improve
> > > performance
> > > > from the previous schema in which all of the data was in the
> > > > parent table, but the end result was significantly slower
> > > > performance (by orders of magnitude).
> > > >
> > > >
> > > >
> > > > ---------------------------(end of
> > > > broadcast)---------------------------
> > > > TIP 5: don't forget to increase your free space map settings
> > > >
> > >
> > > --
> > > Jim Nasby jim@nasby.net
> > > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
> > >
> > >
> > >
> >
>
> --
> Jim C. Nasby, Database Architect decibel@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
>
> Windows: "Where do you want to go today?"
> Linux: "Where do you want to go tomorrow?"
> FreeBSD: "Are you guys coming, or what?"
>
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2006-12-05 03:40 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-26 09:39 Performance question Brian McCane <bmccane@mccons.net>
2002-02-26 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 18:11 Performance question Bob Smith <bsmith@h-e.com>
2005-03-14 07:08 Performance Question Werner vd Merwe <werner@saicom.co.za>
2005-03-14 15:58 ` Re: Performance Question Thomas F.O'Connell <tfo@sitening.com>
2005-03-15 15:54 ` Re: Performance Question weiping <laser@tacacs.zhengmai.net.cn>
2005-03-16 06:45 ` Re: Performance Question Werner vd Merwe <werner@saicom.co.za>
2005-03-21 03:40 ` Re: Performance Question Weiping <laser@qmail.zhengmai.net.cn>
2005-03-14 16:10 Re: Performance Question Thomas F.O'Connell <tfo@sitening.com>
2005-03-14 17:10 ` Re: Performance Question Brad Nicholson <bnichols@ca.afilias.info>
2005-03-14 16:36 Re: Performance Question Thomas F.O'Connell <tfo@sitening.com>
2005-03-15 06:33 Re: Performance Question Werner vd Merwe <werner@saicom.co.za>
2005-03-19 18:09 ` Re: Performance Question Thomas F.O'Connell <tfo@sitening.com>
2005-03-22 06:31 ` Re: Performance Question Werner vd Merwe <werner@saicom.co.za>
2005-03-22 15:55 ` Re: Performance Question Thomas F.O'Connell <tfo@sitening.com>
2005-03-15 15:32 Re: Performance Question Brad Nicholson <bnichols@ca.afilias.info>
2005-03-16 06:28 ` Re: Performance Question Werner vd Merwe <werner@saicom.co.za>
2006-12-02 05:18 Performance question Benjamin Krajmalnik <kraj@illumen.com>
2006-12-05 00:53 ` Jim Nasby <decibel@decibel.org>
2006-12-05 03:25 Re: Performance question Jim C. Nasby <decibel@decibel.org>
2006-12-05 03:40 Re: Performance question Benjamin Krajmalnik <kraj@illumen.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox