元々NoSQLしか使用していなく、今年に入って転職して初めて業務でMySQLを使用しだした。 雰囲気で使ってる感じがしてよくないなと思ったので、色々調べた。 今回まとめたのは、MySQLのInnoDBのトランザクションについての一部。 この記事でまとめる対象の事柄は、既に詳しく素晴らしい記事が存在するが、それなりに難しい内容なのでそれらを読みながら理解の補助的に、またメモ的にまとめたものが本記事。 また、「この概念を理解するには、事前に〇〇の概念の理解が必要で、それを理解するにはこの記事がわかりやすい」といった形で自分が後から振り返られるようにもまとめた。 この記事はあくまでも理解の補助と、どの概要を学んだ方が良いかということをまとめた記事なので、詳細な理解は紹介している記事を読んだ方が良い。 トランザクションの分離レベル トランザクションにはACID属性というものがあって、その中のI(I

MVCCとは MultiVersion Concurrency Controllの略で，RDBのisolation levelがREAD COMMITTEDやREPEATBLE READなんかの時のために採用しているシステムのことです． 簡単に言うと「テーブルの過去の情報を持っておく」です． isolation level DBでは同時に複数トランザクションが一つのテーブルを操作したりします． 例えばあるトランザクションがテーブルAを参照中に，Aの内容を書き換えるような別のトランザクションが存在すると，取ってきたデータに不整合が生じます． この不整合をどの程度許容するかがisolation levelです．通常4種類あります． READ UNCOMMITTED ... COMMITされていないトランザクションAの変更をトランザクションBが参照できます READ COMMITTED ...

（id=3は欠番） このようなユーザテーブルからptの値を読み取ってアプリケーション側でカウントアップしてDBに書き戻す、という場合を考えます。 今、2つのトランザクションAとBがほぼ同時にid=1のpt値を更新したとします。 ロストアップデートが起こる例 A > START TRANSACTION; A > SELECT pt FROM users WHERE id = 1; -- Aがpt=10を読み取る B > START TRANSACTION; B > SELECT pt FROM users WHERE id = 1; -- Bがpt=10を読み取る B > UPDATE users SET pt = 11 WHERE id = 1; -- Bがpt=11を書き戻す B > COMMIT; A > UPDATE users SET pt = 11 WHERE id = 1; -

PostgreSQLのリピータブルリード、Oracleのserializable、SQL Serverのスナップショット分離レベルは 更新のロストが発生したことを自動的に検出し、問題のトランザクションを中断させます。 しかし、MySQL/InnoDBのリピータブルリードは更新のロストを検出しません[23]。 抜粋:: Martin Kleppmann “データ指向アプリケーションデザイン”。 保証するトランザクションのレベルをリピータブルリードにした際に、PostgresSQLであれば更新のロストを自動検出できるが、MySQLだと自動検出できないという内容です。 PostgresSQL、MySQLでそのような状況となった場合に、具体的にどのような挙動となるのか確かめてみました。 検証環境 macOS: Big Sur 11.5.2（20G95） チップ: Apple M1 Docker:

次世代システム研究室の データストア 好きの Y.I. です。 MySQL/MariaDB/Percona Server でロックを取得して排他制御する際に便利なユーザーレベルロック GET_LOCK についてご紹介します。 DBで排他制御というとレコードロックやテーブルロックなどがあります。レコードロックにおいては、 SELECT FOR UPDATE で解放漏れなど排他制御を苦労されたことがあるかと思います。 ご紹介する GET_LOCK だと任意の文字列でロックを取得することができシンプルに排他制御が可能になります。イメージ的には MySQL に任意の文字列でフラグを立てて排他制御するイメージになります。 特に、ユーザー毎に排他制御をする要件に有効です。 例えば、ゲームのようなユーザーIDがありアイテムなどの消費や取得を厳しく排他制御したいケースなどです。 使い方 実行するコマンドは

問題 今、10GBぐらいあるダンプファイルをMySQLのデータベースにリストアしているんだけど、 いったいいつ頃終わるんでしょうね？ 答え１ 私がよく触るある環境では1GBあたり2分ぐらいかかります。 そんな感じで、いつも作業するときに、時間を気にする癖をつけておくとよいです。 SSDとHDD、物理サーバーとクラウド環境などで違うと思うので、日ごろから気にしておくのが良いです。 答え２ ダンプを投入するMySQLが、バイナリログが出力される設定になっていたら、バイナリログの容量が目安になります。 MySQLのダンプファイルはSQLのテキストデータで、バイナリログも実行されたSQLが書かれていくので、投入が終わるころには、バイナリログがだいたいダンプファイルの容量分増えます。 15分でバイナリログが5GB増えていたら、10GBのダンプが入り終わるのはあと15分、トータルで30分ぐらいかなと予

先日大きめ(といっても500万行くらい)のテーブルにインデックス付きのカラムを追加しようとして痛い目にあったので調査。 大きめのテーブルにカラムやインデックスを追加するとどうなるか 今回は単純に、「ALTER TABLE 〜 」で追加しようとしました。追加するカラムは3つで、 varchar(255) インデックスなし varchar(255) ↓のdate 型カラムとマルチカラムインデックスの形式のユニークインデックスあり date インデックスあり SQL を実行し、状況を「SHOW PROCESSLIST」で監視していたら、1つ目のカラム追加で次のような状態に… 最初にState が「copy to tmp table」状態になり、次の状態に遷移するまで1時間かかる 次にState が「Repair with keycache」状態になり、完了までに1時間かかる 次のカラム追加に対す

読者対象 ある程度データベースに関する知識を持っている，経験年数 1 年以上のバックエンドエンジニア 特定のプログラミング言語に依存する部分は含めないため，すべての SQL 使用者を対象とする また，ゼロからの丁寧な説明というよりは，リファレンス感覚で使える記事という形にまとめる。 RDBMS の対象バージョン PostgreSQL: 9.4 以降 MySQL: 8.0.28 以降 id （データ型と INSERT 時のデフォルト埋め） 導入 一般的に採用されやすいプライマリキー用の値として，以下を考える。 連番整数 MySQL では AUTO_INCREMENT， Postgres では IDENTITY や SERIAL と呼ばれるもの UUID v1: ハードウェアごとにユニークな単調増加値 UUID v4: ランダム値 UUID v7（ドラフト）: 単調増加であるタイムスタンプとラ

はじめに こんにちは！リンクアンドモチベーションで SRE をしてます川津と申します！ Web アプリケーションを開発している皆さん！ 日夜性能問題に悩まされていると思います😅 本記事では性能問題における 「CPU 使用率の見方」 に焦点をおいて話そうかと思います！ CPU あるある CPU にまつわる謎？ は大体次の２ケースかな〜、と思います。 Amazon RDS (MySQL DB) の例で挙げてみます。 ① クエリ応答が遅いからスケールアップ！ → あれ？変わらないぞ？ Web アプリ開発していると、API 応答が遅い → 原因は重いクエリ (SQL) というケースはよくあるかと思います。当然速度改善したいです。お金で簡単に解決できるならそうしたい。 例えば RDS のインスタンスタイプ db.r5.xlarge を今使っているとしましょう。 vCPU 数は 4 です。これを ２

遅刻してごめんなさい！ ここでは、「負荷が高い」とはリソースが枯渇した、あるいは枯渇しそうな状態で、かつそれがシステムに影響を及ぼしている、あるいは影響を及ぼしうる状態を指します。 負荷の傾向を見る snmpdが返す値や SHOW ENGINE INNODB STATUS などの値を見て負荷の傾向を見ましょう。 当社ではCloudforecastを使ってこれらの情報メトリクスとして収集しています。 メトリクスを常に計測しておき、通常の状態と異常な状態を可視化しておくことは非常に重要です。 CPUネックかIOネックか 負荷の原因としてどのリソースが不足しているのかを見極めます CPUが原因で詰まっているか、IOが原因で詰まっているか、という具合で大別できるのでまずそれを見ましょう。 CPUネックの場合はCPUを100%近く使いきっていることが多いです。 また、複数コアあって一部のコアを使い切

こんにちは、森田です。 AWSの公式ドキュメントでLAMPのチュートリアルがありますが、今回はこのとき作成したRDBを外部から接続できるようにしてみました。 前提 この通り進めた後の操作になります。 上の作業進めるの大変！って思った方は下記のユーザデータを使用してください。 mysqladmin password XXXXXXXX のXXXXXXXX はパスワードになりますので適宜変更してください。 #!/bin/bash yum update -y # amazon-linux-extrasを使用して Maria DB php をインストール amazon-linux-extras install -y lamp-mariadb10.2-php7.2 php7.2 # mariadb-server , Apacheのインストール yum install -y httpd mariadb-

こんにちは、AWS営業部の洲崎です！ 4連休、2本目のブログ投稿となります。 今回はRDSとRDB on EC2の比較をしてみました。 ※前回同様初心者向けです。 ※勉強不足なところが多いと思いますので、気になる点があればコメント等でご指摘頂けますと幸いです。 ※本ブログは2020/7/23時点での情報になります。 RDSとは RDSとは一言でいうとAWSのフルマネージドなリレーショナルデータベースのサービスになります。 Amazon Relational Database Service (RDS) リレーショナルデータベースのセットアップやオペレーション、スケールが簡単になります。 RDSはAWSのマネージドサービスの為、保守管理が不要となり管理の手間が楽になります。 他にも簡単にMulti-AZ構成を組むことができます。 Multi-AZを利用する事で、異なるアベイラビリティゾーンに

ウィスキー、シガー、パイプをこよなく愛する大栗です。 前回のエントリで HammerDB を使って PostgreSQL のベンチマークを取る手順をまとめました。PostgreSQL 以外にも MySQL やその互換データベースをよく利用することがあるので、今回は MySQL 編ということで HammerDB での手順をまとめたいと思います。 PostgreSQL での手順を確認したい方は、以下のエントリを御覧ください。 HammerDB HammerDB の詳細は前回の説明を参照ください。 やってみる 前回と同様の注意です。 注意 ベンチマーク結果は、データベース・ソフトウェアや提供環境により実施や公開が制限されている場合があります。例えば Oracle ではベンチマークテストの結果は公開を禁じられています。AWS では AWS Service Terms の 1.8 に準じる必要があり

はじめに MySQLをGUIベースで操作できるアプリ「MySQL Workbench」の導入から使い方までをやさしく解説していきます。 本記事ではMacOSを想定していますが、基本的なアプリの使い方は他OSでも同じだと思うので、適宜読み換えていただければと思います。 前提条件 MySQLがインストールされていること。 インストール方法は以下のサイトなどを参考にしてください。 【超簡単】macへMySQLをインストール 環境構築 公式サイトのダウンロードページから、各種OSに対応するものをダウンロードします。 （2019/4/11時点の最新バージョンは、「8.0.15」のようです） https://dev.mysql.com/downloads/workbench/ 会員登録の案内画面が出てきますが、「No thanks, just start my download.」のリンクをクリックす

データストアリプレイスの際に、既存のDBテーブルのサイズ確認をして見積もりを行う際のコマンドをよく忘れるので記載します。 『DBサイズ確認』 まずは、DBのサイズから見ていきます。 MB単位 SELECT table_schema, sum(data_length+index_length) /1024 /1024 AS MB FROM information_schema.tables GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC; +--------------------------+----------+ | table_schema | MB | +--------------------------+----------+ | sample_database1 | 5,243.14 | | s

この記事はエムスリー Advent Calendar 2022の30日目の記事です。 前日は id:kijuky による チームメンバーのGoogleカレンダーの休暇予定一覧をスプレッドシート+GASで作った でした。 AI・機械学習チームの北川(@kitagry)です。 今回はMySQLへのインサートを20倍以上高速化した話について書きます。 仕事をちゃんとしてるか見張る猫 TL; DR はじめに 今回のテーブル バイナリログを無効化する 追試 LOAD DATA INFILE 追試 テーブルの正規化 インデックスを一時的に剥がす まとめ We are hiring!! TL; DR バイナリログをオフにする LOAD DATA INFILEを使う インデックスを一時的に消す はじめに AI・機械学習チームではサイトトップからアプリに至るまで多くの推薦システムがあります。 そこでは推薦ロ

こんにちは、バックエンドエンジニアのmakinoです。先日、LINE LIVEさんとの共催イベントにて「Mirrativを支えるバックエンド開発 ~MySQLとの向き合い方~」というテーマでLTをしました。 connpass.com speakerdeck.com 今回はLTの内容から一部抜粋して、Mirrativのバックエンド開発において遭遇したMySQLに関する問題と、その対策について紹介します。 問題 その1 データ量/QPSの増加に伴って、非効率なクエリが顕在化した サービス初期の段階ではデータ量が少なかったり、ユーザーのアクティビティが少ないために問題がなかったクエリも、サービスの成長に伴ってデータ量・QPSが増加したことによって、MySQLに負荷をかけてしまうことがありました。 具体例を以下にいくつか示します。 数千件レコードのfilesort 適切なindexが利用できればM

MySQLのレプリケーションは非常に簡単に使える割には応用の幅が広いので非常に人気のある機能の一つである。レプリケーションの応用分野は例えば、 バックアップ 参照系の負荷分散 HA（高可用性） ディザスタリカバリ（サイト間レプリケーション） BI（レポーティングetc） という風にとても多くのバリエーションがある。このブログを読んで頂いている皆さんの中にもレプリケーションを使っている方は多いのではないだろうか。ご覧の通りMySQLのレプリケーション機能はミッションクリティカル分野でも利用されているが、レプリケーションの使い方が適切でないとシステムの安定稼働に支障を来してしまってDBAやシステム管理者の肉体的、精神的負担が増大してしまう。逆にレプリケーションを堅牢に運用することが出来ればマクラを高くして眠れるというものだ。レプリケーションはMySQLの代表的な機能であるので、レプリケーション