Re: Optimize update query - Mailing list pgsql-performance

Den 30/11/2012 kl. 15.02 skrev Shaun Thomas <sthomas@optionshouse.com>:

> On 11/29/2012 08:32 PM, Niels Kristian Schjødt wrote:
>
>> If I do a "sudo iostat -k 1"
>> I get a lot of output like this:
>> Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
>> sda               0.00         0.00         0.00          0          0
>> sdb               0.00         0.00         0.00          0          0
>> sdc             546.00      2296.00      6808.00       2296       6808
>> sdd             593.00      1040.00      7416.00       1040       7416
>> md1               0.00         0.00         0.00          0          0
>> md0               0.00         0.00         0.00          0          0
>> md2            1398.00      3328.00     13064.00       3328      13064
>> md3               0.00         0.00         0.00          0          0
>>
>
>> The storage thing is, that the sda and sdb is the SSD drives and the
>> sdc and sdd is the HDD drives. The md0, md1 and md2 is the raid
>> arrays on the HDD's and the md3 is the raid on the SSD's. Neither of
>> the md3 or the SSD's are getting utilized - and I should expect that
>> since they are serving my pg_xlog right?
>
> No, that's right. They are, but it would appear that the majority of your traffic actually isn't due to transaction
logslike I'd suspected. If you get a chance, could you monitor the contents of: 
>
> /var/lib/postgresql/9.2/main/base/pgsql_tmp
>
> Your main drives are getting way, way more writes than they should. 13MB per second is ridiculous even under heavy
writeloads. Based on the TPS count, you're basically saturating the ability of those two 3TB drives. Those writes have
tobe coming from somewhere. 
>
>> #   sudo mkdir -p /ssd/pg_xlog
>
> This is going to sound stupid, but are you *sure* the SSD is mounted at /ssd ?
>
>> #   sudo chown -R  postgres.postgres /ssd/pg_xlog
>> #   sudo chmod 700 /ssd/pg_xlog
>> #   sudo cp -rf /var/lib/postgresql/9.2/main/pg_xlog/* /ssd/pg_xlog
>> #   sudo mv /var/lib/postgresql/9.2/main/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog_old
>> #   sudo ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog
>> #   sudo /etc/init.d/postgresql-9.2 start
>
> The rest of this is fine, except that you probably should have added:
>
> sudo chown -R postgres:postgres /ssd/pg_xlog/*
>
>
> --
> Shaun Thomas
> OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
> 312-444-8534
> sthomas@optionshouse.com
>
> ______________________________________________
>
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

Oh my, Shaun once again you nailed it! That's what you get from working too late in the night - I forgot to run 'sudo
mount-a' I feel so embarrassed now :-( - In other words no the drive was not mounted to the /ssd dir.  
So now it is, and this has gained me a performance increase of roughly around 20% - a little less than what I would
havehoped for but still better - but anyways yes that's right. 
I still see a lot of CPU I/O when doing a lot of writes, so the question is, what's next. Should I try and go' for the
connectionpooling thing or monitor that /var/lib/postgresql/9.2/main/base/pgsql_tmp dir (and what exactly do you mean
bymonitor - size?) 

PS. comment on the "Why not move the data to the SSDs" you are exactly right. i don't think the SSD's will be big
enoughfor the data within a not too long timeframe, so that is exactly why I want to keep my data on the "big" drives. 
PPS. I talked with New Relic and it turns out there is something wrong with the disk monitoring tool, so that's why
therewas nothing in the disk charts but iostat showed a lot of activity.