MySQL 8.0 vs MariaDB 10.6 : Which is best & What I should use from your experience?

[Iron-Man]

Hello friends & Experts✌,

Recently, I discovered that there is something called MySQL versions, and upon further research, I found that I have been using an older version of it (MySQL 5.7) for many years without knowing it.

Initially I thought to just change it; but my hosting provider told me that if I change my MySQL database, I cannot rollback & I can not play with it like I play with changing PHP versions to see which is better. I can only use one database source at a time.

Due to this now I am in a severe dilemma and am paralysed by overthinking and analysis paralysis.

I found its option in WHM panel. There I can change it to latest MySQL 8.0 or MariaDB 10.6.

So I am planning to take full VPS server backup for precautions & then change to MySQL 8.0 or MariaDB 10.6.

But before this I need your recommendation of what to use.

After some Google research (which causes more overthinking and analysis paralysis), most recommend using MariaDB instead of MySQL for various reasons, like that MariaDB is faster than MySQL, is open source, blah blha blah. See these links:
https://shorturl.at/eituA
https://shorturl.at/jpzBL


So I think I should use MariaDB instead of MySQL for overall better speed/performance/security + future compatibility. I never have to worry about changing MariaDB to MySQL Or MariaDB to go EOL.

Or am I wrong?


Please share your genuine recommendations/experience.

Thanks in advance.

Regards,
Iron-Man

P.S: I am neither a developer nor an expert. I am an entrepreneur with expertise in business development, sales, & marketing only. I am not an expert on websites or coding. I just follow documentation & advice from Babiato experts and other members of reputable forums.. My main purpose is to give my clients superior website performance without any bugs/glitches. I am in an IT business without being a developer

 

[dreamcoder]

Use MySQL for it's extensive community support, and MariaDB is nothing but forked version of MySQl only

 

[TKS]

If you have a large amount of queries, you should pay attention to performance. I have researched this topic and found many comparison information on the internet. However, for my average projects, any option is acceptable. Currently, I am using mariadb 10.4 and have no intention to switch to mysql because mysql commands are different from mariadb's, which may cause errors.

It is important to optimize queries and cache data if necessary to reduce the load on the database.

 

[Iron-Man]

Use MySQL for it's extensive community support

Thanks for recommendations brother. But google's seo optimized articles are in favour of MariaDB. So I am little confused. I am telling you my requirements & this way you can recommend what I should use.

In my IT company, most are e-commercce websites only (80%+) and the rest are blogging websites & some company websites too. I use Wordpress & WooCommerce only. So I think there would be a large number of queries every day. So I need such a database platform that will support my requirements + compatible with other performance-based tools like Redis cache or any server cache, + lightspeed server, etc. Also, I do not have to worry about databases going EOL too (& I think MySQL 5.7 is EOL too).

I think you understand what I mean.

So what should I use, as per your opinion? MySQL 8.0 or MariaDB 10.6?

MariaDB is nothing but forked version of MySQl only

What a troll, it would be a great meme for MySQl supporter

 

[Iron-Man]

If you have a large amount of queries, you should pay attention to performance.

Thanks for recommendations brother. Google's seo optimized articles are in favour of MariaDB. I am telling you my requirements & this way you can recommend what I should use.

In my IT company, most are e-commerce websites only (80%+) and the rest are blogging websites & some company websites too. I use Wordpress & WooCommerce only. So I think there would be a large number of queries every day. So I need such a database platform that will support my requirements + compatible with other performance-based tools like Redis cache or any server cache, + lightspeed server, etc. Also, I do not have to worry about databases going EOL too (& I think MySQL 5.7 is EOL too).

I think you understand what I mean. So what should I use, as per your opinion? MySQL 8.0 or MariaDB 10.6?

mysql commands are different from mariadb's, which may cause errors.

So you mean it is easy to find good commands for mariadb than mysql. It is a good positive point then.✌

It is important to optimize queries and cache data if necessary to reduce the load on the database.

Can you give me some suggestion for this related to cache & optimized queries. I really need this advice.

I am thinking about buying a new SSD based dedicated/VPS server + KVM + cloudlinux + lightspeed web server. As I have not much knowledge about server related cache + google research make me more confused due to various types of server cache like redis cache, memcached, varnish cache blah blah blah.

As a non-techie hope you understand what I mean. So plz give me some advice from your research/experience.

 

[TKS]

Firstly, I want to reiterate that this is my personal opinion and I am not a professional. I can only say that my choice is MariaDB because I am familiar with it. One thing to note is that both the newer versions of MySQL and MariaDB have added new features. You should choose the one that will benefit you more.

Regarding caching, there are many types of caching solutions such as Redis and Memcached... These solutions use RAM to store data and provide quick access. For my average websites, I use file caching to ensure that data is not lost in case of unexpected server failures. However, accessing data from file cache will not be as fast as accessing from RAM. Therefore, you can use both solutions to ensure data safety and quick access.

For example, to get a list of top users, the query can take from 0.2 to 10 seconds. To solve this problem, you can save the query result to file cache. Then, anyone accessing afterwards can read the result from file cache and return the result in 0.001 seconds without using many resources as querying directly. However, the data in the file cache is only updated when there is a change in the list of top users. If you have a better solution for this problem, please share.

 

[Iron-Man]

Firstly, I want to reiterate that this is my personal opinion and I am not a professional. I can only say that my choice is MariaDB because I am familiar with it. One thing to note is that both the newer versions of MySQL and MariaDB have added new features. You should choose the one that will benefit you more.

I guess the new version of MySQL is 8.0 which was created many years ago; while the new version of MariaDB is launched in February. Can I use the latest version of MariaDB or stick with MariaDB 10.6

Regarding caching, there are many types of caching solutions such as Redis and Memcached... These solutions use RAM to store data and provide quick access. For my average websites, I use file caching to ensure that data is not lost in case of unexpected server failures. However, accessing data from file cache will not be as fast as accessing from RAM. Therefore, you can use both solutions to ensure data safety and quick access.

For example, to get a list of top users, the query can take from 0.2 to 10 seconds. To solve this problem, you can save the query result to file cache. Then, anyone accessing afterwards can read the result from file cache and return the result in 0.001 seconds without using many resources as querying directly. However, the data in the file cache is only updated when there is a change in the list of top users. If you have a better solution for this problem, please share.

Actually I am less expert thank you. After some research, I found that I can use only one caching system in my server. Not multiple (If I am wrong, tell me). Also regarding file caching, I searched in google of how to add it in WHM; but found no such article. So what is this actually.

Most my websites are woocommerce based & dynamic. So I am thinking about using the redis cache. I think redis cache is better than Memcached & Varnish cache.

 

[dreamcoder]

Thanks for recommendations brother. But google's seo optimized articles are in favour of MariaDB. So I am little confused. I am telling you my requirements & this way you can recommend what I should use.

In my IT company, most are e-commercce websites only (80%+) and the rest are blogging websites & some company websites too. I use Wordpress & WooCommerce only. So I think there would be a large number of queries every day. So I need such a database platform that will support my requirements + compatible with other performance-based tools like Redis cache or any server cache, + lightspeed server, etc. Also, I do not have to worry about databases going EOL too (& I think MySQL 5.7 is EOL too).

I think you understand what I mean.

So what should I use, as per your opinion? MySQL 8.0 or MariaDB 10.6?


What a troll, it would be a great meme for MySQl supporter

I think as per your requirements you should consider doing a performance test yourself. If you have that many wordpress websites then why not pick one & have it running MySQL and another one with MariaDB and compare the performance of both for a couple of weeks, that should give you a fair idea of what performs well with your given server/hosting setup.

Also, consider using a database caching layer like Redis/Memcached and a frontend caching layer like Varnish, both will work great together.

Another way to optimize your website performance could be to consider Nginx as the web server, instead of Apache web server. Nginx is known to give great performance for wordpress websites

Hope it helps!

 

[Iron-Man]

I think as per your requirements you should consider doing a performance test yourself. If you have that many wordpress websites then why not pick one & have it running MySQL and another one with MariaDB and compare the performance of both for a couple of weeks, that should give you a fair idea of what performs well with your given server/hosting setup.

I guess u r reading my mind as I am thinking of doing the same thing...

Also, consider using a database caching layer like Redis/Memcached and a frontend caching layer like Varnish, both will work great together.

I am already using redis. But Intially i thought i can use only one cache in my server. But chatGPT said I can use multiple and I did not believe in it. Now I am confirmed by your reply...Thanks for this info brother. ChatGPT said I can use Redis + Memcached or Redis Cache + Varnish Cache. What should be better for my server who have both dynamic woocommerce websites & blogs websites (90%) and few static websites (<10%).

Another way to optimize your website performance could be to consider Nginx as the web server, instead of Apache web server. Nginx is known to give great performance for wordpress websites

Many months ago I tried it in my server. I am using c-panel as control panel. As per my hosting support team, they said c-panel is not fully compatible for nginx and can be used for caching only. And they recomend me that I should not use it as nginx is better for static websites. So advice me not to use as I have lots of dynamic woocommerce websites. I also tested nginx without listening to them found my websites loading slowly. So revert back...This story was 1 year ago. Not tested now btw..

 

[dreamcoder]

I guess u r reading my mind as I am thinking of doing the same thing...



I am already using redis. But Intially i thought i can use only one cache in my server. But chatGPT said I can use multiple and I did not believe in it. Now I am confirmed by your reply...Thanks for this info brother. ChatGPT said I can use Redis + Memcached or Redis Cache + Varnish Cache. What should be better for my server who have both dynamic woocommerce websites & blogs websites (90%) and few static websites (<10%).



Many months ago I tried it in my server. I am using c-panel as control panel. As per my hosting support team, they said c-panel is not fully compatible for nginx and can be used for caching only. And they recomend me that I should not use it as nginx is better for static websites. So advice me not to use as I have lots of dynamic woocommerce websites. I also tested nginx without listening to them found my websites loading slowly. So revert back...This story was 1 year ago. Not tested now btw..

If that is the case, let me clarify the caching concept for you
Redis/Memcached are both in-memory caching solutions.
Consider this example - if your website is database heavy - meaning users are querying your database but the query does not change that often, like users want to see their products purchased in last 30days ( and this query is the most frequent query contributing to 60-70% of the total query load), here Redis/Memcached will cache the results of this query in the memory, so that next time when the same user wants to see his/her purchased products in last 30days, the DB won't be queried & the results will be directed fetched from the caching memory (Redis/Memcached), so no load on DB & results will be displayed blazingly fast as it's fetched directly from the memory. And if the user modifies his/her filter criteria (like show purchased products in last 7days) - thereby modifying the DB query, then Redis/Memcahed will cach the results of the modified query & keep it ready in the memory in case the user wants to see the results again.

Now a frontend caching solution like Varnish is completely different than Redis/Memcached. It caches the webpage (HTML code, CSS, images etc.) in the memory, so that the most frequently visited webpages (like Homepage, Product page etc.) are in the memory & the web server is not requested for those set of pages again & again. Thereby making the loading of webpages superfast.

So like I said in the beginning my advice would be to use a combination of both - Redis/Memcached - for DB caching and Varnish for webpage caching.
Now I suggest you go for Memcached + Varnish combination first and then after some weeks switch to Redis + Varnish and compare the performance, you will find out yourself which combo is giving you better performance.

Re Nginx - you would need more flexibility to install & configure it to suit your needs & this would require some code optimization efforts from your end. Once upon a time I had a VPS where I migrated a full-fledged wordpress website from Apache web server to Nginx, I had to optimize a lot for my use cases. So if you don't have that support/flexibilty then I advice you to park this topic for future and right now focus on A/B testing the DBs & test the heck out a frontend+DB caching combo like I suggested

Hope it clarifies your doubts! Do share the results/observations of your testing for the benefit of the larger community

 

[xinhnhatvn]

In my personal opinion and experience, Mariadb is better in terms of performance and increasingly gaining more users. Mariadb has new features that bring better performance than Mysql.
- Columnar storage
- Galera Cluster

 

[wooyihoo]

Personal choice is MariaDB. MySQL has a lot of restrictions due to Oracle ownership.
* It is a drop-in replacement of MySQL, no need to update your code.
* 200,000+ connections -- what's not to like about this out of the box feature?
* Galera cluster - multi master replication
* I tried TokuDB storage engine with great compression. Was able to save 100+GB of space because of this.
* These features may be found in MySQL, but personally, I don't like the path that oracle took for this product.

Just my 2 cents.

 

[Iron-Man]

If that is the case, let me clarify the caching concept for you
Redis/Memcached are both in-memory caching solutions.
Consider this example - if your website is database heavy - meaning users are querying your database but the query does not change that often, like users want to see their products purchased in last 30days ( and this query is the most frequent query contributing to 60-70% of the total query load), here Redis/Memcached will cache the results of this query in the memory, so that next time when the same user wants to see his/her purchased products in last 30days, the DB won't be queried & the results will be directed fetched from the caching memory (Redis/Memcached), so no load on DB & results will be displayed blazingly fast as it's fetched directly from the memory. And if the user modifies his/her filter criteria (like show purchased products in last 7days) - thereby modifying the DB query, then Redis/Memcahed will cach the results of the modified query & keep it ready in the memory in case the user wants to see the results again.

Now a frontend caching solution like Varnish is completely different than Redis/Memcached. It caches the webpage (HTML code, CSS, images etc.) in the memory, so that the most frequently visited webpages (like Homepage, Product page etc.) are in the memory & the web server is not requested for those set of pages again & again. Thereby making the loading of webpages superfast.

So like I said in the beginning my advice would be to use a combination of both - Redis/Memcached - for DB caching and Varnish for webpage caching.
Now I suggest you go for Memcached + Varnish combination first and then after some weeks switch to Redis + Varnish and compare the performance, you will find out yourself which combo is giving you better performance.

Re Nginx - you would need more flexibility to install & configure it to suit your needs & this would require some code optimization efforts from your end. Once upon a time I had a VPS where I migrated a full-fledged wordpress website from Apache web server to Nginx, I had to optimize a lot for my use cases. So if you don't have that support/flexibilty then I advice you to park this topic for future and right now focus on A/B testing the DBs & test the heck out a frontend+DB caching combo like I suggested

Hope it clarifies your doubts! Do share the results/observations of your testing for the benefit of the larger community

Thanks brother for clarifying almost all of my doubts related to server caching.

As I have less time (No time to experiment now, have to gave websites to some clients asap) + I will buy more new servers in upcoming month for new clients; I guess I will follow just one method in this server.

I will try redis + varnish as server cache. Stick with current apache & litespeed server. Use MariaDB.

In next server I will use nginx & then experiment with your recommended other server cache + MySQL too.

I think it will save both my time & mental over thinking analyse paralyse

P.S: Thanks again. I also sent you a PM. Plz reply in your free time.

 

[dreamcoder]

Thanks brother for clarifying almost all of my doubts related to server caching.

As I have less time (No time to experiment now, have to gave websites to some clients asap) + I will buy more new servers in upcoming month for new clients; I guess I will follow just one method in this server.

I will try redis + varnish as server cache. Stick with current apache & litespeed server. Use MariaDB.

In next server I will use nginx & then experiment with your recommended other server cache + MySQL too.

I think it will save both my time & mental over thinking analyse paralyse

P.S: Thanks again. I also sent you a PM. Plz reply in your free time.

I'm glad to have helped

This sounds like a plan to me, so go ahead

PS: I have replied to your PM

 

[Iron-Man]

I'm glad to have helped

This sounds like a plan to me, so go ahead

PS: I have replied to your PM

Thank you brother for all of your help & assurance. Your PM reply was awesome too.

Thanks 1000% times again from my

 

[brass9]

Go with mysql 8 - it's backed by the giant Oracle. mariadb is a fork created by the original author of mysql. But they're a small entity, and will not be able to match the footprint of oracle.

A few years ago there was a FUD that Oracle may kill mysql, that's when mariadb gained popularity. But that turned out to be a myth, mysql is adequately supported (perhaps not as well as Oracle's main product, but nevertheless it's actively developed) At this juncture, there aren't many valid reasons to choose one over the other apart from big corp licensing restrictions. mariadb may be slightly more performant, but it's less reliable too. in most cases you won't notice a thing.

I personally use mariadb because it's a smaller download and you can just dump the portable version anywhere and get going.

Personally, I prefer pgsql over mysql for my projects.

 

[brass9]

Consider this example - if your website is database heavy - meaning users are querying your database but the query does not change that often, like users want to see their products purchased in last 30days ( and this query is the most frequent query contributing to 60-70% of the total query load), here Redis/Memcached will cache the results of this query in the memory, so that next time when the same user wants to see his/her purchased products in last 30days, the DB won't be queried & the results will be directed fetched from the caching memory (Redis/Memcached), so no load on DB & results will be displayed blazingly fast as it's fetched directly from the memory. And if the user modifies his/her filter criteria (like show purchased products in last 7days) - thereby modifying the DB query, then Redis/Memcahed will cach the results of the modified query & keep it ready in the memory in case the user wants to see the results again.

Now a frontend caching solution like Varnish is completely different than Redis/Memcached. It caches the webpage (HTML code, CSS, images etc.) in the memory, so that the most frequently visited webpages (like Homepage, Product page etc.) are in the memory & the web server is not requested for those set of pages again & again. Thereby making the loading of webpages superfast.

caching is a widely used technique, but your example is contrived and will not happen if ever. who caches anything for 30+ days? that almost never happens... most apps use redis/mc as a LRU or MRU cache, and almost certainly a stale entry will be evicted upon expiry or memory pressure whichever happens earlier.

your scenario is a self-defeating one. you're basically using redis as a secondary database on top of mysql primary - which invalidates the basic concept of caching. if you have oodles of RAM then you're better off cranking up mysql query cache/index buffer pools, rather than inserting another tech stack. also investing on a fast enterprise grade NVMe or storage tech will greatly improve performance.

if your DB is another server, and redis/mc is on the edge server, then it may make sense to use aggressive caching, however now you have another problem - cache invalidation is a very hard issue to solve.

 

[bhuindia]

Hello friends & Experts✌,

Recently, I discovered that there is something called MySQL versions, and upon further research, I found that I have been using an older version of it (MySQL 5.7) for many years without knowing it.

Initially I thought to just change it; but my hosting provider told me that if I change my MySQL database, I cannot rollback & I can not play with it like I play with changing PHP versions to see which is better. I can only use one database source at a time.

Due to this now I am in a severe dilemma and am paralysed by overthinking and analysis paralysis.

I found its option in WHM panel. There I can change it to latest MySQL 8.0 or MariaDB 10.6.

So I am planning to take full VPS server backup for precautions & then change to MySQL 8.0 or MariaDB 10.6.

But before this I need your recommendation of what to use.

After some Google research (which causes more overthinking and analysis paralysis), most recommend using MariaDB instead of MySQL for various reasons, like that MariaDB is faster than MySQL, is open source, blah blha blah. See these links:
https://shorturl.at/eituA
https://shorturl.at/jpzBL


So I think I should use MariaDB instead of MySQL for overall better speed/performance/security + future compatibility. I never have to worry about changing MariaDB to MySQL Or MariaDB to go EOL.

Or am I wrong?


Please share your genuine recommendations/experience.

Thanks in advance.

Regards,
Iron-Man

P.S: I am neither a developer nor an expert. I am an entrepreneur with expertise in business development, sales, & marketing only. I am not an expert on websites or coding. I just follow documentation & advice from Babiato experts and other members of reputable forums.. My main purpose is to give my clients superior website performance without any bugs/glitches. I am in an IT business without being a developer

Mysql practical condition :i thinks that mysql enterprise edition is good for every startup. As a developer i think that team is work on seeding phase, sometime due to some circumstances team need to change or team members need to chage in this case mysql is usefull. you can hire mysql developer with low cost also.

Mariadb practical condition: after seeding phase if you have good traffic and good fund for more scale, you can switch over to it cause mariadb team may be hire with high price. but it can handle better traffic, so automatically it will usefull in search engine rank also.

This is just my opinion as developer, but you can use whatever you want to use.

 

[dreamcoder]

caching is a widely used technique, but your example is contrived and will not happen if ever. who caches anything for 30+ days? that almost never happens... most apps use redis/mc as a LRU or MRU cache, and almost certainly a stale entry will be evicted upon expiry or memory pressure whichever happens earlier.

your scenario is a self-defeating one. you're basically using redis as a secondary database on top of mysql primary - which invalidates the basic concept of caching. if you have oodles of RAM then you're better off cranking up mysql query cache/index buffer pools, rather than inserting another tech stack. also investing on a fast enterprise grade NVMe or storage tech will greatly improve performance.

if your DB is another server, and redis/mc is on the edge server, then it may make sense to use aggressive caching, however now you have another problem - cache invalidation is a very hard issue to solve.

It's clear from your response that you didn't understand the example & you don't understand how caching actually works, so let me enlighten you a bit.

In my example I meant that if a user wants to see his/her purchased products in the last 30 days, read carefully in the last 30 days. So in the backend a DB query will be fired something like this:
SELECT user_id, product_id, product_name, order_date from PURCHASED_PRODUCTS WHERE order_date >='2023-04-01' and order_date <='2023-04-30' and user_id = 123;

This query will fetch the products purchased by the given user in last 30 days, which will be displayed on the UI. Now it is important to note that we're merely querying the database for last 30days worth of data & then caching the results, not caching entire 30 days data individually (this is the part where you should've paid close attention). Now when the results will be fetched, a key-value pair would be created by the caching solution of your choice (both Redis/Memcached will work the same as both are key-value in-memory stores). When I say a key-value pair would be created it means, in the memory a hash table will be created with the query as the 'key' & the results as the 'value' and next time when the same query is getting fired, Redis/Memcached will use the query as the key & lookup the hash table, this way the value (i.e. the results of the query) will get fetched from this hash table & the DB won't be queried. When we purge the cache memory, we are actually deleting this hash table from memory, so that it can be built afresh. Now why this retrieval is faster than directly retrieving it from the DB - it is because in hash table search is O(1) - in algorithms we call it big O of 1, which means constant time.

And how is it decided which query results to cache & which ones to remove from memory, for that we have cache eviction policies in the form of FIFO, LIFO, LRU, MRU etc. these are nothing but algorithms trying to delete data from cache in a specific way. Now regardless of the cache eviction policy, the results of last 30days query will stay in the memory based on how frequently it is being requested.

Also you mentioned something like if you're having oodles of RAM then create mysql query cache/index buffer pools. I can say from experience, even if you have infinite RAM, even then this won't help & you will have to eventually "introduce another tech stack" in the form of a caching solution. Ofcourse before this we can always analyze the query execution plan of the most frequent/heavy load queries & index mysql tables to finetune them for optimal performance of those queries.

You also mentioned something like Redis/Memcached on edge server, I say why are you trying to introduce distributed db/caching here, when clearly OP wants to keep his entire stack on one VPS, at least read the opening post carefully before suggesting.

To conclude - I suggest you read the posts carefully, understand 'em & then respond. Don't just try to throw some random jargons & assume you know better than others.

 

[dreamcoder]

Thank you brother for all of your help & assurance. Your PM reply was awesome too.

Thanks 1000% times again from my

You're most welcome!!

I'm glad I could be of help