Re: Where is my bottleneck?

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