Re: Optimize update query

[Niels Kristian Schjødt <nielskristian@xxxxxxxxxxxxx>]

Hmm very very interesting. Currently I run at "medium" load compared to the very high loads in the night.

This is what the CPU I/O on new relic show: https://rpm.newrelic.com/public/charts/8RnSOlWjfBy

And this is what iostat shows:

Linux 3.2.0-33-generic (master-db) 11/30/2012 _x86_64_ (8 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util

sda               0.00     3.46   26.62   57.06     1.66     0.68    57.41     0.04    0.43    0.77    0.28   0.09   0.73

sdb               0.03    16.85    0.01   70.26     0.00     2.35    68.36     0.06    0.81    0.21    0.81   0.10   0.73

sdc               1.96    56.37   25.45  172.56     0.53     3.72    43.98    30.83  155.70   25.15  174.96   1.74  34.46

sdd               1.83    56.52   25.48  172.42     0.52     3.72    43.90    30.50  154.11   25.66  173.09   1.74  34.37

md1               0.00     0.00    0.00    0.00     0.00     0.00     3.02     0.00    0.00    0.00    0.00   0.00   0.00

md0               0.00     0.00    0.57    0.59     0.00     0.00     8.00     0.00    0.00    0.00    0.00   0.00   0.00

md2               0.00     0.00   54.14  227.94     1.05     3.72    34.61     0.00    0.00    0.00    0.00   0.00   0.00

md3               0.00     0.00    0.01   60.46     0.00     0.68    23.12     0.00    0.00    0.00    0.00   0.00   0.00

A little reminder md3 is the raid array of the ssd drives sda and sdb and the md0-2 is the array of the regular hdd drives sdc and sdd

The pgsql_tmp dir is not changing at all it's constantly empty (a size of 4.0K).

So It doesn't seem like the ssd drives is at all utilized but the regular drives certainly is. but now i know for sure that the /ssd is mounted correctly:

"sudo df /ssd"

Filesystem     1K-blocks    Used Available Use% Mounted on

/dev/md3       230619228 5483796 213420620   3% /ssd

 

Den 30/11/2012 kl. 16.00 skrev Shaun Thomas <sthomas@xxxxxxxxxxxxxxxx>:

On 11/30/2012 08:48 AM, Niels Kristian Schjødt wrote:

I forgot to run 'sudo mount -a' I feel so embarrassed now :-( - In
other words no the drive was not mounted to the /ssd dir.

Yeah, that'll get ya.

I still see a lot of CPU I/O when doing a lot of writes, so the
question is, what's next. Should I try and go' for the connection
pooling thing or monitor that
/var/lib/postgresql/9.2/main/base/pgsql_tmp dir (and what exactly do
you mean by monitor - size?)

Well, like Keven said, if you have more than a couple dozen connections on your hardware, you're losing TPS. It's probably a good idea to install pgbouncer or pgpool and let your clients connect to those instead. You should see a good performance boost from that.

But what concerns me is that your previous CPU charts showed a lot of iowait. Even with the SSD taking some of the load off your write stream, something else is going on, there. That's why you need to monitor the "size" in MB, or number of files, for the pgsql_tmp directory. That's where PG puts temp files when sorts are too big for your work_mem. If that's getting a ton of activity, that would explain some of your write overhead.

PPS. I talked with New Relic and it turns out there is something
wrong with the disk monitoring tool, so that's why there was nothing
in the disk charts but iostat showed a lot of activity.

Yeah. Next time you need to check IO, use iostat. It's not as pretty, but it tells everything. ;) Just to help out with that, use:

iostat -dmx

That will give you extended information, including the % utilization of your drives. TPS stats are nice, but I was just guessing your drives were stalling out based on experience. Getting an outright percentage is better. You should also use sar. Just a plain:

sar 1 100

Will give you a lot of info on what the CPU is doing. You want that %iowait column to be as low as possible.

Keep us updated.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@xxxxxxxxxxxxxxxx

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email