Re: Where is my bottleneck? - Mailing list pgsql-admin

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