Mysql의 InnoDB는 MVCC ( Multi Version Concurrency Control )을 사용하며, 이는 이전 버전으 데이터와 rollback을 지원하기 위해서 변경된 row의 이전 데이터를 commit/rollback이 완료될 때까지 보관하고 있어서 한다. rollback을 위해서 이러한 데이터는 undo log에 저장이 되며, linked list 형태로 관리되기 때문에 History List 이며, 이 list가 길어지는 혹은 증가하고 있다는 것은 undo 데이터가 많아지고 있으며 transaction이 여전히 commit/rollback 되지 않고 있다는 것을 의미한다. undo데이터가 삭제되는 순간은 undo데이터가 더이상 필요 없어지는 순간이며, 이 것이 삭제 되지 않는 것은 어떤  transaction에서 이 데이터가 계속 필요하다고 혹은 참고하고 있다고 판단되기 때문이다. 

 

History List Length, undo로그가 증가되는 원인은 다음과 같다.

 

- read나 write에 long transaction : 

 

mysql의 default isolation model은 REPEATED_READ이므로 다른 transaction이 해당 row를 변경하고 commit했더라고 그 transaction이전부터 select를하고 지금도 하고 있다면 undo는 보관을 하고 변경되기 이전의 데이터를 select에서 repeat read했을 때에도 제공해야한다. ( 참고로 대부분의 사람들이 착각하는데 select 등의 read는 transaction이 없는 걸로 오해하는데, select도 tranaction과 항상 같이 고민해야하며, transaction의 범위 내에 있다고 생각해야한다. ) 하여 read에 long transaction도 HLL을 증가시킬수 있다.

 

- heavy write

 

많이 기록하는 경우에는 당연히 HLL이 발생할낟.

 

- commit을 하지 않은 user session

 

이 경우는 흔지 않지만, 찾기가 어려우며, DB에 문제를 줄 수 있다. 예들 들어 auto-commit false로 데이터를 한건 넣고 기다린다고 하면, 

commit이 아직 안되었기 때문에 HLL이 purge되지 않고 이후의 HLL에 기록되는 transaction도 purge 되지 않는다. user sessino이기 때문에 timeout이 적용안되서 rollback이 되지않으면서, idle하고 그 이후에 DB에 영향을 주기 않고 있기 때문에 각종 monitoring 지표에서 찾아내기가 어렵다. 해당 user session을 종료하기 전까지는 HLL은 계속 증가한다. 만약 HLL이 계속 증가하면 언젠가는 DB shutdown 혹은 restart상황으로 갈수 있다.

 

조치

 

long running transaction을 찾아서 commit 혹은 rollback 해야한다.

SELECT a.trx_id, 
      a.trx_state, 
      a.trx_started, 
      TIMESTAMPDIFF(SECOND,a.trx_started, now()) as "Seconds Transaction Has Been Open", 
      a.trx_rows_modified, 
      b.USER, 
      b.host, 
      b.db, 
      b.command, 
      b.time, 
      b.state 
FROM  information_schema.innodb_trx a, 
      information_schema.processlist b 
WHERE a.trx_mysql_thread_id=b.id
  AND TIMESTAMPDIFF(SECOND,a.trx_started, now()) > 10 
ORDER BY trx_started

 

 

 

 

 

https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/proactive-insights.history-list.html

 

InnoDB 기록 목록 길이가 크게 늘어남 - Amazon Aurora

이 페이지에 작업이 필요하다는 점을 알려 주셔서 감사합니다. 실망시켜 드려 죄송합니다. 잠깐 시간을 내어 설명서를 향상시킬 수 있는 방법에 대해 말씀해 주십시오.

docs.aws.amazon.com

 

https://lefred.be/content/a-graph-a-day-keeps-the-doctor-away-mysql-history-list-length/

 

A graph a day, keeps the doctor away ! – MySQL History List Length

This is the second article of the series dedicated to MySQL trending. As I wrote before, understanding your workload and seeing the evolution of it over time can help anticipating problems and work…

lefred.be

 

https://www.percona.com/blog/chasing-a-hung-transaction-in-mysql-innodb-history-length-strikes-back/

 

Chasing a Hung MySQL Transaction: InnoDB History Length Strikes Back

In this blog post, I’ll review how a hung MySQL transaction can cause the InnoDB history length to grow and negatively affect MySQL performance.

www.percona.com

https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html

 

MySQL :: MySQL 8.0 Reference Manual :: 15.3 InnoDB Multi-Versioning

15.3 InnoDB Multi-Versioning InnoDB is a multi-version storage engine. It keeps information about old versions of changed rows to support transactional features such as concurrency and rollback. This information is stored in undo tablespaces in a data str

dev.mysql.com

https://www.percona.com/blog/mysql-performance-implications-of-innodb-isolation-modes/

 

MySQL performance implications of InnoDB isolation modes

Peter Zaitsev explains InnoDB Transaction Isolation Modes, their relationship with MVCC, and how they impact MySQL performance.

www.percona.com

 

저작자표시 (새창열림)

'개발관련 > Mysql' 카테고리의 다른 글

AWS Mysql Query Log : slow_query_log : DB 부하 Query 추출  (0) 2023.08.03
Mysql user access  (0) 2021.02.02

Database

•     RDBMS

•     RDS

•     Aurora

•     Redshift

•     NoSQL

•     DynamoDB

•     ElastiCache

•     DocumentDB

•     Neptune

•     QLDB

•     Keyspace

•     Timestream

https://aws.amazon.com/products/storage/

RDS

•    Relational Database Services

•    DB Engine

•    Aurora Mysql, Aurora PostgreSQL compatitable

•    Oracle, SQL Server, MySQL, PostgreSQL, MariaDB

•    Compared to EC2 hosting • manual install, management

•    RDS hosting

•    Install, DB Upgrade, Backup/Recovery

•    Availability, scalability

•    Can not access to OS, no env change • Can not get db superuser

https://towardsdatascience.com/running-mysql-databases-on-aws-ec2-a-tutorial-for-beginners-4301faa0c247?gi=475061565578

RDS



https://towardsdatascience.com/running-mysql-databases-on-aws-ec2-a-tutorial-for-beginners-4301faa0c247?gi=475061565578

High Availability

•    24x7

•    Up and Running and Accessible

•    <> SPOF

•    Backup/Recovery > Up/Run/Access

•    How to reduce time

•    Redundant

•    Single Master, Active/Standby, Master/Slave ( cf, multi master )

•    Failover with Failover Detection ( Not Active ) • Performance Test > Load Test

https://medium.com/awesome-cloud/aws-difference-between-multi-az-and-read-replicas-in-amazon-rds-60fe848ef53a https://aws.amazon.com/premiumsupport/knowledge-center/rds-fail-over/

RDS HA

https://aws.amazon.com/rds/ha/ https://aws.amazon.com/rds/features/multi-az/

RDS HA

https://medium.com/awesome-cloud/aws-difference-between-multi-az-and-read-replicas-in-amazon-rds-60fe848ef53a

Database HA Failover/SwitchOver

•    FailOver

•    Availability Zone failures

•    Primary DB instance compute node failures

•    Networking issues with the primary DB instance

•    Storage or Amazon Elastic Block Store (Amazon EBS) volume issues

•    Cluster Endpoint

•    FailOver Time • Min 30-60s • Binlog

•    Sync/Async

•    Format : STATEMENT/ROW/MIX

https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/UserGuide/Concepts.MultiAZ.html#Concepts.MultiAZ.Failover https://aws.amazon.com/premiumsupport/knowledge-center/rds-fail-over/ https://severalnines.com/blog/introduction-failover-mysql-replication-101-blog/

https://severalnines.com/blog/database-switchover-and-failover-drupal-websites-using-mysql-or-postgresql/

RDS Backup/Restore

•    Backup

•    Daily Backup, 35 days keep on S3

•    Snapshot

•    User defined

•    Restore ( cf. recovery )

•    From backup

•    From instance

•    Recovery

•    Checkpoint

•    Redolog

•    Point In Time Recovery

•    Last 5 min

https://aws.amazon.com/rds/features/backup/

https://learn.microsoft.com/en-us/answers/questions/214377/recovery-vs-restore

RDS Monitoring

•    CloudTrail, CloudWatch

•    Standard

•    Ehhanced

•    Event Notification

https://docs.aws.amazon.com/ko_kr/vpc/latest/userguide/what-is-amazon-vpc.html https://aws.amazon.com/blogs/architecture/one-to-many-evolving-vpc-design/

RDS Security/Encryption

•    Data In transit

•    Data at rest

•    Encryption

•    Storage

•    Automated Backup

•    Read Replica

•    Standby Replica

•    Snapshot

•    Encryption Key

•    TDE

https://aws.amazon.com/rds/features/backup/

Aurora

•    RDS Use EBS

•    Auror use distibuted storage fleet

•    Share Storage, Cluster Volume, Single Virtual Volume

•    Instance Node <> Data Storage

•    Add storage by 10GB unit

•    Storage Replication

https://www.youtube.com/watch?v=0IeLKyBl3CM

https://www.lastweekinaws.com/blog/aurora-vs-rds-an-engineers-guide-to-choosing-a-database/

https://assets.amazon.science/dc/2b/4ef2b89649f9a393d37d3e042f4e/amazon-aurora-design-considerations-for-high-throughput-cloud-nativerelational-databases.pdf

Cluster Volume


https://aws.amazon.com/blogs/database/amazon-aurora-as-an-alternative-to-oracle-rac/ https://aws.amazon.com/ko/blogs/korea/amazon-aurora-under-the-hood-quorum-and-correlated-failure/

RDS Mysql vs Aurora Mysql


https://medium.com/awesome-cloud/aws-difference-between-amazon-aurora-and-amazon-rds-comparison-aws-aurora-vs-aws-rds-databases-

60a69dbec41f

https://d1.awsstatic.com/events/reinvent/2019/REPEAT_Amazon_Aurora_storage_demystified_How_it_all_works_DAT309-R.pdf

ACID / BASE

•    ACID

•    Atomicity

•    Consistency

•    Isolation

•    Durability

•    BASE

•    Basically Available

•    Soft State

•    Eventually Consistent

https://www.geeksforgeeks.org/acid-model-vs-base-model-for-database/

CAP Theorem / PIE

•    CAP

•    Consistency

•    Availability

•    Partition Tolerence

•    PIE

•    Pattern Flexibility

•    Infinite Scale • Efficiency

https://chbussler.medium.com/database-theorems-444f27f911c6 https://en.wikipedia.org/wiki/CAP_theorem http://eincs.com/2013/07/misleading-and-truth-of-cap-<span s

Distribution

•    Partition : key

•    Split into

•    Sharding : key

•    Split into, manual rebalancing

•    Isolation : model • Gather into

•    Cluster : block

•    Gather into, auto rebalancing

https://chbussler.medium.com/database-theorems-444f27f911c6 https://en.wikipedia.org/wiki/CAP_theorem http://eincs.com/2013/07/misleading-and-truth-of-cap-theorem/ https://learning-notes.kovacevic.dev/Databases/Theory/PIE-Theorem

Redshift

•    DataWarehouse

•    Based on PostgreSQL

•    Columnar Storage

•    Multiple Compute Node • Dense, Leader

•    Data Distributed in Compute Node • EVEN, KEY, ALL

•    Redshift Spectrum

•    Read from S3

https://docs.aws.amazon.com/ko_kr/vpc/latest/userguide/what-is-amazon-vpc.html https://aws.amazon.com/blogs/architecture/one-to-many-evolving-vpc-design/

DynamoDB

•       NoSQL, key-value

•       Collections – Table

•       Atrribute – Column (key-value pair)

•       Item – Row – one or more attribute

•       Partition

•       PrimaryKey, datatype

•       PartitionKey, Hashkey : Primary Key One Partition : Simple Primary Key

•       Sort Key, Range Key = Composite Primary Key

•       Partition Key = Not Unique, Partition Key + Sort Key : Unique

•       Scan, Query

•       Secondary index

•       Global secondary index

•       Local Secondary Index

•       Base table, projected attributes

•       DAX : DynamoDB Accelerator : memory caching https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Introduction.html

DocumentDB

•    NoSQL

•    Managed service

•    Compatible with MongoDB

•    3 AZ, 6 Replica = 99.99 Availability

•    PITR : Point In Time Recovery, Saved in S3 • Ecryption Support

https://docs.aws.amazon.com/documentdb/latest/developerguide/what-is.html

ElastiCache

•     Caching

•     Mananged Service

•     Redis, Memcached

•     Unser 1 ms

•     Shard = cluster node

•     Every node has same type, same size

•     SSSN : 1 node, no shard

•     SSMN : 1 shard, 6 node ( rw 1node, max 5 read node ) • MSMN : M shard, max 6 node per shard, softlimit 90 node • Multi shard :

https://docs.aws.amazon.com/AmazonElastiCache/latest/red-ug/WhatIs.html

Neptune

•    Managed Service

•    Graph Database, Germlin, RDF, SPARQL

•   

3 AZ, 6 replica : 99.99 Availability • Eg : social network

 

 

https://docs.aws.amazon.com/neptune/latest/userguide/intro.html https://jiwonny.github.io/projects/aws-neptune-1/

https://aws.amazon.com/blogs/database/populating-your-graph-in-amazon-neptune-from-a-relational-database-using-aws-database-migrationservice-dms-part-2-designing-the-property-graph-model/

QLDB

•    Quantum Ledger

•    Journal

•    Blockchain

•    Amazon Ion

•    PartiQL

https://docs.aws.amazon.com/qldb/latest/developerguide/what-is.html https://docs.aws.amazon.com/qldb/latest/developerguide/ledger-structure.html https://amazon-ion.github.io/ion-docs/ https://partiql.org/

Keyspace ( for Apache Cassandra)

•    Managed service

•    Compatible with Apache Cassandra

•    Performance at scale

•    Region service

https://docs.aws.amazon.com/keyspaces/latest/devguide/what-is-keyspaces.html https://www.youtube.com/watch?v=bwGPdj1nLwg

https://www.youtube.com/watch?v=4ZnlZCbbN_A&list=PLORxAVAC5fUVMfzfZSJmroZvnaZWVb1QT&index=9

 

 

Timestream

•    Time series Database

https://docs.aws.amazon.com/timestream/latest/developerguide/what-is-timestream.html

 


QnA

저작자표시 (새창열림)

'AWS > AWS Ceritified Solutions Architect' 카테고리의 다른 글

08. CloudTrail, CloudWatch, Config  (0) 2023.08.21
07. IAM  (0) 2023.08.21
05. VPC  (0) 2023.07.21
04. Storage  (0) 2023.07.21
03. EC2 and EBS  (0) 2023.07.08

+ Recent posts

티스토리툴바