IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New SAB-DB review
So I'm talking to my bossabout a week ago.

He told me he's under pressure from his boss to dump
Oracle and move EVERYTHING to SQL Server.

It was enough for the failed Postgres project that got rescued
by SQL Server to put this evil thought in the boss at
the next level.

So my boss, who LOVES M$ desktop tools and HATES M$
backend stuff wants to try MySQL again. He's been told
by the Open Source gnomes that the ODBC drivers are
MUCH better. I told him he was insane. Just because
the ODBC driver works does not mean the tool generated
SQL will work.

So, could we please try SAB-DB?

His reponse (I could have scripted it, actually I DID
script it!): What the hell is that?

Note: This guy was a C programmer, adminned Oracle/Solaris
systems, is very good in Perl, and reads the industry rags.
And HE said: What the hell is that?

So I explained that is was SAP's database product that they used
to have for the back-end installs. They open sourced it but
they still sell it, and I'm told they have great support for
it.

His response: So, it's another Interbase?

Me: I don't believe so. They still sell it as part of their
whole suite and they make good money supporting it.

Him: So it could quickly become another Interbase?

Me: Maybe. Dunno. Doubt it since they have lots of installations
as part of their full suite. If you really want to use an open
source base that WORKS, as opposed to a 1/2 ass one like MySQL,
I think we should investigate this.

Him: Ok. You'll be freed up in about 3 weeks. You can spend a
week on it then.

Rather than wait to be freed up (never happen) I started installing
immediately. 1st to my WinXP laptop, then to a dual PIII/1.4Ghz
Linux box.

Note: This is the BEST tutorial page I came across:
[link|http://www.rst-consult.com/database/sapdb/dbmcli_us_html.html|http://www.rst-consu...mcli_us_html.html]

I stumbled across a bunch of stuff. It took me DAYS to figure out
how to load data. And I DID ask our resident SAP-DB (guru|bigot),
who pointed me to web pages that did not help. All the docs refer
to loadercli, which is renamed to repmcli in Linux. Then it took
another day to to figure out how to tell it to use tab delimited.

Really.

They call it compressed, and you have to place the following line
in you loader control file to make it work:

SET COMPRESSED '/ //'

Note the embedded tab!

Dates are painful. I never did figure out how to load multiple date
formats at once. I could not even figure out how to load null
dates. I must be an idiot.

I downloaded all the PDFs. I spent about 6 hours reading them and
taking notes. I didn't expect it to make me a guru, but I did
expect it to give me enough information to know where to look.
I was wrong.

I WANT this to work. It has an Oracle SQL compatible mode. It
is native Unix. It claims really good ODBC. It's own tools
use the ODBC connection. It has a pretty Window Admin application
while still allowing for back-end scripting of everything. So
I'm willing to work through the stupid stuff.

I finally created a decent sized base (12 GB) and loaded 3 million
records and indexed them. The load was pretty quick. About the same
speed as SQL Server, about 1/2 as fast as Oracle. And yes, I used
the nologging fastload option.

I did a full optimize statistics (not sample). This should at this point
give me a best case scenario on "ok" hardware. Not the best, but better
than what I am running SQL/Server on.

A simple count(*) picked it up of the index. Fine, it doesn't have the
stupid PostGres implementation of forcing a table walk for that particular
query.

A simple 2 level group by the resulted in 1,263 rows took over 6 minutes.
Uhoh. The most common thing my interactive users do is create break-down
counts across multiple fields.

I do a sanity check. I log into the SQL/Server box and run the same query.
40 seconds. Note: This is a shared SQL/Server box with just slower CPUs
and the same amount of memory, and is 8 times faster.

Uhoh. Pray I'm bottlenecking on disk. This is because I see that the CPUs
are only 30% active during the query.

I shmooze a bit and commandeer a dual 2.4 GHz Xeon box. It has about 2 weeks
before it is exepected to be rolled out as an Oracle compute server. It has
5 fast SCSI disks and a high end RAID controller. I spent an extra $2K on
the RAID controller. It does NOT get any faster than this for a single
tasking test. I am the only user on this box.

Loading goes noticibly faster, pinning the CPU. OK, no disk bottleneck here.

Group by query run 3 minutes, 32 seconds. Since I expect to have this same
type of box for my final SQL/Server system, I expect that to drop to 15
seconds for SQL/Server. But even if it doesn't there is NO comparison.

Add up the time saved in productions, the happier interactive customer, and
the dark side has just won this one.

Unless someone can point out any tuning problem I might have?
New theres a reason they call it SAP
dunno what you would get calling them, say this is a submarine project and if they want it to be successful etc.
thanx,
bill
will work for cash and other incentives [link|http://home.tampabay.rr.com/boxley/resume/Resume.html|skill set]

questions, help? [link|mailto:pappas@catholic.org|email pappas at catholic.org]

To a lot of people in California hunting anything but the wild tofualope was equivelent to sacarificing babies to satan. S.M. Stirling
Expand Edited by boxley March 23, 2003, 11:50:06 PM EST
New Filesystem?

One suggestion from another list (I've shared your pain) is to test an alternate filesystem. I'd think this would have minimal impact for a database, as you're largely pumping data in and out of a small number of files, but it's possible. See [link|http://www.softorchestra.com/mailman/listinfo/linux-consultants|http://www.softorchestra.com/mailman/listinfo/linux-consultants] for details.

\r\n\r\n

Reiserfs does better than ext2fs in some circumstances. The SGI 64-way system featured in LJ a month or two back used SGI's journaling FS, don't remember if that's XFS or JFS. Performance over Reiser and ext3 was stunning. Might give that a shot.

\r\n\r\n

Also: RAID5 probably slows you. RAID 1-0 is a better performance bet, and offers redundancy.

--\r\n
Karsten M. Self [link|mailto:kmself@ix.netcom.com|kmself@ix.netcom.com]\r\n
[link|http://kmself.home.netcom.com/|http://kmself.home.netcom.com/]\r\n
What part of "gestalt" don't you understand?\r\n
[link|http://twiki.iwethey.org/twiki/bin/view/Main/|TWikIWETHEY] -- an experiment in collective intelligence. Stupidity. Whatever.\r\n
\r\n
   Keep software free.     Oppose the CBDTPA.     Kill S.2048 dead.\r\n[link|http://www.eff.org/alerts/20020322_eff_cbdtpa_alert.html|http://www.eff.org/alerts/20020322_eff_cbdtpa_alert.html]\r\n
New Nah, CPU
The RAID 5 and/or ext3 had no overhead in this test. It was almost pure CPU spin.

Also, XFS (SGI's) is a nono right now for us. Until we move off Red Hat (I believe we will withing 6 months) we can't use it.
New What do you mean by "another Interbase"? Just wondering...
New "another Interbase"
Interbase is seen (from his corporate perspective) as a database tossed off to Open Source when the company was not interested in supporting it any longer.

Even if it is now freely available, and has an active developer set, it doesn't mean squat to us when we find a particular problem when our 3rd party software that conflicts with it. The cost of SQL Server is nothing compared to the cost of NOTHING happening on a project while we wait for the showstopper to be resolved.

As evil as SQL Server is, even if it EATS the entire database and needs 3 days of downtime, it is KNOWN. We know the 3rd party software is written against it 1st, Oracle 2nd, and we know that no matter how many sleepless nights dealing with it happen, there is an end in sight.

We accept that we are not a database development shop, we don't have the skills, nor do we have the time, money or expertise to hire the really bright people and have them add what we need.

There was hope for SAP-DB based on the fact that it seems like it is being actively supported by the commericial entity that owned it before open sourcing, and also the fact it is very Oracle line in it's SQL syntax. But upon reviewing our dependance on the 3rd part app as writing this, even if it was as quick, it would have been a hell of a fight to release it to production. I'd probably run parallel for 6 months, which would cost me a hell of a lot mroe effort than the cost of a few more SQL Server licenses.
     SAB-DB review - (broomberg) - (5)
         theres a reason they call it SAP - (boxley)
         Filesystem? - (kmself) - (1)
             Nah, CPU - (broomberg)
         What do you mean by "another Interbase"? Just wondering... -NT - (CRConrad) - (1)
             "another Interbase" - (broomberg)

Unless I missed it, I don't think that they had Barbie in mind as one of the 4 horsemen.
82 ms