Re: Optimize update query - Mailing list pgsql-performance

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@autouncle.com <mailto:nielskristian@autouncle.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@autouncle.com <mailto:nielskristian@autouncle.com>>:
>
>      > Den 30/11/2012 kl. 02.24 skrev "Kevin Grittner" <kgrittn@mail.com
>     <mailto:kgrittn@mail.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@postgresql.org
>     <mailto:pgsql-performance@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-performance
>