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

Re: optimizing selects on time-series data in Pg

From: Aditya <aditya(at)grot(dot)org>
To: sfpug(at)postgresql(dot)org
Subject: Re: optimizing selects on time-series data in Pg
Date: 2003-08-01 18:31:10
Message-ID: 20030801183110.GB29105@mighty.grot.org (view raw or flat)
Thread:
Lists: sfpug
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

In response to

Responses

sfpug by date

Next:From: AdityaDate: 2003-08-01 18:38:57
Subject: Re: optimizing selects on time-series data in Pg
Previous:From: Stephan SzaboDate: 2003-08-01 18:28:05
Subject: Re: optimizing selects on time-series data in Pg

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