Alternatives to MS SQL Server

scottalanmiller

On a large scale, though, something to consider is just how slow and problematic SW online community is - partially this is because they are using a relational backend for non-relational data. They need many, huge database servers to make it as fast as it is. ML does similar traffic, but on a tiny, by comparison, NoSQL database that screams past them in performance. The difference in costs to maintain, and the differences in performance, are really significant.

Obsolesce

@scottalanmiller said in Alternatives to MS SQL Server:

@tim_g said in Alternatives to MS SQL Server:

But then again, I'm never dealing with DBs that are doing 1m transactions a second. They are not heavily used as far as t/s goes.

That's hardly the big selling point for avoiding MS SQL Server. MS SQL Server is a great product, from a technology standpoint. But it is relational, making it not ideal for 90%+ of workloads. And it is not free, making it ideal for about 0% of workloads. Given that there are "equally good" free products, it is one of those times that you really have to evaluate hard why any product made by a competent development team with any intention of making something good for customers would ever even consider looking at it.

It's all about the combination of human losses stemming from having to manage the overhead of dealing with the complexities of MS SQL Server. And about adding in the cost of buying it, which is a staggering cost.

While other databases might be faster, that's just about reducing your hardware costs, which might be measureable but are unlikely to be significant. But there are huge features that MS SQL Server doesn't do well, like global replication and more granular locking that some other databases do far better.

Yes I agree and have the same viewpoint as you.

However, we've been locked in prior to this.

All I can say publicly is that we're on a forward path to SaaS.

scottalanmiller

@tim_g said in Alternatives to MS SQL Server:

@scottalanmiller said in Alternatives to MS SQL Server:

@tim_g said in Alternatives to MS SQL Server:

But then again, I'm never dealing with DBs that are doing 1m transactions a second. They are not heavily used as far as t/s goes.

That's hardly the big selling point for avoiding MS SQL Server. MS SQL Server is a great product, from a technology standpoint. But it is relational, making it not ideal for 90%+ of workloads. And it is not free, making it ideal for about 0% of workloads. Given that there are "equally good" free products, it is one of those times that you really have to evaluate hard why any product made by a competent development team with any intention of making something good for customers would ever even consider looking at it.

It's all about the combination of human losses stemming from having to manage the overhead of dealing with the complexities of MS SQL Server. And about adding in the cost of buying it, which is a staggering cost.

While other databases might be faster, that's just about reducing your hardware costs, which might be measureable but are unlikely to be significant. But there are huge features that MS SQL Server doesn't do well, like global replication and more granular locking that some other databases do far better.

Yes I agree and have the same viewpoint as you.

However, we've been locked in prior to this.

All I can say publicly is that we're on a forward path to SaaS.

I think a handy thing to do is to look at MS SQL Server as a flag that a product is either made by a company that is ....

Clueless and wasting their own money (internal use.)
Clueless and wasting your money (private use.)
Intentionally trying to lock you in for some reason (MS back room deal.)
Abandoned software that the vendor can't maintain.

There's no good reason to find software locked into it. So finding that it exists can be, in many cases, a good enough reason to just walk away from a potential product right then and there.

Emad R

Sorry but we need to break the PostgreSQL bubble:

I read alot of benchmarks and reviews, and you are much better served with MariaDB 100% of the time. Facts check:

MariaDB performance is better in every single way.
Old but realistic bench:

https://2bits.com/articles/benchmarking-postgresql-vs-mysql-performance-using-drupal-5x.html

There is others, and you can do test yourself (maybe a good idea for ML user to bench them up) but at the end of the day only recently PostgreSQL performance matches up with MariaDB.

Alot of people complain how MariaDB allows invalid data, or is not strict , you can just over ride this with:
https://mariadb.com/kb/en/library/sql-mode/#strict-mode
We are talking editing line and adding 3 words then restarted the service, and BAM you have strict rules like PostgreSQL
People tend to like PostgreSQL cause it is more Open that MariaDB, which does not make any sense, but it is just fall back from Oracle purchasing MySQL. and fearing something wrong will go with MariaDB, will guess what we have the source code for MariaDB, and we can fork it million times. more over the binary compatibility one with all the mature core from MySQL is good enough and sufficient 90% of the application (MariaDB 5.5.59)
it is stupid assumption to think that MariaDB is good for web based apps, you can use it to power anything, including enterprise application.
I dont get why you anyone would use PostgreSQL ? it seems to me like old Debian OS, it is there and good to be there, but you wont be actually using it for anything new.
Better support and tools from every company with MariaDB.

At the end your free to have choice and I respect this, but dont assume PostgreSQL is better in any way, when all the facts states it is worse.

I might back this up with Benchmark test shortly...

scottalanmiller

@emad-r said in Alternatives to MS SQL Server:

Sorry but we need to break the PostgreSQL bubble:

I read alot of benchmarks and reviews, and you are much better served with MariaDB 100% of the time. Facts check:

MariaDB performance is better in every single way.
Old but realistic bench:

https://2bits.com/articles/benchmarking-postgresql-vs-mysql-performance-using-drupal-5x.html

There is others, and you can do test yourself (maybe a good idea for ML user to bench them up) but at the end of the day only recently PostgreSQL performance matches up with MariaDB.

That's for non-relational data, which is exactly what I already said. MariaDB is faster than PostgreSQL in situations where neither one is a good choice at all.

scottalanmiller

Here is an interesting article on safety concerns with MariaDB for critical data:

https://www.cybertec-postgresql.com/en/why-favor-postgresql-over-mariadb-mysql/

scottalanmiller

@emad-r said in Alternatives to MS SQL Server:

Alot of people complain how MariaDB allows invalid data, or is not strict , you can just over ride this with:
https://mariadb.com/kb/en/library/sql-mode/#strict-mode
We are talking editing line and adding 3 words then restarted the service, and BAM you have strict rules like PostgreSQL

Yes, BUT....

This is almost never on in benchmarks, making MariaDB seems faster when it generally is not. Because it is optional to make it fully relational, and since almost no one does, reports of its speed are very misleading.

This only applies to some engines, and not the ones that people normally mean when referencing MariaDB. Fully relational isn't even an option in the commonly assumed engine.

scottalanmiller

No performance info, but a good article:

https://blog.panoply.io/postgresql-vs-mariadb

scottalanmiller

Another good one. MySQL and MariaDB lack ACID compliance, which is huge. PostgreSQL is fully ACID and more SQL complete.

Also some performance numbers and notes. They reiterate, MySQL is good for web workloads where relational data isn't needed.

http://www.cloudhack.in/2017/03/12/mysql-vs-postgresql-relational-database-comparison/

Emad R

@scottalanmiller said in Alternatives to MS SQL Server:

Another good one. MySQL and MariaDB lack ACID compliance, which is huge. PostgreSQL is fully ACID and more SQL complete.

Also some performance numbers and notes. They reiterate, MySQL is good for web workloads where relational data isn't needed.

http://www.cloudhack.in/2017/03/12/mysql-vs-postgresql-relational-database-comparison/

Everyone uses InnoDB currently so that is not an excuse, fresh installs on Centos 7(which people consider old) defaults to InnoDB

So it is ACID compatible 100%.

Emad R

@scottalanmiller said in Alternatives to MS SQL Server:

Another good one. MySQL and MariaDB lack ACID compliance, which is huge. PostgreSQL is fully ACID and more SQL complete.

Also some performance numbers and notes. They reiterate, MySQL is good for web workloads where relational data isn't needed.

http://www.cloudhack.in/2017/03/12/mysql-vs-postgresql-relational-database-comparison/

You know what I am benching them right now, hold on with me...

scottalanmiller

@emad-r said in Alternatives to MS SQL Server:

@scottalanmiller said in Alternatives to MS SQL Server:

Another good one. MySQL and MariaDB lack ACID compliance, which is huge. PostgreSQL is fully ACID and more SQL complete.

Also some performance numbers and notes. They reiterate, MySQL is good for web workloads where relational data isn't needed.

http://www.cloudhack.in/2017/03/12/mysql-vs-postgresql-relational-database-comparison/

Everyone uses InnoDB currently so that is not an excuse, fresh installs on Centos 7(which people consider old) defaults to InnoDB

So it is ACID compatible 100%.

The use and assumption of InnoDB is VERY new and not what most knowledge and testing of MySQL or MariaDB are based on. MariaDB only just moved to it as the default. And even InnoDB is not 100% ACID, so that point is moot.

MySQL is anything but "everyone uses InnoDB". And InnoDB lacks ACID completeness.

scottalanmiller

@emad-r said in Alternatives to MS SQL Server:

@scottalanmiller said in Alternatives to MS SQL Server:

Another good one. MySQL and MariaDB lack ACID compliance, which is huge. PostgreSQL is fully ACID and more SQL complete.

Also some performance numbers and notes. They reiterate, MySQL is good for web workloads where relational data isn't needed.

http://www.cloudhack.in/2017/03/12/mysql-vs-postgresql-relational-database-comparison/

You know what I am benching them right now, hold on with me...

What kind of workload?

scottalanmiller

WordPress company thinks MyISAM remains the dominant engine just weeks ago. I think you'll find InnoDB is only just starting to catch on. Anyone bringing databases of any age with them will almost always be on MyISAM as InnoDB was rarely recommended for decades.

https://premium.wpmudev.org/blog/mysql-databases/

Emad R

@scottalanmiller said in Alternatives to MS SQL Server:

@emad-r said in Alternatives to MS SQL Server:

@scottalanmiller said in Alternatives to MS SQL Server:

Another good one. MySQL and MariaDB lack ACID compliance, which is huge. PostgreSQL is fully ACID and more SQL complete.

Also some performance numbers and notes. They reiterate, MySQL is good for web workloads where relational data isn't needed.

http://www.cloudhack.in/2017/03/12/mysql-vs-postgresql-relational-database-comparison/

Everyone uses InnoDB currently so that is not an excuse, fresh installs on Centos 7(which people consider old) defaults to InnoDB

So it is ACID compatible 100%.

The use and assumption of InnoDB is VERY new and not what most knowledge and testing of MySQL or MariaDB are based on. MariaDB only just moved to it as the default. And even InnoDB is not 100% ACID, so that point is moot.

MySQL is anything but "everyone uses InnoDB". And InnoDB lacks ACID completeness.

sorry but what is "InnoDB is not 100% ACID", this is more of speculation than anything .

I tried to bench both, but I was unable to complete, I found good tool called Hammerbench.

It worked on MariaDB, but when I tried using it on PSQL, but I was unable to remote manage and connect to PostgreSQL,
cause their latest client does not support SSH tunneling, so I downgraded to 3, but that also didnt work, can I needed to configure this file:
nano /var/lib/pgsql/data/pg_hba.conf
I tried everything with the last lines
host all all 127.0.0.1/32 ident
host all all ::1/128 ident
like:
host all all 0.0.0.0/0 md5

then restarted the service, with no use, I was unable to connect it remotely, thus the bench didnt resume.

What I can only tell you that is it is smaller download size thats it

scottalanmiller

@emad-r said in Alternatives to MS SQL Server:

sorry but what is "InnoDB is not 100% ACID", this is more of speculation than anything .

That would mean it being ACID compliant is speculation as well. But if there is no way to know, then we must treat it as not ACID compliant.

scottalanmiller

This is how Oracle states it officially: "MySQL includes components such as the InnoDB storage engine that adhere closely to the ACID model,"

Emad R

@scottalanmiller said in Alternatives to MS SQL Server:

@emad-r said in Alternatives to MS SQL Server:

sorry but what is "InnoDB is not 100% ACID", this is more of speculation than anything .

That would mean it being ACID compliant is speculation as well. But if there is no way to know, then we must treat it as not ACID compliant.

This is the closest I can find regarding this

https://mariadb.com/kb/en/library/about-xtradb/

Anyway the reason I say this I worked on packaging

Nginx
MariaDB
PHP

on windows as standalone package @ 2013
https://alternativeto.net/software/whimp/
when I was in university.

So till now I admire how easy it was and how InnoDB was the right choice that you need to select, I did that back 5 years ago.

I will check Postgresql when I have more free time, it was peculiar to me that it was 20 mb download and install, instead of 100+ for MariaDB on Centos 7.

scottalanmiller

@emad-r said in Alternatives to MS SQL Server:

@scottalanmiller said in Alternatives to MS SQL Server:

@emad-r said in Alternatives to MS SQL Server:

sorry but what is "InnoDB is not 100% ACID", this is more of speculation than anything .

That would mean it being ACID compliant is speculation as well. But if there is no way to know, then we must treat it as not ACID compliant.

This is the closest I can find regarding this

https://mariadb.com/kb/en/library/about-xtradb/

Anyway the reason I say this I worked on packaging

Didn't MariaDB just drop XtraDB to go back to InnoDB? I thought that they just announced it.

scottalanmiller

@emad-r said in Alternatives to MS SQL Server:

So till now I admire how easy it was and how InnoDB was the right choice that you need to select, I did that back 5 years ago.

It depends on what you are doing. Lots of things that are done on MariaDB, like many PHP web apps, actually should be MyISAM rather than InnoDB. It was the default for a long time for a reason, it's screaming fast and if you are just doing simple reads most of the time, it's really good.