Thread: IDE x SAS RAID 0 on HP DL 380 G5 P400i controller performance problem

Hi people,

I have a particular performance problem under a system installed.

In the lab I have an old Microside + Dual Core machine 1GB RAM with 40gb HD IDE and
a newer machine HP DL 380 G5 4GB RAM and 500GB SAS under RAID 0 (4 disks) P400i Smart Array Controller.

**Both with Linux Kernel 2 .4.37.9 and Postgresql 7.2**

Under a insertion test we get a performance of 2.5 secs under 2000 inserts (table with a single char(50) column) in the IDE disk.
And 500GB RAID 0 (4 disks!) and 37.5 secs under the same test!

I tried:
- All the postgresql.conf tuning possibles
- All the kernel tuning values possibles
- fstab mount options
- limits.conf values
- I/O tests with boonie++ that shows that P400 is superior than IDE - as expected
- 'massive' Google search combinations without answers
- Mandriva 2009.1: result is about 12 secs (but it need to be kernel 2.4 with PGSQL 7.2)


The real case:
~100k database insertions that runs in 2 min in the IDE, against ~40 minutes (P400i) in the HP.

The same test in the HP Blade G5 P200 controller works fine.

Thanks for any answers/analisys.

Best regards.

--
Daniel Ferreira

Daniel Ferreira de Lima <dfl.man@gmail.com> wrote:

> **Both with Linux Kernel 2 .4.37.9 and Postgresql 7.2**

Wow.  You really need to upgrade.

> Under a insertion test we get a performance of 2.5 secs under 2000
> inserts (table with a single char(50) column) in the IDE disk.
> And 500GB RAID 0 (4 disks!) and 37.5 secs under the same test!

Is each insert in its own database transaction?  (That is you've
done nothing to BEGIN a transaction, do the inserts, and then
COMMIT?)  If so, you're old IDE is lying to PostgreSQL when it says
it has committed each insert.  If you pull the power cord on the
computer a few times during this insert test, you'll find you wind
up missing rows which were supposedly inserted, and you will likely
have database corruption, which might render the database totally
unusable.

If you implement your RAID with a good controller which has a
battered backed-up RAM cache, and that is configured to write-back,
you'll see much better performance.

Search the archives for BBU and you'll find many other discussions
of this.

-Kevin

Thanks, Kevin.

--
Daniel Ferreira

Daniel Ferreira de Lima wrote:
>
>
>
>     > **Both with Linux Kernel 2 .4.37.9 and Postgresql 7.2**
>
>     Wow.  You really need to upgrade.
>
>
> Yes, but unfortunately, actually it's impossible and economically
> inviable...

Generally, getting good performance out of PostgreSQL 7.2 is also
impossible and economically inviable.  You can either put resources
toward upgrading to a newer version, which will fix many of the
performance issues here, or toward changes to the old version that are
not guaranteed to actually improve anything.

That said, a look into the write-caching+BBU policy on your controller
is worthwhile.  If you're running this application successfully on some
hardware but not others, that could be a source for the difference.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

I think it's really a BBU/BBWC problem.
The tests that we made in the lab with HP Blade G5 (G6 doesn't support kernel version 2.4) turning the battery off show us the 'same' performance of the "pizza-box" HP DL 380 G5..  an old joke.  40 secs to 2000 insertions: like a chariot.

We're finding the cache expansion and batteries (and.. why it's not default???).


I think that this thread is now finished.

Thanks!
--
Daniel Ferreira

If you are using ext3, your performance on the RAID card may also improve if the postgres xlog is not on the same partition as the data.  Otherwise, for every transaction commit, all of the data on the whole partition will have to be sync()'d not just the xlog.