Skip to main content
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link

I was thinking that I only considered MySQL related solutions. Take a look herehere (feature comparisons) about what's available in freebie servers from the big vendors. There's also PostgreSQL which is, in the eyes of many (and especially me, a more capable DB server than MySQL.

I was thinking that I only considered MySQL related solutions. Take a look here (feature comparisons) about what's available in freebie servers from the big vendors. There's also PostgreSQL which is, in the eyes of many (and especially me, a more capable DB server than MySQL.

I was thinking that I only considered MySQL related solutions. Take a look here (feature comparisons) about what's available in freebie servers from the big vendors. There's also PostgreSQL which is, in the eyes of many (and especially me, a more capable DB server than MySQL.

Clarified - brought up to date.
Source Link
Vérace
  • 30.6k
  • 9
  • 71
  • 84

If it's a data warehouse system you're looking at, you may wish to consider InfiniDB. It's an Open Source columnar storage engine designed for DW type loads - it falls into the NewSQL paradigm as defined by Michael Stonebraker. There's also InfoBright, which is a similar offering. Note about InfiniDB - it's now under the aegis of MariaDB.org/com - not sure where this project will go in the future.

I was thinking that I only considered MySQL related solutions. Take a look here (feature comparisons) about what's available in freebie servers from the big vendors. There's also PostgreSQL which is, in the eyes of many (and especially me, a more capable DB server than MySQL.

[EDIT - in response to comments]

NewSQL - check out the link I posted above - note, it has been changed, my original one now appears to be dead, even though it worked yesterday.

There is not so much of a learning curve here - one of the key ideas behind NewSQL is that it explicitly retains much of OldSQL such as the SQL language and ACID transactions. The fundamental point is that NewSQL splits data processing into two types of task and takes a different approach for each. OLTP

OLTP (bankingOnline Transaction Processing - banking, shopping &c.) is done on a shared nothing sharded in-memory architecture and OLTPOLAP (yourOnline Analytical Processing - your issue - data warehousing/DW - i.e. reporting/aggregating over long periods) is done with compressed columnar stores of data.

As regards the speed issue - MySQL may be faster (and not by a whole lot) for simple read-heavy applications with little data-processing, but once you start writing complex DW type queries, PostgreSQL begins to shine! 

Take a look here and here. If you are looking at comparisons, pay no attention to any sites that mention MyISAM table types - the default for MySQL now is InnoDB which does enforce ACID transactions and referential integrity.

PostgreSQL has windowing functions and common table expressions (CTEs) - MySQL doesn't (at least not mainstream - yet - InfiniDB does). If you want a fast, read-heavy OLTP database, I'd go with MySQL. If you want a serious DW database and you have a choice from the get-go, I'd go with PostgreSQL. 

IMHO, you'll save yourself a lot of work in the long run by not having to implement CTEs and windowing functions in your own code. Take a look at what Baron Schwartz wrote about this here and here. Schwawtz wrote the book on MySQL high performance.

If it's a data warehouse system you're looking at, you may wish to consider InfiniDB. It's an Open Source columnar storage engine designed for DW type loads - it falls into the NewSQL paradigm as defined by Michael Stonebraker. There's also InfoBright, which is a similar offering.

I was thinking that I only considered MySQL related solutions. Take a look here (feature comparisons) about what's available in freebie servers from the big vendors. There's also PostgreSQL which is, in the eyes of many, a more capable DB server than MySQL.

[EDIT - in response to comments]

NewSQL - check out the link I posted above - note, it has been changed, my original one now appears to be dead, even though it worked yesterday.

There is not so much of a learning curve here - one of the key ideas behind NewSQL is that it explicitly retains much of OldSQL such as the SQL language and ACID transactions. The fundamental point is that NewSQL splits data processing into two types of task and takes a different approach for each. OLTP (banking, shopping &c.) is done on a shared nothing sharded in-memory architecture and OLTP (your issue) is done with compressed columnar stores of data.

As regards the speed issue - MySQL may be faster (and not by a whole lot) for simple read-heavy applications with little data-processing, but once you start writing complex DW type queries, PostgreSQL begins to shine! Take a look here and here. If you are looking at comparisons, pay no attention to any sites that mention MyISAM table types - the default for MySQL now is InnoDB which does enforce ACID transactions and referential integrity.

PostgreSQL has windowing functions and common table expressions (CTEs) - MySQL doesn't (at least not mainstream - yet - InfiniDB does). If you want a fast, read-heavy OLTP database, I'd go with MySQL. If you want a serious DW database and you have a choice from the get-go, I'd go with PostgreSQL. IMHO, you'll save yourself a lot of work in the long run by not having to implement CTEs and windowing functions in your own code. Take a look at what Baron Schwartz wrote about this here and here. Schwawtz wrote the book on MySQL high performance.

If it's a data warehouse system you're looking at, you may wish to consider InfiniDB. It's an Open Source columnar storage engine designed for DW type loads - it falls into the NewSQL paradigm as defined by Michael Stonebraker. There's also InfoBright, which is a similar offering. Note about InfiniDB - it's now under the aegis of MariaDB.org/com - not sure where this project will go in the future.

I was thinking that I only considered MySQL related solutions. Take a look here (feature comparisons) about what's available in freebie servers from the big vendors. There's also PostgreSQL which is, in the eyes of many (and especially me, a more capable DB server than MySQL.

[EDIT - in response to comments]

There is not so much of a learning curve here - one of the key ideas behind NewSQL is that it explicitly retains much of OldSQL such as the SQL language and ACID transactions. The fundamental point is that NewSQL splits data processing into two types of task and takes a different approach for each.

OLTP (Online Transaction Processing - banking, shopping &c.) is done on a shared nothing sharded in-memory architecture and OLAP (Online Analytical Processing - your issue - data warehousing/DW - i.e. reporting/aggregating over long periods) is done with compressed columnar stores of data.

As regards the speed issue - MySQL may be faster (and not by a whole lot) for simple read-heavy applications with little data-processing, but once you start writing complex DW type queries, PostgreSQL begins to shine! 

Take a look here and here. If you are looking at comparisons, pay no attention to any sites that mention MyISAM table types - the default for MySQL now is InnoDB which does enforce ACID transactions and referential integrity.

PostgreSQL has windowing functions and common table expressions (CTEs) - MySQL doesn't (at least not mainstream - yet - InfiniDB does). If you want a fast, read-heavy OLTP database, I'd go with MySQL. If you want a serious DW database and you have a choice from the get-go, I'd go with PostgreSQL. 

IMHO, you'll save yourself a lot of work in the long run by not having to implement CTEs and windowing functions in your own code. Take a look at what Baron Schwartz wrote about this here and here. Schwawtz wrote the book on MySQL high performance.

Small clarification of typo.
Source Link
Vérace
  • 30.6k
  • 9
  • 71
  • 84

If it's a data warehouse system you're looking at, you may wish to consider InfiniDB. It's an Open Source columnar storage engine designed for DW type loads - it falls into the NewSQL paradigm as defined by Michael Stonebraker. There's also InfoBright, which is a similar offering.

Of course, you may not require any special software at all - vanilla MySQL on a good server may be right up your alley. As has been pointed out, 11 operations/second is not a great deal on modern (even relatively modest) servers.

Obviously, this depends on your particular queries/application, load &c. and of course, budget :-). I would urge you to evaluate solutions and test before committing to any given approach.

[EDIT]

I was thinking that I only considered MySQL related solutions. Take a look here (feature comparisons) about what's available in freebie servers from the big vendors. There's also PostgreSQL which is, in the eyes of many, a more capable DB server than MySQL.

[EDIT - in response to comments]

NewSQL - check out the link I posted above - note, it has been changed, my original one now appears to be dead, even though it worked yesterday.

There is not so much of a learning curve here - one of the key ideas behind SQLNewSQL is that it explicitly retains much of OldSQL such as the SQL language and ACID transactions. The fundamental point is that NewSQL splits data processing into two types of task and takes a different approach for each. OLTP (banking, shopping &c.) is done on a shared nothing sharded in-memory architecture and OLTP (your issue) is done with compressed columnar stores of data.

As regards the speed issue - MySQL may be faster (and not by a whole lot) for simple read-heavy applications with little data-processing, but once you start writing complex DW type queries, PostgreSQL begins to shine! Take a look here and here. If you are looking at comparisons, pay no attention to any sites that mention MyISAM table types - the default for MySQL now is InnoDB which does enforce ACID transactions and referential integrity.

[EDIT - in response to OP's further comment about having no knowledge of PostgreSQL]

The fundamentals of SQL are essentially the same for MySQL and PostgreSQL. I had never worked with PostgreSQL before last year (I'm a mature student) and had to install a PostgreSQL instance on College machines. It worked a treat - straight out of the box - compiled from source. At heart, the fundamentals of database servers are reasonably similiar (without being identical!).

However, it is worth noting that PostgreSQL is a) much more standards compliant than MySQL and b) it's SQL language is significantly richer. Check constraints work in PostgreSQL -they don't in MySQL (I still find this a shocking lack!). What's even worse is that if you include them in your CREATE TABLE statements, the server will accept them without throwing an error!

PostgreSQL has windowing functions and common table expressions (CTEs) - MySQL doesn't (at least not mainstream - yet - InfiniDB does). If you want a fast, read-heavy OLTP database, I'd go with MySQL. If you want a serious DW database and you have a choice from the get-go, I'd go with PostgreSQL. IMHO, you'll save yourself a lot of work in the long run by not having to implement CTEs and windowing functions in your own code. Take a look at what Baron Schwartz wrote about this here and here. Schwawtz wrote the book on MySQL high performance.

If it's a data warehouse system you're looking at, you may wish to consider InfiniDB. It's an Open Source columnar storage engine designed for DW type loads - it falls into the NewSQL paradigm as defined by Michael Stonebraker. There's also InfoBright, which is a similar offering.

Of course, you may not require any special software at all - vanilla MySQL on a good server may be right up your alley. As has been pointed out, 11 operations/second is not a great deal on modern (even relatively modest) servers.

Obviously, this depends on your particular queries/application, load &c. and of course, budget :-). I would urge you to evaluate solutions and test before committing to any given approach.

[EDIT]

I was thinking that I only considered MySQL related solutions. Take a look here (feature comparisons) about what's available in freebie servers from the big vendors. There's also PostgreSQL which is, in the eyes of many, a more capable DB server than MySQL.

[EDIT - in response to comments]

NewSQL - check out the link I posted above - note, it has been changed, my original one now appears to be dead, even though it worked yesterday.

There is not so much of a learning curve here - one of the key ideas behind SQL is that it explicitly retains much of OldSQL such as the SQL language and ACID transactions. The fundamental point is that NewSQL splits data processing into two types of task and takes a different approach for each. OLTP (banking, shopping &c.) is done on a shared nothing sharded in-memory architecture and OLTP (your issue) is done with compressed columnar stores of data.

As regards the speed issue - MySQL may be faster (and not by a whole lot) for simple read-heavy applications with little data-processing, but once you start writing complex DW type queries, PostgreSQL begins to shine! Take a look here and here. If you are looking at comparisons, pay no attention to any sites that mention MyISAM table types - the default for MySQL now is InnoDB which does enforce ACID transactions and referential integrity.

[EDIT - in response to OP's further comment about having no knowledge of PostgreSQL]

The fundamentals of SQL are essentially the same for MySQL and PostgreSQL. I had never worked with PostgreSQL before last year (I'm a mature student) and had to install a PostgreSQL instance on College machines. It worked a treat - straight out of the box - compiled from source. At heart, the fundamentals of database servers are reasonably similiar (without being identical!).

However, it is worth noting that PostgreSQL is a) much more standards compliant than MySQL and b) it's SQL language is significantly richer. Check constraints work in PostgreSQL -they don't in MySQL (I still find this a shocking lack!). What's even worse is that if you include them in your CREATE TABLE statements, the server will accept them without throwing an error!

PostgreSQL has windowing functions and common table expressions (CTEs) - MySQL doesn't (at least not mainstream - yet - InfiniDB does). If you want a fast, read-heavy OLTP database, I'd go with MySQL. If you want a serious DW database and you have a choice from the get-go, I'd go with PostgreSQL. IMHO, you'll save yourself a lot of work in the long run by not having to implement CTEs and windowing functions in your own code. Take a look at what Baron Schwartz wrote about this here and here. Schwawtz wrote the book on MySQL high performance.

If it's a data warehouse system you're looking at, you may wish to consider InfiniDB. It's an Open Source columnar storage engine designed for DW type loads - it falls into the NewSQL paradigm as defined by Michael Stonebraker. There's also InfoBright, which is a similar offering.

Of course, you may not require any special software at all - vanilla MySQL on a good server may be right up your alley. As has been pointed out, 11 operations/second is not a great deal on modern (even relatively modest) servers.

Obviously, this depends on your particular queries/application, load &c. and of course, budget :-). I would urge you to evaluate solutions and test before committing to any given approach.

[EDIT]

I was thinking that I only considered MySQL related solutions. Take a look here (feature comparisons) about what's available in freebie servers from the big vendors. There's also PostgreSQL which is, in the eyes of many, a more capable DB server than MySQL.

[EDIT - in response to comments]

NewSQL - check out the link I posted above - note, it has been changed, my original one now appears to be dead, even though it worked yesterday.

There is not so much of a learning curve here - one of the key ideas behind NewSQL is that it explicitly retains much of OldSQL such as the SQL language and ACID transactions. The fundamental point is that NewSQL splits data processing into two types of task and takes a different approach for each. OLTP (banking, shopping &c.) is done on a shared nothing sharded in-memory architecture and OLTP (your issue) is done with compressed columnar stores of data.

As regards the speed issue - MySQL may be faster (and not by a whole lot) for simple read-heavy applications with little data-processing, but once you start writing complex DW type queries, PostgreSQL begins to shine! Take a look here and here. If you are looking at comparisons, pay no attention to any sites that mention MyISAM table types - the default for MySQL now is InnoDB which does enforce ACID transactions and referential integrity.

[EDIT - in response to OP's further comment about having no knowledge of PostgreSQL]

The fundamentals of SQL are essentially the same for MySQL and PostgreSQL. I had never worked with PostgreSQL before last year (I'm a mature student) and had to install a PostgreSQL instance on College machines. It worked a treat - straight out of the box - compiled from source. At heart, the fundamentals of database servers are reasonably similiar (without being identical!).

However, it is worth noting that PostgreSQL is a) much more standards compliant than MySQL and b) it's SQL language is significantly richer. Check constraints work in PostgreSQL -they don't in MySQL (I still find this a shocking lack!). What's even worse is that if you include them in your CREATE TABLE statements, the server will accept them without throwing an error!

PostgreSQL has windowing functions and common table expressions (CTEs) - MySQL doesn't (at least not mainstream - yet - InfiniDB does). If you want a fast, read-heavy OLTP database, I'd go with MySQL. If you want a serious DW database and you have a choice from the get-go, I'd go with PostgreSQL. IMHO, you'll save yourself a lot of work in the long run by not having to implement CTEs and windowing functions in your own code. Take a look at what Baron Schwartz wrote about this here and here. Schwawtz wrote the book on MySQL high performance.

In response to OP's comments.
Source Link
Vérace
  • 30.6k
  • 9
  • 71
  • 84
Loading
added 1272 characters in body
Source Link
Vérace
  • 30.6k
  • 9
  • 71
  • 84
Loading
added 424 characters in body
Source Link
Vérace
  • 30.6k
  • 9
  • 71
  • 84
Loading
Source Link
Vérace
  • 30.6k
  • 9
  • 71
  • 84
Loading