Re: Optimize update query - Mailing list pgsql-performance

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

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

While this is fixable, I suspect you have managed to leave the xlogs
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ødt wrote:
> Hmm I'm getting suspicious here. Maybe my new great setup with the SSD's is not really working as it should., and
maybenew 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
md2is 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>:
>
>> Den 30/11/2012 kl. 02.24 skrev "Kevin Grittner" <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
chartin the link. 
>> However, as I'm not very familiar with these deep down database and server things, I had no idea wether a disk
bottleneck 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,
andthat it is not at all capable of handling the throughput? 
>>
>
>
>