pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedwhere is my bottleneck?
11+ messages / 7 participants
[nested] [flat]
* where is my bottleneck?
@ 2002-03-02 11:01 Ben <bench@silentmedia.com>
2002-03-02 14:44 ` Re: where is my bottleneck? Jeff Fitzmyers <flume33@yahoo.com>
0 siblings, 1 reply; 11+ messages in thread
From: Ben @ 2002-03-02 11:01 UTC (permalink / raw)
To: pgsql-admin; pgsql-general@postgresql.org
Hello everybody. I'm trying to build a postgres server to act as the
repository for a bunch of data-crunching clients. Hardware-wise, the
machine is a dual p3 700 with 768MB of RAM and 3 striped 10k RPM u160
drives sitting on a DPT SmartRAID V. Software-wise, I've installed linux
2.4.18 and postgres 7.2.
The database is structured such that there is a control table that every
client will perform indexed lookups from and the very occasional update
on, and this table is small enough that it easily fits in memory. All the
data is written to a second table and never accessed again.
It seems to me that such a machine should be able to handle an awful lot
of simple database work, but I'm disappointed in how little seems to be
getting through. Each client connection does ~1500 lookups in the control
table and another ~1500 inserts into the data table. After I add about 10
of these clients simultaneously, the cpu load (according to vmstat) goes
to about 30% idle. Interestingly, despite the fact that the disks are not
very busy, adding more clients does not make the processors work
harder.... they stay around 20-30% loaded even if I triple the load to 30
simultaneous clients.
I've checked all the sql commands I use, and all the lookups and updates
are using indices. The system isn't swapping, and posgres is using its
400MB of shared buffers to good effect. I have noticed that context
switches seem rediculously high - about 30,000/sec, give or take 10k - but
having nothing to compare it to I don't know if that's unreasonable for a
busy database machine or even what I could do about it if it wasn't.
I am using a much-derided 8139-based ethernet card, but seeing as how I'm
only transfering about 30KB/sec each way (and I know for a fact that even
*that* chipset can transmit well over 2MB/sec), I don't think that is the
blocking issue.
Does anybody have any suggestions as to how I can make this server work
closer to its limits? It takes 10 clients about 3 minutes to run, meaning
that postgres is roughly maxing out at (5,000 indexed lookups + 5,000
inserts)/sec. It seems to me that my hardware should be able to support
more than that. Am I being unreasonable? I'm particularly interested as to
why adding more clients doesn't make postgres work harder, when the disks
are not being used very much and vmstat claims the cpus are not fully
utilized.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: where is my bottleneck?
2002-03-02 11:01 where is my bottleneck? Ben <bench@silentmedia.com>
@ 2002-03-02 14:44 ` Jeff Fitzmyers <flume33@yahoo.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jeff Fitzmyers @ 2002-03-02 14:44 UTC (permalink / raw)
To: pgsql-general@postgresql.org
> getting through. Each client connection does ~1500 lookups in the
> control
> table and another ~1500 inserts into the data table.
Have you tried a timing 100,000 simple INSERTs / SELECTs / UPDATEs
locally? Then you would have an approximate number for raw capability.
Then add a client connection. Then multiple connections. Then build up
to the clients queries.
Interesting question, Jeff
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [GENERAL] where is my bottleneck?
@ 2002-03-02 17:40 Ben <bench@silentmedia.com>
0 siblings, 0 replies; 11+ messages in thread
From: Ben @ 2002-03-02 17:40 UTC (permalink / raw)
To: Helge Bahmann <bahmann@math.tu-freiberg.de>; +Cc: pgsql-general@postgresql.org; pgsql-admin
The strace for one of the clients is a lot of data. However, it certainly
doesn't seem right that, out of the 33038 commands I captured in the
strace, 31037 of them were semop().
BTW, after watching stats a bit closer, the number of context switches is
"only" about 1000/sec with just one client attached; attaching more than
one client makes the number jump up to the 30000/sec mark.
On Sat, 2 Mar 2002, Helge Bahmann wrote:
> On Sat, 2 Mar 2002, Ben wrote:
> > I've checked all the sql commands I use, and all the lookups and updates
> > are using indices. The system isn't swapping, and posgres is using its
> > 400MB of shared buffers to good effect. I have noticed that context
> > switches seem rediculously high - about 30,000/sec, give or take 10k - but
>
> This is ridiculously high; can you attach "strace" to one of the postgres
> processes while stressing the system and send a snippet of the output?
> (strace -p <pid>)
>
> I suspect Postgres locking code may be responsible for this huge amount of
> ctx switches; if you see huge amount of pointless select() statements
> in the trace, this would prove my theory and it would be time to
> rethink the current locking strategy.
>
> Regards
> --
> Helge Bahmann <bahmann@math.tu-freiberg.de> /| \__
> Network admin, systems programmer /_|____\
> _/\ | __)
> $ ./configure \\ \|__/__|
> checking whether build environment is sane... yes \\/___/ |
> checking for AIX... no (we already did this) |
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Where is my bottleneck?
@ 2006-01-24 18:39 Arnau <arnaulist@andromeiberica.com>
2006-01-24 19:08 ` Re: Where is my bottleneck? Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 11+ messages in thread
From: Arnau @ 2006-01-24 18:39 UTC (permalink / raw)
To: pgsql-admin
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] 11+ messages in thread
* Re: Where is my bottleneck?
2006-01-24 18:39 Where is my bottleneck? Arnau <arnaulist@andromeiberica.com>
@ 2006-01-24 19:08 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-01-24 19:53 ` Re: Where is my bottleneck? Arnau <arnaulist@andromeiberica.com>
0 siblings, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2006-01-24 19:08 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; +Cc: pgsql-admin
On Tue, 2006-01-24 at 12:39, Arnau 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
> 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?
Well, this might get more traction on the perform list, just fyi. Admin
is more generally for questions about adding users, setting permissions
and such.
That said, it looks like you're likely I/O bound.
Do you have Hyperthreading turned on? generally this results in slower,
not faster performance, as the caches / registers in the pseudo CPUs are
often thrashed harder by having it turned on. I've found that having it
turned off generally gives better performance under heavy parallel load.
The most common changes to look at making in postgresql.conf are to
raise shared buffers. A setting of a 1000 to 10000 is pretty common.
You might do better with a lower random_page_cost, normally between 1.4
and 2.0 is good. Any lower than that and you're likely to see index
scans chosen when seq scans are really the better choice.
You should really upgrade your version to the latest 7.4 branch.
There are a LOT of performance enhancements in 8.0/8.1. If you can
upgrade to the latest 8.1 version that might help as well.
Turn on the logging of long queries, and run explain analyze on one or
more of those long running queries, and post the output here.
What do iostat and vmstat have to say?
Does your RAID 5 have the option for batter backed cache?
Is your load mostly read, or a mix or read and write?
Read through this document:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where is my bottleneck?
2006-01-24 18:39 Where is my bottleneck? Arnau <arnaulist@andromeiberica.com>
2006-01-24 19:08 ` Re: Where is my bottleneck? Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-01-24 19:53 ` Arnau <arnaulist@andromeiberica.com>
0 siblings, 0 replies; 11+ messages in thread
From: Arnau @ 2006-01-24 19:53 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
Scott Marlowe wrote:
> Well, this might get more traction on the perform list, just fyi. Admin
> is more generally for questions about adding users, setting permissions
> and such.
I know and I apologize for the cross-posting, I also sent the same
message to the performance list.
>
> That said, it looks like you're likely I/O bound.
>
> Do you have Hyperthreading turned on?
yes I have, when I do cat /proc/cpuinfo I get 4 CPUs
>
> What do iostat and vmstat have to say?
here I post the result of iostat 10 -x
asme@SD22-SINER5:/$ iostat 10 -x
Linux 2.4.26-1-686-smp (SD22-SINER5) 01/24/06
avg-cpu: %user %nice %sys %iowait %idle
16.03 0.00 94.98 0.00 144.54
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
avgrq-sz avgqu-sz await svctm %util
scsi/host0/bus1/target0/lun0/disc
89.37 14.15 48.15 95.82 1.30 25.54 0.65 12.77
0.79 0.00 0.05 0.00 0.00
scsi/host0/bus1/target0/lun0/part1
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4.26 0.00 48.69 48.63 0.00
scsi/host0/bus1/target0/lun0/part2
0.01 0.25 0.02 0.22 0.22 3.77 0.11 1.89
17.12 0.00 6.29 1.80 0.04
scsi/host0/bus1/target0/lun0/part5
0.02 0.02 0.01 0.00 0.21 0.20 0.10 0.10
33.85 0.00 58.89 38.02 0.05
scsi/host0/bus1/target0/lun0/part6
0.05 0.35 0.10 0.47 1.18 6.55 0.59 3.27
13.58 0.00 2.70 1.64 0.09
scsi/host0/bus1/target0/lun0/part7
0.06 0.60 0.01 0.03 0.53 5.10 0.27 2.55
137.26 0.00 38.02 22.68 0.09
scsi/host0/bus1/target0/lun0/part8
0.14 0.57 0.01 0.03 1.18 4.79 0.59 2.40
155.70 0.00 12.07 14.63 0.06
scsi/host0/bus1/target0/lun0/part9
89.09 12.35 48.00 95.07 107.78 5.13 53.89 2.56
0.09 0.00 0.04 0.02 0.05
avg-cpu: %user %nice %sys %iowait %idle
27.35 0.00 16.25 0.00 56.40
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
avgrq-sz avgqu-sz await svctm %util
scsi/host0/bus1/target0/lun0/disc
420.60 2070.00 87.30 152.10 4060.00 17830.40 2030.00
8915.20 91.44 14.06 57.70 4.16 99.60
scsi/host0/bus1/target0/lun0/part1
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part2
0.00 0.70 0.00 1.40 0.00 16.80 0.00 8.40
12.00 0.07 52.14 5.00 0.70
scsi/host0/bus1/target0/lun0/part5
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part6
0.00 0.40 0.00 0.40 0.00 6.40 0.00 3.20
16.00 0.02 40.00 40.00 1.60
scsi/host0/bus1/target0/lun0/part7
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part8
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part9
420.60 2068.90 87.30 150.30 4060.00 17807.20 2030.00
8903.60 92.03 13.97 57.77 4.19 99.60
avg-cpu: %user %nice %sys %iowait %idle
31.38 0.00 15.55 0.00 53.08
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
avgrq-sz avgqu-sz await svctm %util
scsi/host0/bus1/target0/lun0/disc
388.80 2266.70 219.20 182.00 4876.80 19571.20 2438.40
9785.60 60.94 21.54 1059879.18 2.49 99.80
scsi/host0/bus1/target0/lun0/part1
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part2
0.00 0.30 0.00 0.40 0.00 5.60 0.00 2.80
14.00 0.01 17.50 17.50 0.70
scsi/host0/bus1/target0/lun0/part5
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part6
0.00 0.20 0.00 0.40 0.00 4.80 0.00 2.40
12.00 0.01 22.50 22.50 0.90
scsi/host0/bus1/target0/lun0/part7
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part8
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00
scsi/host0/bus1/target0/lun0/part9
388.80 2266.20 219.20 181.20 4876.80 19560.80 2438.40
9780.40 61.03 21.52 54.32 2.49 99.80
> Does your RAID 5 have the option for batter backed cache?
I don't know how to do that, I didn't configure this machine.
> Is your load mostly read, or a mix or read and write?
On this machine there are about 200 DDBB running on it, the activity
on them is a mix of read/write operations some depending on the tables
of each DB. The size of the DB varies a lot, every day we do a pg_dump
with a compressed format. The biggest one takes 746M, 5 are between 46M
and 13M. 40 between 10M - 1M and the rest their dump takes less than 1M.
Cheers!
--
Arnau
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where is my bottleneck?
@ 2006-01-25 09:58 John Jensen <JRJ@ft.fo>
0 siblings, 0 replies; 11+ messages in thread
From: John Jensen @ 2006-01-25 09:58 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; pgsql-admin
Hi Arnau,
Poor performance on idle cpu is normally due to an I/O bottleneck.
The bottleneck can be either network (unlikely but easy to check) or
disk i/o. Excessive disk i/o can be caused by memory starvation or
maybe you just need to move a lot of data. Adding memory will
give you more cache space and in "some cases" reduce physical i/o
and thereby improve performance. This depends on how the data
is accessed.
These comments are pretty general and goes for any application.
Your post is a bit slim on information. So here are some questions:
- Is all the memory used by postgres ?
- Do you run any other applications on the machine ?
(if other apps use all the memory then move them to an other box to
free up memory)
- Run vmstat 1 and post the first 50 lines of data
- How large is your database (disk usage under postgres-x.x.x/data/base
)
- Do you have indices on all fields you query on ?
(if not then you force full-table scan's which cause excessive i/o
- Make sure the datatypes in your queries match those in the indices ?
(if not then the indices are not used and you force full-table scans)
- Did you install from source or an rpm ?
(the default source config is set up to use far to little memory for
buffer cache)
Apart from that I would suggest turning on the statistics collection.
That tells you:
- How many times each table is hit
- Number of full table vs. index scans for each table
(that tells A LOT about your indices)
- Number of blocks read for each table
Armed with that information you can hunt down the expensive queries
and optimize them. Use EXPLAIN PLAN a lot here.
For optimizing queries I can recommend this book (
http://www.singingsql.com/ ).
You could always throw money at the problem:
- more memory
- Hotter disks (Raid 10)
- Split the base into multiple tablespaces (postgresql 8.0 or higher)
on multiple
disk systems and controllers.
Cheers,
John
>>> Arnau <arnaulist@andromeiberica.com> 01/24/06 6:39 pm >>>
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
---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where is my bottleneck?
@ 2006-01-25 16:57 Arnau <arnaulist@andromeiberica.com>
2006-01-25 18:03 ` Re: Where is my bottleneck? Dario Brignardello <dbrignar@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Arnau @ 2006-01-25 16:57 UTC (permalink / raw)
To: pgsql-admin; +Cc: John Jensen <JRJ@ft.fo>
Hi John,
> Your post is a bit slim on information. So here are some questions:
> - Is all the memory used by postgres ?
I'm not sure how to look at that (how could I do it?). Here you are
the result of a top
SD22-SINER5:~# top
top - 15:09:50 up 453 days, 11:47, 3 users, load average: 4.08, 3.90, 2.64
Tasks: 70 total, 3 running, 67 sleeping, 0 stopped, 0 zombie
Cpu(s): 6.3% user, 37.1% system, 0.0% nice, 56.6% idle
Mem: 3748956k total, 3623988k used, 124968k free, 82976k buffers
Swap: 2097136k total, 13896k used, 2083240k free, 3283128k cached
The parameters related with memory usage of postgresql.conf all are the
default values (I haven't changed any value in the postgresql.conf file)
# - Memory -
#shared_buffers = 1000
#sort_mem = 1024
#vacuum_mem = 8192
# - Free Space Map -
#max_fsm_pages = 20000
#max_fsm_relations = 1000
# - Kernel Resource Usage -
#max_files_per_process = 1000
#preload_libraries = ''
> - Do you run any other applications on the machine ?
No, this machine is only used as DB server.
> (if other apps use all the memory then move them to an other box to
> free up memory)
> - Run vmstat 1 and post the first 50 lines of data
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us
sy id wa
1 0 13900 118920 43584 3333540 0 0 1 0 0 0 6
37 57 0
1 4 13900 120592 42976 3330616 0 0 8096 5296 506 16905 55
20 24 0
2 2 13900 123112 43020 3330228 0 0 3480 6316 369 12585 42
22 36 0
6 1 13900 123192 42844 3329656 0 0 1596 11260 387 12969 34
16 50 0
1 3 13900 122112 42156 3330296 0 0 2460 9688 424 18758 49
26 25 0
5 1 13900 121588 41916 3331160 0 0 5160 1940 399 14535 65
18 18 0
5 2 13900 118028 41824 3335788 0 0 5708 0 448 15004 58
21 21 0
0 5 13900 124692 41864 3329456 0 0 2804 11912 291 3897 21
13 66 0
1 1 13900 122904 41868 3331332 0 0 3232 128 263 14511 54
19 27 0
4 1 13900 121148 41876 3333004 0 0 6408 0 474 17310 55
26 18 0
0 3 13900 122824 41724 3331336 0 0 5336 15564 415 7371 35
12 53 0
4 1 13900 122228 41720 3332060 0 0 3460 7228 311 5875 27
8 65 0
0 3 13900 123820 41760 3328892 0 0 2360 6232 339 8945 31
12 57 0
3 1 13900 124608 41748 3330724 0 0 2832 11568 389 6441 28
12 60 0
0 2 13900 124348 41756 3329404 0 0 2052 10924 254 3193 26
8 65 0
1 3 13900 123876 41764 3332488 0 0 3220 6124 374 3568 36
9 55 0
2 3 13900 122740 41768 3333052 0 0 3380 11288 383 2666 20
9 71 0
0 3 13900 123972 41660 3333612 0 0 4092 10408 600 4906 6
8 86 0
1 4 13900 122592 41720 3334056 0 0 3716 7940 511 1312 3
5 92 0
0 3 13900 126512 41736 3330188 0 0 1008 10996 219 569 7
5 88 0
1 3 13900 125312 41824 3331052 0 0 3476 12244 572 4143 16
12 72 0
0 3 13900 124808 41880 3331756 0 0 2948 7752 520 5399 13
14 74 0
2 3 13900 122528 41940 3333740 0 0 3460 10760 473 5091 15
10 75 0
1 3 13900 123568 41976 3332608 0 0 2304 11728 428 3819 15
10 75 0
0 4 13900 123020 42032 3331328 0 0 3032 10748 416 2934 13
9 79 0
0 4 13900 121264 42084 3331544 0 0 2544 12672 439 5509 14
13 74 0
2 3 13900 122464 42152 3332336 0 0 2796 7236 658 8765 17
12 71 0
0 3 13900 123536 42180 3330408 0 0 4240 11792 594 7849 22
12 66 0
0 3 13900 124760 42180 3330720 0 0 3000 11868 432 4875 14
11 75 0
1 4 13900 119916 42184 3331788 0 0 3140 4116 400 10334 19
18 63 0
1 2 13900 123820 42148 3332680 0 0 3108 7040 340 5566 21
10 69 0
2 2 13900 121072 42064 3336180 0 0 3908 9620 326 2966 16
10 74 0
0 2 13900 122220 42060 3335028 0 0 3476 5752 376 1822 9
9 82 0
0 2 13900 122472 42036 3334868 0 0 4644 0 322 1328 3
4 92 0
1 2 13900 119216 42064 3338292 0 0 5444 0 357 821 2
4 93 0
1 2 13900 120080 41808 3337176 0 0 3908 0 297 453 1
2 97 0
0 2 13900 123048 41680 3334556 0 0 6600 0 415 620 3
4 93 0
1 2 13900 122036 41708 3334988 0 0 3360 6004 303 703 7
6 87 0
1 1 13900 123900 41732 3333736 0 0 5496 0 340 2434 19
15 66 0
1 5 13900 119736 41748 3336340 0 0 1824 11040 321 2457 11
8 82 0
1 2 13900 122952 41772 3333604 0 0 1836 5468 371 5545 18
13 70 0
1 2 13900 122628 41776 3333048 0 0 2036 10948 258 2677 10
9 81 0
1 2 13900 121376 41820 3335164 0 0 3540 5644 362 3740 19
13 68 0
1 1 13900 123332 41840 3333872 0 0 2444 0 235 1844 17
13 70 0
1 2 13900 122136 41876 3334664 0 0 6500 5716 459 6047 20
13 67 0
0 4 13900 119048 41508 3334900 0 0 6664 4152 413 3603 21
14 65 0
3 2 13900 118292 41520 3332324 0 0 4768 1472 530 22070 34
21 46 0
1 2 13900 124236 41560 3328272 0 0 2292 8860 401 13135 36
13 51 0
2 1 13900 119888 41624 3332456 0 0 7308 0 808 6437 27
8 65 0
0 1 13900 131548 41636 3326756 0 0 2856 7632 483 1225 8
7 86 0
> - How large is your database (disk usage under postgres-x.x.x/data/base
> )
SD22-SINER5:/var/lib/postgres# du --max-depth=1 -h
17G ./data
360K ./dumpall
17G .
> - Do you have indices on all fields you query on ?
> (if not then you force full-table scan's which cause excessive i/o
> - Make sure the datatypes in your queries match those in the indices ?
> (if not then the indices are not used and you force full-table scans)
I know that I can tune my queries, but I think I could get more
performance from the hardware I have.
> - Did you install from source or an rpm ?
> (the default source config is set up to use far to little memory for
> buffer cache)
I installed from the debian repositories, apt-get intall postgresql
>
> Apart from that I would suggest turning on the statistics collection.
> That tells you:
> - How many times each table is hit
> - Number of full table vs. index scans for each table
> (that tells A LOT about your indices)
> - Number of blocks read for each table
My settings are, do I should change anything else?
# - Query/Index Statistics Collector -
stats_start_collector = true
stats_command_string = true
#stats_block_level = false
stats_row_level = true
#stats_reset_on_server_start = true
Thank you very much for your help
--
Arnau
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where is my bottleneck?
2006-01-25 16:57 Re: Where is my bottleneck? Arnau <arnaulist@andromeiberica.com>
@ 2006-01-25 18:03 ` Dario Brignardello <dbrignar@gmail.com>
2006-01-25 18:58 ` Re: Where is my bottleneck? Guido Barosio <gbarosio@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Dario Brignardello @ 2006-01-25 18:03 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; +Cc: pgsql-admin@postgresql.org, John Jensen <JRJ@ft.fo>
Hi, Arnu,
>
> The parameters related with memory usage of postgresql.conf all are the
> default values (I haven't changed any value in the postgresql.conf file)
Well, there is actually plenty of room to optimize there, I would suggest to
go to
http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-RESOURCE
and take a look, specially the values for shared_buffers and sort_mem should
be higher (mind you, you also have to tune your kernel parameters or your
engine will not start, so be careful :-) )
> I know that I can tune my queries, but I think I could get more
>performance from the hardware I have.
Actually you can, but a bad query is always a bad
query, even when runing on big iron, so you should be sure you
are not wasting hw resources, specially if those are scarce :-)
> My settings are, do I should change anything else?
>
> # - Query/Index Statistics Collector -
> stats_start_collector = true
> stats_command_string = true
> #stats_block_level = false
> stats_row_level = true
> #stats_reset_on_server_start = true
Take a look at the pg_stat_* views, to see what could be out off the way. To
an explanation of the statistics views, check:
http://www.postgresql.org/docs/7.4/interactive/monitoring-stats.html
Hope it helps.
Best regards
Dario
--
Atte: Dario Brignardello
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where is my bottleneck?
2006-01-25 16:57 Re: Where is my bottleneck? Arnau <arnaulist@andromeiberica.com>
2006-01-25 18:03 ` Re: Where is my bottleneck? Dario Brignardello <dbrignar@gmail.com>
@ 2006-01-25 18:58 ` Guido Barosio <gbarosio@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Guido Barosio @ 2006-01-25 18:58 UTC (permalink / raw)
To: Dario Brignardello <dbrignar@gmail.com>; +Cc: arnaulist@andromeiberica.com, pgsql-admin@postgresql.org, John Jensen <JRJ@ft.fo>
Hi Arnu,
Take a look at contrib/pg_buffers and contrib/pgstattuple also!!
Regards,
Guido. (dariussss)
On 1/25/06, Dario Brignardello <dbrignar@gmail.com> wrote:
>
> Hi, Arnu,
>
>
> >
> > The parameters related with memory usage of postgresql.conf all are the
> > default values (I haven't changed any value in the postgresql.conf file)
>
> Well, there is actually plenty of room to optimize there, I would suggest
> to go to
> http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-RESOURCE
>
>
> and take a look, specially the values for shared_buffers and sort_mem
> should be higher (mind you, you also have to tune your kernel parameters or
> your engine will not start, so be careful :-) )
>
>
> > I know that I can tune my queries, but I think I could get more
> >performance from the hardware I have.
>
> Actually you can, but a bad query is always a bad
> query, even when runing on big iron, so you should be sure you
> are not wasting hw resources, specially if those are scarce :-)
>
>
> > My settings are, do I should change anything else?
> >
> > # - Query/Index Statistics Collector -
>
> > stats_start_collector = true
> > stats_command_string = true
> > #stats_block_level = false
> > stats_row_level = true
> > #stats_reset_on_server_start = true
>
> Take a look at the pg_stat_* views, to see what could be out off the way.
> To an explanation of the statistics views, check:
> http://www.postgresql.org/docs/7.4/interactive/monitoring-stats.html
>
> Hope it helps.
> Best regards
> Dario
>
>
> --
> Atte: Dario Brignardello
>
>
--
/"\ ASCII Ribbon Campaign .
\ / - NO HTML/RTF in e-mail .
X - NO Word docs in e-mail .
/ \ -----------------------------------------------------------------
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Where is my bottleneck?
@ 2006-01-26 08:57 John Jensen <JRJ@ft.fo>
0 siblings, 0 replies; 11+ messages in thread
From: John Jensen @ 2006-01-26 08:57 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; pgsql-admin
Hi Arnau,
>> - Is all the memory used by postgres ?
> I'm not sure how to look at that (how could I do it?).
In TOP you can see how much memory is used by postmaster process'es.
> SD22-SINER5:~# top
> top - 15:09:50 up 453 days, 11:47, 3 users, load average: 4.08, 3.90, 2.64
> Tasks: 70 total, 3 running, 67 sleeping, 0 stopped, 0 zombie
> Cpu(s): 6.3% user, 37.1% system, 0.0% nice, 56.6% idle
> Mem: 3748956k total, 3623988k used, 124968k free, 82976k buffers
> Swap: 2097136k total, 13896k used, 2083240k free, 3283128k cached
> The parameters related with memory usage of postgresql.conf all are the
> default values (I haven't changed any value in the postgresql.conf file)
> # - Memory -
> #shared_buffers = 1000
> #sort_mem = 1024
> #vacuum_mem = 8192
> # - Free Space Map -
> #max_fsm_pages = 20000
> #max_fsm_relations = 1000
> # - Kernel Resource Usage -
> #max_files_per_process = 1000
> #preload_libraries = ''
Take the suggestions from Scott Marlowe's mail, bump up the shared buffer
memory usage. Apart from that there is a lot of really great info on the
Varlena page he refers to. Go read it.
>> - Run vmstat 1 and post the first 50 lines of data
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us
sy id wa
1 0 13900 118920 43584 3333540 0 0 1 0 0 0 6
37 57 0
1 4 13900 120592 42976 3330616 0 0 8096 5296 506 16905 55
20 24 0
<<stuff deleted>>
NOW this is interesting. Your I/O is dominated by write operations.
Do you make a lot of inserts ? What is the database used for ?
What is the application in front of it ?
>> - How large is your database (disk usage under postgres-x.x.x/data/base
>> )
>SD22-SINER5:/var/lib/postgres# du --max-depth=1 -h
>17G ./data
>360K ./dumpall
>17G .
All this indicates a usage pattern where data is pumbed in at say 10Gigs an hour
and is deleted after roughly two hours. Only 1/3 to ½ of the data is ever queried.
Is this correct ?
If this is the case then you may have to throw hardware at the problem.
Raid 10 instead of raid 5, split the base over multiple raid devices and multiple
controllers.
> My settings are, do I should change anything else?
> # - Query/Index Statistics Collector -
> stats_start_collector = true
> stats_command_string = true
> #stats_block_level = false
> stats_row_level = true
> #stats_reset_on_server_start = true
I run with:
stats_command_string = on
stats_block_level = on
stats_row_level = on
stats_reset_on_server_start = on
That definatel gives you what you need.
/John
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-01-26 08:57 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-02 11:01 where is my bottleneck? Ben <bench@silentmedia.com>
2002-03-02 14:44 ` Jeff Fitzmyers <flume33@yahoo.com>
2002-03-02 17:40 Re: [GENERAL] where is my bottleneck? Ben <bench@silentmedia.com>
2006-01-24 18:39 Where is my bottleneck? Arnau <arnaulist@andromeiberica.com>
2006-01-24 19:08 ` Re: Where is my bottleneck? Scott Marlowe <smarlowe@g2switchworks.com>
2006-01-24 19:53 ` Re: Where is my bottleneck? Arnau <arnaulist@andromeiberica.com>
2006-01-25 09:58 Re: Where is my bottleneck? John Jensen <JRJ@ft.fo>
2006-01-25 16:57 Re: Where is my bottleneck? Arnau <arnaulist@andromeiberica.com>
2006-01-25 18:03 ` Re: Where is my bottleneck? Dario Brignardello <dbrignar@gmail.com>
2006-01-25 18:58 ` Re: Where is my bottleneck? Guido Barosio <gbarosio@gmail.com>
2006-01-26 08:57 Re: Where is my bottleneck? John Jensen <JRJ@ft.fo>
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