pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhere is my bottleneck?
6+ messages / 6 participants
[nested] [flat]
* Where is my bottleneck?
@ 2006-01-24 18:40 Arnau Rebassa Villalonga <arebassa@androme.es>
0 siblings, 4 replies; 6+ messages in thread
From: Arnau Rebassa Villalonga @ 2006-01-24 18:40 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I have a performance problem and I don't know where is my bottleneck.
I have postgresql 7.4.2 running on a debian server with kernel
2.4.26-1-686-smp with two Xeon(TM) at 2.80GHz and 4GB of RAM and a RAID
5 made with SCSI disks. Maybe its not the latest hardware but I think
it's not that bad.
My problem is that the general performance is not good enough and I
don't know where is the bottleneck. It could be because the queries are
not optimized as they should be, but I also think it can be a postgresql
configuration problem or hardware problem (HDs not beeing fast enough,
not enough RAM, ... )
The configuration of postgresql is the default, I tried to tune the
postgresql.conf and the results where disappointing, so I left again the
default values.
When I do top I get:
top - 19:10:24 up 452 days, 15:48, 4 users, load average: 6.31, 6.27, 6.52
Tasks: 91 total, 8 running, 83 sleeping, 0 stopped, 0 zombie
Cpu(s): 24.8% user, 15.4% system, 0.0% nice, 59.9% idle
Mem: 3748956k total, 3629252k used, 119704k free, 57604k buffers
Swap: 2097136k total, 14188k used, 2082948k free, 3303620k cached
Most of the time the idle value is even higher than 60%.
I know it's a problem with a very big scope, but could you give me a
hint about where I should look to?
Thank you very much
--
Arnau
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Where is my bottleneck?
@ 2006-01-30 09:25 Richard Huxton <dev@archonet.com>
parent: Arnau Rebassa Villalonga <arebassa@androme.es>
3 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2006-01-30 09:25 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; +Cc: pgsql-performance
Arnau Rebassa Villalonga wrote:
>
> The configuration of postgresql is the default, I tried to tune the
> postgresql.conf and the results where disappointing, so I left again the
> default values.
That's the first thing to fix. Go to the page below and read through the
"Performance Tuning" article.
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Where is my bottleneck?
@ 2006-01-30 11:00 Michael Stone <mstone+postgres@mathom.us>
parent: Arnau Rebassa Villalonga <arebassa@androme.es>
3 siblings, 1 reply; 6+ messages in thread
From: Michael Stone @ 2006-01-30 11:00 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 24, 2006 at 07:40:22PM +0100, Arnau Rebassa Villalonga wrote:
> I have a performance problem and I don't know where is my bottleneck.
[snip]
> Most of the time the idle value is even higher than 60%.
It's generally a fairly safe bet that if you are running slow and your
cpu is idle, your i/o isn't fast enough.
Mike Stone
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Where is my bottleneck?
@ 2006-01-30 16:49 Luke Lonergan <llonergan@greenplum.com>
parent: Arnau Rebassa Villalonga <arebassa@androme.es>
3 siblings, 0 replies; 6+ messages in thread
From: Luke Lonergan @ 2006-01-30 16:49 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; pgsql-performance
Arnau,
On 1/24/06 10:40 AM, "Arnau Rebassa Villalonga" <arebassa@androme.es> wrote:
> I know it's a problem with a very big scope, but could you give me a
> hint about where I should look to?
Try this:
time bash -c "dd if=/dev/zero of=bigfile bs=8k count=2000000 && sync"
time dd if=bigfile of=/dev/null bs=8k
And report the results back here. If it takes too long to complete (more
than a couple of minutes), bring up another window and run "vmstat 1" then
report the values in the columns "bi" and "bo".
- Luke
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Where is my bottleneck?
@ 2006-01-30 20:10 Jim C. Nasby <jnasby@pervasive.com>
parent: Arnau Rebassa Villalonga <arebassa@androme.es>
3 siblings, 0 replies; 6+ messages in thread
From: Jim C. Nasby @ 2006-01-30 20:10 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; +Cc: pgsql-performance
On Tue, Jan 24, 2006 at 07:40:22PM +0100, Arnau Rebassa Villalonga wrote:
> Hi all,
>
> I have a performance problem and I don't know where is my bottleneck.
> I have postgresql 7.4.2 running on a debian server with kernel
You should really upgrade to the latest 7.4 version. You're probably
vulnerable to some data-loss issues.
> 2.4.26-1-686-smp with two Xeon(TM) at 2.80GHz and 4GB of RAM and a RAID
> 5 made with SCSI disks. Maybe its not the latest hardware but I think
Generally speaking, databases (or anything else that does a lot of
random writes) don't like RAID5.
> My problem is that the general performance is not good enough and I
> don't know where is the bottleneck. It could be because the queries are
> not optimized as they should be, but I also think it can be a postgresql
> configuration problem or hardware problem (HDs not beeing fast enough,
> not enough RAM, ... )
What kind of performance are you expecting? What are you actually
seeing?
> The configuration of postgresql is the default, I tried to tune the
> postgresql.conf and the results where disappointing, so I left again the
> default values.
Probably not so good... you'll most likely want to tune shared_buffers,
sort_mem and effective_cache_size at a minimum. Granted, that might not
be your current bottleneck, but it would probably be your next.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Where is my bottleneck?
@ 2006-02-02 12:14 Markus Schaber <schabi@logix-tt.com>
parent: Michael Stone <mstone+postgres@mathom.us>
0 siblings, 0 replies; 6+ messages in thread
From: Markus Schaber @ 2006-02-02 12:14 UTC (permalink / raw)
To: Michael Stone <mstone+postgres@mathom.us>; +Cc: pgsql-performance
Hi, Michael,
Michael Stone wrote:
>> I have a performance problem and I don't know where is my bottleneck.
>
> [snip]
>
>> Most of the time the idle value is even higher than 60%.
>
> It's generally a fairly safe bet that if you are running slow and your
> cpu is idle, your i/o isn't fast enough.
Or the query is misoptimized (low work_mem, missing indices) and cause
much more I/O than necessary.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-02-02 12:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-24 18:40 Where is my bottleneck? Arnau Rebassa Villalonga <arebassa@androme.es>
2006-01-30 09:25 ` Richard Huxton <dev@archonet.com>
2006-01-30 11:00 ` Michael Stone <mstone+postgres@mathom.us>
2006-02-02 12:14 ` Markus Schaber <schabi@logix-tt.com>
2006-01-30 16:49 ` Luke Lonergan <llonergan@greenplum.com>
2006-01-30 20:10 ` Jim C. Nasby <jnasby@pervasive.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