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