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?
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.
Or maybe you have hit an bug in the 3.2 kernel. At least one of those
has been frequently discussed.
>> 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
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.
>> 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