Performance Design.

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.

Performance Design Process

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

【What is Performance Design?】

• Performance Design is about overall database performance, such as data response time. Performance tuning is performed to meet the performance defined in the requirements specification and so on.
• Performance tuning is to devise ways to speed up DBMS access.
• A typical example is SQL performance tuning, which aims to increase speed by improving the way SQL is written, but there are various other methods.

Performance Design

【Determine the required performance】
※For new development, first create a prototype and conduct load testing before determining both capacity and performance. Then, performance tuning is performed to meet the necessary performance requirements.

Performance Tuning Methods

• Denormalization, Adding Derived Attributes
• Index Creation
• SQL Performance Tuning
• Reduce Transaction Isolation Levels
• Partitioning, Disk Distribution

※Changing the way SQL is written and the table structure makes a big difference in performance.

※It is important to optimize indexes, and an optimizer is used for this purpose.

Denormalization, Adding Derived Attributes

• If a table is normalized, it must be joined when it is used. Since this can cause a drop in performance, processing speed is a priority and a table may not be normalized.

Index Creation

• Creating indexes can speed up DB access. However, depending on the amount of data and update frequency, using indexes may actually slow down the process, so it is important to design indexes appropriately.
• To optimize indexes, use optimizers (software for optimization), etc.

SQL Performance Tuning

• The way SQL is written can make a difference in performance.
• For example, the IN and EXISTS clauses used in the subquery syntax often differ in performance, even when the process returns the same results.

Reduce Transaction Isolation Levels

• The higher the transaction isolation level, the more consistent it is, but the worse the performance.
• When performance is required, it is possible to improve overall transaction throughput by purposely lowering the isolation level.

Partitioning, Disk Distribution

• Performance can be improved by distributing data to multiple locations to avoid database access concentration.
• This can be done by logically partitioning or physically distributing disks.

Deliverables(Physical Data Model)

• URL：NONE.

References

• 「徹底攻略 データベーススペシャリスト教科書 令和3年度」：https://www.amazon.co.jp/%E5%85%A8%E6%96%87PDF%E3%83%BB%E5%8D%98%E8%AA%9E%E5%B8%B3%E3%82%A2%E3%83%97%E3%83%AA%E4%BB%98-%E5%BE%B9%E5%BA%95%E6%94%BB%E7%95%A5-%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%82%B9%E3%83%9A%E3%82%B7%E3%83%A3%E3%83%AA%E3%82%B9%E3%83%88%E6%95%99%E7%A7%91%E6%9B%B8-%E4%BB%A4%E5%92%8C3%E5%B9%B4%E5%BA%A6/dp/4295009903/ref=tmm_pap_swatch_0?_encoding=UTF8&qid=&sr=

Word Explanation

NONE.

Summary/What I learned this time

【Summary】
First create a prototype and conduct load testing before determining both capacity and performance. Then, performance tuning is performed to meet the necessary performance requirements.

【What I learned this time】
NONE.