Re: Which SQL is the best for servers?

On Feb 16, 6:09 am, pg <pen...@catholic.org> wrote:

I am involved with a SQL server project. The server would be used in a
very heavy duty environment, with hundreds of thousands, if not
millions of database enquiries per minutes.

Perhaps you should clarify here - what exactly do you mean by
millions of "database enquiries per minutes"?

Will these be reads? Or will there be lots of updating going on?

What is the nature of these database "enquiries"?

The server would run Linux or one of the BSD variant, with at least
32GB of RAM. We are not very certain of the hardware specs yet because
we haven't decided on which SQL to use.

Fine - Oracle won't be supported on *BSD.

I know that Oracle, MySQL and PostgreSQL are all designed for heavy
duty uses.

Yes - but what sort of heavy duty use? MySQL will be better IMHO for
reading - less transactional overhead - which is both good and bad!

And I checked all available online resources for a SQL comparison and
all I could find is some articles dated 2005 or so !
So, here's my questions:

1. Are there any recent SQL comparison article available?

All of the commercial vendors specifically forbid benchmarks - except
the
ones they choose to publish themselves!

2. Since the server may come with only 32GB of RAM, which SQL can run
the "leanest" - that is, not a memory hog?

32GB of RAM is a large system - but RAM isn't everything! What is the
OS going to be? That's your first decision.

What about the disk configuration? What's the CPU configuration?

3. The server might also become a web-server, which SQL can tie itself
to the Web-based enquiry they best?

Generally a web server feeds user input to an app server which then
fires SQL at a database.

These tiers should all run on separate machines - or at least separate
virtual machines.

Please give me your suggestion / opinion. Thank you !!

You could look at Firebird - it's solid and simple to administer. The
answer to this
question depends on your requirements, which you haven't explained
enough here.

Paul...

On Feb 15, 10:09 pm, pg <pen...@catholic.org> wrote:

I am involved with a SQL server project. The server would be used in a
very heavy duty environment, with hundreds of thousands, if not
millions of database enquiries per minutes.

The server would run Linux or one of the BSD variant, with at least
32GB of RAM. We are not very certain of the hardware specs yet because
we haven't decided on which SQL to use.

I know that Oracle, MySQL and PostgreSQL are all designed for heavy
duty uses.

And I checked all available online resources for a SQL comparison and
all I could find is some articles dated 2005 or so !

So, here's my questions:

1. Are there any recent SQL comparison article available?

2. Since the server may come with only 32GB of RAM, which SQL can run
the "leanest" - that is, not a memory hog?

3. The server might also become a web-server, which SQL can tie itself
to the Web-based enquiry they best?

Please give me your suggestion / opinion. Thank you !!

I agree with those who say you are going about this backwards. No-way
no-how will a single open source os box handle millions of
"enquiries" per minute.

Sounds to me like someone has an idea for some web page they think is
going to attract a gazillion users. This is what I predict: You will
spend $5 million over several years financed via a shoot-the-moon
business plan designed to attract venture capital. At the end, it
will all fizzle out after numerous rounds of layoffs.

It wouldn't be the first time. Good luck in this economic
environment. That's as good a prediction as any given this amount of
information. Unless the domain you are posting from indicates some
religious thing. Some religious databases use Oracle.

As to the database engine: There is a reason I'm strongly biased
towards Oracle. That reason being, unless there is something special
about the processing involved, either exceedingly simple, complex or
specialized, it is near impossible to build a bespoke system cheaper
than buy and modify off the shelf software. For business systems,
ACID is a very important consideration, and Oracle simply handles the
concurrency issues better for most business processes. Google, for a
big example, doesn't care about concurrency issues, because their
business model is simply to approximate eyeballing of ads - and they
don't have to account for how they measure it. This happens to be
evil.

In general, it takes about an order of magnitude more people to write
and maintain a custom system than implement an off the shelf system.
Given the cost of a large implementation this can make sense for open
source - or not. The maintenance costs are often underestimated
either way, but moreso in customization. When a startup starts to
transition to a business, variable costs are cut. That would be
you.

Two guys and a box? I've seen that work, where a huge classical
development failed - more than once. It depends. Most startups fail.

Check out Oracle XE and apex. No cost to you, and you can pay to
scale as appropriate.

jg
--
@home.com is bogus.
http://www3.signonsandiego.com/stories/2009/feb/16/1b16twitter19224-whats-twitters-fiscal-fate/?uniontrib

joel garry wrote:

[...]

Check out Oracle XE

[...]

As far as I know, Oracle has never issued any patches for Oracle XE.
Given the stream of patches for the "regular" Oracle database, I fear
that an Oracle XE installation will have a number of known bugs -
possibly security bugs.

Or?

(Note: Follow-up set to comp.databases and comp.databases.oracle.server.)

--
Troels

Paulie wrote:

On Feb 16, 6:09 am, pg <pen...@catholic.org> wrote:

I am involved with a SQL server project. The server would be used in a
very heavy duty environment, with hundreds of thousands, if not
millions of database enquiries per minutes.

Perhaps you should clarify here - what exactly do you mean by
millions of "database enquiries per minutes"?

Will these be reads? Or will there be lots of updating going on?

What is the nature of these database "enquiries"?

The server would run Linux or one of the BSD variant, with at least
32GB of RAM. We are not very certain of the hardware specs yet because
we haven't decided on which SQL to use.

Fine - Oracle won't be supported on *BSD.

I know that Oracle, MySQL and PostgreSQL are all designed for heavy
duty uses.

Yes - but what sort of heavy duty use? MySQL will be better IMHO for
reading - less transactional overhead - which is both good and bad!

And I checked all available online resources for a SQL comparison and
all I could find is some articles dated 2005 or so !
So, here's my questions:

1. Are there any recent SQL comparison article available?

All of the commercial vendors specifically forbid benchmarks - except
the
ones they choose to publish themselves!

2. Since the server may come with only 32GB of RAM, which SQL can run
the "leanest" - that is, not a memory hog?

32GB of RAM is a large system - but RAM isn't everything! What is the
OS going to be? That's your first decision.

Huh... 32GB of RAM is NOT a large system - more of a smallish-medium
size. :)

<snip>

First: You really do need to define your database and transaction model
before figuring out what engine to use.

Your enemy is going to be scalability in the I/O bus unless everyone is
querying the same information constantly. Oracle's cache or MySQL Query
Cache could handle a lot of that - Most db engines have some sort of
caching feature you *may* want to use.

Should you choose an open-source, make sure your code AND your DDL uses
as much ANSI standards as possible so when you do need to move to
something else, it won't be as painful. (auto-incrementing columns vs.
sequences etc...).

Should you choose an open-source, make sure your code AND your DDL uses as
much ANSI standards as possible so when you do need to move to something
else, it won't be as painful. (auto-incrementing columns vs. sequences
etc...).

I really wouldn't go for database independence ... Choose a RDBMS and use
all of its features !

On 16.02.2009 15:59, Walt wrote:

pg wrote:

I am involved with a SQL server project. The server would be used in a
very heavy duty environment, with hundreds of thousands, if not
millions of database enquiries per minutes.

The server would run Linux or one of the BSD variant, with at least
32GB of RAM. We are not very certain of the hardware specs yet because
we haven't decided on which SQL to use.

I know that Oracle, MySQL and PostgreSQL are all designed for heavy
duty uses.

And I checked all available online resources for a SQL comparison and
all I could find is some articles dated 2005 or so !

So, here's my questions:

1. Are there any recent SQL comparison article available?

2. Since the server may come with only 32GB of RAM, which SQL can run
the "leanest" - that is, not a memory hog?

3. The server might also become a web-server, which SQL can tie itself
to the Web-based enquiry they best?

Please give me your suggestion / opinion. Thank you !!

A couple points:

1) You are looking for a Relational Database Management System, or
RDBMS. SQL is the *language* one uses to communicate with the RDBMS,
it's not the system itself. Try Googling on the correct terms and
you'll get better results.

Full ack!

2) You are going about it backwards by speccing the hardware and then
trying to find software to run on it. The correct approach is to define
your business needs, then find the software that solves your problems,
and only at that point do you decide on the hardware.

And more specifically: in these performance regions you need to have a
clear understanding of your data and your query load (what SELECTs, how
many modifying operations etc.).

3) Don't run the webserver and the database server on the same hardware
(although you might be able to get away with it through virtualization).
It's just very bad from a security standpoint.

One reasonable approach for this would be to choose a RDBMS which allows
scaling by adding more hardware. A clustered DB does not only help in
scaling but it will often give you added robustness.

Another approach is to partition your data and have several DB servers
which host only part of the data. Whether this is feasible depends of
course on your data and business needs. If you have highly
interconnected data then chances are that this is not an option.

Good luck!

robert

PS: Please do not cross post to so many groups. F'up to comp.databases

Jerry Stuckle says...

pg wrote:

The server would run Linux or one of the BSD variant

You also missed DB2, SQL Server and several others.

Scrap MSSQL Server as a candidate given the above O/S requirement.

Geoff M

Running 3 RHEL4 boxes in an Oracle RAC cluster, with web interfaces
(RHEL5/Apache/php) on separate VMWare ESX gear.

Matthias Hoys wrote:

Should you choose an open-source, make sure your code AND your DDL uses as
much ANSI standards as possible so when you do need to move to something
else, it won't be as painful. (auto-incrementing columns vs. sequences
etc...).

I really wouldn't go for database independence ... Choose a RDBMS and use
all of its features !

Nothing like a serious addiction combined with a single source to get
your fix to loose a lot of money...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Matthias Hoys wrote on 16.02.2009 22:56:

Should you choose an open-source, make sure your code AND your DDL uses as
much ANSI standards as possible so when you do need to move to something
else, it won't be as painful. (auto-incrementing columns vs. sequences
etc...).

I really wouldn't go for database independence ... Choose a RDBMS and use
all of its features !

Full ACK.

The only reason to go for DBMS independence is when you are creating a standard
product which should run on multiple platforms.

Geoff Muldoon wrote:

Jerry Stuckle says...

pg wrote:

The server would run Linux or one of the BSD variant

You also missed DB2, SQL Server and several others.

Scrap MSSQL Server as a candidate given the above O/S requirement.

Geoff M

Running 3 RHEL4 boxes in an Oracle RAC cluster, with web interfaces
(RHEL5/Apache/php) on separate VMWare ESX gear.

The OS should be picked based on the requirements of the database and
rest of the system, not vice versa.

I'm not saying I'm recommending SQL Server (or any other RDBMS for that
matter). But determining the hardware and OS before the rest of the
requirements are determined places artificial limits on the rest of the
system.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

In article <gncr90$61p$1@news.motzarella.org>, Jerry Stuckle says...

Geoff Muldoon wrote:

Jerry Stuckle says...

pg wrote:

The server would run Linux or one of the BSD variant

You also missed DB2, SQL Server and several others.

Scrap MSSQL Server as a candidate given the above O/S requirement.

Geoff M

Running 3 RHEL4 boxes in an Oracle RAC cluster, with web interfaces
(RHEL5/Apache/php) on separate VMWare ESX gear.

The OS should be picked based on the requirements of the database and
rest of the system, not vice versa.

In an ideal world, yes.

I'm not saying I'm recommending SQL Server (or any other RDBMS for that
matter). But determining the hardware and OS before the rest of the
requirements are determined places artificial limits on the rest of the
system.

My comments were simply based on the OPs restriction on O/S. There may be
valid non-ideal-world reasons for that restriction.

Geoff M

"Geoff Muldoon" <geoff.muldoon@trap.gmail.com> wrote in message
news:MPG.2404902b6db30900989715@news.x-privat.org...

Jerry Stuckle says...

pg wrote:

The server would run Linux or one of the BSD variant

You also missed DB2, SQL Server and several others.

Scrap MSSQL Server as a candidate given the above O/S requirement.

Geoff M

Running 3 RHEL4 boxes in an Oracle RAC cluster, with web interfaces
(RHEL5/Apache/php) on separate VMWare ESX gear.

Sybase ASE should be on your list too.

Terry Dykstra

hpuxrac wrote:

On Feb 16, 1:09 am, pg <pen...@catholic.org> wrote:

I am involved with a SQL server project. The server would be used in a
very heavy duty environment, with hundreds of thousands, if not
millions of database enquiries per minutes.

The server would run Linux or one of the BSD variant, with at least
32GB of RAM. We are not very certain of the hardware specs yet because
we haven't decided on which SQL to use.

I know that Oracle, MySQL and PostgreSQL are all designed for heavy
duty uses.

And I checked all available online resources for a SQL comparison and
all I could find is some articles dated 2005 or so !

So, here's my questions:

1. Are there any recent SQL comparison article available?

2. Since the server may come with only 32GB of RAM, which SQL can run
the "leanest" - that is, not a memory hog?

3. The server might also become a web-server, which SQL can tie itself
to the Web-based enquiry they best?

Please give me your suggestion / opinion. Thank you !!

Hundreds of thousands or millions of queries per minute on 1 32 gig
server?

Sounds pretty unlikely.

Oracle or DB2 are probably the only 2 viable choices but it doesn't
sound like a good plan from the get go.

For that possible transaction rate I'd suggest something
other than an x86-based server; an entry-level zSeries
running zVM and multiple instances of Linux-for-zSeries
comes to mind.

--
-- Marten Kemp
(Fix name and ISP to reply)

Serge Rielau wrote:

Matthias Hoys wrote:

Should you choose an open-source, make sure your code AND your DDL
uses as much ANSI standards as possible so when you do need to move
to something else, it won't be as painful. (auto-incrementing columns
vs. sequences etc...).

I really wouldn't go for database independence ... Choose a RDBMS and
use all of its features !

Nothing like a serious addiction combined with a single source to get
your fix to loose a lot of money...

Matthias - you should take note of that statement from Serge... and
myself. While bigoted in our db of choice, we do agree that
single-sourcing your options is a GREAT way to send your favorite
salesman to Tahiti while you play with the box the toys came in...

I work for one of the top 50 Oracle support customers (we also have DB2,
MySQL and SQL Server). I use MySQL on my home servers. Each has their
strengths and weaknesses, each has their place in the overall scheme.

Again, define your transactional data flow as best you can (and if you
need help there are lots of really good consultants out here that can
help if you need it), from there you would figure out Database engine,
OS platform, storage options and so on down the line. With the
transactional load you started out with - it is HIGHLY unlikely that it
will be Linux on a small server.

[since I am posting this from the c.d.o.s NG] RAC is highly scalable. I
have recently had to add a node to a DW that adds more data in a day
than most db's do in 2-3 years... And we did this on the fly. We have
other RAC environments that are "commodity" servers - this one is not.
RAC was chosen in this case for its scalability as well as availability.

Working for my current employer, I have a real good idea as to what
millions of transactions actually look like and what it takes to support
that kind of workload.

Geoff Muldoon wrote:

In article <gncr90$61p$1@news.motzarella.org>, Jerry Stuckle says...

Geoff Muldoon wrote:

Jerry Stuckle says...

pg wrote:

The server would run Linux or one of the BSD variant

You also missed DB2, SQL Server and several others.

Scrap MSSQL Server as a candidate given the above O/S requirement.

Geoff M

Running 3 RHEL4 boxes in an Oracle RAC cluster, with web interfaces
(RHEL5/Apache/php) on separate VMWare ESX gear.

The OS should be picked based on the requirements of the database and
rest of the system, not vice versa.

In an ideal world, yes.

I'm not saying I'm recommending SQL Server (or any other RDBMS for that
matter). But determining the hardware and OS before the rest of the
requirements are determined places artificial limits on the rest of the
system.

My comments were simply based on the OPs restriction on O/S. There may be
valid non-ideal-world reasons for that restriction.

Geoff M

More than just ideal world. The higher the requirements, the more
important it is. And he has some pretty high requirements.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Paulie <linehan.paul@gmail.com> wrote:

[snip]

No CPU data, no disk array data - they haven't even chosen
an OS and are not sure where to put their web server (and
no mention of an app server tier!).

It might be a real project, but it has a feel of irreality. I
can not tell if it is wishful thinking or just a homework project.

Maybe they should run with the mauve db?

Paulie, I am shocked, simply shocked at your poor advice.

OP stated: 'Since the server may come with only 32GB of RAM,
which SQL can run the "leanest" - that is, not a memory hog?'

Remember: mauve has the most RAM, so it would not be appropriate
here.
</silly>

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Jerry Stuckle says...

The server would run Linux or one of the BSD variant

You also missed DB2, SQL Server and several others.

Scrap MSSQL Server as a candidate given the above O/S requirement.

The OS should be picked based on the requirements of the database and
rest of the system, not vice versa.

In an ideal world, yes.

My comments were simply based on the OPs restriction on O/S. There may be
valid non-ideal-world reasons for that restriction.

More than just ideal world. The higher the requirements, the more
important it is. And he has some pretty high requirements.

No wanting to get into a philosophical argument, but ...

Sometimes restrictions are imposed. Hopefully these will NOT conflict
with the requirements, and in the OP's case there is still acceptable
remaining flexibility of choice.

My upcoming project will have the RDBMS mandated. I can live with that,
because the option imposed on me can more than comfortably handle the
requirements. Is this ideal? No. Are the motives for this imposed
restriction reasonable in this case? Yes.

Geoff M

Geoff Muldoon wrote:

Jerry Stuckle says...

The server would run Linux or one of the BSD variant

You also missed DB2, SQL Server and several others.

Scrap MSSQL Server as a candidate given the above O/S requirement.

The OS should be picked based on the requirements of the database and
rest of the system, not vice versa.

In an ideal world, yes.

My comments were simply based on the OPs restriction on O/S. There may be
valid non-ideal-world reasons for that restriction.

More than just ideal world. The higher the requirements, the more
important it is. And he has some pretty high requirements.

No wanting to get into a philosophical argument, but ...

Sometimes restrictions are imposed. Hopefully these will NOT conflict
with the requirements, and in the OP's case there is still acceptable
remaining flexibility of choice.

My upcoming project will have the RDBMS mandated. I can live with that,
because the option imposed on me can more than comfortably handle the
requirements. Is this ideal? No. Are the motives for this imposed
restriction reasonable in this case? Yes.

Geoff M

If artificial restrictions which affect the success of the project are
imposed, then it's time to get rid of those restrictions.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Feb 16, 11:12 am, Paulie <linehan.p...@gmail.com> wrote:

On Feb 16, 5:51 pm, joel garry <joel-ga...@home.com> wrote:

Check out Oracle XE and apex.  No cost to you, and you can pay to
scale as appropriate.

Before rushing to download Oracle XE, check out

http://www.oracle.com/technology/pub/articles/cunningham-database-xe....

Limitations.
1 GB of RAM (OP has 32),
1 CPU (with 32GB of RAM?) and a
4GD data limit.

For millions of queries per hour? For POC of an app, this is fine,
however for
performance testing, it's a non-runner.

I guess I wasn't clear enough on the "and you can pay to scale as
appropriate."

For testing/development purposes, you can download the various
editions of Oracle and see what they can do. The XE/Apex (or whatever
development environs) is just for getting something working quick.
When you see what the other editions can do, then you decide what you
need - plus you can decide on the low end, not a big deal to move up
if the situation warrants. The patching issue Troels mentioned may or
may not make a difference for a production environment exposed to the
world, but I'm not advocating XE for this in production, just for
developing.

Of course, one usual screwup is testing time/volume of rows returned,
where some toy db can outperform Oracle. Real performance testing
requires realistic load tests, and that can be a lot of work,
especially for a small group with one box.

You are allowed AFAIK, download the full server for testing (but not
deployment). The OP hasn't really given the group enough information
about the system for anyone here to be able to answer any
serious questions about an app that's (supposedly) going
to be almost as busy as Google!

I think we may all agree on this!

No CPU data, no disk array data - they haven't even chosen
an OS and are not sure where to put their web server (and
no mention of an app server tier!).

Since they seem uncertain of actual volume, all these things need to
be put in terms of a scalability plan.

Maybe they should run with the mauve db?

With scissors!

jg
--
@home.com is bogus.
http://www3.signonsandiego.com/stories/2009/feb/02/1m2ferry22928-robert-g-ferry-air-force-veteran-was/?uniontrib