SEO

Hi,

I was called to find out why one of our PostgreSQL servers has not a
satisfatory response time. The server has only two SCSI disks configurated
as a RAID1 software.

While collecting performance data I discovered very bad numbers in the I/O
subsystem and I would like to know if I´m thinking correctly.

Here is a typical iostat -x:

avg-cpu: %user %nice %system %iowait %idle

50.40 0.00 0.50 1.10 48.00



Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
avgrq-sz avgqu-sz await svctm %util

sda 0.00 7.80 0.40 6.40 41.60 113.60 20.80 56.80
22.82 570697.50 10.59 147.06 100.00

sdb 0.20 7.80 0.60 6.40 40.00 113.60 20.00 56.80
21.94 570697.50 9.83 142.86 100.00

md1 0.00 0.00 1.20 13.40 81.60 107.20 40.80 53.60
12.93 0.00 0.00 0.00 0.00

md0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00



Are they not saturated?



What kind of parameters should I pay attention when comparing SCSI
controllers and disks? I would like to discover how much cache is present in
the controller, how can I find this value from Linux?



Thank you in advance!



dmesg output:

....

SCSI subsystem initialized

ACPI: PCI Interrupt 0000:04:02.0[A] -> GSI 18 (level, low) -> IRQ 18

scsi0 : Adaptec AIC79XX PCI-X SCSI HBA DRIVER, Rev 3.0

<Adaptec (Dell OEM) 39320 Ultra320 SCSI adapter>

aic7902: Ultra320 Wide Channel A, SCSI Id=7, PCI 33 or 66Mhz, 512
SCBs



Vendor: SEAGATE Model: ST336607LW Rev: DS10

Type: Direct-Access ANSI SCSI revision: 03

target0:0:0: asynchronous

scsi0:A:0:0: Tagged Queuing enabled. Depth 4

target0:0:0: Beginning Domain Validation

target0:0:0: wide asynchronous

target0:0:0: FAST-160 WIDE SCSI 320.0 MB/s DT IU QAS RDSTRM RTI WRFLOW
PCOMP (6.25 ns, offset 63)

target0:0:0: Ending Domain Validation

SCSI device sda: 71132959 512-byte hdwr sectors (36420 MB)

sda: Write Protect is off

sda: Mode Sense: ab 00 10 08

SCSI device sda: drive cache: write back w/ FUA

SCSI device sda: 71132959 512-byte hdwr sectors (36420 MB)

sda: Write Protect is off

sda: Mode Sense: ab 00 10 08

SCSI device sda: drive cache: write back w/ FUA

sda: sda1 sda2 sda3

sd 0:0:0:0: Attached scsi disk sda

Vendor: SEAGATE Model: ST336607LW Rev: DS10

Type: Direct-Access ANSI SCSI revision: 03

target0:0:1: asynchronous

scsi0:A:1:0: Tagged Queuing enabled. Depth 4

target0:0:1: Beginning Domain Validation

target0:0:1: wide asynchronous

target0:0:1: FAST-160 WIDE SCSI 320.0 MB/s DT IU QAS RDSTRM RTI WRFLOW
PCOMP (6.25 ns, offset 63)

target0:0:1: Ending Domain Validation

SCSI device sdb: 71132959 512-byte hdwr sectors (36420 MB)

sdb: Write Protect is off

sdb: Mode Sense: ab 00 10 08

SCSI device sdb: drive cache: write back w/ FUA

SCSI device sdb: 71132959 512-byte hdwr sectors (36420 MB)

sdb: Write Protect is off

sdb: Mode Sense: ab 00 10 08

SCSI device sdb: drive cache: write back w/ FUA

sdb: sdb1 sdb2 sdb3

sd 0:0:1:0: Attached scsi disk sdb

ACPI: PCI Interrupt 0000:04:02.1[b] -> GSI 19 (level, low) -> IRQ 19

scsi1 : Adaptec AIC79XX PCI-X SCSI HBA DRIVER, Rev 3.0

<Adaptec (Dell OEM) 39320 Ultra320 SCSI adapter>

aic7902: Ultra320 Wide Channel B, SCSI Id=7, PCI 33 or 66Mhz, 512
SCBs

....



Reimer

Carlos H. Reimer wrote:
> While collecting performance data I discovered very bad numbers in the
> I/O subsystem and I would like to know if I´m thinking correctly.
>
> Here is a typical iostat -x:
>
>
> avg-cpu: %user %nice %system %iowait %idle
>
> 50.40 0.00 0.50 1.10 48.00
>
>
>
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
> avgrq-sz avgqu-sz await svctm %util
>
> sda 0.00 7.80 0.40 6.40 41.60 113.60 20.80
> 56.80 22.82 570697.50 10.59 147.06 100.00
>
> sdb 0.20 7.80 0.60 6.40 40.00 113.60 20.00
> 56.80 21.94 570697.50 9.83 142.86 100.00
>
> md1 0.00 0.00 1.20 13.40 81.60 107.20 40.80
> 53.60 12.93 0.00 0.00 0.00 0.00
>
> md0 0.00 0.00 0.00 0.00 0.00 0.00 0.00
> 0.00 0.00 0.00 0.00 0.00 0.00
>
>
>
> Are they not saturated?
>

They look it (if I'm reading your typical numbers correctly) - %util 100
and svctime in the region of 100 ms!

On the face of it, looks like you need something better than a RAID1
setup - probably RAID10 (RAID5 is probably no good as you are writing
more than you are reading it seems). However read on...

If this is a sudden change in system behavior, then it is probably worth
trying to figure out what is causing it (i.e which queries) - for
instance it might be that you have some new queries that are doing disk
based sorts (this would mean you really need more memory rather than
better disk...)

Cheers

Mark



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Carlos H. Reimer wrote:

>
>
> avg-cpu: %user %nice %system %iowait %idle
>
> 50.40 0.00 0.50 1.10 48.00
>
>
>
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
> avgrq-sz avgqu-sz await svctm %util
>
> sda 0.00 7.80 0.40 6.40 41.60 113.60 20.80
> 56.80 22.82 570697.50 10.59 147.06 100.00
>
> sdb 0.20 7.80 0.60 6.40 40.00 113.60 20.00
> 56.80 21.94 570697.50 9.83 142.86 100.00
>
> md1 0.00 0.00 1.20 13.40 81.60 107.20 40.80
> 53.60 12.93 0.00 0.00 0.00 0.00
>
> md0 0.00 0.00 0.00 0.00 0.00 0.00 0.00
> 0.00 0.00 0.00 0.00 0.00 0.00
>
>
>
> Are they not saturated?
>
>
>
> What kind of parameters should I pay attention when comparing SCSI
> controllers and disks? I would like to discover how much cache is
> present in the controller, how can I find this value from Linux?
>
>
These number look a bit strange. I am wondering if there is a hardware
problem on one of the drives
or on the controller. Check in syslog for messages about disk timeouts
etc. 100% util but 6 writes/s
is just wrong (unless the drive is a 1980's vintage floppy).



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

David Boreham wrote:

>
> These number look a bit strange. I am wondering if there is a hardware
> problem on one of the drives
> or on the controller. Check in syslog for messages about disk timeouts
> etc. 100% util but 6 writes/s
> is just wrong (unless the drive is a 1980's vintage floppy).
>

Agreed - good call, I was misreading the wkB/s as wMB/s...

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Hi,

I´ve taken a look in the /var/log/messages and found some temperature
messages about the disk drives:

Nov 30 11:08:07 totall smartd[1620]: Device: /dev/sda, Temperature changed 2
Celsius to 51 Celsius since last report

Can this temperature influence in the performance?

Reimer

> -----Mensagem original-----
> De: David Boreham [mailto:david_list@boreham.org]
> Enviada em: sexta-feira, 1 de dezembro de 2006 00:25
> Para: carlos.reimer@opendb.com.br
> Cc: pgsql-performance@postgresql.org
> Assunto: Re: [PERFORM] Bad iostat numbers
>
>
> Carlos H. Reimer wrote:
>
> >
> >
> > avg-cpu: %user %nice %system %iowait %idle
> >
> > 50.40 0.00 0.50 1.10 48.00
> >
> >
> >
> > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
> > avgrq-sz avgqu-sz await svctm %util
> >
> > sda 0.00 7.80 0.40 6.40 41.60 113.60 20.80
> > 56.80 22.82 570697.50 10.59 147.06 100.00
> >
> > sdb 0.20 7.80 0.60 6.40 40.00 113.60 20.00
> > 56.80 21.94 570697.50 9.83 142.86 100.00
> >
> > md1 0.00 0.00 1.20 13.40 81.60 107.20 40.80
> > 53.60 12.93 0.00 0.00 0.00 0.00
> >
> > md0 0.00 0.00 0.00 0.00 0.00 0.00 0.00
> > 0.00 0.00 0.00 0.00 0.00 0.00
> >
> >
> >
> > Are they not saturated?
> >
> >
> >
> > What kind of parameters should I pay attention when comparing SCSI
> > controllers and disks? I would like to discover how much cache is
> > present in the controller, how can I find this value from Linux?
> >
> >
> These number look a bit strange. I am wondering if there is a hardware
> problem on one of the drives
> or on the controller. Check in syslog for messages about disk timeouts
> etc. 100% util but 6 writes/s
> is just wrong (unless the drive is a 1980's vintage floppy).
>
>
>
>


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Carlos H. Reimer wrote:

>I´ve taken a look in the /var/log/messages and found some temperature
>messages about the disk drives:
>
>Nov 30 11:08:07 totall smartd[1620]: Device: /dev/sda, Temperature changed 2
>Celsius to 51 Celsius since last report
>
>Can this temperature influence in the performance?
>
>
it can influence 'working-ness' which I guess in turn affects performance

But I'm not sure if 50C is too high for a disk drive, it might be ok.

If you are able to, I'd say just replace the drives and see if that
improves things.



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hi,

If you look the iostat data it shows that the system is doing much more
writes than reads. It is strange, because if you look in the pg_stat tables
we see a complete different scenario. Much more reads than writes. I was
monitoring the presence of temporary files in the data directory what could
denote big sorts, but nothing there too.

But I think it is explained because of the high number of indexes present in
those tables. One write in the base table, many others in the indexes.

Well, about the server behaviour, it has not changed suddenly but the
performance is becoming worse day by day.


Reimer


> -----Mensagem original-----
> De: Mark Kirkwood [mailto:markir@paradise.net.nz]
> Enviada em: quinta-feira, 30 de novembro de 2006 23:47
> Para: carlos.reimer@opendb.com.br
> Cc: pgsql-performance@postgresql.org
> Assunto: Re: [PERFORM] Bad iostat numbers
>
>
> Carlos H. Reimer wrote:
> > While collecting performance data I discovered very bad numbers in the
> > I/O subsystem and I would like to know if I´m thinking correctly.
> >
> > Here is a typical iostat -x:
> >
> >
> > avg-cpu: %user %nice %system %iowait %idle
> >
> > 50.40 0.00 0.50 1.10 48.00
> >
> >
> >
> > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
> > avgrq-sz avgqu-sz await svctm %util
> >
> > sda 0.00 7.80 0.40 6.40 41.60 113.60 20.80
> > 56.80 22.82 570697.50 10.59 147.06 100.00
> >
> > sdb 0.20 7.80 0.60 6.40 40.00 113.60 20.00
> > 56.80 21.94 570697.50 9.83 142.86 100.00
> >
> > md1 0.00 0.00 1.20 13.40 81.60 107.20 40.80
> > 53.60 12.93 0.00 0.00 0.00 0.00
> >
> > md0 0.00 0.00 0.00 0.00 0.00 0.00 0.00
> > 0.00 0.00 0.00 0.00 0.00 0.00
> >
> >
> >
> > Are they not saturated?
> >
>
> They look it (if I'm reading your typical numbers correctly) - %util 100
> and svctime in the region of 100 ms!
>
> On the face of it, looks like you need something better than a RAID1
> setup - probably RAID10 (RAID5 is probably no good as you are writing
> more than you are reading it seems). However read on...
>
> If this is a sudden change in system behavior, then it is probably worth
> trying to figure out what is causing it (i.e which queries) - for
> instance it might be that you have some new queries that are doing disk
> based sorts (this would mean you really need more memory rather than
> better disk...)
>
> Cheers
>
> Mark
>
>
>
>
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Thu, 30 Nov 2006, Carlos H. Reimer wrote:

> I would like to discover how much cache is present in
> the controller, how can I find this value from Linux?

As far as I know there is no cache on an Adaptec 39320. The write-back
cache Linux was reporting on was the one in the drives, which is 8MB; see
http://www.seagate.com/cda/products/...93,541,00.html
Be warned that running your database with the combination of an uncached
controller plus disks with write caching is dangerous to your database
integrity.

There is a common problem with the Linux driver for this card (aic7902)
where it enters what's they're calling an "Infinite Interrupt Loop".
That seems to match your readings:

> Here is a typical iostat -x:
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
> sda 0.00 7.80 0.40 6.40 41.60 113.60 20.80 56.80
> avgrq-sz avgqu-sz await svctm %util
> 22.82 570697.50 10.59 147.06 100.00

An avgqu-sz of 570697.50 is extremely large. That explains why the
utilization is 100%, because there's a massive number of I/O operations
queued up that aren't getting flushed out. The read and write data says
these drives are barely doing anything, as 20kB/s and 57KB/s are
practically idle; they're not even remotely close to saturated.

See http://lkml.org/lkml/2005/10/1/47 for a suggested workaround that may
reduce the magnitude of this issue; lower the card's speed to U160 in the
BIOS was also listed as a useful workaround. You might get better results
by upgrading to a newer Linux kernel, and just rebooting to clear out the
garbage might help if you haven't tried that yet.

On the pessimistic side, other people reporting issues with this
controller are:

http://lkml.org/lkml/2005/12/17/55
http://www.ussg.iu.edu/hypermail/lin...12.2/0390.html
http://www.linuxforums.org/forum/per...angs-boot.html
and even under FreeBSD at
http://lists.freebsd.org/pipermail/a...st/003973.html

This Adaptec card just barely works under Linux, which happens regularly
with their controllers, and my guess is that you've run into one of the
ways it goes crazy sometimes. I just chuckled when checking
http://linux.adaptec.com/ again and noticing they can't even be bothered
to keep that server up at all. According to
http://www.adaptec.com/en-US/downloa...I+Card+39320-R
the driver for your card is "*minimally tested* for Linux Kernel v2.6 on
all platforms." Adaptec doesn't care about Linux support on their
products; if you want a SCSI controller that actually works under Linux,
get an LSI MegaRAID.

If this were really a Postgres problem, I wouldn't expect %iowait=1.10.
Were the database engine waiting to read/write data, that number would be
dramatically higher. Whatever is generating all these I/O requests, it's
not waiting for them to complete like the database would be. Besides the
driver problems that I'm very suspicious of, I'd suspect a runaway process
writing garbage to the disks might also cause this behavior.

> Ive taken a look in the /var/log/messages and found some temperature
> messages about the disk drives:
> Nov 30 11:08:07 totall smartd[1620]: Device: /dev/sda, Temperature changed 2
> Celsius to 51 Celsius since last report
> Can this temperature influence in the performance?

That's close to the upper tolerance for this drive (55 degrees), which
means the drive is being cooked and will likely wear out quickly. But
that won't slow it down, and you'd get much scarier messages out of smartd
if the drives had a real problem. You should improve cooling in this case
if you want to drives to have a healthy life, odds are low this is
relevant to your performance issue though.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

People recommend LSI MegaRAID controllers on here regularly, but I have
found that they do not work that well. I have bonnie++ numbers that show
the controller is not performing anywhere near the disk's saturation level
in a simple RAID 1 on RedHat Linux EL4 on two seperate machines provided by
two different hosting companies. In one case I asked them to replace the
card, and the numbers got a bit better, but still not optimal.

LSI MegaRAID has proved to be a bit of a disapointment. I have seen better
numbers from the HP SmartArray 6i, and from 3ware cards with 7200RPM SATA
drives.

for the output: http://www.infoconinc.com/test/bonnie++.html (the first line
is a six drive RAID 10 on a 3ware 9500S, the next three are all RAID 1s on
LSI MegaRAID controllers, verified by lspci).

Alex.

On 12/4/06, Greg Smith <gsmith@gregsmith.com> wrote:
>
> On Thu, 30 Nov 2006, Carlos H. Reimer wrote:
>
> > I would like to discover how much cache is present in
> > the controller, how can I find this value from Linux?
>
> As far as I know there is no cache on an Adaptec 39320. The write-back
> cache Linux was reporting on was the one in the drives, which is 8MB; see
>
> http://www.seagate.com/cda/products/...93,541,00.html
> Be warned that running your database with the combination of an uncached
> controller plus disks with write caching is dangerous to your database
> integrity.
>
> There is a common problem with the Linux driver for this card (aic7902)
> where it enters what's they're calling an "Infinite Interrupt Loop".
> That seems to match your readings:
>
> > Here is a typical iostat -x:
> > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
> > sda 0.00 7.80 0.40 6.40 41.60 113.60 20.80 56.80
> > avgrq-sz avgqu-sz await svctm %util
> > 22.82 570697.50 10.59 147.06 100.00
>
> An avgqu-sz of 570697.50 is extremely large. That explains why the
> utilization is 100%, because there's a massive number of I/O operations
> queued up that aren't getting flushed out. The read and write data says
> these drives are barely doing anything, as 20kB/s and 57KB/s are
> practically idle; they're not even remotely close to saturated.
>
> See http://lkml.org/lkml/2005/10/1/47 for a suggested workaround that may
> reduce the magnitude of this issue; lower the card's speed to U160 in the
> BIOS was also listed as a useful workaround. You might get better results
> by upgrading to a newer Linux kernel, and just rebooting to clear out the
> garbage might help if you haven't tried that yet.
>
> On the pessimistic side, other people reporting issues with this
> controller are:
>
> http://lkml.org/lkml/2005/12/17/55
> http://www.ussg.iu.edu/hypermail/lin...12.2/0390.html
>
> http://www.linuxforums.org/forum/per...angs-boot.html
> and even under FreeBSD at
> http://lists.freebsd.org/pipermail/a...st/003973.html
>
> This Adaptec card just barely works under Linux, which happens regularly
> with their controllers, and my guess is that you've run into one of the
> ways it goes crazy sometimes. I just chuckled when checking
> http://linux.adaptec.com/ again and noticing they can't even be bothered
> to keep that server up at all. According to
>
> http://www.adaptec.com/en-US/downloa...I+Card+39320-R
> the driver for your card is "*minimally tested* for Linux Kernel v2.6 on
> all platforms." Adaptec doesn't care about Linux support on their
> products; if you want a SCSI controller that actually works under Linux,
> get an LSI MegaRAID.
>
> If this were really a Postgres problem, I wouldn't expect %iowait=1.10.
> Were the database engine waiting to read/write data, that number would be
> dramatically higher. Whatever is generating all these I/O requests, it's
> not waiting for them to complete like the database would be. Besides the
> driver problems that I'm very suspicious of, I'd suspect a runaway process
> writing garbage to the disks might also cause this behavior.
>
> > Ive taken a look in the /var/log/messages and found some temperature
> > messages about the disk drives:
> > Nov 30 11:08:07 totall smartd[1620]: Device: /dev/sda, Temperature
> changed 2
> > Celsius to 51 Celsius since last report
> > Can this temperature influence in the performance?
>
> That's close to the upper tolerance for this drive (55 degrees), which
> means the drive is being cooked and will likely wear out quickly. But
> that won't slow it down, and you'd get much scarier messages out of smartd
> if the drives had a real problem. You should improve cooling in this case
> if you want to drives to have a healthy life, odds are low this is
> relevant to your performance issue though.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>

On Mon, 2006-12-04 at 01:17, Alex Turner wrote:
> People recommend LSI MegaRAID controllers on here regularly, but I
> have found that they do not work that well. I have bonnie++ numbers
> that show the controller is not performing anywhere near the disk's
> saturation level in a simple RAID 1 on RedHat Linux EL4 on two
> seperate machines provided by two different hosting companies. In one
> case I asked them to replace the card, and the numbers got a bit
> better, but still not optimal.
>
> LSI MegaRAID has proved to be a bit of a disapointment. I have seen
> better numbers from the HP SmartArray 6i, and from 3ware cards with
> 7200RPM SATA drives.
>
> for the output: http://www.infoconinc.com/test/bonnie++.html (the
> first line is a six drive RAID 10 on a 3ware 9500S, the next three are
> all RAID 1s on LSI MegaRAID controllers, verified by lspci).

Wait, you're comparing a MegaRAID running a RAID 1 against another
controller running a 6 disk RAID10? That's hardly fair.

My experience with the LSI was that with the 1.18 series drivers, they
were slow but stable.

With the version 2.x drivers, I found that the performance was very good
with RAID-5 and fair with RAID-1 and that layered RAID was not any
better than unlayered (i.e. layering RAID0 over RAID1 resulted in basic
RAID-1 performance).

OTOH, with the choice at my last place of employment being LSI or
Adaptec, LSI was a much better choice.

I'd ask which LSI megaraid you've tested, and what driver was used.
Does RHEL4 have the megaraid 2 driver?

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings