Message-ID: <50B87A1F.9020502@catalyst.net.nz>
Date: Fri, 30 Nov 2012 22:19:27 +1300
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
To: Vitalii Tymchyshyn <tivv00@gmail.com>
CC: =?UTF-8?B?TmllbHMgS3Jpc3RpYW4gU2NoasO4ZHQ=?=
	<nielskristian@autouncle.com>, Kevin Grittner <kgrittn@mail.com>,
	sthomas@optionshouse.com, pgsql-performance@postgresql.org,
	Willem Leenen <willem_leenen@hotmail.com>
Subject: Re: Optimize update query
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>
In-Reply-To: 
 <CABWW-d0OAT7L0beYR160jieG4=-PMHhmiMVMh=v3pNYspd9XCA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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

However as you mention, they are great at random IO too, so Niels, it=20
might be worth putting your postgres logs *and* data on the SSDs and=20
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 =D0=BB=D0=B8=D1=81=D1=82. 2012 04:33, "Niels Kristian Schj=C3=B8dt"
> <nielskristian@autouncle.com <mailto:nielskristian@autouncle.com>> =D0=BD=
=D0=B0=D0=BF=D0=B8=D1=81.
>
>     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-dri=
ves-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=C3=B8dt
>     <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=C3=B8dt 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 li=
ke
>      >> 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
>


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance