pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John Jensen <JRJ@ft.fo>
To: arnaulist@andromeiberica.com
To: pgsql-admin@postgresql.org
Subject: Re: Where is my bottleneck?
Date: Thu, 26 Jan 2006 08:57:08 +0000
Message-ID: <s3d88efc.092@fs3.ft.fo> (raw)

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




view thread (11+ messages)

Message-ID: <s3d88efc.092@fs3.ft.fo>
Permalink:  ../s3d88efc.092@fs3.ft.fo/
Also on:    postgresql.org/message-id/s3d88efc.092@fs3.ft.fo

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: JRJ@ft.fo, arnaulist@andromeiberica.com
  Subject: Re: Where is my bottleneck?
  In-Reply-To: <s3d88efc.092@fs3.ft.fo>
  References: <s3d88efc.092@fs3.ft.fo>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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