Re: Do I have a hardware or a software problem? - Mailing list pgsql-performance

Den 11/12/2012 kl. 18.25 skrev Jeff Janes <jeff.janes@gmail.com>:

> On Tue, Dec 11, 2012 at 2:04 AM, Niels Kristian Schjødt
> <nielskristian@autouncle.com> wrote:
>> Den 11/12/2012 kl. 00.58 skrev Jeff Janes <jeff.janes@gmail.com>:
>>
>>>
>>> The fact that there is much more writing than reading tells me that
>>> most of your indexes are in RAM.  The amount of index you are rapidly
>>> reading and dirtying is large enough to fit in RAM, but is not large
>>> enough to fit in shared_buffers + kernel's dirty-buffer comfort level.
>
>> Maybe I should mention, that I never see more than max 5Gb out of my total 32Gb being in use on the server… Can I
somehowutilize more of it? 
>
> What tool do you use to determine that?  Is that on top of the 4GB
> shared_buffers, are including it?

Okay I might not have made myself clear, I was talking "physical" memory utilization. Here is the stats:
free -m
total       used       free     shared    buffers     cached
Mem:         32075      25554       6520          0         69      22694
-/+ buffers/cache:       2791      29284
Swap:         2046        595       1451
>
> How big is your entire data set?  Maybe all your data fits in 5GB
> (believable, as all your indexes listed below sum to < 2.5GB) so there
> is no need to use more.

It doesn't we are a search engine for used cars, and there are quite a lot of those out there :-) However, my indexes
arealmost all partial indexes, which mean that they are only on cars which is still for sale, so in that sense, the
indexesthem selves doesn't really grow, but the tables do. 

>
> Or maybe you have hit an bug in the 3.2 kernel.  At least one of those
> has been frequently discussed.
>
Might be true - but likely?
>
>>> You could really crank up shared_buffers or vm.dirty_background_ratio,
>>> but doing so might cause problems with checkpoints stalling and
>>> latency spikes.  That would probably not be a problem during the
>>> night, but could be during the day.
>
>> What do you have in mind here? Tweaking what parameters to what values?
>
> I'd set shared_buffers to 20GB (or 10GB, if that will hold all of your

I had that before, Shaun suggested that I changed it to 4GB as he was talking about a strange behavior when larger than
thaton 12.04. But I can say, that there has not been any notable difference between having it at 4Gb and at 8Gb. 

> data) and see what happens.  And probably increase checkpoint_timeout
> and checkpoint_segments about 3x each. Also, turn on log_checkpoints
> so you can see what kinds of problem those changes may be causing
> there (i.e. long sync times).  Preferably you do this on some kind of
> pre-production or test server.
>
> But if your database is growing so rapidly that it soon won't fit on
> 240GB, then cranking up shared_buffers won't do for long.  If you can
> get your tables and all of their indexes clustered together, then you
> can do the updates in an order that makes IO more efficient.  Maybe
> partitioning would help.

Can you explain a little more about this, or provide me a good link?
>
>
>>> I don't know how big each disk is, or how big your various categories
>>> of data are.  Could you move everything to SSD?  Could you move all
>>> your actively updated indexes there?
>
>> With table spaces you mean?
>
> Yes.  Or moving everything to SSD if it fits, then you don't have go
> through and separate objects.
>
> The UPDATE you posted in a previous thread looked like the table
> blocks might also be getting dirtied in a fairly random order, which
> means the table blocks are in the same condition as the index blocks
> so maybe singling out the indexes isn't warranted.
>
> Cheers,
>
> Jeff