Re: Optimize update query

Most modern SSD are much faster for fsync type operations than a
spinning disk - similar performance to spinning disk + writeback raid
controller + battery.

However as you mention, they are great at random IO too, so Niels, it
might be worth putting your postgres logs *and* data on the SSDs and
retesting.

Regards

Mark

On 30/11/12 21:37, Vitalii Tymchyshyn wrote:
> Actually, what's the point in putting logs to ssd? SSDs are good for
> random access and logs are accessed sequentially. I'd put table spaces
> on ssd and leave logs on hdd
>
> 30 лист. 2012 04:33, "Niels Kristian Schjødt"
> <nielskristian(at)autouncle(dot)com <mailto:nielskristian(at)autouncle(dot)com>> напис.
>
> Hmm I'm getting suspicious here. Maybe my new great setup with the
> SSD's is not really working as it should., and maybe new relic is
> not monitoring as It should.
>
> 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? - so maybe I did something
> wrong in the setup. Here is the path I followed:
>
> # 1) First setup the SSD drives in a software RAID1 setup:
> #
> http://askubuntu.com/questions/223194/setup-of-two-additional-ssd-drives-in-raid-1
> #
> # 2) Then move the postgres pg_xlog dir
> # sudo /etc/init.d/postgresql-9.2 stop
> # sudo mkdir -p /ssd/pg_xlog
> # 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
>
> Can you spot something wrong?
>
>
>
> Den 30/11/2012 kl. 02.43 skrev Niels Kristian Schjødt
> <nielskristian(at)autouncle(dot)com <mailto:nielskristian(at)autouncle(dot)com>>:
>
> > Den 30/11/2012 kl. 02.24 skrev "Kevin Grittner" <kgrittn(at)mail(dot)com
> <mailto:kgrittn(at)mail(dot)com>>:
> >
> >> Niels Kristian Schjødt wrote:
> >>
> >>> Okay, now I'm done the updating as described above. I did the
> >>> postgres.conf changes. I did the kernel changes, i added two
> >>> SSD's in a software RAID1 where the pg_xlog is now located -
> >>> unfortunately the the picture is still the same :-(
> >>
> >> You said before that you were seeing high disk wait numbers. Now it
> >> is zero accourding to your disk utilization graph. That sounds like
> >> a change to me.
> >>
> >>> When the database is under "heavy" load, there is almost no
> >>> improvement to see in the performance compared to before the
> >>> changes.
> >>
> >> In client-visible response time and throughput, I assume, not
> >> resource usage numbers?
> >>
> >>> A lot of both read and writes takes more than a 1000 times as
> >>> long as they usually do, under "lighter" overall load.
> >>
> >> As an odd coincidence, you showed your max_connections setting to
> >> be 1000.
> >>
> >> http://wiki.postgresql.org/wiki/Number_Of_Database_Connections
> >>
> >> -Kevin
> >
> > Hehe, I'm sorry if it somehow was misleading, I just wrote "a lot
> of I/O" it was CPU I/O, it also states that in the chart in the link.
> > However, as I'm not very familiar with these deep down database
> and server things, I had no idea wether a disk bottle neck could
> hide in this I/O, so i went along with Shauns great help, that
> unfortunately didn't solve my issues.
> > Back to the issue: Could it be that it is the fact that I'm using
> ubuntus built in software raid to raid my disks, and that it is not
> at all capable of handling the throughput?
> >
>
>
>
> --
> Sent via pgsql-performance mailing list
> (pgsql-performance(at)postgresql(dot)org
> <mailto:pgsql-performance(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>