Re: Need setup help for Postgresql 8.1.3 on Solaris

Gellert, Andre wrote:
> Hello Richard,
>
>>> We have such a testsystem now, 4 x AMD Opteron (double core techn.)
>>> 885 2.6ghz , running with 32 GB Ram and fast 10.000rpm SAS discs,
>> How many disks? What RAID?
>
> 4 Disks, RAID1.
>
>> Is your disk I/O saturating? Try vmstat/iostat (or whatever the Solaris
>> equivalent is).
>
> Hard to say, due to account restrictions I cannot control more than this vmstat output gives me:
>
> This is during loading Data, 98% idle:
>
> -bash-3.00$ vmstat 5
>  kthr      memory            page            disk          faults      cpu
>  r b w   swap      free  re  mf pi  po  fr de sr m1 m1 m1 m2   in   sy   cs us sy id
>  0 0 0 31865080 27723492 248 43 138 118 79 0  5  1  1  1  0  6873 4235 1481  2  1 98
>  0 0 0 31675384 27518448 603 14 0 0 0 0  0  0  0  0  0 15808 1695 1243 1  1 98
>  0 0 0 31675384 27518436 609 0 0 0 0  0  0  0  0  0  0 16027 1703 1266 1  1 98
>  0 0 0 31675372 27518408 602 0 0 0 0  0  0  0  0  0  0 15808 1682 1231 1  1 98
>  0 0 0 31675356 27513660 1280 0 0 0 0 0  0  0  0  0  0 44234 1908 861  1  1 98

More interestingly your disks appear to be doing nothing. Which can't be
right. They're not all zeroes though, so I'm not sure why you're seeing
no disk activity.

Nothing's blocking on disk I/O though...

That also seems like a large number of interrupts for an idle machine.

>  0 0 0 31673996 27510360 318 0 0 0 0  0  0  0  0  0  0 12950 1082 849  0  1 99
>  0 0 0 31673996 27508960 408 203 0 40 40 0 0 0 0  0  0 14094 1318 883  0  1 99
>  0 0 0 31673996 27507568 443 0 0 0 0  0  0  0  0  0  0 14330 1373 1052 1  1 98
> and so on. During the whole import of data it keeps mainly 99% idle.
>
> This is during "working" on indexes creation:
>
> -bash-3.00$ vmstat 5
>  kthr      memory            page            disk          faults      cpu
>  r b w   swap  free  re  mf pi po fr de sr m1 m1 m1 m2   in   sy   cs us sy id
>  0 0 0 31851400 27708172 252 44 129 111 74 0 5 1 0 0 0 7169 4006 1419  2  1 98
>  0 0 0 31598656 27425288 50 14 0 0 0  0  0  0  0  0  0 1608   72  156 13  0 87
>  0 0 0 31598656 27425224 6973 0 0 0 0 0  0  0  0  0  0 147690 3516 161 12 4 84
>  0 0 0 31598656 27424696 13194 0 0 3 3 0 0  0  0  0  0 279048 6620 147 12 7 81
>  0 0 0 31598656 27424116 14528 0 0 0 0 0 0  0  0  0  0 307360 7285 153 12 8 80
>  0 0 0 31598656 27423852 13503 0 0 0 0 0 0  0  0  0  0 285909 6767 149 12 7 81

Now that's a *huge* number of interrupts, unless I'm mis-reading this.
I'm afraid I don't know enough about Solaris to say for sure, but
there's something strange going on here.

> Looks fine, doesn't it?
>
>> I think your work_mem is too large (100MB) and maintenance_work_mem too
>> small, for a restore with 32GB of RAM anyway.
>
> I decreased the import time from >40 minutes to 32 minutes with changing the parameters to:
>
> shared_buffers = 450000  # kept same
> temp_buffers = 100000    # kept same
>
> work_mem =     32768                # now 32mb , before: 102400 = 100mb
> maintenance_work_mem =  2048000 # now 2.048mb, before: 32768 = 32mb
>
> Should I encrease these parameters?

Nothing that should cause these problems.

Anything unusual about your installation? Any oddities from ./configure
or similar?

--
   Richard Huxton
   Archonet Ltd