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

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