Enhancing your Amazon RDS for MariaDB databases with MariaDB version 10.6 - AWS Online Tech Talks

Key Takeaways

[Music] hi everyone welcome to our tech talk where we are going to talk about enhancing your amazon audios for mariadb databases with mariadb 10.6 my name is komalpal i'm a product manager and with me i have vijay karumaji who is a specialist solutions architect at aws and we are going to talk about some of the new features that we have introduced in amazon rds as well as new features and functionality introduced in the community version of mariadb 10.6 our agenda for today we'll start off with the enhancements that have been made to amazon rds for mariadb followed by a short demo and the community mariadb 10.6 major enhancements and we'll close with why you should run a managed maria db service on amazon rds so let's jump right in i'll start off with the amazon rds for mariadb enhancements in q1 of 2022 we introduced the new version mariadb 10.6 with amazon rds and with that we enabled several new features we have also introduced delayed replication as a disaster recovery strategy we've expanded our portfolio of storage engines on rds mariadb and we've added my rocks along with innodb we have also introduced one-step major upgrades which are also known as multi-major version upgrades and finally we've added integration with the aws identity and access management service which is more frequently called iam in the next set of slides i'm going to go over each of these features in a lot more detail let's start with delayed replication now what is delay replication delay replication is a way for customers to set a configurable time by which a replica lags behind the source database why would you need that it's typically used as a disaster recovery strategy so as an example let's say you accidentally deleted a table on your primary or the source database you can recover from that by stopping your application and promoting your read replica to be the new source database because you had configured a delay between the source and the replica the replica never experienced the table deletion now a couple of things to note here delayed replication on amazon rds is managed through stored procedures it cannot be configured through the management console or the cli or the api so let's get into how to set up delayed replication on your rds mariadb instance if you're setting it up for a read replica while you're creating the read replica all you would need to do is call a stored procedure called audio set configuration and what you need to pass to this procedure is a target delay in seconds so for example the first example we have here is a delayed replication setup during a read replica creation for 1r so we pass the target delay parameter a value of 3600 which is 1r in seconds if you wanted to configure delayed replication on an existing read replica what you need to do is first stop replication by calling the rds stop replication procedure then you can set up a lag on the replica by calling the audio set source delay and once again pass it the value of the delay in seconds 3600 in our example and then you can restart the replication by calling the rds start replication procedure if you have any questions about delayed replication we are happy to take those in the chat or save them for the end of the session where we will take q a now let's talk about the next new thing we've expanded the storage engine portfolio for mariadb on amazon rds by adding the myrox storage engine now we have two storage engines that are supported on mariadb that are enodb and my rocks my rocks is an open source storage engine which was originally developed by facebook which added roxdb to mysql and to mariadb the difference between innodb and myrocks is the architecture myrocks uses the log structured merge architecture whereas innerdb uses b plus 3. and this difference in architecture results in several benefits which are specifically obvious for right intensive workloads now what are these benefits let's talk about each of them the four benefits that we observe while using my rocks are greater space efficiency which means you use less storage because of the better compression that myrox offers over innodb you can also get greater writing efficiency which means you use less io for write intensive workloads you can do faster replication because my rocks doesn't need to update secondary keys and doesn't need read random reads associated with those and you can also achieve faster data loading due to just lesser compaction overhead with my rocks now of course the actual gains that you will see for your specific workloads are dependent on the workload itself as well as your parameter configuration and the infrastructure that you are running behind it my rocks storage engine is available on amazon rds for mariadb 10.6 and higher how to set up my rocks it's very straightforward you define the storage engine that you use while you're creating your tables so as part of the create table command you would pass engine equals rocks roxdb and that will change the storage engine from innodb to roxdb by default amazon rds for mariadb will always default to innodb now couple of nodes here mariadb does not guarantee asset for transactions that span storage engines so we recommend that you do not use transactions that would span both innodb and myrock stables and secondly myrox does not support serializable isolation or cap locks which means myrocks cannot be used with statement based replication if you want to use statement replica based replication we recommend that you use innodb the next enhancement to amazon rds for mariadb is a feature called multi-major version upgrades so previously if you wanted to go from an old version of mariadb let's say version 10.4 to the latest version which at the time of this recording is 10.6 you would have to do that in two steps first you would have to go from 10.4 to 10.5 and then you would go from 10.5 to 10.6 with the multi-major version upgrades we've made that easy by making it a one-step upgrade which means you can go directly from 10.4 to 10.6 in a single step and this is supported for any older version that's available on amazon rds for mariadb so you can go from 10.2 or 10.3 or 10.4 or 10.5 directly to 10.6 this avoids customers from having to take multiple down times associated with each incremental upgrade a node of caution as we're talking about upgrades always test before making an image version upgrade you can do that by running an assessment using the mariadb check upgrade command and also after the upgrade make sure you've tested your application with the upgraded major version before you deploy anything to production and finally let's talk about the latest new feature which is identity and access management integration with amazon rds for mariadb now aws i am provides fine gained access control across all of your aws resources it allows you to specify who can access which services and which resources under which conditions this is all managed via a policy-based mechanism customers can define least privileged permissions by setting up their policies in the way that's appropriate for them im allows you to centralize all of your access management in one place for all of your aws resources im is currently supported with amazon rds for mariadb 10.6.5 and higher if you want to set up iam on an existing rds mariadb instance you would just call a modify db instance command and enable iim database authentication on it using a command shown on your screen to do this it will not require any downtime and is done applied almost instantly to see how this works in practice i'm going to hand over to vijay who's going to walk you through a demo of how i am can be configured on an actual rds mariadb instance and we're going to follow that up with some new enhancements that have been made in the mariadb 10.6 community edition over to you vijay thank you komo as part of this demo we're going to do the six steps first we're going to enable the am authentication on the mydbrds instance and then we will create a database user account that uses the aws authentication token and then we will create an im user but if you would like to try it with imroll you can feel you can do it with im rules as well so we will create an im user and then to that im user we will add an im policy that has a tight integration with our database user and then we will generate an aws authentication token that token will act as a password for that uh database user and then we will connect to our rds maya db instance using im authentication so let's get started screen we have an rds mega db instance which is on 10 6 7 version and to enable im authentication you can enable it by simply using nws console by clicking the modify or you can use cli or api calls right so i have already enabled the am authentication on this instance so let's check under the configurations you can see i im authentication is already enabled if your rds instance is not enabled with im authentication simply click on modify and under the database authentication section select the password and i am on the am database authentication option which would enable the im authentications where you can use leverage i have users or roles to connect to this rds instance right to enable uh im authentication it does not require any reboot so it is simply a dynamic change so with that let me cancel let me come out of it here and then next step let's create a database user um for that i'm going to use an ids instance let me connect to that rds instance with my master user credentials now let's create a db user so in here i have created an idb user and then in the create db user syntax if you see right identified with authentication plugin as rds so which means this db user is going to use im authentication feature and then lets also make sure this user only can connect using ssl connections because im authentication requires an ssl connection now let's provide some permissions to this uh mydb user so here as you see i'm just providing the select permissions now let's verify what we have created so far for that i'm going to query our my sequel user table just to show you we have created a mydb user and the mydb user using plugin called aws authentication plugin which means this user is using im authentication and then let me also show you the databases that we have and for this demo i'm going to use my lab let's see the tables that we have and we have a table called speakers let me quickly show you what we have within that so in the speakers table we have como and myself right have today's speakers well let me exit out let me play this out now let's do step three and four let's create an iem user and attach a policy that maps the database user that we created in step two to this am user or a role that you create right so for this demo i have already created an i am user so under identity access management console in iam you can see under users you can see an user called am user and then this user already attached an im policy so i just want to show you how this policy looks like and as you see here the policy has a resource and a statement in the statement action it can do a disconnect and the resource is this is the resource it can connect to right so let's see how the resource string looks like so in the resource string what you have is a region and then account id and then dbi resource id and a db username so as we know now region is east one or the reason that you are that you have your rds instance and the account id that your ids instance is running under and dbi resource id which is nothing but uh your rds mariadb resource id that you will get it from the configuration tab of the rds instance and then db user that we created in the step two so once you have all this configuration um for your uh resource then attach that policy to your uh iam user so for part of this demo i have already attached policy but this is an example policy that i would like to show you here so that is the same policy that we discussed i have attached it with the all the updated information once you have it now let's see what we need to do um [Music] we need to generate authentication token and then using that authentication token as a password to our db user we're going to connect to the mariadb instance now as first step what i'm going to do is i'm going to create some environment variables that i'm going to use as part of the connection string to connect to rds instance so i exported the rds instance connection string uh into the rds host now i'm going to generate the token so if you look at here right so i'm just creating a token this token is using aws rds generate db authentication token cli so if you are using im authentication in your application then you need to make sure you have you generate with generatedb authentication token cli this requires a host as a input and then port number and the region and then mydb user right so the db user that requires that we're going to use it as part of the item application so which i have generated and now it is in the uh token environment variable so let me quickly show you how that does it look like so this is how the token looks like it is quite a huge string right i cannot use this whole string as part of my connection string so that's why i'm using the environment variables to save that password so here the good part is right this token valued only 15 minutes so this is the additional layer of the security that for im authentication provides to you now let's with the token now let's connect to our rds instance [Music] so as you see here i'm using the host as uh ids host and the port number then db user that we created and the password as our token and then which requires an sso as i am authentication requires some ssl connections we need to make sure we have ssl equals to one if you are using and command line arguments so now let's see we are successfully connected to is it stands using the configurations now quickly let's see in the databases that this mydb user can see so you can see my labs are there so let's use my lab and let's see the tables and tables we have speakers let's quickly create that to make sure we have options to read those tables this mydb user can do that so if you quickly want to test to see if you want if you can insert anything [Music] so i'm just making sure try to insert a dummy record into that speakers table and you cannot insert any records because when we created this mydb user we just provided the select permissions so that is another layer of security you can use for your users so you have two layers of security one inside the password is only valid for 15 minutes and second layer of security you can use is the permissions that you create for your database users with that that demo is done so let's get back to our presentation so now let's look at enhancements in mariadb 10.6 that brings to the table here we put together some of the enhancements uh in marielle b 10.6 some of them are oracle pl sql compatibility new json table function introduced for the first time in mariadb 10.6 atomic ddl feature is added for reliability this schema is introduced for monitoring and troubleshooting purpose for performance mariadb introduced ignored indexes and some inbuilt you know db enhancements let's dive deep into these enhancements oracle pls sql inbuilt compatibility is introduced in 10.3 with mariadb 10.6 there are additional functions being added here if you see on the screen right we have add month or actual function this function is used to add subtract months from a given date or date time value it is very similar to a date add function in mariadb that to care supports date date time timestamp as parameters and returns a formatted converted text value for us and the next one is cis guide is a viral function which is similar to uuid function in mariadb which is universally unique identifier and row number is another function that returns a number for each row extracted from a query so all these functions are available in mariadb 10.6 if you're coming from oracle to mariadb all you need to do is use sql mode in rds mariadb parameter groups if you configure that sql mode to oracle the existing code that you're running on oracle will seamlessly work on mariadb the next one is a json table function json table function is the first table function in myadb server before my id 10.6 if you wanted to written a json file into your tabular format what you have to do is you have to use json value which allows you to retrieve scalar values and then you have to use json query which allows you to retrieve json objects within mariadb to get the job done first but with this json table function that transforms the json data into relational table so that json data can be queried and joined with regular tables and json table function can be used within select statement that i'm going to show you a simple example on the next slide you can write the json table function in your from class within a select statement json table function can be used in the multi-table syntax of the delete and update statements as well however there is uh when you write a json table function it always requires a cable alias so let's see how it looks like here is an example i have a simple uh table which has an id and a name and a patch is a json format it takes this table right and we insert a row which has the json json file information and then when you look at the select statement here right whenever we're calling the json table function all we need to do is call out those uh alias names as pt and then refer that in your from clause within the select statement and then once you do that your results will be in a tabler format so to achieve the same thing before 10.6 you have to use uh json query and json value functions right so this made it very pretty simple to query any of the json data that you have within your database next one is atomic ddl mariadb 10.6 introduces atomic ddl atomic means that either the operation succeeds and is logged into the binary log file or completely reversed class f means that in case of a crash after server restarts all the tables are consistent there are no temporary files on the file system and there are no data within the binary log files so with 10.6 you can do create tables views sequences triggers these are all atomic functions now so either they are completely completely fully uh executed or it will be completely reverted to its original state and then if you see drop table and replace table and drop database or class save functions so the storage engines that support atomic leds are you know db uh my zone and my rocks db storage engines that support uh fully atomic pdls maria db 10.6 introduced a very exciting schema for database for database administrators which is a schema that is added in 10.6 this helps administrator to get insights into the database usage for troubleshooting or any of the analytics uh if you want to see how your systems are running if you want to do some analysis you can query this schema which introdu which gives you collection of views functions and stored procedures so if you want to use schema in ids mariadb what you need to do is you need to enable performance schema within the ids parameter group turn it on and then you will see schema which start collecting all the data that is required for you to troubleshoot as a dba next very exciting feature that introduced by maya db 10.6 is ignored indexes the optimizer will treat ignore ignored indexes as as if they don't exist at all they will not be used in the query plan or as a source of the statistical information so that's why they are pretty safe to ignore them so whenever you're planning on deleting an index right the first thing what you want to do is you simply mark them as ignored index and then you monitor your health of your workload and if if everything works as expected then you can simply drop those indexes right that is the one of the use case with these ignored indexes i'm gonna show you how it looks like but another thing what i want to point out here is tables with primary keys cannot be ignored so that is one thing you may want to make sure before you trying to make them ignore indexes this is how it looks like so here we ha in this example we are creating a table with two columns one primary key other with an index of k1 if you see i'm simply altering the table and then saying alter index k1 is ignored when i say that ignored and if you execute the execution plan if you look into the execution plan where you see the use file sort that means we are not using any more index uh in this scenario we are using file sorts to execute this query uh and if you see the next one all the table table 1 index k1 not ignored when you not ignored that then you realized you need this index because your performance was bad so you want to enable it again you can say simply not ignore when you do that you actually start using indexes as you see on the screen we are using the index in the execution query plan and then some of the other improvements i want to call out here is optimization to speed up inserts into an empty table which is dramatically improved if you're trying to do and bulk loads within an empty table performance improvements are really good with money ldb 10.6 and then there are some enhancements around temporary table space is committed from the buffalo plus list so that whenever uh whenever when where because of this improvement right so you don't see any offhand tables within the storage subsystem and there are additional improvements around you know db deadlock detectors with that i'm going to pass it on to cuomo who is going to talk about why run mariadb is on amazon rds thank you so much vijay now in the last section of this presentation let's talk about why you should run managed mariadb on rds let's start with why amazon rds now what you have on your screen in front of you is a snippet from a 2020 idc report which captures the benefits that customers have received when they moved from an on-premise or hosted in self-managed environment to amazon rds as you see on the top left section of the slide customers saw about 39 lower cost over a period of three years in addition to the cost benefits rds customers were also able to improve their staff efficiencies and spend time on innovating and optimizing their applications and capabilities instead of operational headaches with amazon rds backups snapshots scaling high availability and upgrades are all handled with a few simple clicks leading to this improved staff efficiency who uses amazon audios hundreds of thousands of customers a small subset of that is presented in front of you i'm going to talk about one specific customer out of these called travel tech which is a customer based in the uk travel tech as the name suggests is a travel technology company that connects travel booking agents with suppliers in the travel and tourism industry they were facing an aging data center and they needed more elasticity and scalability to be able to manage the volatility that the travel and tourism industry is constantly faced with which has been more pronounced in the last few years than ever together with our aws partner cirrus hq we had them migrate their data center to a fully cloud-based architecture on aws including amazon rds for mariadb this helped them avoid not only a six-figure cost of hardware but also improved their application page times by up to 50 percent and saved their employees time which they were spending on self-managing the hardware to focus on the things that mattered most which is innovating for their customers with that if you want to learn more about travel tech and how aws and amazon rds for mariadb helped them achieve these benefits please refer to our publicly available case study the links to which we'll provide at the end of this presentation now what does amazon rds for mariadb offer as a managed service the benefits can be broken down into four categories amazon rds offers availability and durability for all of your mariadb workloads through features such as multi-ac read replicas and auto backups we offer performance and easy scalability with both compute and storage we offer easy administration where you can manage and monitor all of your resources with just a few simple clicks or api calls and finally security and compliance which are offered via network security im encryption and a support for a variety of industry standard compliance protocols let's talk about each of these in detail over the next few slides starting with availability and durability now amazon offers rds multi-ac deployment which offers enhanced availability and durability for database instances which makes them a fit for production database workloads each availability zone runs its own physically distinct independent infrastructure and is engineered to be highly reliable with rds you have the benefit of running your primary and your standby into physically distinct availability zones whenever a failure happens rds will manage the failover automatically and make the operations resume as quickly as possible since the end point for your database instance will remain the same after failover your application can resume operations without any manual intervention required the next feature here that we are going to talk about is rds read replicas rds makes it easy for you to create read replicas of your database and automatically keeps them in sync with the masters read replicas provide two benefits one read scaling so that you can relieve pressure from your primary source database and two you can use it in disaster recovery use cases with rds for mariadb you can set up the replica read replica as multi-az allowing you to use the read replica as a dr target when you promote this read replica to be a standalone database it will already be multi-az enabled another availability and durability feature that rds offers are automated backups with rds you get one schedule backup a day and you can choose what backup time window is appropriate for you rds backups you as well as your transaction logs and in order to ensure durability and availability of your backups rds will keep multiple copies of your backup in each availability zone where you have an instance deployed and finally if you want to set up your own backups we offer the option of snapshots snapshots are user initiated backups of your instance that are stored in amazon s3 that are kept until you explicitly delete them although these database snapshots serve operationally and as full backups you are only billed for incremental storage use because snapshots are always incremental you can also copy your snapshots across accounts and regions for additional dr the next set of features is related to performance the rds currently offers about 25 instance types that you can choose from to find the best fit for your cpu memory and price requirements you can also scale your database compute and storage with just a few clicks or api calls you can scale up to as much as 128 vcpus and 3 900 gigabytes of ram or scale down to as little as one vcpu and one gigabyte of ram recently rds also announced graviton two instances which are based on the custom built aws graviton 2 processor with 64-bit arm neoverse cores these instances have been seen to achieve up to 52 percent better price performance on amazon rds graviton 2 is available both across our general purpose category of instances as well as memory optimized instances and do look out for free tier coming soon on graviton based instances which you can try out all of your rds mariadb workloads on moving on to the ease of administration rds makes it easy to go from project conception to project deployment hardware provisioning patching backup restores scaling and high availability are all accessible within a few button clicks or api calls with rds you get a collection of 15 to 18 monitoring metrics that are automatically available to you which can be viewed in the rds console cloud watch or via the cloud watch apis you can keep an eye on metrics like cpu utilization memory storage and latency you can either view the metrics through tools that amazon provides such as cloud watch enhanced monitoring or performance insights or you can pull the metrics into your own monitoring tool and last but not the least security and compliance rds makes it easy to control access to your database at multiple levels network security is provided via amazon vpc security groups resource level permissions can be controlled through aws iam data encryption is available through integration with aws kms or key management service and finally compliance and assurance programs for various industries like finance healthcare government that are all managed by rds for our infrastructure let's talk about each of these in a little bit more detail first network level access is secured via vpc security groups a vpc security group allows you to specify access rules for your mariadb database instance each rule is a combination of three things that you see here in the table before you there's protocol port range and source the source can either be a cider block that covers a range of ip addresses or it could also be another security group this gives you the flexibility to have a multi-tiered architecture for your database access at a resource level you can restrict permissions using an iam policy we spoke a lot about iam through the demo and the previous part of the presentation so i won't go into it in much detail here but if you have any questions please drop them in the chat or hold them until the end of the presentation where we'll take q a finally encryption of data at rest is provided through integration with aws kms or key management service kms uses industry standard aes 256 encryption to protect your data stored on the underlying host that your rds for mariadb instance is running on the great thing about kms is that all of the keys are tied to your aws accounts and are fully managed by you kms thus provides an additional layer of protection against unauthorized access to the underlying storage for your rds mediadb instance and finally let's talk compliance aws takes responsibility for compliance of the rds service and the infrastructure related to it and you as the customer take responsibility for your application on aws rds for mariadb is authorized for several contra compliance protocols as you see listed on the slide in front of you this includes fedramp pcie hipaa uk government programs etc you can take the audit findings from your application and combine them with the appropriate attestation from aws's third-party auditors to have a complete verification for compliance for your application on aws you can also check the compliance service scope link that we've presented at the bottom of the slide this will help you find out whether a specific compliance protocol is supported or not against any aws service that you want to use and before we close and move on to the q a i'm going to share with you a list of resources that we've compiled to make it easy for you to use rds for mariadb we have a bunch of links customer case studies blogs and also a link to some free digital trainings through the aws skill builder platform if at the end of this presentation you have you still have questions about amazon rds for mariadb or you want to reach out to us we suggest you post your questions on the amazon repost platform and make sure to tag them as mariadb so that the questions make it all the way directly to us with that i'm going to stop the presentation and open up the flow for questions thank you so much [Music] you