Re: Optimize update query

Oh, yes. I don't imagine DB server without RAID+BBU :)
When there is no BBU, SSD can be handy.
But you know, SSD is worse in linear read/write than HDD.

Best regards, Vitalii Tymchyshyn

2012/11/30 Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>

> 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<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<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<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<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<pgsql-performance(at)postgresql(dot)org>
>> <mailto:pgsql-performance(at)**postgresql(dot)org<pgsql-performance(at)postgresql(dot)org>
>> >)
>>
>> To make changes to your subscription:
>> http://www.postgresql.org/**mailpref/pgsql-performance<http://www.postgresql.org/mailpref/pgsql-performance>
>>
>>
>

--
Best regards,
Vitalii Tymchyshyn