Re: Do I have a hardware or a software problem?

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

> On Mon, Dec 10, 2012 at 2:51 PM, Niels Kristian Schjødt
> <nielskristian@autouncle.com> wrote:
>
>> synchronous_commit = off
>>
>> The pg_xlog folder has been moved onto the SSD array (md3), and symlinked
>> back into the postgres dir.
>
> With synchronous_commit = off, or with large transactions, there is
> probably no advantage to moving those to SSD.
>
>
>> 2)
>> When the database is loaded like this, I see a lot of queries talking up to
>> 1000 times as long, as they would when the database is not loaded so
>> heavily.
>
> What kinds of queries are they?  single-row look-ups, full table scans, etc.
Well Mostly they are updates. Like the one shown in the previous question I referenced.
>>
>> #### Notes and thoughts
>> ##############################################################################
>>
>> As you can see, even though I have moved the pg_xlog folder to the SSD array
>> (md3) the by far largest amount of writes still goes to the regular HDD's
>> (md2), which puzzles me - what can that be?
>
> Every row you insert or non-HOT update has to do maintenance on all
> indexes of that table.  If the rows are not inserted/updated in index
> order, this means you every row inserted/updated dirties a randomly
> scattered 8KB for each of the indexes.  If you have lots of indexes
> per table, that adds up fast.
>
> 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? 
> So you are redirtying the same blocks over and over, PG is
> desperately dumping them to the kernel (because shared_buffers it too
> small to hold them) and the kernel is desperately dumping them to
> disk, because vm.dirty_background_ratio is so low.  There is little
> opportunity for write-combining, because they don't sit in memory long
> enough to accumulate neighbors.
>
> How big are your indexes?
This is a size list of all my indexes: 117 MB, 118 MB, 11 MB, 12 MB, 12 MB, 12 MB, 12 MB, 140 MB, 15 MB, 15 MB, 16 kB,
16kB, 16 kB, 16 kB, 16 kB, 16 kB, 16 kB, 16 kB, 16 kB, 16 kB, 16 kB, 16 kB, 16 MB, 16 MB, 176 kB, 176 kB, 17 MB, 18 MB,
19MB, 23 MB, 240 kB, 24 MB, 256 kB, 25 MB, 25 MB, 26 MB, 26 MB, 27 MB, 27 MB, 27 MB, 27 MB, 280 MB, 2832 kB, 2840 kB,
288kB, 28 MB, 28 MB, 28 MB, 28 MB, 28 MB, 28 MB, 28 MB, 28 MB, 29 MB, 29 MB, 3152 kB, 3280 kB, 32 kB, 32 MB, 32 MB,
3352kB, 3456 kB, 34 MB, 36 MB, 3744 kB, 3776 kB, 37 MB, 37 MB, 3952 kB, 400 kB, 408 kB, 40 kB, 40 kB, 40 kB, 416 kB,
416kB, 42 MB, 432 kB, 4520 kB, 4720 kB, 47 MB, 48 kB, 496 kB, 49 MB, 512 kB, 52 MB, 52 MB, 5304 kB, 5928 kB, 6088 kB,
61MB, 6224 kB, 62 MB, 6488 kB, 64 kB, 6512 kB, 71 MB, 72 kB, 72 kB, 8192 bytes, 8400 kB, 88 MB, 95 MB, 98 MB 
> 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?
> .
> Rather than moving maintenance to the day and hoping it doesn't
> interfere with normal operations, I'd focus on making night-time
> maintenance more efficient, for example by dropping indexes (either
> just at night, or if some indexes are not useful, just get rid of them
> altogether), or cranking up shared_buffers at night, or maybe
> partitioning or look into pg_bulkload.
>
>> From stat 3) (the iostat) I notice that the SSD's doesn't seem to be
>> something near fully utilized - maybe something else than just pg_xlog could
>> be moved her?
>
> 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?
> Or, more fundamentally, it looks like you spent too much on CPUs (86%
> idle) and not nearly enough on disks.  Maybe you can fix that for less
> money than it will cost you in your optimization time to make the best
> of the disks you already have.
The SSD's I use a are 240Gb each which will grow too small within a few months - so - how does moving the whole data
dironto four of those in a RAID5 array sound? 
>
> Cheers,
>
> Jeff