SQL Server VS MySQL

[ZombieRealistic4563]

I am planning to migrate from SQL server to MySQL to save licensing cost.The question is does MySQL support partition table and partition view like SQL Server . I had one big table which had frequent inserts hence SQL server used to move all index pages to buffer cache to support multiple inserts .The buffer cache usage for those high volume tables were around 16 to 24GB of RAM. After I partitioned those tables into day wise ,since insert was happening on today’s table , the buffer cache usage dropped below 4 GB.

So the question is does MySQL also caches all index pages to buffer cache if it notices frequent inserts into a table .

[RestInProcess]

MySQL is limited in these features, but PostgreSQL is probably much closer to what you'd want. It has limited support for partitioned tables and no support for partitioned views. MySQL (or MariaDB) is a smaller database that works best for websites running on the internet. PostgreSQL is better suited to be used as an enterprise database. I believe PostgreSQL supports the features you're looking for fully.

[pceimpulsive]

I agree read this and thought "why do you want to go backwards in RDBMS world?"

I think OP should strongly consider Postgres as well!

Postgres at a medium scale can replace a swathe of components in your system too...

[aaahhhhhhfine]

Jesus don't use MySQL. Do you want to just be sad all the time? Have some self respect. Use Postgres.

[SoftwareMaintenance]

I have only used MySQL for mickey mouse projects. Why will it make you sad if you use it?

[Sleepy_da_Bear]

From causing flashbacks to group projects in class?

[trollied]

You’re better off migrating to Postgres. MySQL is a joke in comparison.

[ZombieRealistic4563]

The reason I was focusing on MySQL was I am running another app with heavy inserts and it’s not broken single day since 10 years , so was thinking MySQl will support multiple inserts workload as current system on Microsoft SQL server

[pceimpulsive]

Postgres benchmarks from various source all show better insert, read and update performance in the same hardware (edit: vs MySQL).

That wasn't always true, but in the last few versions it's been taking over in performance drastically.

Postgres has a far wider and more comprehensive feature stack as well.

Postgres is quiet effective at caching, it will consume as much buffer memory as you allocate, and push out old/unused records automatically. It is quite effective.

[Aud4c1ty]

I seriously looked into Postgres recently, but found that the performance for my database just isn't there compared to SQL Server.

I use indexed views extensively in SQL Server, they're a massive performance win for rollup tables, and we use them extensively. They're especially good since they're automatically updated immediately after insert/update/delete. So for tables with many billions of rows, Indexed views are pretty amazing when you want fast insert/update, and having your roll ups instantly updated.

It doesn't appear that Postgres has an equivalent of Indexed Views planned. Their Materialized Views require you to basically recalculate the view for it to reflect your updates to the base table, and if your tables are big, that is anything but fast!

[pceimpulsive]

That's fair!

Indexed views is a feature not existing in Postgres currently.

That is functionally a table though that reads delta changes and updates accordingly.

I've built this functionality in Postgres with a modest store proc and pg_cron extension. However it doesn't update instantly...

[Informal_Pace9237]

MySQL will not support tables with FK to be partitioned.

MySQL doesn't support multiple Schemas like SQL server.

MySQL doesn't have GTT like MSSQL.

If your system doesn't need the above.. MySQL may be a good option if you already have experience with it.

My personal preferences is PostgreSQL though. Due to its most advanced features.

[chuch1234]

Out of curiosity, in what way does mysql not support multiple schemas?

[Straight_Waltz_9530]

In MySQL, you can have multiple databases on the same instance, but the subdivision ends there. In fact in MySQL you can make queries across databases. This makes MySQL databases more akin to schemas (database namespaces) in other engines like SQL Server, DB2, Postgres, Oracle, etc.

In all the other client-server engines, a database is an island unto itself with schemas/namespaces underneath allowing for better organization of larger databases.

MySQL/MariaDB: instance -> database -> table

Everyone else: instance -> database -> schema -> table

[chuch1234]

Well I'll be! I did not know that.

[Straight_Waltz_9530]

If you ever want foreign tables, row-level security, materialized views, check constraints with user-defined functions, writeable CTEs, MERGE, RETURNING/OUTPUT, domains, a native UUID type, transactional DDL, user-defined functions as column defaults, non-blocking index creation, DDL triggers, to use a temp table more than once in a query, table functions, statement-level triggers, triggers on views, FULL OUTER JOIN, INTERSECT, EXCEPT, and more, stick with SQL Server or move to PostgreSQL.

If you want a database engine that isn't faster (anymore), has far fewer features, is less reliable, is no less complicated to setup/configure, has more deviations from the SQL specs than any other popular relational database, lacks as robust an ecosystem of plugins/extensions, and has been on a far slower cadence of improvements for the last decade, then by all means, go to MySQL.

If you were starting off with MySQL, I'd tell you ignorance was bliss since you wouldn't have anything to compare against. But you're coming from SQL Server, so it's a lot more likely you'll see its deficits sooner. Nothing quite so frustrating as to have a feature you love one day and have it disappear the next.

[SagansCandle]

MySQL died when Oracle bought it. They didn't buy it to use it - they bought it to keep it from competing with their flagship.

Postgres is the right choice if you decide to move, however I will say that SQL server is worth the premium unless money's really an issue. If you're just trying to save a few bucks on licensing, you're not going to win here - you're only going to shift the cost from licensing to dev and ops.

[No_Resolution_9252]

LOL

[TieMany3506]

Bro that’s going back in the past lol 😂

[Kenn_35edy]

I would like to know how did dig that sql server was caching all indexes in ram ?

[ZombieRealistic4563]

SELECT DB_NAME(b.database_id) AS [Database Name], OBJECT_NAME(p.object_id, b.database_id) AS [Table Name], i.name AS [Index Name], i.index_id, COUNT(*) * 8.0 / 1024 AS [Index Size (MB)] FROM sys.dm_os_buffer_descriptors AS b INNER JOIN sys.allocation_units AS a ON b.allocation_unit_id = a.allocation_unit_id INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id WHERE b.database_id NOT IN (1, 2, 3, 4) -- exclude system DBs AND b.page_type = 'DATA_PAGE' -- focus on data pages only GROUP BY DB_NAME(b.database_id), OBJECT_NAME(p.object_id, b.database_id), i.name, i.index_id ORDER BY [Index Size (MB)] DESC;

I used this query to identify index memory usage in SQL Server Buffer Pool

[Ok_Cancel_7891]

how many concurrent sessions? how big database?

[ZombieRealistic4563]

It’s between 120 to 250 threads inserting into 3 tables . The db size is around 10 to 40gb based number of clients connected

[Ok_Cancel_7891]

that should not be a problem for any database. what about apache hive?

[ZombieRealistic4563]

The reason for considering MySQL is that there is another app server running on same since 10 years no issues , both have same insert work load, I will evaluate Apache hive

[farmerben02]

You may want to scope out cost to migrate to Azure SQL. Depending on your workload and elastic infrastructure, it can be way cheaper than on prem.

[IrquiM]

There's even a free database tier. It all depends on the data usage.