概要 MySQLでIN句を使用する時はIN句に渡す要素数に注意する必要があるとよく先輩エンジニアの方から聞いていたのですが、実際に大量の要素を渡すと何がまずいのかはっきり分かっていなかったので調べてみました。 この記事で伝えたいこと MySQLでIn句に大量の要素を渡すとまずい理由 まずい状況を回避するために気をつけるべきポイント 先に結論 MySQLでIN句に大量の要素を渡すとインデックスを貼っていたカラムだとしてもフルスキャンが発生しスロークエリになる可能性があります。 フルスキャンが発生してしまう条件はテーブルに設定してあるインデックスの内容とrange_optimizer_max_mem_size の設定値に依存しており、MySQL8でデフォルトの設定値 & シンプルなテーブルであってもおおよそ数万件の要素数をIN句に渡すとフルスキャンが発生する可能性があると考えられます。 検証環

この記事は MySQL Advent Calendar 2023 2日目の記事です。 (ちょっとフライング。。) 今回は僕がマジか、と思ってしまった MySQL の挙動について共有させていただきます。 BEGIN 中に BEGIN をすると COMMIT される 結論から言うとこれだけです ^^;; アプリエンジニアの方からお問い合わせをいただいた時にはこのことを意識したことすらなかったのでトランザクションの終了は COMMIT or ROLLBACK にてされるのだという先入観で動いていました。 ざっくりと言うとこんな感じ BEGIN; INSERT INTO testtable VALUES (1); BEGIN; このタイミングで別のターミナルを立ち上げて中身を確認するとなんと testtable には 1 という値が入っています。 個人的にはまじかーー、な挙動だったのでもしかしたら

MySQL 8.0 では、テーブル全件に対するSELECT COUNT(*)がパラレルスキャンによって高速化されましたが、MySQL 5.7 以前よりも遅くなるケースが発生したので、少し調べてみました。 テストの内容 インスタンスを再起動してバッファプールをクリアした状態から、3 つあるテーブルの全件SELECT COUNT(*)を続けて実行し、実行時間を計測 ①〜④ ではテストデータのサイズ＞バッファプールのサイズ ⑤ ではテストデータのサイズ＜バッファプールサイズ テストケースは 5 つ ①：test_short[1]→test_long[2]→test_long_uk[3]の順に、SELECT COUNT(*) FROM テーブル名をそれぞれ 10 回以上連続で実行する test_short→test_long→test_long_ukを 1 セットとして 10 回繰り返すのではなく

駅メモ！開発基盤チームです。 今回はサービスで利用している Amazon Aurora MySQL を v2 から v3 へ移行したときのことを書きます。 概要 駅メモ！をはじめとする弊社のサービスでは、データストアとして Amazon Aurora MySQL(以降 Aurora MySQL) を利用しています。すでにアナウンスされている通り、Aurora MySQL v2 は 2024 年 10 月 31 日に 標準サポート終了を迎えるため、Aurora MySQL v3 への移行が重要な課題になっていました。これに対し、駅メモ！開発基盤チームでは綿密な計画を立て、今年の初め頃に無事に移行を完了させることができました。このエントリはその時にどんな手法を取ったかを書きます。誰かの参考になればと思います。 やったこと 最初にざっくりとした流れを示します。 調査 新機能・廃止された機能の調査

対象読者 Webアプリケーションエンジニア SQLチューニングの基本を学習ずみ 使用環境 MySQL ５.6 一覧表示の抱えるリスク webアプリケーションにおけるデータの一覧表示処理は、SQLが遅くなる可能性の高い処理です。 時間が経つとともにデータが増え、複数のテーブル結合で走査件数が多くなりやすいことに加え、大抵の場合、ソート機能を備えているからです。 このソート処理は、走査件数が多くなるほどにコストが非常に高まってくる問題児です。 この記事ではそんなソート処理への対策について、自分自身の理解を深めるためにも内容をまとめてみたいと思います。 大規模のデータの全件を処理するとコストが高いので、ページングなどでLIMIT句をつけると良いと考えても、LIMIT句が適用されるのはSQL評価順序の最後なので、その前に処理時間のかかる箇所があると改善されません。 遅い箇所を調べていくのですが、S

データベース 技術顧問 @mita2 FYI: MySQL の SQL クエリチューニングの要所を掴む勉強会を開催しました！ - ANDPAD Tech Blog

これはなに ども、レバテック開発部のもりたです。 今回はMySQLでのスロークエリログについて調査してまとめました。 スロークエリログといえば古くからパフォーマンスチューニングの力強い味方といったふうに語られることも多いですが、最近はクラウドで使える便利なツールも生まれています。この記事ではスロークエリログの一般的な使い方を紹介するとともに、他のツールとの比較や、どんな場面でスロークエリログが役に立つのか、また役に立たない場合はどんなツールを利用することができるのかについてまとめました。 足りないところなどあればおおいにマサカリ投げていただけると幸いです。 記事の流れ 記事の流れ この記事はそこそこ長いので、初めに記事の流れを解説します。適宜読み飛ばしてください。 なぜスロークエリログなのか ここではそもそもスロークエリログをなぜ確認したいのかみたいなところを説明します スロークエリログの

メディア統括本部 サービスリライアビリティグループ（SRG）の鬼海雄太（@fat47）です。 #SRG（Service Reliability Group）は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。 本記事は、MySQ

CHAR型 CHAR型を使用した場合、固定長の文字列として保存されます。 保存できる文字列の長さは0文字以上255文字以下の範囲で指定できます。 これはどういうことかというと、 もし格納された文字列がテーブル作成時に指定された文字数よりも短かった場合、文字列の右側の末尾にスペースで補完されるということです。 例えば、以下のようなchar_testsテーブルがあった時に、char_columnカラムに'a b'を保存しようとすると、DBには'a b 'のように保存されます。 VARCHAR型 VARCHAR型を使用した場合、可変長の文字列として保存されます。 先ほどのchar_testsテーブルの例で言うと、char_columnカラムに'a b'を保存しようとすると、DBには'a b'のように、そのままの文字列が保存されます。 また、VARCHARでは可変長で保存するために、長さの情報を2

データベースアップグレード後の性能劣化、イヤですよね。 去る2023年某日、弊社ではAmazon Aurora MySQL 互換エディション 2 (MySQL 5.7 互換) から Aurora MySQL 互換エディション 3 (MySQL 8.0 互換) にアップグレードしました。当時の背景やアップグレードに関する知見は以下の記事をぜひ読んでみてください。 blog.smartbank.co.jp ソフトウェアバージョンアップをするとき、旧バージョンが抱えていた問題の解決などの恩恵を我々は期待します。しかし時には予期せぬデグレーションに遭遇することもあります。我々のMySQL 8.0へのアップグレード前後においてもいくつかの問題に遭遇しました。 本記事ではそんな問題の一つ、MySQL 8.0のオプティマイザが選択したセミジョイン最適化が性能劣化を引き起こした事例と解決方法について紹介し

はじめに SQLを利用するときには極力NULLを使わないようにしようと言われることが多いです。 SQLではNULLのデータが入っていることで奇妙な挙動をしてしまうことがあります。 その中の一つでCOUNT関数が存在します。 COUNT関数はその名の通りで条件に合致したデータ数を取得するための関数です。 例えば、条件に合致するデータが3件ある場合は3 と返却を行います。 簡単ですね。 ただ、データ内にNULLが含まれていると、COUNT関数の対象によって、返される値が異なる場合があります。 具体的には以下の通りです。 COUNT(*) NULLのカラムを数える COUNT(col_name) NULLのカラムを数えない なかなかとややこしいですね。。。 実際に例を見てみましょう。 例 以下のようなテーブルを考えます。 col_name

こんにちは、駅奪取エンジニアの id:kimkim0106（旧: id:kaoru_k_0106）です。 今回の記事は、駅奪取でテーブルにレコードが「無ければ INSERT、あれば UPDATE」（いわゆる UPSERT）をする箇所で Duplicate entry が出ていたのを修正したり、未然に防ぐ実装をしたときに得られた知見です。 このような処理はよく使われますが、うまく実装しないとエラーが発生したりパフォーマンスの問題が生じたりします。 この記事では、自分が試した方法のメリット・デメリットについて説明します。 目次 前提条件 Duplicate entry とは 1. Duplicate entry が出たらトランザクション自体をやり直す 2. INSERT ... ON DUPLICATE KEY UPDATE 3. とりあえず INSERT して Duplicate entry

はじめに こんにちは。SRE部ECプラットフォーム基盤SREブロックの石田です。 本記事では、Aurora Serverless v2を本番導入するにあたってどのような検討をし、どのように導入していったか、また導入後に得られた効果について紹介します。 はじめに Aurora Serverless とは 背景 比較検討 比較内容 方針の決定 アーキテクチャ 導入 1. Aurora Serverless v2を手動で構築 2. AWS CloudFormationでProvisioned型Aurora MySQLバージョン3を再構築 3. AWS CloudFormationでAurora Serverless v2に移行 4. 負荷試験・障害試験 負荷試験 障害試験 導入により得られた効果 柔軟なスケーリング インフラコスト 最後に Aurora Serverless とは Aurora

シンプルフォーム株式会社で SRE をしている守屋です。 本記事では Aurora MySQL の OOM(メモリ不足)エラーについて、原因となるクエリを特定するために役立つ Tips を弊社での実例を交えてご紹介します。 発端 突如 Slack に鳴り響く不吉な通知。 「パターン青！障害です！！」 どうやら本番環境の Aurora クラスターがフェイルオーバーしてアプリケーションが DB コネクションエラーを引き起こした模様です。幸いインスタンスは冗長化していて Aurora のフェイルオーバーは高速であるため、ユーザー目線では瞬断が発生した程度の比較的影響が小さめな障害に留まりました。しかし SRE としては捨ておけない状況です！早速原因の調査を始めました。 フェイルオーバーの原因 結論から言うとメモリ使用量がスパイクして OOM エラーが発生したことが原因でした。根拠としては Aur

これはなに ども、レバテック開発部のもりたです。 今回はSQLのサブクエリについてまとめます。仕事でクエリを書く際、サブクエリは頻出の構文だと思うんですが、同時にサブクエリの書き方を完全に理解しているよという人は案外少ないのではないでしょうか？[1] 実際、MySQLの公式ドキュメントを見ると12ページくらいを割かれており、意外と奥深いのがサブクエリです。使いこなせると便利ですし、何よりちょっとSQLのコツみたいなのがわかって面白いよ、ということで記事にしてみました。 前提 この記事は以下の前提を含んでいます。 環境 MySQL8.0系 読者の知識 なんとなくサブクエリが書ける けど相関サブクエリとかになると「あーっ」つってGoogle meetを閉じてしまうくらいのレベル感 記事のボリューム 18,000文字 おれの卒論が20,000文字だった マサカリ 間違ってたら投げてくれ〜〜 それ

このエントリでは、Time-based SQLインジェクション、すなわち時間差を利用したSQLインジェクションが意外に実用的だったという報告をします。デモ映像ありです。 はじめに Time-based SQL Injectionという攻撃があります。これはブラインドSQLインジェクションの一種で、ある条件の場合に一定時間（例えば5秒）スリープし、そうでない時との応答時間の差で情報を盗もうというものです。1回のHTTPリクエストで1ビットの情報が得られるので、それを積み重ねることによって、いくらでも情報を盗めるはずです…理論的には。 しかし、「理屈はそうでも、時間が掛かりすぎるよね」ということで、深くは追っかけていませんでした。SQLインジェクションの検査には有効でも、悪用としての実用性はあまりないと考えていたのです。 きっかけ きっかけは、以下のYahoo!知恵袋に以下の質問です。 SQL

はじめに MySQL(InnoDB)でSQLのパフォーマンスチューニングをするときに役に立つ知識をエッセンスとしてまとめました。結合(JOIN)やB-treeインデックスの探索の仕組み、実行計画の基本的な見方を紹介します。 想定する読者は、SQLのパフォーマンスを改善する必要があるが実行計画をみてもいまいちピンと来ない方です。インデックスの作成の経験や、複合インデックスやカーディナリティの知識があることを前提にしています。目標は、実行計画の内容がよく分からない読者が、実行計画をみただけでクエリが実行される様子をイメージでき、自信を持ってクエリの改善にあたることができるようにすることです。 ストレージエンジンはInnoDBを前提としています。また、インデックスはB-treeインデックスを想定しています。全文検索の転置インデックスや空間検索のR-treeインデックスについては触れません。 イン

シードデータで動作確認して大丈夫だったのに、本番反映してみたら想定してなかった挙動・エラーが出た😱そんな経験はありませんか。 恥ずかしながら私は今までに何回もありました。機能開発だけじゃなくバッチやマイグレーションなんかでも発生しがちなコレ。またはシードデータで動作確認できても、本番データでも通用するか検証ができないままプルリクを作る、なんていうこともあると思います。今回はこちらを無くす試みをしたお話です。 「もう本番DBで開発しちゃえばいいじゃない」の問題点 この課題を解決するには、極論すると本番DBで開発するしかないのですが、そうなると言うまでもなく以下の問題が出てきます。 レビュー通過してないコードが本番に影響を与える トライ＆エラーができない 個人情報をはじめとするセンシティブな情報が開発者の端末に漏れる データ量が多すぎてローカルに持ってこれない しかし言い換えると、これらをク

駅メモ！チームエンジニアの id:yumlonne です。 この記事では Redis の sorted sets で実装していたランキング処理を MySQL に移行した仕組みを紹介します。 背景 駅メモ！には複数のランキングがあり、Redis の sorted sets を使うことでパフォーマンスの高いランキング処理を実現していました。 中にはリリースからの全期間に渡るデータを利用するランキングもあり、Redis のメモリ使用率は日に日に増えていく一方でした。 何度か Redis をスケールアップしてメモリを増やすことで対応していましたが、根本的に対応しなければ今後も Redis をスケールアップもしくはスケールアウトさせ続けるしか選択肢がなく、コストが増え続けてしまう状況でした。 調査したところ、一部のランキングがメモリ使用率の 2/3 程度を占めていることが判明しました。 そこで、その

この記事は新野淳一氏のブログ「Publickey」に掲載された「GitHub、1200台以上のMySQL 5.7を8.0へアップグレード。サービス無停止のまま成功させる」（2023年12月12日掲載）を、ITmedia NEWS編集部で一部編集し、転載したものです。 米GitHubが提供するGitHub.comは、世界最大のソースコード管理システムを始めとするソフトウェア開発者向け支援サービスを提供しています。 そのGitHub.comはRuby on Railsで構築されており、同社はつねにRubyとRuby on Railsをアップデートし続けていることを今年（2023年）4月に明らかにしています。 参考：GitHubは200万行規模のRailsアプリケーションであり、毎週RailsとRubyを最新版にアップデートし続けている そして同社はこのGitHub.comを支える1200台以上