Which SQL is the best for servers?
pg
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 !!
pg
considering Ingres.
Is there any other SQL that you would recommend?
Thank you !!
PS. Although we prefer open-sourced programs, we definitely will pay
for it !!
Jack
"pg" <pen...@catholic.org> wrote in message
news:dd9edcff-7d7f-49ca...@l33g2000pri.googlegroups.com...
Well, use Oracle database. If needed use RAC/GRID, if you really have so
heavy dyty usage ;)
But you could try with single server. (and later take GRID and add more
nodes, if usage growns)
Do not put web-server into same box (server) as database.
HTH
Jack
The Natural Philosopher
> 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.
>
Muh as I love Mysql, its not likley that a basically freeware project
naintained out of love is ever going to compete with a real heavyweight
like oracle from a serious and large company whose life blood depends on
it not failing.
> 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 !!
Look a php/apache/mysql/linux solution is a great way to prototype the
application at low cost.
But if you are really going heavyweight, you may need to change any or
all of that in the future.
If you look at the context of a very large user base and process table,
then obviously multithreading will win over solely replicating code
pages everywhere. That's at lest most database engines will do, and Zeus
rather than apache used to be the web serer of choice for high usage as
well..
But there are many man more issue at stake. Mysql is good but even it is
a bit flimsy when it comes to atomic transactions and rollback. Ther are
also less safeguards in it, in that the way tables are related in the
relatinal database is not inherent in the engine.
That is, consider an applicationn like a sales order, where one table
reprsentst the order header and a series of rows in another table
represent the actual items that comprise the order. In MySQL there is as
far as I know no way to express that relationship other than coding in
the forms themselves. Whereas I believe that Oracle allows you to assign
that relationship so that attempting to e.g delete a header without also
deleting the lines associated with it, gives an error.
With respect, I think you are approaching this the wrong way around.
First you have to define the application in a reasonable amount of
detail. That should suggest the softare plastforms that will be best for
it, and then and only then, should you worry about what hardware it
should run on.
E.g. If you were t pick Oracle, you simply ask them 'what is the
platform you are most happy to support' and if it happens to be a
Cluster of SUN's linked by optical fibres, thats what you buy.
Millions of transactions a minute is stiff going irrespective of
anything else.
I would be worried about Disk I/O at least as being far more relevant
than what database package..
gym dot scuba dot kennedy at gmail
bind variables (prepared statements) and build in scalability from the
start, don't try to bolt it on afterwards. The question is more complex ;
how fast are the disks, how are they set up, what type of queries, what is
the IO load etc.
Mauve is the best db.
Jim
The Natural Philosopher
I did a bit with Ingres..and the guy we got in to work on it said 'its
not Oracle though'
Oracle is the 'you wont get fired for buying it' standard. The best.
And I did some work with them as well. By and large a pretty
professional outfit, and well used to major stuff ..
I would certainly be inviting a technical salesmen to your door from
them to explain why you should buy their products and inviting him or
her to comment on your design, and specify what hardware it would run on.
Look, if this is serious commercial code, forget open source. You want
to pay for someone to fix stuff fast if it falls over.
The fact that you have to ask all this, almost says to me that open
source wont be your friend here. Open source is for people who know what
they are doing already..or are prepared to invest a lot of time and
effort making it work.
I mean the reason that - say - Mysql wont tell you how many transactions
a minute it can handle, is because most likely if that is a serious
issue, you wont pick Mysql anyway!
The Natural Philosopher
There speaks a man who looks like he is posting from the oracle group.
From my far more limited knowledge, it makes sense to me.
> HTH
> Jack
>
>
Maxwell Lol
> Do not put web-server into same box (server) as database.
This will improve performance and security.
Putting a database on a web server is ill-advised.
That's like providing two front doors instead of one to attackers.
Jerry Stuckle
You also missed DB2, SQL Server and several others.
I don't know of any recent comparison articles, but I've found them to
be only worth bragging rights. Whether or not those benchmarks apply to
the real world is highly questionable.
The truth is - when you ask a question like this, you'll get plenty of
answers, most of them from people who are quite partial to their own
database.
There is no one database which is best for everything. What is best for
one person is not necessarily best for another. What you need to do is
your own evaluation of each product based on your needs.
Yes, it means a lot of work. But if you're going to be hitting it as
hard as you say, it's work you need to do yourself.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Jean-David Beyer
My requirements were much less than yours. Single-user on large workstation.
I first tried to use Microsoft Access, but it was too buggy and would not
let me design a simple database. I then tried postgreSQL (in about 1996, and
it did not work right -- then. So I switched to Informix, which was a pain
because it did not have an interface to C++, so I had zillions of little
interface routines written in C to be called from C++. But it worked with
Red Hat Linux 5.0. When Red Hat upgraded that to 5.2, Informix did not work
at all, and Informix was unwilling to help. At that point, I switched to
IBM's DB2 and it worked fine, though it was like delivering milk with a fire
truck. When I switched to Red Hat Enterprise Linux 3, I had trouble getting
DB2 to run. IIRC, the problem was that the interface to the raw file systems
changed, and I did not want to pay to upgrade DB2. By then I looked at MySQL
and postgreSWL again and I picked postgreSQL because MySQL did not handle
transactions in a way I felt confident of. As far as I can tell, postgreSQL
works fine the way I run it. It seems to be faster, even without raw file
systems, than I managed to get DB2 to run with them.
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 08:20:01 up 26 days, 11 min, 3 users, load average: 4.23, 4.34, 4.29
Serge Rielau
> PS. Although we prefer open-sourced programs, we definitely will pay
> for it !!
http://www.ibm.com/software/data/db2/express/
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
toby
> I forgot to add, other than Oracle, MySQL and PostgreSQL, we are also
> considering Ingres.
There is also Firebird.
As for the proprietary options, how much money do you want to waste?
Walt
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.
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.
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.
Paulie
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...
joel garry
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
Troels Arvin
[...]
> 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
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.html
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.
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!
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!).
Maybe they should run with the mauve db?
Paul...
> jg
blueparty
> 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.
>
Are you going to sell the final product as proprietary software ? MySQL
might be tricky in that case. MySQL connectivity components (at leas
JDBC) are GPL. That means your code must be GPL, too. If you want to
sell a proprietary application that works with MySQL, customer needs to
purchase MySQL server, as far as I understand. It might be difficult to
explain why they need to pay for it, while they could download it for free.
B
Michael Austin
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...).
Matthias Hoys
>
> 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 !
Robert Klemme
> 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
Geoff Muldoon
> 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.
Serge Rielau
your fix to loose a lot of money...
Thomas Kellerer
Full ACK.
The only reason to go for DBMS independence is when you are creating a standard
product which should run on multiple platforms.
Jerry Stuckle
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.
hpuxrac
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.
Geoff Muldoon
> 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
Terry Dykstra
"Geoff Muldoon" <geoff....@trap.gmail.com> wrote in message
news:MPG.2404902b6...@news.x-privat.org...
Sybase ASE should be on your list too.
Terry Dykstra
Marten Kemp
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)
Michael Austin
> 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.
Michael Austin
> 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...
>
>
This is addressed to the OP: - you should take note of that statement
Jerry Stuckle
More than just ideal world. The higher the requirements, the more
important it is. And he has some pretty high requirements.
Gene Wirchenko
[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.
Geoff Muldoon
> >>>>> 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
Jerry Stuckle
If artificial restrictions which affect the success of the project are
imposed, then it's time to get rid of those restrictions.
joel garry
> 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
>
>
> 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.