Skip site navigation (1) Skip section navigation (2)

Re: Where is my bottleneck?

From: Arnau <arnaulist(at)andromeiberica(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Cc: John Jensen <JRJ(at)ft(dot)fo>
Subject: Re: Where is my bottleneck?
Date: 2006-01-25 16:57:07
Message-ID: 43D7ADE3.1050803@andromeiberica.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-admin by date

Next:From: Daniel MDate: 2006-01-25 17:07:16
Subject: Problem with SSL - Server went 100% Busy - Part II
Previous:From: Yantao ShiDate: 2006-01-25 16:53:46
Subject: Re: psql copy script

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group