From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Vitalii Tymchyshyn <tivv00@gmail.com>
Cc: Niels Kristian Schjødt <nielskristian@autouncle.com>
Cc: Kevin Grittner <kgrittn@mail.com>
Cc: sthomas@optionshouse.com, pgsql-performance@postgresql.org, Willem Leenen <willem_leenen@hotmail.com>
Subject: Re: Optimize update query
Date: Fri, 30 Nov 2012 22:19:27 +1300
Message-ID: <50B87A1F.9020502@catalyst.net.nz> (raw)
In-Reply-To: <CABWW-d0OAT7L0beYR160jieG4=-PMHhmiMVMh=v3pNYspd9XCA@mail.gmail.com>
References: <20121130012426.69300@gmx.com>
<C5E296A1-F820-4B04-9DBD-E78380BD3300@autouncle.com>
<855F8817-6C9A-4EFB-BB80-752BF7B73B56@autouncle.com>
<CABWW-d0OAT7L0beYR160jieG4=-PMHhmiMVMh=v3pNYspd9XCA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz, tivv00@gmail.com, nielskristian@autouncle.com, kgrittn@mail.com, willem_leenen@hotmail.com
Subject: Re: Optimize update query
In-Reply-To: <50B87A1F.9020502@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox