|
Home > Archive > PostgreSQL Administration > January 2006 > Where is my bottleneck?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Where is my bottleneck?
|
|
|
| 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
| |
| Scott Marlowe 2006-01-24, 8:24 pm |
| 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/Gene...dbits/perf.html
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| John Jensen 2006-01-26, 4:59 pm |
| 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
[color=darkred]
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
|
| 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_proce
ss = 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_collecto
r = true
stats_command_string
= true
#stats_block_level = false
stats_row_level = true
#stats_reset_on_serv
er_start = true
Thank you very much for your help
--
Arnau
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Dario Brignardello 2006-01-26, 4:59 pm |
| 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/...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_collecto
r = true
> stats_command_string
= true
> #stats_block_level = false
> stats_row_level = true
> #stats_reset_on_serv
er_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/...ring-stats.html
Hope it helps.
Best regards
Dario
--
Atte: Dario Brignardello
| |
| Guido Barosio 2006-01-26, 4:59 pm |
| 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,
>
>
>
> Well, there is actually plenty of room to optimize there, I would suggest
> to go to
> http://www.postgresql.org/docs/7.4/...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 :-) )
>
>
>
> 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 :-)
>
>
>
>
> 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/...ring-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 .
/ \ -----------------------------------------------------------------
| |
| John Jensen 2006-01-26, 4:59 pm |
| Hi Arnau,
> 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_proce
ss = 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.
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 ?
[color=darkred]
[color=darkred]
>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_collecto
r = true
> stats_command_string
= true
> #stats_block_level = false
> stats_row_level = true
> #stats_reset_on_serv
er_start = true
I run with:
stats_command_string
= on
stats_block_level = on
stats_row_level = on
stats_reset_on_serve
r_start = on
That definatel gives you what you need.
/John
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
|
|
|
|
|