6 ms·

As someone running the default mysql install for a SaaS app, are there any downsides to moving to MariaDB? I'm assuming they are still fully compatible with the

As someone running the default mysql install for a SaaS app, are there any downsides to moving to MariaDB? I'm assuming they are still fully compatible with the native mysql protocol?

SDGT 13y ago
None that I've ever seen. MariaDB is a straight drop in replacement for MySQL. I, however, am a postgres guy now. Our last MySQL project was retired a few months ago.
- 1qaz2wsx3edc 13y ago
  I can second this, it's a drop in replacement. Postgres is still my preference for a RDBMS. Schemas, Triggers, JSON, Geo, PL, robust data types, it just offers so much more then MySQL/innoDB/MyISAM.
  - hrbrtglm 13y ago
    Absolutely, multiple schemas is a really neat feature in a SaaS app context. You can give each tenant his own schema, that way you can even make some nice split-testing when you incorporate new features in your app requiring changes in you database. Once a fraction of your tenants have tested/approved (much better if they choose themself to take part of a beta) the new model, you are good for rolling out progressively a full implementation to all of your tenants.
    - captainmojo 13y ago
      I really like this idea. Have you found any persistence frameworks that support the use of schemas WITH connection pools? Also, running multiple versions of a domain model concurrently seems like it'd be a headache.
      - hrbrtglm 13y ago
        Well, you're right it's not just as easy as I wrote it. For the moment I'm just doing it the ugly way when testing 2 versions whith differences I maintain and detect directly in the application code logic. Not nice.
        captainmojo 13y ago
        Yeah, it's really hard, but I think you're on to something that would be really useful. For connection pooling, if there were an easy way to set the schema search path prior to using a connection object in application code, something like that might work. I obviously don't like the extra query per request, but there are worse things! For the multi-version domain model, the only way I could think of doing it was partitioning the deployment environment according to version. So, 2n servers, n running each version, and slowly transitioning users from the old segment to the new. That sounds really hard, but not impossible. It also starts getting into some tough environment, human resource and priority questions, but for what it's worth, it'd keep the application logic cleaner! :) Anyway, if you ever end up with something elegant on this, I would be truly interested in reading about it. It's a good example of how a DBA concept can help app dev teams, who, in alarming numbers, prioritize an RDBMS' ease of use over its power.
        hrbrtglm 13y ago
        Well, let me explain the structure I choose : Each tenant is assigned a subdomain (No vhost, all routed by the app to the same codebase) with the name he choosed when he subscribed. So this name is retrieved by inspecting the host header the browser send while connecting to the webapp, this name is checked in the public schema to find an appropriate uuid assigned (if it exists of course). Something like : App_DB (database) -- public (public schema) -- tenants (tenants table) -- tenant_uuid uuid -- tenant_name text (same as subdomain name) -- coderev numeric (for split testing) -- some tenant general info like creation date, choosen plan, ... -- some other tables accessible by all tenants like shared stats, queues, etc ... I then use this tenant_uuid as the schema name for this tenant : -- "b6e42fd1-d5b9-4de4-ba6b-6eca1dae06ff" (tenant_uuid schema name) -- users (it's a multi-user webapp per tenants, so ...) -- email (for auth) -- password (bcrypt for auth) -- etc ... -- other tables needed for the webapp This for each tenant, so some tenants can have a different schema stucture based on their public.coderev When the user login, his credentials are checked in his tenant schema, the tenant name and uuid are set in his session for not messing with other tenants data. I think there are 2 ways to deal with connection pooling (If we are both talking about PgBouncer || pgpool connection pooling to be sure). - The first one is to create a new PostgreSQL user for each tenant in order to access only his schema. Then there is no need to set the schema search path prior the connection object as by definition his search_path will be set to $user,public (http://www.postgresql.org/docs/9.1/static/ddl-schemas.html http://www.postgresql.org/docs/9.1/static/ddl-schemas.html) But then, I can't really see the point of a connection pooling. I don't really like this solution, so many users, roles, passwords ... - The second (which I choose) is to connect with a role having access to all the schemas. You can set this one for connection pooling. The search_path is only set to public, and the queries use the qualified name to access the tenant data, ie: SELECT * FROM "b6e42fd1-d5b9-4de4-ba6b-6eca1dae06ff".users You can use the connections readily made available by the connection pool and query the data for the tenant you need without touching the search_path. The security is now dealt with the application and not the DB. For the multi-version domain model, the coderev set in the public.tenants schema is retrieved with the tenant_uuid associated with the tenant name and then used by the webapp controller to route to the associated code version. No need for a second server, just a different branch on the same server. Transitioning a user then just really mean updating his schema and routing it through the new codebase. And that's why I said it's very ugly, because I still need to implement a good DDL and DML stategy in order to navigate between different versions without losing data. Like you said, it's a tough environment. Unfortunately, I have nothing elegant to propose but I'm also interested in reading how others deal with that kind of stuff. I still lack fluency to write a blog post or something like that which could encourage debating or discussions. I'd be very glad if yourself find some interesting stuff on this topic to share it with me, my email address is my hn username @ gmail.com
    - porker 13y ago
      As a long-time MySQL user who hasn't used Postgres for over a decade, can you share (in non-documentation language) what schemas are good for and how they're useful?
      - jakobe 13y ago
        One compelling use case I've heard for schemas is versioning. Say you want to upgrade a stored function in your database. You need to make sure that both the new and the old version of the function are available until all application code that uses the stored procedure has been updated. Without schemas, you'd have to add a version identifier to each function, like get_customer_5(), get_customer_6() etc. With schemas, you can just put different versions of the functions into different schemas, and just change the search_path in your application code to use functions from the new schema. When all application code has been updated, you can drop the old schema.
      - hrbrtglm 13y ago
        I'll take a typical context of a SaaS app, multi-tenant architecture. When it comes to store the data of your tenants, you have to decide between different approaches which will impact the level of isolation of each tenant's data. I will not discuss the benefits and disadvantages of each because it clearly depends of your needs, security guidelines and sometimes regulations regarding your business, backup strategy, geo-isolation, sharding, etc ... With MySQL, you can choose 2 way to architecture your data : - the fully isolated way : each tenant has his own database. - the shared way : all tenants share the same schema, you usually use a tenant_id column on tables to query your data adequately. Some other RDBMS like PostgreSQL offer you a third way between the fully isolated a shared data system : - all tenant access the same database but their data are stored on their own schema. It's up to you to decide if the tenants access their schema with the same database role (kind of user) or if you create a role for accessing solely their own schema. Example : App_DB -- Public schema -- tenant_id uuid -- tenant_name (subdomain name for example) -- etc ... -- tenant (named after the public.tenant_id) -- your application tables -- tenant (named after the public.tenant_id) -- your application tables -- tenant (named after the public.tenant_id) -- Some beta tables model for version n+1 of your app or whatever, you can add more table columns for this very tenant if he needs special features for example. I hope it's non-documentation language but still english language :) (Need to improve my english and my writing skills)
  - copergi 13y ago
    Mysql has schemas too, they are just mislabeled as "databases". The fact that mysql doesn't offer multiple databases is a bit of a concern of course.
    - mst 13y ago
      Plus IIRC you can't foreign key between them.
toomuchtodo 13y ago
> As someone running the default mysql install for a SaaS app, are there any downsides to moving to MariaDB? MariaDB is essentially a drop in replacement; its also faster than MySQL: https://mariadb.com/kb/en/moving-from-mysql/ https://mariadb.com/kb/en/moving-from-mysql/ https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ "For all practical purposes, MariaDB is a binary drop in replacement of the same MySQL version (for example MySQL 5.1 -> MariaDB 5.1, MariaDB 5.2 & MariaDB 5.3 are compatible. MySQL 5.5 will be compatible with MariaDB 5.5)." "This means that for most cases, you can just uninstall MySQL and install MariaDB and you are good to go. (No need to convert any datafiles if you use same main version, like 5.1). You must however still run mysql_upgrade to finish the upgrade. This is needed to ensure that your mysql privilege and event tables are updated with the new fields MariaDB uses."
- agildehaus 13y ago
  Is 10 still a drop-in replacement for MySQL 5.5?
  - toomuchtodo 13y ago
    https://mariadb.com/kb/en/mariadb-10010-release-notes/ https://mariadb.com/kb/en/mariadb-10010-release-notes/ "With this release, MariaDB 10.0 is now the current stable version of MariaDB. It is an evolution of the MariaDB 5.5 series with several entirely new features not found anywhere else and with backported and reimplemented features from MySQL 5.6." I want to say yes, but as always, TEST FIRST.
- voidlogic 13y ago
  >> its also faster than MySQL I have heard this is not true for high core count scale up type systems. Has anyone here done that kind of testing?
madsushi 13y ago
You can literally just type 'apt-get install mariadb-server' into Ubuntu (once you've added the sources) and you won't even know MySQL is gone. You can even still type 'mysql -p' to log in to MariaDB locally.
- icelancer 13y ago
  >'apt-get install mariadb-server' This simple command replaces mysql in one shot? I might test it tonight if so. Do I have to run mysql_upgrade? Got a good recommendation for a guide?
  - madsushi 13y ago
    The MariaDB page has great instructions for various distros: https://downloads.mariadb.org/mariadb/repositories/#mirror=osuosl https://downloads.mariadb.org/mariadb/repositories/#mirror=o... Just pick your distro and you're good to go. I didn't have to run anything else.
- wolf550e 13y ago
  Careful! At least for mariadb 5.5 setup on Ubuntu 12.04 LTS, you need to pin the mariadb versions of packages because Ubuntu breaks stuff with their updates. # cat /etc/apt/preferences Package: * Pin: origin repo.percona.com Pin-Priority: 1003 Package: * Pin: origin ftp.osuosl.org Pin-Priority: 1002 # tail /etc/apt/sources.list deb ftp://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu precise main deb-src ftp://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu precise main
d64f396930663ee 13y ago
I was using it for a long time, and then after some update I was completely unable to get it to run on any new servers. Not sure what changed, but using the package manager route was no longer viable. Building from source also failed for reasons that are totally ungoogleable. Surely my incompetence plays a role, but I had to abandon it after every single workaround in their troubleshooting section failed to resolve the issue.
dawkins 13y ago
If you use replication with MySQL 5.6 and and GTID (Global Transaction ID) I heard some issues about not being fully compatible but I don't remember what it was exactly.

As someone running the default mysql install for a SaaS app, are there any downsides to moving to MariaDB? I'm assuming they are still fully compatible with the native mysql protocol?

SDGT 13y ago

None that I've ever seen. MariaDB is a straight drop in replacement for MySQL. I, however, am a postgres guy now. Our last MySQL project was retired a few months ago.

1qaz2wsx3edc 13y ago
I can second this, it's a drop in replacement. Postgres is still my preference for a RDBMS. Schemas, Triggers, JSON, Geo, PL, robust data types, it just offers so much more then MySQL/innoDB/MyISAM.
- hrbrtglm 13y ago
  Absolutely, multiple schemas is a really neat feature in a SaaS app context. You can give each tenant his own schema, that way you can even make some nice split-testing when you incorporate new features in your app requiring changes in you database. Once a fraction of your tenants have tested/approved (much better if they choose themself to take part of a beta) the new model, you are good for rolling out progressively a full implementation to all of your tenants.
  - captainmojo 13y ago
    I really like this idea. Have you found any persistence frameworks that support the use of schemas WITH connection pools? Also, running multiple versions of a domain model concurrently seems like it'd be a headache.
    - hrbrtglm 13y ago
      Well, you're right it's not just as easy as I wrote it. For the moment I'm just doing it the ugly way when testing 2 versions whith differences I maintain and detect directly in the application code logic. Not nice.
      - captainmojo 13y ago
        Yeah, it's really hard, but I think you're on to something that would be really useful. For connection pooling, if there were an easy way to set the schema search path prior to using a connection object in application code, something like that might work. I obviously don't like the extra query per request, but there are worse things! For the multi-version domain model, the only way I could think of doing it was partitioning the deployment environment according to version. So, 2n servers, n running each version, and slowly transitioning users from the old segment to the new. That sounds really hard, but not impossible. It also starts getting into some tough environment, human resource and priority questions, but for what it's worth, it'd keep the application logic cleaner! :) Anyway, if you ever end up with something elegant on this, I would be truly interested in reading about it. It's a good example of how a DBA concept can help app dev teams, who, in alarming numbers, prioritize an RDBMS' ease of use over its power.
        hrbrtglm 13y ago
        Well, let me explain the structure I choose : Each tenant is assigned a subdomain (No vhost, all routed by the app to the same codebase) with the name he choosed when he subscribed. So this name is retrieved by inspecting the host header the browser send while connecting to the webapp, this name is checked in the public schema to find an appropriate uuid assigned (if it exists of course). Something like : App_DB (database) -- public (public schema) -- tenants (tenants table) -- tenant_uuid uuid -- tenant_name text (same as subdomain name) -- coderev numeric (for split testing) -- some tenant general info like creation date, choosen plan, ... -- some other tables accessible by all tenants like shared stats, queues, etc ... I then use this tenant_uuid as the schema name for this tenant : -- "b6e42fd1-d5b9-4de4-ba6b-6eca1dae06ff" (tenant_uuid schema name) -- users (it's a multi-user webapp per tenants, so ...) -- email (for auth) -- password (bcrypt for auth) -- etc ... -- other tables needed for the webapp This for each tenant, so some tenants can have a different schema stucture based on their public.coderev When the user login, his credentials are checked in his tenant schema, the tenant name and uuid are set in his session for not messing with other tenants data. I think there are 2 ways to deal with connection pooling (If we are both talking about PgBouncer || pgpool connection pooling to be sure). - The first one is to create a new PostgreSQL user for each tenant in order to access only his schema. Then there is no need to set the schema search path prior the connection object as by definition his search_path will be set to $user,public (http://www.postgresql.org/docs/9.1/static/ddl-schemas.html http://www.postgresql.org/docs/9.1/static/ddl-schemas.html) But then, I can't really see the point of a connection pooling. I don't really like this solution, so many users, roles, passwords ... - The second (which I choose) is to connect with a role having access to all the schemas. You can set this one for connection pooling. The search_path is only set to public, and the queries use the qualified name to access the tenant data, ie: SELECT * FROM "b6e42fd1-d5b9-4de4-ba6b-6eca1dae06ff".users You can use the connections readily made available by the connection pool and query the data for the tenant you need without touching the search_path. The security is now dealt with the application and not the DB. For the multi-version domain model, the coderev set in the public.tenants schema is retrieved with the tenant_uuid associated with the tenant name and then used by the webapp controller to route to the associated code version. No need for a second server, just a different branch on the same server. Transitioning a user then just really mean updating his schema and routing it through the new codebase. And that's why I said it's very ugly, because I still need to implement a good DDL and DML stategy in order to navigate between different versions without losing data. Like you said, it's a tough environment. Unfortunately, I have nothing elegant to propose but I'm also interested in reading how others deal with that kind of stuff. I still lack fluency to write a blog post or something like that which could encourage debating or discussions. I'd be very glad if yourself find some interesting stuff on this topic to share it with me, my email address is my hn username @ gmail.com
  - porker 13y ago
    As a long-time MySQL user who hasn't used Postgres for over a decade, can you share (in non-documentation language) what schemas are good for and how they're useful?
    - jakobe 13y ago
      One compelling use case I've heard for schemas is versioning. Say you want to upgrade a stored function in your database. You need to make sure that both the new and the old version of the function are available until all application code that uses the stored procedure has been updated. Without schemas, you'd have to add a version identifier to each function, like get_customer_5(), get_customer_6() etc. With schemas, you can just put different versions of the functions into different schemas, and just change the search_path in your application code to use functions from the new schema. When all application code has been updated, you can drop the old schema.
    - hrbrtglm 13y ago
      I'll take a typical context of a SaaS app, multi-tenant architecture. When it comes to store the data of your tenants, you have to decide between different approaches which will impact the level of isolation of each tenant's data. I will not discuss the benefits and disadvantages of each because it clearly depends of your needs, security guidelines and sometimes regulations regarding your business, backup strategy, geo-isolation, sharding, etc ... With MySQL, you can choose 2 way to architecture your data : - the fully isolated way : each tenant has his own database. - the shared way : all tenants share the same schema, you usually use a tenant_id column on tables to query your data adequately. Some other RDBMS like PostgreSQL offer you a third way between the fully isolated a shared data system : - all tenant access the same database but their data are stored on their own schema. It's up to you to decide if the tenants access their schema with the same database role (kind of user) or if you create a role for accessing solely their own schema. Example : App_DB -- Public schema -- tenant_id uuid -- tenant_name (subdomain name for example) -- etc ... -- tenant (named after the public.tenant_id) -- your application tables -- tenant (named after the public.tenant_id) -- your application tables -- tenant (named after the public.tenant_id) -- Some beta tables model for version n+1 of your app or whatever, you can add more table columns for this very tenant if he needs special features for example. I hope it's non-documentation language but still english language :) (Need to improve my english and my writing skills)
- copergi 13y ago
  Mysql has schemas too, they are just mislabeled as "databases". The fact that mysql doesn't offer multiple databases is a bit of a concern of course.
  - mst 13y ago
    Plus IIRC you can't foreign key between them.

toomuchtodo 13y ago

> As someone running the default mysql install for a SaaS app, are there any downsides to moving to MariaDB? MariaDB is essentially a drop in replacement; its also faster than MySQL: https://mariadb.com/kb/en/moving-from-mysql/ https://mariadb.com/kb/en/moving-from-mysql/ https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ "For all practical purposes, MariaDB is a binary drop in replacement of the same MySQL version (for example MySQL 5.1 -> MariaDB 5.1, MariaDB 5.2 & MariaDB 5.3 are compatible. MySQL 5.5 will be compatible with MariaDB 5.5)." "This means that for most cases, you can just uninstall MySQL and install MariaDB and you are good to go. (No need to convert any datafiles if you use same main version, like 5.1). You must however still run mysql_upgrade to finish the upgrade. This is needed to ensure that your mysql privilege and event tables are updated with the new fields MariaDB uses."

agildehaus 13y ago
Is 10 still a drop-in replacement for MySQL 5.5?
- toomuchtodo 13y ago
  https://mariadb.com/kb/en/mariadb-10010-release-notes/ https://mariadb.com/kb/en/mariadb-10010-release-notes/ "With this release, MariaDB 10.0 is now the current stable version of MariaDB. It is an evolution of the MariaDB 5.5 series with several entirely new features not found anywhere else and with backported and reimplemented features from MySQL 5.6." I want to say yes, but as always, TEST FIRST.
voidlogic 13y ago
>> its also faster than MySQL I have heard this is not true for high core count scale up type systems. Has anyone here done that kind of testing?

madsushi 13y ago

You can literally just type 'apt-get install mariadb-server' into Ubuntu (once you've added the sources) and you won't even know MySQL is gone. You can even still type 'mysql -p' to log in to MariaDB locally.

icelancer 13y ago
>'apt-get install mariadb-server' This simple command replaces mysql in one shot? I might test it tonight if so. Do I have to run mysql_upgrade? Got a good recommendation for a guide?
- madsushi 13y ago
  The MariaDB page has great instructions for various distros: https://downloads.mariadb.org/mariadb/repositories/#mirror=osuosl https://downloads.mariadb.org/mariadb/repositories/#mirror=o... Just pick your distro and you're good to go. I didn't have to run anything else.
wolf550e 13y ago
Careful! At least for mariadb 5.5 setup on Ubuntu 12.04 LTS, you need to pin the mariadb versions of packages because Ubuntu breaks stuff with their updates. # cat /etc/apt/preferences Package: * Pin: origin repo.percona.com Pin-Priority: 1003 Package: * Pin: origin ftp.osuosl.org Pin-Priority: 1002 # tail /etc/apt/sources.list deb ftp://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu precise main deb-src ftp://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu precise main

d64f396930663ee 13y ago

I was using it for a long time, and then after some update I was completely unable to get it to run on any new servers. Not sure what changed, but using the package manager route was no longer viable. Building from source also failed for reasons that are totally ungoogleable. Surely my incompetence plays a role, but I had to abandon it after every single workaround in their troubleshooting section failed to resolve the issue.

dawkins 13y ago

If you use replication with MySQL 5.6 and and GTID (Global Transaction ID) I heard some issues about not being fully compatible but I don't remember what it was exactly.