Обсуждение: IDE x SAS RAID 0 on HP DL 380 G5 P400i controller performance problem

От:
Daniel Ferreira de Lima
Дата:

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

От:
"Kevin Grittner"
Дата:

Daniel Ferreira de Lima <> 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

От:
Daniel Ferreira de Lima
Дата:


 
> **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...

 
> 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.

It's a single inserts.sql with 2000 inserts. 
But it's interesting test, I'll check the inserted data if it exist or not.
Maybe it's inserting only in RAM and not at disk and this nice performance is fake.
 
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.

I'm working on it.. a have been seen posts in the web of people saying things
about the BBU in P400i be disabled by default. Or problems with older
version of kernel module (cciss) turn it off by some kind of incompatibility.

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

-Kevin
 
Thanks, Kevin.

--
Daniel Ferreira
От:
Greg Smith
Дата:

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
   www.2ndQuadrant.us


От:
Daniel Ferreira de Lima
Дата:


 
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.

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
От:
Scott Carey
Дата:

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.

Also, what is the performance difference between all the inserts in one script if you do:

* all your statements in the script
vs.
* first line is "BEGIN;"  then all your statements, then "COMMIT;" at the end?   

If these two are about the same on your old IDE drive, then your I/O stack (file system + OS + hardware) is lying to you about fsync().  The latter should be a lot faster on your RAID card if write-back caching is not on.



On Jul 19, 2010, at 12:53 PM, Daniel Ferreira de Lima wrote:


 
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.

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