Re: Where is my bottleneck?

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