Oracle VS MySql for pure indexed query.

I've got a project that will cost about $80,000
in Oracle licensing, and we would like to avoid that.

Very simple.

2 tables.

Table 1) 50 million records.
Name, address, city, state, zip, bit of demographics.

Table 2) 130 million records.
Contact information. dates. etc.

Monthly reload of the data.

Table 1 will be queries on the following keys:
Almost Unique Id
Zip Code
Name
Address
State


Table 2 will be queried on "Almost Unique Id", after the
person has figured it out via the Table 1 query.

Queries on Table 1 are expect to return less than 100 records,
and typically 1 or 2.


Response time is CRITICAL. 2 second avg, 4 second max.
There may be 200 queries going on at once, and I'd like
to design to scale much higher if possible. The general
user population is about 1000 people, but the activity is
regional bursts.

Back end disk will be fibrechannel attached array. Raw
data size is about 200GB, which will go to about 120GB in
a variable len record database.

There must be 2 systems for safety, so total disk will be
about 1 TB.

Now the question:
Do I need a Sun/Oracle front end, or can I use
Intel Linux/MySQL or Sun/MySQL?

Has anyone had experience comparing the 2 for this type
of application?

> Interbase is free, and very good.

Interbase is "seen" as going away. Whether true
or not, I can't split my attention any further.
I've been directed to research MySQL, and if that can't
handle it, PostGreSQL. I always felt PostGreSQL was more
technically advanced than MySQL, while MySQL had the initial
speed advantage. PostGreSQL might have caught up. MySQL
is definately simpler.

> I don't know that MySQL or even Postgres are capable of
> scaling that far. Postgres has been tested as being faster
> then MySQL in many instances, and it's an all around better
> database than MySQL.

I've loaded my initial 50MM record table into MySQL already,
and am currently indexing the primary key. I'll let you
know.

I needed the following in my table create to allow enough
data:

TYPE=MyISAM
RAID_TYPE=STRIPED
RAID_CHUNKS=30
RAID_CHUNKSIZE=10000
MIN_ROWS=60000000
MAX_ROWS=500000000


It took about 4 hours of various trial and error failures.

On non-64 bit systems, the max file size is 4GB, even on
Linux 2.4.x, which means you need to enable RAID in MySQL,
which is not the default. An when enabled, you then need to give
it an estimate of how many rows you will be using, to allow
it to correctly allocated the internal table structures.

The binary MySQLs don't have RAID enabled, and to compile
it you need gcc 2.95, which was not on my system at the time.

> You might investigate DB/2 as well. It will certainly scale
> that high, but I don't know what the cost is.

Big bucks. Might as well use Oracle.

> The Linux IO will be much faster than Sun. We have seen
> this tested. It's actually quite amazing how much faster it is.

Examples? I've seen the reverse, but then again, I can aggregate
more/simply on my Sun 450s with Veritas. I haven't played with
LVM to duplicate the layouts.

> It can't. It's an open source project now. It will always be as
> available as MySQL or Postgres.

It takes a certain amount of critical mass to ensure the long
term viability of any open source project. I certainly won't
be bug fixing Interbase, and unless there is a critical reason
to go with it as opposed to any other, I'd choose the other.

> As to postgres being more complex, I will most certainly disagree there.
> It's amazingly simple to set up and easy to use, especially
> if you are used to Oracle.

Just a simple issue of preference, I guess. I've done both.
Since I lived in Oracle for many years. I can deal with it's
complexity without blinking. I can even explain it to others.
On the other hand, stupid simple stuff escaped me with
PostGreSQL, and I even have the Elephant book. I might be
prejudiced against it, though, after speaking to the marketing
slime at Great Bridge. I'm happy to see that tank, simply
because they were bullshitting on the benchmarks. I met them
at 'something'-XPO in NY last year. When I called them on
it, they agreed that the marketing guys pushed a bit.


> The Sun/Linux testing was done by another company we're dealing
> with, in single machines.
> The basis was servlet and JMS load testing. The IO coming out of
> the linux box was much higher.

Highly unlikely. I can consistently pump 180MB per second via 2
FC channel cards, and actually process the data as opposed to
spinning on the IO. On the other hand, until very recently, Intel/AMD
CPUs were MUCH faster than SPARC. So if you had a CPU intensive process
(which includes Java interpretation), you'd bottleneck on the CPU
before the IO, but if you were measuring the IO, it would "look"
slower. Did you do simultaneous CPU measurements during these tests?

And are you sure the bottleneck wasn't in what was feeding you
the data?

Also, since you were 'servlet' testing, were you network bound?
When going Sun->Sun, I can move about 75MB (mega BYTE) per
second via GB ethernet. When a Linux box gets in the mix
(Dual PIII-866 I think) the performance drops to about 30MB
per sec, and I see the Linux CPU spinning on the packets.

I'm comparing 3 CPU Sun 450s with Dual CPU Linux boxen here.
In most cases, the Linux box is faster on CPU but slower on IO,
so I end up buying a central 450 for the IO, and a stack of
Linux boxes as compute servers.

I usually always have a few performance windows up when I am
working on a box. 'top', 'iostat', 'ddu_watch (shows disk utilization,
simple df script). I can 'feel' when my disks start to get overloaded,
and I have the same processed running on Intel/Linux and Sparc/Solaris,
which means I usually spot bottlenecks very fast.

On almost unique id.
As opposed to 13 minutes on Sun/Oracle.

Now I'm trying to create 7 indexes simultaneously.

I suspect I will find there is 1.25 hours overhead,
and then each index will be 15 minutes. If that
is true, and the query times are OK, we have
a winner.

Actually, if EACH one takes 1.5 hours, and it runs
overnight once a month, and it saves $80,000, we
still have a winner.

I've got 1/2 the indexes on the PgSQL done. I'll try
to let the MySQL complete, and then compare query speeds.

If the MySQL takes 24 hours on this box, I can double the
CPU to it, if the query speeds make it worthwhile.

1) Split the data into 100 tables, access via 1st two
characters of zip.

2) Tell MySQL to create indexes during table create.
When the 1st load happens, it will do a quick build of
indexes. This is a one shot deal. Faster to reload and
rebuild, than to create index afterwards.

3) Custom code Perl API to determine correct table and
lookup there.

Actual load and index took about 50 minutes.
Perl mod took 10 minutes.

Queries run between 5 and 30 queries a second,
with occassional hesitations. Dual XEON with 2GB
of memory for cache should cure that. I'll find out
on Friday. It's a winner.