rubicon44TechBlog
    Search by

    Determine File Placement.

    created_at:September 17, 2022

    updated_at:September 17, 2022

    Table-of-contents

    Today, I am going to explain physical design process.

    Purposes of this article creation

    • To be able to create DB tables in MySQL in Ruby on Rails on my own.
    • To be able to do physical design.

    File Placement

    In this case, I will create physical data model for a SNS task management application on Cacoo as a deliverable.

    【What is File Placement?】

    • Once the data area for storing data is calculated, the physical arrangement of the files on the hardware and software is determined.
    • The DBMS only allows users to see the logical concepts of the database, such as tables, indexes, etc., and not the physical files that accompany them.
    • However, since it is the physical files that increase the capacity, it is necessary to understand how much capacity to take up, since this is related to the infrastructure design.

    Types of files stored in DB

    • Data files
    • Index files
    • System files
    • Temporary files
    • Log files

    ※Data files, index files, and temporary files require a large amount of disk I/O.

    ※At least, it is desirable to place “data files,” which are the most disk I/O-intensive, on a separate disk.

    Data files

    • A file that a user stores in a DB. The data stored in this file is “referenced” or “updated” by the DB.

    • Since this is a very frequently accessed file, it has the highest disk I/O load.

    Index files

    • A file in which indexes created in the DB are stored; in a DBMS, table data and indexes are managed separately.

    ※However, developers do not need to be aware of whether or not to access this file explicitly. This is because the DBMS automatically determines whether to use indexes or not.

    System files

    • Stores data for internal management of the DBMS. The developer does not need to be aware of this either.

    Temporary files

    • It is used to store temporary data inside the DBMS; it is automatically deleted when SQL processing is completed, so there is no need to be aware of it as a capacity.

    ※Temporary data includes, for example, “data after SQL subquery expansion” and “data expanded by GROUP BY clause, etc.

    Log files

    • When data in a table is changed, the data file is not updated immediately. It is spit out here once and the “data file” is updated in a batch.

    • This is also not the type of file that continuously increases capacity, as it is written in batches and then deleted.

    Log file types for each DBMS
    DBMS type Log file name
    Orale REDO Log
    PostgreSQL、SQLServer、DB2 Transaction Log
    MySQL Binary Log

    Using Read Replicas in RDS

    • One file structure that can be used to improve performance in AWS RDS is the creation of RDS Read Replicas.

    Deliverables(Physical Data Model)

    • RDS Read Replicas will not be used this time.

    References

    Word Explanation

    NONE.

    Summary/What I learned this time

    【Summary】
    NONE.

    【What I learned this time】
    NONE.

    © 2022, rubicon44TechBlog All rights reserved.