By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Question on mysql table design

0

Hello All, we have mainly worked in Oracle Databases in past. Now designing tables for Aurora mysql. For one of the reporting applications.

As we understand mysql database is relational too. And supports all sorts of constraints, indexes, joins, partitions, triggers , procedures etc. And thus , we are going for a normalized designed approach while designing tables.

For example, a look up or master tables to have report configurations, and detail tables to have each report execution start , end time and status of completion. Another table for holding report column details. Audit columns in each of these table to audit the user detail activity through triggers etc.

Wanted to check with experts here, is there any specific points, which we should take care of keeping an eye on future scalability and performance of our mysql database and related applications? Any size constraints etc?

As because, in some databases people suggest to have flattened data as much as possible, as the joins are not performant and on the other hand storage is cheap. Is there any such thing with mysql? Or we can safely opt for a fully normalized data model here? Appreciate your guidance.

Topics
AnalyticsDatabase
Tags
Amazon AuroraMySQL
Language
English
awslearner0073
asked 10 months ago219 views
1 Answer
1

As you are building this solution on Amazon Aurora, the first thing is to be aware of the benefits and limitation on Aurora platform. The best place to look is here. Mainly you should look at the Max volume size for Aurora MySQL cluster (128 TiB) and Max SQL table size (64 Tib).

One great resource that I highly recommend too look at is the Oracle to Aurora MySQL Migration Playbook that potentially will provide a lot of answers to your questions. Even though if you are not really migrating (you are redesigning) the hints and points to consider in this resource are valuable.

One thing else to consider is to understand how Aurora is working. Amazon Aurora provides a powerful approach on running RDBMS engines. The versions of DB's running on Aurora are modified to benefit from Aurora architecture but they are mostly drop-in compatible with what you might know from MySQL. In brief, Aurora decouples storage from the DB compute resource and some of the operations are pushed to storage layer and it is not done in the server node layer. This provides a huge performance benefit.

I highly recommend that you get acquainted as well with how Aurora works. I recommend watching this video from re:Invent 2022 that explains the inner workings in less than one hour.

At last, you can look at the best practices for Aurora MySQL that you can use as input in your design.

AWS
Behrang_KS
answered 10 months ago
  • awslearner0073
    10 months ago

    Thank You So much for the details. I went through few of them, and it seem there doesn't exist any specific issues though apart from the database size and table size restrictions.

    However, going through some blogs, I saw mysql has some specific designs with regards to its indexes as compared to others. In mysql the indexed are clustered indexes which mean the rows are embedded inside the b-tree structure of the primary key. While non clustered cases the rows are stored separately from the indexes. So many secondary indexes access may be terrible idea here. Also saw few discussions on the isolation levels, using UUID as primary keys etc. Not able to digest fully the exact pros and cons though.

    So, wanted to understand if we have any such list of points, which we need to be aware of before doing table design or application design on Aurora mysql or postgress?

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content