Alternatives to MS SQL Server

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.