Re: Optimize update query

Hmm I'm getting suspicious here. Maybe my new great setup with the SSD's is not really working as it should., and maybe
newrelic 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 bottle
neckcould 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? 
>