Thread: 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...
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 !
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.
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.