Re: optimizing selects on time-series data in Pg

On Fri, Aug 01, 2003 at 11:05:22AM -0700, Josh Berkus wrote:
> Aditya,
>
> > shared_buffers = 128 # 2*max_connections, min 16 (default 64)
> > ...
> > #effective_cache_size = 1000 # default in 8k pages
>
> You could certainly stand to raise these, unless your system is low on RAM ...
>
> > PIII-800/512MB RAM running FreeBSD 4-STABLE with the database data files
> > NFS mounted via a 100Mbps/full-duplex private network from a lightly-loaded
> > Network Appliance F87 fileserver (I'm pretty certain that we're not IO
> > bound).
>
> Mind checking TOP to see how much RAM your other processes on this server eat?
> That will give us a good baseline for figuring out how much memory to give
> Postgres.

I do have some stats collection turned on in postgresql.conf, if that makes
any difference:

#
# Access statistics collection
#
stats_start_collector = true
stats_reset_on_server_start = false
stats_command_string = true
stats_row_level = false
stats_block_level = true

and top, right after I ran Stephan's suggestion and just as top started up
(the CPU sits at 95-99% idle most of the time otherwise):

last pid: 36063; load averages: 0.38, 0.56, 0.39 up 453+17:25:41 11:25:25
68 processes: 1 running, 65 sleeping, 2 stopped
CPU states: 8.9% user, 0.0% nice, 16.3% system, 16.0% interrupt, 58.8% idle
Mem: 95M Active, 297M Inact, 79M Wired, 25M Cache, 61M Buf, 2456K Free
Swap: 1024M Total, 388K Used, 1023M Free

PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
36021 postgres -1 0 7716K 4868K nfsrcv 0:01 2.15% 2.15% postgres
93 root 2 0 212K 28K sbwait 505:27 0.83% 0.83% nfsiod
36031 postgres 2 0 7540K 4772K sbwait 0:00 0.68% 0.68% postgres
36063 aditya 28 0 2012K 1084K RUN 0:00 1.82% 0.54% top
36030 postgres 2 0 7492K 4560K sbwait 0:02 0.44% 0.44% postgres
58188 postgres 2 0 12472K 6208K select 636:35 0.00% 0.00% postgres
992 spread 2 0 2480K 1684K select 415:03 0.00% 0.00% spread
94 root 10 0 212K 28K nfsidl 237:25 0.00% 0.00% nfsiod
1018 postgres 2 0 5864K 3916K sbwait 172:00 0.00% 0.00% postgres
95 root 10 0 212K 28K nfsidl 103:31 0.00% 0.00% nfsiod
1016 check 2 0 5900K 4368K sbwait 95:47 0.00% 0.00% perl
96 root 10 0 212K 28K nfsidl 91:35 0.00% 0.00% nfsiod
58187 postgres 2 0 7712K 3680K select 52:20 0.00% 0.00% postgres
84 root 2 0 1436K 1076K select 27:17 0.00% 0.00% syslogd
37671 postgres 2 0 5468K 2720K select 18:42 0.00% 0.00% postgres
86937 root 4 0 2536K 2176K bpf 16:19 0.00% 0.00% arpwatch
51 root 10 0 916K 724K nanslp 11:53 0.00% 0.00% ipmon
58186 postgres 2 0 6756K 2996K select 11:20 0.00% 0.00% postgres
37673 postgres 2 0 5500K 2740K select 10:26 0.00% 0.00% postgres
104 root 10 0 996K 640K nanslp 6:48 0.00% 0.00% cron
4869 root 2 0 1316K 824K select 6:27 0.00% 0.00% ntpd
39644 bind 2 0 2844K 2356K select 3:55 0.00% 0.00% named
4955 root 2 0 2116K 1096K select 2:42 0.00% 0.00% master
66245 postgres 2 0 8824K 6324K sbwait 2:38 0.00% 0.00% postgres
67834 postgres 2 0 7560K 4864K sbwait 2:26 0.00% 0.00% postgres
82584 root 2 0 2372K 1460K select 2:16 0.00% 0.00% sshd
1008 aditya 2 0 2800K 1952K sbwait 2:09 0.00% 0.00% perl
37672 postgres 2 0 6456K 3656K select 1:45 0.00% 0.00% postgres
46699 postfix 2 0 2204K 1236K select 1:08 0.00% 0.00% qmgr
7373 root 2 0 2328K 1596K select 0:42 0.00% 0.00% sshd