Den 30/11/2012 kl. 15.02 skrev Shaun Thomas <stho...@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 logs like 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 write loads. Based on the TPS count, > you're basically saturating the ability of those two 3TB drives. Those writes > have to be 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 > stho...@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 have hoped 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 connection pooling thing or monitor that /var/lib/postgresql/9.2/main/base/pgsql_tmp dir (and what exactly do you mean by monitor - 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 enough for 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 there was nothing in the disk charts but iostat showed a lot of activity. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
