株式会社ラクーンホールディングスのエンジニア/デザイナーから技術情報をはじめ、世の中のためになることや社内のことなどを発信してます。 bashパフォーマンスMySQLInnoDBDB設計インデックス こんにちは、羽山です。 今回は MySQL のプライマリキーに UUID を採用する場合に起きるパフォーマンスの問題を仕組みから解説します。 MySQL(InnoDB) & UUID のパフォーマンスについては各所でさんざん議論・検証されていますが、論理的に解説した記事が少なかったり一部には誤解を招くようなものもあるため、しっかりと理由から理解するための情報として役立つことができればと思っています。 UUID と比較される古き良き昇順/降順のプライマリキーはというと、 MySQL の InnoDB において良いパフォーマンスを出すために縁の下の力持ちのような働きをしてくれているケースが実は少な
Deleted articles cannot be recovered. Draft of this article would be also deleted. Are you sure you want to delete this article?
みなさん、おはようございます！ CARTA fluct エンジニア の なっかー@konsent_nakka です。 CARTA TECH BLOG アドベントカレンダー 12/14ということで、普段DBのロックについてあまり意識したことがない人に向けた実は覚えておきたいロックについての知識をざっとまとめてみました。 とりあえずこれだけ読んでおけば最低限は困らない、もし何か困った時にはあそこで出てきた内容をもう少し深く調べて見るか、というきっかけになれば良いなと思います。 厳密な定義よりも普段DBを扱う中でロックについてあまり意識したことがないような人にもすっと入ってくるように簡単な表現を優先して書いていますがご了承ください。 目次 留意事項 排他ロックと共有ロック トランザクション分離レベル SELECTのロックレベルを変更する 共有ロック: LOCK IN SHARE MODE 排他ロ
Redis互換で25倍高速とする「Dragonfly」が登場。2022年の最新技術でインメモリデータストアを実装 Redisやmemcachedに代表されるインメモリデータストアは、高速なデータアクセスを要求される場面で使われています。 このインメモリデータストアを2022年の最新技術を用いて設計、実装することで、Redis/memcached互換を実現しつつRedisの25倍高速とする「Dragonfly」が登場しています（開発元のアナウンス、GitHub）。 Redisやmemcachedが登場した十数年前と比べて、現在ではCPUのマルチコア化やI/Oの高速化、メモリの大容量化など、ハードウェア技術が大きく進化しています。 これらを最大限活用する設計と実装を取り入れることでRedisやmemcachedよりも大幅な高速化と高効率化を目指したのがDragonflyです。 採用した主な技術
2022年7月13日にカラーミーショップで提供開始した「副管理者機能」のアップデートにあたって、従前の挙動を変えずにデータベーススキーマの構造を変える必要がありました。また、サービスの提供を停止することなく、スキーマの構造の変更を進める必要がありました。 この記事では、サービスを停止せずにデータベースの構造を徐々に変更するデータベースリファクタリングをどのように進めたかについて紹介します。 「データベースリファクタリング」とは データベースリファクタリングについて体系的に述べた書籍として“Refactoring Databases”があります。この本では、データベースリファクタリングのさまざまなパターンにおいて、スキーマの変更、データマイグレーション（既存データの移行）、アプリケーションの変更それぞれをどのように進めるべきかについて解説しています。ここでは、”Refactoring Dat
はじめに こんにちは。calloc134 です。 前のハッカソンイベントで、UUID をプライマリキーに利用するかどうかの議論がありました。 結果的にはあまりパフォーマンス要件の高くないアプリケーションであったため、プライマリキーとして UUID を採用することにしたのですが、イベント終了後に気になったため、調査を行いました。 今回は、この調査の結果を元に、MySQL と PostgreSQL におけるインデックスの内部構造の違いと、UUID をプライマリキーにする際の問題についてまとめてみたいと思います。 インデックスの概要 インデックスとは インデックスとは、データベースのテーブルに対して、アクセスを高速に行うための指標となる構造のことです。 インデックスとは日本語で索引ですが、まさに辞書の索引のように、アクセスにおいての手助けをしてくれます。 より具体的に解説すると、データベースにお
最近、MySQLのパラメータの調整をする機会があったのですが、特定のパラメータを変更した際に、メモリの消費量にどう影響するのか、というのを調査する際に、インターネッツを彷徨ったところ、サイトによって書いてあることにバラつきがあったので、自分でもまとめてみることにした。 結論から書くと、参考にしたのは以下のオライリーの書籍「MySQLトラブルシューティング」で、記述が一番わかりやすく書かれていた。 このエントリは、この書籍の 「3.9.3 オプションの安全値を計算する」 にて記載がある内容をまとめたものになる。 MySQLトラブルシューティング 作者:Sveta SmirnovaオライリージャパンAmazon 著者について Sveta Smirnova(スヴェータ・スミルノヴァ): Oracle社MySQLサポートグループ・バグ検証グループの主席テクニカルサポートエンジニアとして毎日MySQ
RDSが朝のピーク時間帯にI/Oスパイクで応答不能になりました。前日夜にリリースしたdeleted_atへの単独インデックスが原因です。stagingのEXPLAINでは複合インデックスが正しく選択されていたので、レビューでは検出できていません。 根っこにあるのはMySQL 8.0 innodb_stats_methodのデフォルト値nulls_equalと、IS NULLに対するコスト計算の噛み合わせです。8.0系で現在も未修正のバグに類する挙動で、NULL多数カラムへの単独インデックスがトリガーになります。 テーブルとクエリ 問題が起きたのはチケット管理SaaSのticketsテーブルです。ソフトデリートでdeleted_atを持つよくある設計です。 CREATE TABLE tickets ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, w
こんにちは。株式会社スマートバンクのサーバーサイドエンジニアをやっておりますid:moznionです。 すっかり秋めいてきましたね。秋といえばMySQL*1、ということで今回は先日解消した「MySQLのロックに起因するブロックタイムアウト」のトラブルシューティングついて記していきたいと思います。 事の発端 ある時を境にSentryに ActiveRecord::LockWaitTimeout というエラーがしばしば報告されるようになっていました。 SentryにActiveRecord::LockWaitTimeoutが上がってきている様子 Mysql2::Error::TimeoutError: Lock wait timeout exceeded という文言から、MySQL上でロックを取っている他のクエリにブロックされ、そのブロックが長時間に渡ったため自クエリがタイムアウトしてabor
こんにちは otsubo です。MySQL (InnoDB) のロックについて整理する機会があったので記事にします。 はじめに 全ての ロックタイプ を網羅するのは大変なため、 レコードロック ギャップロック ネクストキーロック を中心にまとめます。この3つはトランザクション内で UPDATE、DELETE、 SELECT ... FOR UPDATE / SHARE するときに獲得されるロックです。INSERT 時のインサートインテンションロックもちょっとだけ扱います。テーブルロックやメタデータロックは扱いません。 InnoDB ではクエリによって特定の「範囲」がロックされることがあります。これを知らないと思わぬ不具合に繋がります。 範囲をロックして InnoDB は何を実現したいのか、どのような仕組みでロックするのかを知っておくと開発に役立ちます。 記事を通して下記を前提とします。 M
前回の在籍も含めると、累計9年半、本家MySQLチームでInnoDBの性能改善をターゲットに開発の仕事してきました。5.7でのb-tree index scaleや、8.0の初期の新機能で入ってしまった性能問題の修正など貢献できましたが、ここ数年は開発が進む度に導入される性能劣化に追いつけなくなってしまいました。悪いコードを見つけて直そうとしても抵抗が大きいのも大きな要因です。（遅くしたいのでしょうか？遅いことが認識できないのでしょうか？） この度、体制変更で退職を勧められたのを機に、別の営みでMySQL/InnoDBの性能を改善していくことにしました。どうせ、性能劣化に(私よりも)無頓着な現体制では私が性能を改善することは困難なので、成果は出ないでしょう。（直しても、同時に導入される新機能・修正による劣化に改善分を喰いつぶされることもしばしばあった、と考えています。キリがありません。そも
はじめに MySQL(InnoDB)でSQLのパフォーマンスチューニングをするときに役に立つ知識をエッセンスとしてまとめました。結合(JOIN)やB-treeインデックスの探索の仕組み、実行計画の基本的な見方を紹介します。 想定する読者は、SQLのパフォーマンスを改善する必要があるが実行計画をみてもいまいちピンと来ない方です。インデックスの作成の経験や、複合インデックスやカーディナリティの知識があることを前提にしています。目標は、実行計画の内容がよく分からない読者が、実行計画をみただけでクエリが実行される様子をイメージでき、自信を持ってクエリの改善にあたることができるようにすることです。 ストレージエンジンはInnoDBを前提としています。また、インデックスはB-treeインデックスを想定しています。全文検索の転置インデックスや空間検索のR-treeインデックスについては触れません。 イン
この記事は、LayerX Tech Advent Calendar 2024 の7日目の記事です。 tech.layerx.co.jp こんにちは。バクラクビジネスカード開発チーム エンジニアの iwamatsu です。 何か書くことないかな〜と頭を悩ませていたところ、見たことのない不思議なデッドロックに出くわしたので、今回はそれについて書こうと思います。 実行環境 バージョン: MySQL 8.0.39 ストレージエンジン: InnoDB トランザクション分離レベル: REPEATABLE READ 発生した事象 以下のようなユーザーテーブルがあるとします。 CREATE TABLE `users` ( `id` INT NOT NULL, `name` VARCHAR(255) NOT NULL, `lucky_color` VARCHAR(255) NOT NULL, PRIMARY
zenn.dev を読んでの感想です。「シーケンスナンバーをPKにする」以外の項目については言及しませんが、言及しないことは正当性や妥当性を保証していることにはならないです。 InnoDB(MySQL)を想定してます。が、原理は割と一般的なので他のDBでも適用できることが多いと思います。 追記:一般的とは分散でないような"普通の"RDBMSを想定してましたが、分散システム(distributed systemないしreplicated system)のような場合では話が違います。 なぜシーケンシャルな値がよいか 端的にいうと書き込み操作時にバッファープール(baffuer pool)に読み混む必要のあるページが少なくて済むからです。その結果書き込み操作時にバッファープールにページが存在する可能性が高くレイテンシー的に有利になる可能性が高いです。 バッファープール、ページ、btreeなど具体
こんにちは。カミナシで「カミナシレポート」の開発を担当しているソフトウェアエンジニアの佐藤です。 カミナシレポートのバックエンドは Go で開発しており、データベースには Amazon Aurora MySQL を使用しています。また、データベースアクセスには ORM ライブラリの GORM を採用しています。 ほとんどのテーブルでは、プライマリキー（ID列）に AUTO INCREMENT を利用しています。これらのテーブルに GORM の Create メソッドなどを使って新しいレコードを挿入すると、AUTO INCREMENT で採番された値が自動的に対応する Struct のフィールドに反映されます。 AUTO INCREMENT による値の採番は MySQL 側で実行されているため、Go 側の Struct のフィールドに反映させるためには、Go アプリケーション側が何らかの方法
Developing a System to Investigate Lock Contention (Blocking) Causes in Aurora MySQL Hello. I am @p2sk from the DBRE team. In the DBRE (Database Reliability Engineering) team, our cross-functional efforts are dedicated to addressing challenges such as resolving database-related issues and developing platforms that effectively balance governance with agility within our organization. DBRE is a relat
いろいろありますが、今後のことを考える前にまずは、バージョン8.0.xの現状を一旦整理・理解してから決めようと思います。 念を押しておきますが、このブログの「内容は個人の考えであって、所属組織とは方針が異なる」と考えてください。 MySQL内部の人は、クラウドとか最新のサーバーとかしか利用していないのかも知れず、MySQL 8.0 が日に日に遅くなっていることに気づいていない人しかいないのでしょう。しかし、数年前のローカルPCで動かすと年々動作が鈍くなっているのを感じます。マイナーバージョンアップで単スレッド性能が下がり続けるなんて商用システムではリスキーです。 証明が難しく、ずっと放置せざるを得なかったのですが、非常に重要な事柄ですので今一度、オープンになっているソースを基に分析をしてみます。 まず、測るモノサシを決めましょう。以前のエントリ「MySQLバージョンアップによるInnoDB
社内で若者向けに作ったデータベースとストレージの入門資料です
こんにちは、shallow1729:detailです。今回は先日MyNA会というイベントで発表したMySQLの標準のストレージエンジンであるInnoDBのMVCCのガベージコレクションについて書こうと思います。発表自体もアーカイブされているので以下から見る事ができます。 「日本MySQLユーザ会会(MyNA会) 2021年07月 -下位レイヤ勉強会-」 公開版 - YouTube まず前半ではMVCCに関連するデータ構造を見ながらガベージコレクションの重要性やlong-running transactionの問題点について解説します。後半では実際のガベージコレクション(purge)の処理をソースコードレベルで追いながら、ユーザーに提供されているパラメーターを解説をします。 これまでに比べると踏み込んだ話題なのであまり基礎的な事は解説しません。知らない単語が多いかもしれないですが、適宜調べな
こんにちは、CTOの森です。iimonは今回が初のアドベントカレンダー参加です！ 本記事はiimonアドベントカレンダー1日目の記事となります。 はじめに 検証した環境 MySQL/mecabのインストール 大量のデータを入れる 1レコードのINSERTにかかった時間 検索してみる 検索文字列が「出来事」の場合 インデックスなし N-gram(bi-gram) IN NATURAL LANGUAGE MODE IN BOOLEAN MODE MeCab IN NATURAL LANGUAGE MODE IN BOOLEAN MODE 検索文字列が「チューリングはロンドンのリッチモンドに住み」の場合 まとめ 参照したサイト 最後に はじめに 今回はMySQLで全文検索機能を使う際のパフォーマンスについて書こうと思います！ 全文検索をちゃんと使うのであればElasticsearchやSolrな
PlanetScale Postgres is the fastest way to run Postgres in the cloud. Plans start at just $5 per month. Learn more By Ben Dicken | September 9, 2024 What is a B-tree?The B-tree plays a foundational role in many pieces of software, especially database management systems (DBMS). MySQL, Postgres, MongoDB, Dynamo, and many others rely on B-trees to perform efficient data lookups via indexes. By the ti
スターフェスティバル株式会社 の バックエンドエンジニアの @ikkitang です。 上記の自己紹介にもありますが、私はプライベートで日本PostgreSQLユーザー会の理事をやっていて、その活動の一貫として先日の 2022-10-01（土)に行われたOpen Source Conference 2022 Online/Hiroshimaで最新バージョンのPostgreSQL15の最新機能について発表してきました。 スライドは公開しておりますので、以下を見て頂く事で全てを完全に理解していただけるかもしれませんが、解説しながらまとめたいと思います。 PostgreSQLのリリースサイクル PostgreSQLはPostgreSQL開発コミュニティというユーザーグループ主導で開発されています。 毎年9~10月にメジャーバージョンをリリースしておりまして、今年はPostgreSQL15のリリー
日本MySQLユーザ会(MyNA会) 2021年07月 -下位レイヤ勉強会- での発表資料です。
念を押しておきますが、このブログの「内容は個人の考えであって、所属組織とは方針が異なる」と考えてください。 前のエントリでは、MySQL 8.0は、clangのPGO+LTOでビルドしないと本来の性能が出ない。ということを証明しました。その後、PGO+LTOといってもプロファイリングをどうしたらいいのかと、デスクトップマシンの空き時間でひたすらビルドとtpcc(ramfs)を繰り返した結果、興味深いことがわかりました。 tpccのようなある程度複雑なベンチマークは、 ベンチマークそのもの（この場合tpcc）をプロファイリングするよりも、 mysql-testのスクリプトを組み合わせて工夫したほうが性能が出る ということです。（少なくとも私の環境で、ではですが） つまり、 ビルドしてテストスクリプトが流せる環境であれば、総合的に最適に近いバイナリが生成できるということです。誰でもビルドできま
Go 1.18がリリースされましたね。 Go 1.18の新機能の中で最も注目を集めている機能はやはりジェネリクスだと思います。 そのジェネリクスを使用してSQLに関するミスをできる限りコンパイルの段階で防ぐことを目指すSQL Builder「GenORM」を作ったので、この記事ではその紹介をしていきます。 リポジトリ: https://github.com/mazrean/genorm ドキュメント: https://mazrean.github.io/genorm-docs/ja/ コード例 仕組みの説明の前に、GenORMのコード例を見ていただきたいと思います。 今回は以下のようなテーブルを使用します。 CREATE TABLE `users` ( `id` char(36) NOT NULL, `name` varchar(64) NOT NULL, `password` char(
Primary Key design is an important thing for InnoDB performance, and choosing a poor PK definition will have an impact on performance and also write propagation in databases. When this comes to Aurora, this impact is even worse than you may notice. In short, we consider a poor definition of a Primary Key in InnoDB as “anything but quasi sequential values”, which may cause very random access to dat
AWS Database Blog Achieve a high-speed InnoDB purge on Amazon RDS for MySQL and Amazon Aurora MySQL Purge is a housekeeping operation in a MySQL database. The InnoDB storage engine relies on it to clean up undo logs and delete-marked table records that are no longer needed for multiversion concurrency control (MVCC) or rollback operations. While our applications pursue a database design that aims
AWS re:Invent 2022 にて RDS の Blue/Green が発表されたことを受けて、この辺の具体的な運用をどのように改善できるのか考えていきます。 たいした内容ではないですが、丁寧に最適化して慣れれば、それなりに強い効果を発揮できそうな感じはあります。 ALTER TABLE の辛み まず復習からですが、RDS に Blue/Green が実装されるほど辛い運用はなんだったかというと、重い ALTER TABLE にあります。 ALTER には色々あるのでアレですが大雑把に言うと、容量や行数が大きいテーブルに対して実行すると、数時間単位～の処理時間がかかることが多く、しかもパッと見で進捗を把握できないという問題を抱えていました。それに対して色んな対策を取っていたと思います。例えば…… Handler_write SHOW GLOBAL STATUS LIKE ‘Hand
この記事はMySQLのカレンダー | Advent Calendar 2023 - Qiitaの19日目の記事です。 MySQLのredoログには何が書かれているのだろうか？ そんな疑問を解決するために私はアマゾンの奥地へと旅立つことにしました。 MySQLはSQLをパースし、実行計画を立てたあと、実際にストレージ(メモリ含む)でデータを処理する部分はプラガブルなストレージエンジンに実装を移譲する設計になっています。 しかし、処理をストレージエンジンがトランザクションをサポートしないことも選択できるため、クラッシュリカバリのための機構を実装していない可能性もあります。 そのため、レプリケーションにも利用されるバイナリログがWrite Ahead Loggingされていて、クラッシュリカバリやPITRにもバイナリログが主に使われています。(と、筆者は理解しています。). なので、運用上はバイ
遊びで始めたNew Relic MCP、気づいたらChatOpsなオブザーバビリティボットができてました/From New Relic MCP to a ChatOps Observability Bot
はじめに みなさんこんにちはメリークリスマス🎄 ついにアドベントカレンダー最終日！！！ 現在SODAでwebエンジニアをしているtoshikiです。(3記事目で謎の自己紹介) CTOからflexispotのデスクを譲り受ける代わりにテックブログ3記事執筆するという約束を果たすべく、SODAのAdvent Calendar 2023で3枠担当することになり、この記事をもって無事プレゼントの配達が完了しました🎅(sorenani) 今日はクリスマス当日ということで、自分自身理解が曖昧だったMySQLのストレージエンジンであるInnoDBのロック周りの挙動をMySQLの公式ドキュメントを読みつつ調査してまとめてみました。 この記事でわかるInnoDBのロックの種類 共有ロックと排他ロック インテンションロック ギャップロック ネクストキーロック 検証環境 MySQLのVersion mysq
DBを運用していると「DB全体で何MB利用しているのだろう？」「各テーブルごとに何MB利用しているのだろう？」といったことを確認する必要がでてきます。ここでは、容量を確認する方法について紹介します。 SELECT table_schema, floor(SUM(data_length + index_length) / 1024 / 1024) AS ALL_MB, floor(SUM((data_length) / 1024 / 1024)) AS DATA_MB, floor(SUM((index_length) / 1024 / 1024)) AS INDEX_MB FROM information_schema.tables GROUP BY table_schema ORDER BY sum(data_length + index_length) DESC; mysql> SEL
この記事の対象 この記事は、MySQL Serverが突然の死を迎えた上に、以下のような重症症状が出た方向けの記事です。 MySQLサーバが以下のようなエラーを吐いて起動に失敗する 2022-06-16T11:30:47.188361Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 11:30:48 UTC - mysqld got signal 11 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. Thread pointer: 0x0 Attempting backtrace. You can use the following information t
こんにちは。フォージビジョンの藤川と申します。 2023年9月初めに、AWSよりAmazon RDS for MySQL 5.7の標準サポート終了に関する内容と延長サポートに関する内容の通知がありました。以下は、2023/9/13時点のAWS公式サイトの情報です。 メール本文にも記載がありましたが、各々の情報は以下のAWS公式サイトに記載があります。 Amazon RDS for MySQL 5.7の標準サポート終了 2023/9/13時点では、表示言語を英語にすることで、標準サポート終了日の最新の情報や延長サポートの内容を確認することができました。 docs.aws.amazon.com Amazon AuroraとAmazon RDSがMySQLとPostgreSQLデータベースの延長サポートを発表 aws.amazon.com データベースのメジャーバージョンのアップデートについては
はじめに SQLBoilerは、スキーマに合わせたORMを生成するツールです。 READMEにも書かれていますが、意外と気づきづらい部分があったりするので、使い方から、ちょっとしたTips、ハマりポイントをまとめます。 少しでも参考になれば幸いです。 前提 MySQL SQLBoiler v4.16.1 生成したコードはmodelパッケージに配置されているとします テーブルは以下を想定 CREATE TABLE `teams` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_
Deleted articles cannot be recovered. Draft of this article would be also deleted. Are you sure you want to delete this article?
概要 InnoDB では、追加・更新・削除操作を繰り返していると、断片化（フラグメンテーション）という現象が発生します。 これはいわば、ゴミみたいなもので、テーブルのデータを削除してもディスク容量が減りません。 このゴミが増えてくると、クエリ処理が遅くなる可能性があリます。 例えばレコードが100万件あるテーブルの内、99万9999件を削除し1件の状態にしても、テーブルが占有している領域は100万件分使っているということになります。 今回は実際にテストデータを作成し、フラグメンテーションの発生とその解消法について確認していきたいと思います。 フラグメンテーションの詳細については本記事では述べないので、気になる方は下記記事が分かりやすかったのでご参照ください。 前提 MySQL 5.7.31 InnoDB テストデータの挿入 まずテスト用のDBとテーブルを作り、約100万件テストデータを挿入
データベースの設計や運用において、エンジニアが最も頭を悩ませる問題の一つに、データの不整合を防ぐことがあるかと思います。これは信頼性やパフォーマンスの向上に直結するものとなります。 MySQL InnoDB Clusterは、複数のMySQLサーバー間でデータを同期し、一貫性のあるデータベースを提供するための強力な仕組みとなるものです。 そこでInnoDB Clusterの基本概念や、主要コンポーネントの役割についてまとめてみました。 MySQL InnoDB Clusterとは MySQLデータベースにおいて高可用性を実現するための技術で、自動フェイルオーバー機能を提供します。 これにより、データベースシステムのダウンタイムを最小限に抑えることが可能になります。 MySQL InnoDB Clusterを使用する目的と理由 InnoDB Clusterは、複数のデータベースサーバーを組み
MySQL の InnoDB では、断片化（フラグメンテーション）という現象が発生する。 フラグメンテーションが発生すると、クエリ処理が遅くなったり、サーバーの容量をたくさん使い問題が起こる。フラグメンテーションを解消するには最適化をおこなう。 断片化についてと最適化の方法に関するメモ。 断片化（フラグメンテーション）とは 断片化とは、ディスク上のインデックスページの物理的な順序がページ上のレコードのインデックス順序とかけ離れているか、またはインデックスに割り当てられた 64 ページのブロック内に未使用のページが多数存在することを示します。 MySQL 5.6 リファレンスマニュアル – 14.10.4 テーブルのデフラグ DB で DELETE クエリを実行すると、すぐに物理的な削除は行われない。削除フラグ的なのがつけられる論理削除となる。 なので、このレコードがあった場所には穴が空いた
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く