[gentoo-performance] Implementing Optimization for Database Server - Nice story btw :+P

[Javier Villavicencio]

First trying to revive the "benchmark these CFLAGS in a real-app with nice -n 
-20" idea i had writing on this thread [1], and second showing some success on 
my work and looking "not to do the same thing that someone else maybe already 
did...", I'm posting this "experience" and a little question.

I where working with linux in various places over the time but all of those 
"places" had policies and stuff about what kind of linux distribution they'll 
use (or not use at all) so the only thing I could do with gentoo was "I use it 
only on my machine". But now, I work in a complete mess of an extremely bad 
directed "gouvernamental institution", one of those that makes use of 
-INTENSIVE- processing power, but, looking for the minimal-lowest-nonexistent 
"cost".

Sounds funny, right? :+)

Well here is when gentoo makes my life easier:
The "guy before me" was a fan of OpenBSD which I respect and still use as our 
"internet" firewalls, but it really lacks of the support that everyone else 
needed at work. A little example of this: They where working with databases in 
"a couple" of RDBMSs: Gupta/Centura SQLBase, Oracle, Interbase, among others. 
So... "how in the hell i can make a apache+mysql+php-THING work with these 
databases on OBSD". just "no way" so before starting the real "web services" 
they asked me to prove myself "migrating" some "non-cost-effective" M$ services, 
as I did:

The hardware they have are some Fujistu-Siemens Primergy P470 servers, with 
duals PentiumII or PentiumIII with no more than 500Mhz the P3 and 450Mhz the P2, 
if I wanted 1Gb of ram they had this but wasn't necessary, and the only good 
thing i liked about these where the hot-pluggable backplanes with Mylex DAC960 
and DAC1100 SCSI-2 Raid controllers.

So I took my livecd and migrated 2 M$ systems and an old redhat: First was the 
"internet S and A" server (ISA :+P) to squid->winbind/samba->w2kdomaingroups. 
Then was the mailserver, which was a qmail installed from some sort of strange 
script that downloads and compiles and installs and configured all by himself 
without asking a sh*t, which had more than 3000 viruses incoming per week, now 
it's a postfix+mysql+amavis+clamav+dspam solution made by myself (of course 
following TONS of howtos :+P). And the last one was the webserver itself, which 
was an "it need to be restarted every 4 hours" win2k with apache and php,and now 
it can access ANY database that have an ODBC driver even in WINDOWS (using a 
DBTCP proxy in a win2k machine google it, it's cool, i also patched the php 
module to compile and added a pear module to access it from Pear DB.php ;+) from 
php using apache and accessing oracle 8.1.7 and oracle 9.2.0.5.

So far, I didn't needed "great" optimization. CFLAGS like "-O2 -march=pentium3/2 
-frename-registers -fomit-frame-pointers -pipe" did the magic very well.

But now "the time has come" X'D

I, now, have to make -THE- database server.

I already started it, but with -extra-safe-cflags-and-package-versions-, because 
it should run Oracle 9.2.0.5 and you can find a lot of "troubles" rather than 
"solutions" in non-certified (and in certified too btw) distributions where to 
run oracle.

Now after 3 weeks of recovering dead disks, going for the S40 storage, and 
compiling/downloading/installing everything to the tiniest detail....
it's working...

This is what I've got:

The computer have two PentiumIII 500Mhz with 1Gb of ECC RAM, and a total of 14 
SCSI-2 hard disks (yes 14), 2 of them (9Gb each) in a raid1 (mirror) 
configuration for the booting system (gentoo ;+) with reiserfs on a DAC960PRL 
controller.
The other 12 are configured in this way: since the DAC1100 that connects the 
server to the storage cannot handle in a single array pack more than 8 disks 
this storage has 2 RAID5 configurations one made of 8 9Gb disks and the other 
made of 4 18Gb disks, which I combined in three different "partitions" using 
software raid0 (stripping) with a total of (around) 110Gb, divided into 
60Gb/30Gb/20Gb (oracle users my guess this u01/u02/u03 :+) that holds the big 
swap partition (2Gb outside the softraid) and, of course, will hold the 
database. Since this is going to handle "big" files, it has XFS tuned 
accordingly with Daniel Robbins suggestions ;+).

Now, regarding the original subject, I'm thinking about doing an emerge -e world 
with new cflags, check if it breaks anything, if it works faster, if I can 
extract all the juice this machines have, so I'm about to start "benchmarking"

But, taking in mind the -fact- that this wont recompile the oracle stuff (and 
oracle doesn't compile anything on your machine, it just "links" stuff) I was 
thinking about "forcing" some cflags to the glibc itself and other libs that may 
oracle use that can be optimized "the gentoo way".

After this, I'm asking before I start this "adventure" X'D, if anyone, anybody 
did something similar before, results, advices, CFLAGS!!!, even ~x86 stuff like 
gcc3.4 if it doesn't break oracle ;+), kernel schedulers (staircase?, cfq?, and 
io: anticipatory?, deadline?), kernel patches, anything (ideas, insults, flames 
X'D) can be of help and will be appreciated.

(I may add that this is a sort of "competition" between me and the DBA, that 
says: "the server must be a redhat 8, since oracle has certified it" and I say: 
"the server must be tested first with gentoo since it will run faster and we 
need speed on that old machines we have, and if it works solid, make the 
switch"... So this will be (in the end) a benchmark with a title like this 
"Indestructible Redhat-Oracle" vs "Fast-Bleeding-Edge Gentoo-Oracle".)

So, I hope the enthusiasm from all the gentoo users can help me win this little 
battle ;+)

Thanks a lot for reading all of this, I know is very large, but I thought it 
would be nice to make it known that gentoo is growing in scales like this :+)

Salu2,
Javier.

[1] http://forums.gentoo.org/viewtopic.php?p=1805512

--
gentoo-performance@g.o mailing list