トップ  > メモ一覧  > カテゴリ「保守・改善」の絞り込み結果 : 23件

23件中 1 〜 10 表示  1 | 2 | 3  次の10件> 最後»

No.5167 mysqldump で 1行ずつの insert 文で出力するオプション

mysqldump で 1行ずつの insert 文で出力するオプション
--skip-extended-insert
更新:2014/10/21 19:51 カテゴリ: MySQL  > 保守・改善 ▲トップ

No.4652 mysqldump 中のInsert文にカラム名の指定を入れる

mysqldump 中のInsert文にカラム名の指定を入れる
「-c」オプション        dump中のInsert文にカラム名の指定を入れる
更新:2012/12/07 18:06 カテゴリ: MySQL  > 保守・改善 ▲トップ

No.4499【引用】1台から500台までのMySQL運用 MySQL Beginners

1台から500台までのMySQL運用 MySQL Beginners
http://www.slideshare.net/kazeburo/1500mysql-mysql-beginners

引用元

更新:2012/06/17 00:49 カテゴリ: MySQL  > 保守・改善 ▲トップ

No.3903 特定レコードのスロークエリを抽出するコマンド

tail -n 5000 mysql-slow.log|grep -B 3 -i ashiato
更新:2011/07/14 21:48 カテゴリ: MySQL  > 保守・改善 ▲トップ

No.3902 WHERE条件を指定してdump

== WHERE条件を指定してdump

mysqldump --opt -u root -p --default-character-set=utf8 bbmf_jp_menue ashiato -t "--where=updated_at > '2010-10-14' and member_id_to is not NULL" > dump.sql

更新:2011/07/14 21:23 カテゴリ: MySQL  > 保守・改善 ▲トップ

No.3863【引用】MySQL のレプリケーションが動いているのか監視するスクリプト

MySQL のレプリケーションが動いているのか監視するスクリプト
- TokuLog 改メ tokuhirom’s blog
http://d.hatena.ne.jp/tokuhirom/20090807/1249637349

■ [ mysql ] MySQL の レプリケーション が動いているのか監視する スクリプト
【注意】
この記事は 2005-08-19 17:29:10 に 自分 がかいた記事の再掲です。

レプリケーション が止まってて悲しいことになることがあるので、監視することにした。
#!/usr/bin/perl
use strict ;
use Data::Dumper;
use DBI;

my $dbh = DBI-> c...

引用元

更新:2011/06/30 13:24 カテゴリ: MySQL  > 保守・改善 ▲トップ

No.1638 データベース毎のディスクサイズを調べる

データベース毎のディスクサイズを調べる

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;

更新:2011/04/25 18:51 カテゴリ: MySQL  > 保守・改善 ▲トップ

No.3255 mysqlデータディレクトリを退避してのSQL実行時間検証作業

mysqlデータディレクトリを退避してのSQL実行時間検証作業

== ■現状のバックアップDBのディレクトリバックアップ
sshログイン
rootユーザに変更

=== mysqld停止
/etc/init.d/mysqld stop

=== mysqlデータディレクトリのバックアップ
cp -a /var/data/mysql /var/data/mysql_bcup1129

=== レプリケーション情報ファイルの退避★★★★
★これ重要
mkdir -p ~/bcup_1129/conf
mv /var/data/mysql/master.info ~/bcup_1129/conf/
mv /var/data/mysql/relay-log.info ~/bcup_1129/conf/
↑のファイルにレプリケーション情報が保存されている

== mysqld起動
①起動の実施
/etc/init.d/mysqld start

②mysqld起動後の状態確認
* mysqlログインし、状態確認を行う
** レプリケーションが停止しているか?

== ■SQL実行時間の計測
下記SQLの実行・時間計測
mysql -u root db-name


---------
実際に実行するSQL文
---------

== ■バックアップDBの復旧
=== mysqld停止
/etc/init.d/mysqld stop

=== 計測に利用したmysqlデータディレクトリの移動
mv /var/data/mysql /var/data/mysql_after_1129

=== mysqlデータディレクトリの復旧
mv /var/data/mysql_bcup1129 /var/data/mysql

=== mysqlディレクトリの状態確認
cd /var/data/mysql/
ls -al
※権限・パーミッションなど

== mysqld起動
①起動の実施
/etc/init.d/mysqld start

②mysqld起動後の状態確認
* mysqlログインし、状態確認を行う
** レプリケーションが開始しているか?

更新:2010/11/30 00:47 カテゴリ: MySQL  > 保守・改善 ▲トップ

No.1894 全テーブルの統計情報をサイズ順に一覧表示する

全テーブルの統計情報をサイズ順に一覧表示する


select
table_name, engine, table_rows as tbl_rows, avg_row_length as rlen, 
floor((data_length+index_length)/1024/1024) as allMB, 
floor((data_length)/1024/1024) as dMB, 
floor((index_length)/1024/1024) as iMB 
from information_schema.tables 
where table_schema=database() 
order by (data_length+index_length) desc;


MySQLにおいて、テーブルサイズやインデックスサイズ、レコード数、平均レコード長などの統計情報を知る上でshow table statusは定番です。ただ雑多な表示項目も多いので、たくさんのテーブルの統計を見る場合、必要な情報だけを返したいことは多いです。また全テーブル のうち、どのテーブルが一番大きいのかを知りたいとか、サイズが多い順に一覧表示したいとか、一目で分かるような情報がほしいことも多いです。
こういうときはinformation_schema.tablesを使うと便利です。以下の例では、appデータベースの全テーブルについて、「テーブ ルサイズ+インデックスサイズ」の大きい順に、ストレージエンジン、レコード数、平均レコード長、テーブルサイズ(MB)、インデックスサイズ(MB)な どを返しています。
  1. use app;  
  2. select  
  3. table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,  
  4. floor((data_length+index_length)/1024/1024) as allMB,  
  5. floor((data_length)/1024/1024) as dMB,  
  6. floor((index_length)/1024/1024) as iMB  
  7. from information_schema.tables  
  8. where table_schema=database()  
  9. order by (data_length+index_length) desc;  
  10.   
  11. +------------+--------+----------+------+-------+------+------+  
  12. | table_name | engine | tbl_rows | rlen | allMB | dMB  | iMB  |  
  13. +------------+--------+----------+------+-------+------+------+  
  14. | stock      | InnoDB |  9999831 |  381 |  3639 | 3639 |    0 |  
  15. | order_line | InnoDB | 28493701 |   95 |  2593 | 2593 |    0 |  
  16. | customer   | InnoDB |  2972004 |  673 |  2130 | 1909 |  221 |  
  17. | orders     | InnoDB |  3000309 |   60 |   301 |  171 |  129 |  
  18. | history    | InnoDB |  2997455 |   82 |   236 |  236 |    0 |  
  19. | new_order  | InnoDB |   905600 |   37 |    48 |   32 |   15 |  
  20. | item       | InnoDB |   100160 |  110 |    10 |   10 |    0 |  
  21. | district   | InnoDB |      917 |  178 |     0 |    0 |    0 |  
  22. | warehouse  | InnoDB |      100 |  163 |     0 |    0 |    0 |  
  23. +------------+--------+----------+------+-------+------+------+  
  24. rows in set (0.84 sec)  


自分は、ここをスタートラインにして、怪しいテーブルに焦点をあててチューニングをすることが多いです。ただしshow table statusと同様、数値項目は概算値なので、毎回微妙に値が変わることに注意してください。

引用元

更新:2010/09/28 10:09 カテゴリ: MySQL  > 保守・改善 ▲トップ

No.2933 2010-07-03MySQLでサービス停止のないALTERTABLEの検討14

2010-07-03 MySQLでサービス停止のないALTER TABLEの検討 このエントリーを含むブックマーク Add Starazurestone (red)hirose31 (green)hxmasaki14hamaco

MySQLでテーブルへのカラム追加、インデックス追加やテーブルの再編成などを行うと、その間テーブルに共有ロックがかかってしまいます。そのためこれらのメンテナンス処理は、通常利用者の少ない深夜早朝帯にサービスを止めて実施する必要があります。本日はそれを無停止、オンラインのままでできないかという話題です。

基本的なアイデア

  1. メンテナンス対象の元テーブルをコピーして、作業用の仮テーブルを作ります
  2. 仮テーブルに対して、カラム追加などの変更を加えます
  3. その間、元テーブルに対して行われる更新処理について差分を記録しておきます
  4. 仮テーブルの変更が終わったら、記録しておいた差分データを仮テーブルに反映します
  5. 差分データの反映が終わったら、元テーブルと仮テーブルを入れ替えます

これと似たようなことを考えた方は結構いらっしゃるのではないでしょうか。ただ、言うは易し、行うは難しです。整合性がきちんと取れるかどうか分からない、こんな危険なことはできないというのが普通の反応だと思います。

openark kit

このアイデアを実装したプログラムがあります。openark kitoak-online-alter-tableです。

openark kitはイスラエル在住のMySQLコンサルタントShlomi Noach氏が開発したMySQLユーティリティ集です。10種類のプログラムから構成されており、すべてPythonで書かれています。ライセンスBSDです。類似のMySQLユーティリティ集としてはMaatkitが有名ですが、Webで探すとそれ以外にもさまざまなソフトウェアが見つかります。

oak-online-alter-tableの仕組み

oak-online-alter-tableがどのようにしてオンラインのままテーブル定義を変更するのか、その仕組みを確認していきましょう。これはMySQLの一般クエリログをONにしてプログラムを実行することで、簡単に調べることができます。oak-online-alter-tableの仕組みは、実際には上で述べた基本的なアイデアよりも少し複雑になっています。

1. 空の仮テーブルを作成する

CREATE TABLE … LIKE文を用いて、元テーブルと同じ構造の仮テーブルを作成します。データのコピーはまだ行いません。

f:id:sh2:20100703155806p:image

CREATE TABLE sbtest.__oak_sbtest LIKE sbtest.sbtest;

2. 仮テーブルに変更を加える

仮テーブルに対し、カラム追加、インデックス追加などの変更を加えます。

f:id:sh2:20100703155807p:image

ALTER TABLE sbtest.__oak_sbtest ADD test VARCHAR(10);

3. 元テーブルにトリガを作成する

元テーブルに対してAFTER INSERT、AFTER UPDATE、AFTER DELETEの3つのトリガを作成します。これらのトリガによって、元テーブルに対する更新が仮テーブルに伝播するようになります。MySQLのトリガはそれぞれのイベントに対して一つしか作成できないため、すでにトリガの存在するテーブルに対してoak-online-alter-tableを使用することはできません。

f:id:sh2:20100703155808p:image

CREATE TRIGGER sbtest.sbtest_AI_oak AFTER INSERT ON sbtest.sbtest FOR EACH ROW
    REPLACE INTO sbtest.__oak_sbtest (c, pad, k, id) VALUES (NEW.c, NEW.pad, NEW.k, NEW.id);
//
CREATE TRIGGER sbtest.sbtest_AU_oak AFTER UPDATE ON sbtest.sbtest FOR EACH ROW
BEGIN
    DELETE FROM sbtest.__oak_sbtest WHERE (id) = (OLD.id);
    REPLACE INTO sbtest.__oak_sbtest (c, pad, k, id) VALUES (NEW.c, NEW.pad, NEW.k, NEW.id);
END;
//
CREATE TRIGGER sbtest.sbtest_AD_oak AFTER DELETE ON sbtest.sbtest FOR EACH ROW
    DELETE FROM sbtest.__oak_sbtest WHERE (id) = (OLD.id);
//

それぞれのトリガを見てみると、INSERTイベントに対してはREPLACE文、UPDATEイベントに対してはDELETEおよび REPLACE文、DELETEイベントに対してはDELETE文が発行されています。UPDATEイベントに対して一旦DELETEを行っているのは、 主キーを更新するSQLに対応するためです。

f:id:sh2:20100703155809p:image

4. 元テーブルから仮テーブルにレコードをコピーする

INSERT IGNORE文を用いて、元テーブルのレコードを仮テーブルにコピーします。仮テーブルにすでにトリガによってコピーされたレコードがある場合、INSERT IGNORE文はエラーを返すことなく単にそのレコードを無視します。

f:id:sh2:20100703155810p:image

INSERT IGNORE INTO sbtest.__oak_sbtest (c, pad, k, id)
    (SELECT c, pad, k, id FROM sbtest.sbtest
     WHERE (((id > @unique_key_range_start_0) OR ((id = @unique_key_range_start_0)))
            AND ((id < @unique_key_range_end_0) OR ((id = @unique_key_range_end_0))))
     LOCK IN SHARE MODE)

oak-online-alter-tableはすべてのレコードを一度にコピーするのではなく、デフォルトで1,000レコードずつコピーを行います。こうすることで、アプリケーション側の処理がロック待ちのタイムアウトで失敗することを防ごうとしています。また、システムが過負荷になることを防ぐため、1,000レコード処理するごとにスリープ時間を設けることもできるようになっています。

f:id:sh2:20100703155811p:image

5. 元テーブルで削除されたレコードを、仮テーブルから削除する

INSERT IGNORE文によるレコードのコピーには、一つだけ元テーブルと仮テーブルの間に不整合を生じさせるパターンがあります。それは次の図に示すような、DELETE文とINSERT IGNORE文が同時に発生するパターンです。

f:id:sh2:20100703155812p:image

DELETE文によるレコードの削除がCOMMITされる前にINSERT IGNORE文が発行されると、一度は削除されたレコードがINSERT IGNORE文によって復活してしまいます。これに対処するため、oak-online-alter-tableはレコードのコピー後にもう一度テーブルをスキャンし、元テーブルで削除されているレコードを仮テーブルからも削除します。

f:id:sh2:20100703155813p:image

DELETE FROM sbtest.__oak_sbtest
WHERE (((id > @unique_key_range_start_0) OR ((id = @unique_key_range_start_0)))
       AND
      ((id < @unique_key_range_end_0) OR ((id = @unique_key_range_end_0))))
  AND (id) NOT IN
      (SELECT id FROM sbtest.sbtest
       WHERE (((id > @unique_key_range_start_0) OR ((id = @unique_key_range_start_0)))
              AND
              ((id < @unique_key_range_end_0) OR ((id = @unique_key_range_end_0))))
      )

6. 元テーブルと仮テーブルをリネームし、旧:元テーブルを削除する

RENAME TABLE文を用いてテーブルの名前を変更します。MySQLのRENAME TABLE文は、複数のテーブルに対してアトミックに名称変更を行うことができます。最後に旧:元テーブルを削除して、作業完了です。

f:id:sh2:20100703161626p:image

RENAME TABLE sbtest.sbtest TO sbtest.__arc_sbtest,
             sbtest.__oak_sbtest TO sbtest.sbtest;
DROP TABLE IF EXISTS sbtest.__arc_sbtest;

修正パッチ

現在公開されているopen arkバージョン111のoak-online- alter-tableには、テーブルの一意キーが数値型(TYNYINT、SMALLINT、INT、BIGINT)の場合に内部で型変換のエラーが発 生してしまうという不具合があります。また、テーブルの情報を取得するために最初にごく短い時間テーブルロックをかけるのですが、更新負荷の高い状況では まれにトリガの存在するテーブルに対するLOCK TABLESが失敗するという問題があります。これら二つの問題に対処する修正パッチを作成したので、オリジナル版が改修されるまでの間、当面このパッチをご利用いただければと思います。

*** oak-online-alter-table_org	2010-07-03 19:42:08.000000000 +0900
--- oak-online-alter-table	2010-07-03 20:04:25.000000000 +0900
***************
*** 726,732 ****
                      AS ratio_complete
                  """
              ratio_complete = float(get_row(ratio_complete_query)["ratio_complete"])
!             verbose("%s range (%s), (%s), %s" % (description, ",".join(unique_key_range_start_values), ",".join(unique_key_range_end_values), get_progress_and_eta_presentation(elapsed_times, elapsed_time, ratio_complete)))
          elif unique_key_type == "temporal":
              ratio_complete_query = """
                  SELECT
--- 726,732 ----
                      AS ratio_complete
                  """
              ratio_complete = float(get_row(ratio_complete_query)["ratio_complete"])
!             verbose("%s range (%s), (%s), %s" % (description, ",".join(map(str, unique_key_range_start_values)), ",".join(map(str, unique_key_range_end_values)), get_progress_and_eta_presentation(elapsed_times, elapsed_time, ratio_complete)))
          elif unique_key_type == "temporal":
              ratio_complete_query = """
                  SELECT
***************
*** 935,942 ****
  
              shared_columns = get_shared_columns()
  
-             create_custom_triggers()
              lock_tables_write()
              unique_key_min_values, unique_key_max_values, range_exists = get_unique_key_range()
              unlock_tables()
  
--- 935,942 ----
  
              shared_columns = get_shared_columns()
  
              lock_tables_write()
+             create_custom_triggers()
              unique_key_min_values, unique_key_max_values, range_exists = get_unique_key_range()
              unlock_tables()
  

動作確認

実際に、oak-online-alter-tableの動作を確認してみましょう。

今回はまずSysBenchのテーブルを用い、これに対して参照のみを行うトランザクションを1スレッド、更新を行うトランザクションを1スレッド立ち上げて、50ミリ秒間隔で繰り返し負荷をかけます。そして負荷テストの途中でテーブルの再編成を行い、スループットがどのように変化するかを確認します。SysBenchのテストデータはデフォルトで1万レコードとなっていますが、このテストでは10倍の10万レコードを用意しています。

負荷テスト開始10秒後にALTER TABLE文によるテーブル再編成をかけます。

mysql> ALTER TABLE sbtest ENGINE = InnoDB;
Query OK, 100000 rows affected (5.56 sec)
Records: 100000  Duplicates: 0  Warnings: 0

この処理には5.56秒かかりました。

続いて、負荷テスト開始30秒後にoak-online-alter-tableによるテーブル再編成をかけます。

$ oak-online-alter-table -u sbtest --ask-pass -S /var/lib/mysql/mysql.sock -d sbtest -t sbtest --sleep=20
-- Connecting to MySQL
Password:
-- Table sbtest.sbtest is of engine innodb
-- Checking for UNIQUE columns on sbtest.sbtest, by which to chunk
-- Possible UNIQUE KEY column names in sbtest.sbtest:
-- - id
-- Table sbtest.__oak_sbtest has been created
-- No ALTER statement provided
-- Checking for UNIQUE columns on sbtest.__oak_sbtest, by which to chunk
-- Possible UNIQUE KEY column names in sbtest.__oak_sbtest:
-- - id
-- Checking for UNIQUE columns on sbtest.sbtest, by which to chunk
-- - Found following possible unique keys:
-- - id (int)
-- Chosen unique key is 'id'
-- Shared columns: c, pad, k, id
-- Attempting to lock tables

-- Tables locked WRITE
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- id (min, max) values: ([1L], [100000L])
-- Tables unlocked
-- Copying range (1), (1000), progress: 0%
-- Will sleep for 0.02 seconds
-- Copying range (1000), (2000), progress: 1%
-- Will sleep for 0.02 seconds
-- Copying range (2000), (3000), progress: 2%
-- Will sleep for 0.02 seconds
-- Copying range (3000), (4000), progress: 3%
-- Will sleep for 0.02 seconds

…

-- Deleting range (96000), (97000), progress: 96%
-- Will sleep for 0.02 seconds
-- Deleting range (97000), (98000), progress: 97%
-- Will sleep for 0.02 seconds
-- Deleting range (98000), (99000), progress: 98%
-- Will sleep for 0.02 seconds
-- Deleting range (99000), (100000), progress: 99%
-- Will sleep for 0.02 seconds
-- Deleting range 100% complete. Number of rows: 0
-- Table sbtest.sbtest has been renamed to sbtest.__arc_sbtest,
-- and table sbtest.__oak_sbtest has been renamed to sbtest.sbtest
-- Table sbtest.__arc_sbtest was found and dropped
-- ALTER TABLE completed

こちらの処理には23.06秒かかりました。

このときSysBenchのスループットは以下のように変化しました。

f:id:sh2:20100704052828p:image

ALTER TABLE文によるテーブル再編成時はテーブルに共有ロックがかかるため、更新トランザクションが完全に止まってしまうことが分かると思います。一方oak-online-alter-tableによるテーブル再編成では、スループットこそ落ちるものの更新トランザクションが完全に止まることは避けられています。

CPU使用率は以下のように変化しました。

f:id:sh2:20100704053035p:image

ALTER TABLE文が完全にCPUを使い切ってしまうのに比べ、oak-online-alter-tableはスリープ時間を設定することで負荷の高騰を抑えられていることが分かります。

まとめ

MySQLのALTER TABLE文は現状内部的にテーブルコピーを伴っており、テーブルへのカラム追加、インデックス追加やテーブルの再編成などを行うとテーブルに共有ロックがかかってしまいます。そのためこれら各種メンテナンス作業をオンラインのままで行うことは難しく、これがシステムの無停止運用を妨げる要因の一つとなっていました。こうしたオンラインメンテナンス機能は従来Oracleなど商用RDBMSが得意としてきたところですが、MySQLでもoak-online-alter-tableで示された考え方を応用することで、今後運用性を改善していけるのではないかと思います。

参考ですが、PostgreSQLにも同様の考え方に基づくpg_reorgというテーブル再編成ツールが用意されています。PostgreSQLは追記型アーキテクチャであることから、VACUUMというガベージコレクション処理を運用にどのように組み込むかがとても重要なのですが、pg_reorgによって運用性がかなり改善されました。こうした動きもおさえておきたいですね。

引用元

更新:2010/08/16 03:03 カテゴリ: MySQL  > 保守・改善 ▲トップ
23件中 1 〜 10 表示  1 | 2 | 3  次の10件> 最後»

FuelPHP

Mac

フロントエンド開発

web開発

プロマネ

マネタイズ

プレゼン

webサービス運用

webサービス

Linux

サーバ管理

MySQL

ソース・開発

svn・git

PHP

HTML・CSS

JavaScript

ツール, ライブラリ

ビジネス

テンプレート

負荷・チューニング

Windows

メール

メール・手紙文例

CodeIgniter

オブジェクト指向

UI・フロントエンド

cloud

マークアップ・テキスト

Flash

デザイン

DBその他

Ruby

PostgreSQL

ユーティリティ・ソフト

Firefox

ハードウェア

Google

symfony

OpenPNE全般

OpenPNE2

Hack(賢コツ)

OpenPNE3

リンク

個人開発

その他

未確認

KVS

ubuntu

Android

負荷試験

オープンソース

社会

便利ツール

マネー

Twig

食品宅配

WEB設計

オーディオ

一般常識

アプリ開発

Python

サイトマップ

うずら技術ブログ

たませんSNS

rss2.0