Message-ID: <50B87EA8.50002@catalyst.net.nz>
Date: Fri, 30 Nov 2012 22:38:48 +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: =?ISO-8859-1?Q?Niels_Kristian_Schj=F8dt?=
 <nielskristian@autouncle.com>
CC: Kevin Grittner <kgrittn@mail.com>, sthomas@optionshouse.com,
	Willem Leenen <willem_leenen@hotmail.com>, pgsql-performance@postgresql.org
Subject: Re: Optimize update query
References: <20121130012426.69300@gmx.com>
	<C5E296A1-F820-4B04-9DBD-E78380BD3300@autouncle.com>
	<855F8817-6C9A-4EFB-BB80-752BF7B73B56@autouncle.com>
In-Reply-To: <855F8817-6C9A-4EFB-BB80-752BF7B73B56@autouncle.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

When I try your command sequence I end up with the contents of the new=20
pg_xlog owned by root. Postgres will not start:

PANIC:  could not open file "pg_xlog/000000010000000600000080" (log file=20
6, segment 128): Permission denied

While this is fixable, I suspect you have managed to leave the xlogs=20
directory that postgres is actually using on the HDD drives.


When I do this I normally do:
$   service postgresql stop
$   sudo mkdir -p /ssd/pg_xlog
$   sudo chown -R  postgres.postgres /ssd/pg_xlog
$   sudo chmod 700 /ssd/pg_xlog
$   sudo su - postgres
postgres $   mv /var/lib/postgresql/9.2/main/pg_xlog/* /ssd/pg_xlog
postgres $   rmdir /var/lib/postgresql/9.2/main/pg_xlog
postgres $   ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog
postgres $   service postgresql start

regards

Mark

On 30/11/12 15:32, Niels Kristian Schj=F8dt wrote:
> 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 H=
DD'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 p=
g_xlog right? - so maybe I did something wrong in the setup. Here is the pa=
th 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=F8dt <nielskristian@au=
touncle.com>:
>
>> Den 30/11/2012 kl. 02.24 skrev "Kevin Grittner" <kgrittn@mail.com>:
>>
>>> Niels Kristian Schj=F8dt 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 serv=
er 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 ubuntu=
s built in software raid to raid my disks, and that it is not at all capabl=
e of handling the throughput?
>>
>
>
>


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