other

MySQLの最新バージョン5.6がついにリリース!!!
http://nippondanji.blogspot.jp/2013/02/mysql-56-mysql56.html
今回のバージョンアップは、目立った機能追加は無くて、性能の改善が中心のようです。
・オプティマイザの改善
・レプリケーションの改善
・InnoDBのパフォーマンス改善
前回の5.5リリースが2010年12月だったから、2年ぶりのバージョンアップ。
MySQLはマイナーバージョンアップであっても、ガッツリと性能アップしてくるんで楽しみですな。
ミドルウェアのバージョンアップでワクワクするのはMySQLさんぐらいやでー。
test.png

【カテゴリ】データベース
other

PostgreSQL ver 8.4 で動作済み

以下のように数字が入っている文字列を昇順・降順したいと思います。

 例 )
  jaswill1
  jaswill2
  jaswill3

【文字列ソート】

 ORDER BY
    TO_NUMBER ( SUBSTRING ( USR_ID FROM ‘[0-9].*$’ ) , ‘[9]’ ) , USR_ID


ORDER NY句に記述されている「SUBSTRING」で部分文字列(後ろの‘[9]’)を取得し、

「TO_NUMBER」で数値に変更しています。FORM句では条件に該当するものを取得します。

あとは昇順か降順かをORDER BY句に追記するだけです。

あと、よくあるのが重複チェックですね。

せっかくなので、メモメモ φ(。。)

【重複データ取得】————————————————————–

 SELECT
    A.USR_ID AS USR_ID
 FROM
    TABLE_A A
 WHERE
    A.DELETE_FLG = 0
 GROUP BY
    A.USR_ID
 HAVING
    COUNT ( A.USR_ID )  >  1 
—————————————————————————————

 っで、↑コレを【文字列ソート】と組み合わせると・・・

【重複データの文字列ソート】————————————————

 SELECT
    A.USR_ID AS USR_ID
 FROM

    TABLE_A A
 WHERE

    A.DELETE_FLG = 0
    AND

    EXISTS
    (  SELECT
         AA.USR_ID AS USR_ID
     FROM

         TABLE_A AA
     WHERE

          AA.USR_ID = A.USR_ID
     GROUP BY

                    AA.USR_ID

     HAVING

                   
COUNT ( AA.USR_ID )  >  1
    )
 ORDER BY
     TO_NUMBER ( SUBSTRING ( A.USR_ID FROM ‘[0-9].*$’ ) , ‘[9]’ ) , A.USR_ID
    ,A.USR_ID DESC(ASC)

—————————————————————————————

・・・雑だけど気にしない。

これで、重複した文字列をソートできます。

分かりやすいように、ナンバー(No)とかつけると、順番になっている事が分かりやすいと思います。

ちょっとした備忘録です。

【カテゴリ】システム系データベース
【タグ】
other

こんにちは、Mizutaniです。

PostgreSQLで正規表現ができることをネットで調り、さっそくトライ!
したのですが、検索の仕方が悪かったのか、日本語交じりだとうまくいきませんでした。
なので、基礎的な正規表現の例を紹介します。

ちなみにバージョンは PostgreSQL 8.4です。


◆正規表現はPostgreSQLで用意されているものを用います。

SELECT * FROM table_a WHERE id ~ ‘[0-9]+’;

これを応用します。

◆半角英数字も問題なくチェックできます。

SELECT * FROM table_a WHERE id ~ ‘[0-9a-zA-Z]*’;

◆ただし、このままだと日本語が混じった文(abcあd)は判断できなかったので修正。

SELECT * FROM table_a WHERE id ~ ‘^[0-9a-zA-Z]*$’;

これで半角英数字のチェックはできると思います。
意外と簡単にできますね。

◆ちなみに、
~ (チルダ)は必ずパターンの先頭に書きます。
先頭以外に配置してしまうとリテラルとして解釈されるので注意。
あと は行の終わりを意味します。

以上です。

【カテゴリ】システム系データベース
【タグ】

Oracle11gの罠

masaki-watanabe

こんばんわ。くまぜみです。

 

昨日からの台風で、名古屋は100万人避難勧告がでたり

電車が止まったりで・・・。

 

本日、ユーザ様から電話がありました。

「データベースへ接続できません。ってでるんだけど」

 

何も変更してないことを確認。

まずはOSのリブート。復旧しない。

 

リモートサポートができないユーザ様だったので大変。

アラートファイルを送ってもらったり、サービスの起動状況を確認してもらったり。

 

Oracleは、正常に動作しているっぽいなぁ。

アプリ側の設定も確認。特に異常なし。なんでだ。

 

ユーザ様には申し訳ないが、Sqlplusで接続確認をお願いした。

「ORA-28001って出てますね。」

 

あまり聞いたことがないエラーだな。

 

インターネットで検索すると、大量にヒット!

 

Oracle11gから、デフォルトのユーザプロファイルでパスワードの有効期限が180日になっているとのこと。パスワードを無期限にするには、以下の手順を実施。

(ユーザ様に、sqlplusにて実施をお願いとは、申し訳なかった・・・。)

1.sqlplus sys/<パスワード> as sysdba
2.alter profile default limit password_life_time unlimited;
3.alter user <ユーザID> identified by <新パスワード>;
4.alter user <ユーザID> account unlock;

 

再度システムへログインできるかの確認を依頼。

(結果をビクビクしながら待ってました。)

 

「つながりました。」とユーザ様のお声。

 

ほっとしました。

解決するまでに、2時間程度かかってしまいました。

 

勉強しておかないとね。。。

【カテゴリ】データベース
【タグ】

Oracleバックアップの覚書2

other
mogeraです。
Oracleバックアップについての覚書その2
エラーコード:ORA-00600とは
SI ObjectBrowserのデータ生成ツールを使ってテストデータを10万件ほど生成しているときに発生。
固有のエラーコードがない内部エラーはこれで出力されるそうです。いわゆる「不明なエラー」なので発生する状況は様々。
マニュアルにはサポートに問い合わせるように、といったことが書かれています。
http://download.oracle.com/docs/cd/E16338_01/server.112/b56318/e0.htm#sthref17
そういうわけにもいかないので手順をさかのぼってエラー再現性を探ってみました。
どうやら今回の場合は表領域の空きが不足したのが原因のようです。
使用していたUSERS表領域を拡張したら成功しました。
データファイル:USERS01.DBF
ファイルサイズ:10MB⇒100MBに拡張
AUTOEXTEND:1MB
自動拡張するから大丈夫、と考えていたのが拙かったようです。
ではどれぐらいの領域を確保すればいいのか。
簡単に1行当たりのデータサイズを調べます。
列ごとの平均バイト数を合算して行のサイズとします。
表:TESTには10万レコードをランダム生成しての結果。
SELECT ROUND((AVG(VSIZE(ID))
+AVG(VSIZE(TESTNAME))
+AVG(VSIZE(CREATEDATE))
+AVG(VSIZE(UPDATEDATE))
),9) AS AVG_ROW_SIZE
FROM TEST_USER.TEST
結果: AVG_ROW_SIZE
68.41405
1行当たり約70バイトで計算すれば、10万行で7MB必要になります。
USERSには別のテストデータもあったので、それで容量が足りなくなったのでしょう。
しかし何故自動拡張が機能しなかったかは解りません…
【カテゴリ】データベース

Oracleバックアップの覚書1

other
mogeraです。
Oracleのバックアップについて調べたので、その覚書です。
Recovery Manager(RMAN)の利用
RMANはOracle標準のバックアップ・リストア・リカバリ用ユーティリティ。
バックアップ対象はデータベース全体、表領域単位、データファイル単位。
差分・増分バックアップが可能。
ポイントはRMANのリポジトリの保存場所。
DBスキーマをリポジトリの保存場所に指定できる。(デフォルトでは制御ファイルに保存)
これをリカバリ・カタログと呼ぶ。
これにより冗長性の確保は勿論、1つのリカバリ・カタログは複数サーバのRMANリポジトリを管理できるため、バックアップの集中管理が可能。
以下、操作手順を簡単に。
リカバリ・カタログのユーザーにはrecovery_catalog_ownerを付与
(リカバリ・カタログの管理権限のロール)
SQL> grant recovery_catalog_owner to rman;//ユーザー:rmanをリカバリ・カタログにする
RMANの起動。リカバリ・カタログにするスキーマへ接続
>rman CATALOG rman@REPO/RCAT//CATALOG指定でリカバリ・カタログへの接続
リカバリ・カタログを作成
RMAN> create catalog;
バックアップ対象のデータベースへ接続する(SYSDBA権限のあるユーザーで接続)
RMAN>connect TARGET sys/TGT//TARGET指定でバックアップ対象への接続
リカバリ・カタログへ登録する
RMAN> register database;
次はバックアップの取得。
INCREMENTALは差分増分、level0はそのベースとなる基本バックアップをとる、というオプション。
RMAN>BACKUP INCREMENTAL LEVEL 0 DATABASE;//DB全体のバックアップ
差分増分だけの取得はlevel1を指定。
RMAN>BACKUP INCREMENTAL LEVEL 1 DATABASE;
最後にリストアとリカバリ。
表領域:USERSを復旧する
RMANでリカバリ・カタログとリカバリ対象に接続
>rman CATALOG rman@REPO/RCAT
RMAN>connect TARGET sys/TGT
USERSをオフラインにする
RMAN>SQL ’ALTER TABLESPACE USERS OFFLINE';
リストア実行
RMAN>RESTORE TABLESPACE USERS;
リカバリの実行
RMAN>RECOVER TABLESPACE USERS;
USERSをオンラインに戻す
SQL> ALTER TABLESPACE USERS ONLINE;
【カテゴリ】データベース

大量データのimport後にORA-0600発生

ゆでたまご

Oracleに対して、大量データのimportを行った後にimport対象の
テーブルを結合等でSELECT文を発行すると、ORA-0600が発生したので
対応方法を覚え書き。

どうも大量データのimportを行うと、import前の統計情報と
import後のテーブルの状態がアンマッチとなり、オプティマイザが
正常な実行計画を戻せず、ORA-0600が発生するようです。

対応方法としては、統計情報を再取得することでimport後の統計情報が
取得され、問題なく実行計画を戻せるようになります。

統計情報の再取得を行うには、

ANALYZE TABLE テーブル名 COMPUTE STATISTICS

で1テーブル毎の分析を行い、スキーマ単位で行う場合は、

DBMS_UTILITY.ANALYZE_SCHEMA(‘スキーマ名’,’COMPUTE’);
もしくは、
DBMS_STATS.GATHER_SCHEMA_STATS (‘スキーマ名’,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’GATHER’);
を実行することで、再取得できます。

【カテゴリ】データベース
other

最近、サーバ構築系の仕事が多いので、自宅での検証用にネットブックを購入し、
CentOSをインスコしてシコシコいじくってます。
PB218734.JPG

ネットブックは、スペックが低いですが個人的なLinuxの検証用には最適のマシンです。
・初期費用が安い(3万くらい)。
・維持費が格安(電気代が1ヶ月つけっぱなしで700円くらい)。
・静か(ファンやハードディスクの音がほとんど無い)。

そんな中、MySQLを使った仕事がはじまるかもしれないので、色々とイジくりまわしてみました。

その中で感じたのが、データ件数が多くなると(10万件以上)、SQLでのデータ検索が非常に重くなる・・・。

オプティマイザの実行計画を見てみると、どうもテーブル結合するときにテーブルをフルスキャンしている。
インデックスを適切に貼っても改善されないのでネットで調べてみると、どうもMySQLはオプティマイザの性能があまりよくないようです。

現在MySQLの正式リリース版はバージョン5.1(2010年11月20日)ですが、
開発バージョンのMySQL5.5で、かなりオプティマイザが改善されているという情報があったのでインスコしてみたのでメモします。

CentOSはパッケージ管理に「yum」を使用できますので、これを利用しインスコします。

まず、yumのデフォルトリポジトリではMySQL5.5をインスコできないので、
remiリポジトリを使いインスコする準備をします。

①remiリポジトリの情報をダウンロードし、RPMにインスコ。

wget http://rpms.famillecollet.com/enterprise/remi-release-5.rpm 
wget http://download.fedora.redhat.com/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
rpm -Uvh epel-release-5-4.noarch.rpm remi-release-5.rpm

②remiリポジトリの設定ファイルが出きるので、編集する。

vi /etc/yum.repos.d/remi.repo
priority=1
※ファイルの一番下に追加

③ダンプファイルで、既存のデータを保存しておく。

mysqldump データベース名 -u root -pパスワード --opt  > ダンプファイル名

④既存のMySQLを削除

yum remove mysql mysql-server mysql-libs

⑤既存のデータファイルが残っているので、削除(リネームして一応取っておく)

mv mysql _mysql
※既存のデータファイルが残したままインスコできますが、データのストレージが違うためか、エラーが頻発し、悩むことになるので消しましょう。

⑥MySQL5.5をインスコ。

yum --enablerepo=remi-test install mysql mysql-server
yum --enablerepo=remi install  mysqlclient15
※libmysqlclient15が無いとエラーになったら、まず上記をインスコする。

⑦正常インスコ後、MySQLを起動。

/etc/init.d/mysqld start

⑧MySQLにログイン。

mysql -u root -p
※パスワードがまだ設定されていないので、パスワード入力は何も入力せずにエンター

⑨ログイン後、バージョン情報が表示されるので5.5になっているか確認する。

Server version: 5.5.7-rc MySQL Community Serve

⑩一度、MySQLをログアウトし、以前のデータをリストアする(完了)。

exit;
mysql -u root -p データベース名 < ダンプファイル名
※データベースはリストア前に手動で作っておく。

⑪その他(外部からMySQL接続できるように、ユーザ作成)。

GRANT ALL PRIVILEGES ON *.* TO ユーザ名@'%' IDENTIFIED BY 'パスワード' WITH GRANT OPTION;

⑫その他(統計情報を再構築)。

mysqlcheck --analyze -optimize --database データベース名 -u root -p
※データを大量にINSERT、DELETEしているとオプティマイザが正しく動作しなくなるので、適度に統計を取りなおす。

MySQLインスコ後、問題のSQLは爆速になりました。
実行計画で、正しくインデックスが使用されているのも確認。

MySQL5.1でもJOIN句の変わりに、STRAIGHT_JOIN句を書くと、デーブルを結合する順番を指定できるので、適切なインデックスを使用することは可能です。
ですが、運用が始まるとデータ件数が変わり、思ったとおりにインデックスが使用されなくなることも考えられるかと思います。
そうなると遅くなるたびに、SQLを書き換えることになり現実的ではありません。

やっぱりオプティマイザが正しくインデックスを使用してくれるのが、一番楽です。

ただ、MySQL5.5は2010年11月時点では、開発バージョンなので、お客様に納品するシステムでは、正式バージョンの5.1を使用することになりそうです。

早く、5.5が正式リリースされることを祈るまうす。

インデックスの有用性を検証

other

.kawa table{
border-collapse: collapse;
}
.kawa th {
border: solid 1px #666666;
padding: 1em;
background-color: #FAFAD2;
color: #000000;
}
.kawa tr.head th{
background-color: #04E69B;
}
.kawa td {
border: solid 1px #666666;
color: #000000;
padding: 0.5em;
background-color: #FFFFFF;
}
.kawa h2{
background-color: #B0E0E6;
border: solid 1px #666666;
font-weight: bold;
padding: 0.2em;
color: #000000;
}


インデックスを使用すると体感で早くなったと感じることも多いと思います。
ですが今回は速度の向上を視覚化するため、
ORACLEの機能の一つでもある実行計画を使い、検証してみます。

また前提条件として、オプティマイザの実行計画を明示的に指示することが
可能な「ヒント」を使います。

今回使用するヒント

ヒント 意味
データへのアクセスパス FULL 全表走査
INDEX 索引スキャン
INDEX_COMBINE ビットマップ・アクセス・パスを使用

今回使用する「人事テーブル」表
 ・以下の列にUniqueインデックス(B-Tree)
        ID
 ・以下の列にnonUniqueインデックス(B-Tree)
       性別
     都道府県
 ・10万件のデータを保持

WS000000.jpg

※使用アプリ:SI Objerct browser

 カーディナリティの高いカラムで検証

IDカラムを使用します。
単一プライマリキーでもあるため、カーディナリティの高いカラムです。

では早速、実行計画を見てみましょう。
ヒントを使用し、フルスキャンするよう指示しています。
※緑文字がヒント

WS000001.jpg

上記の画像からは、以下の内容が読み取れます。
 ①人事テーブルをフルスキャン
 ②かかったコストが98

※このコストを小さくしていくことがチューニングの目標となります。

では次に、索引スキャンするよう指示します。

WS000003.jpg

上記の画像からは、以下の内容が読み取れます。
 ①ID列の索引表をユニークスキャン
 ②ユニークスキャンで得たROWIDを使用し人事テーブルにアクセス
 ③かかったコストが2

50倍早くなりました。

 カーディナリティの低いカラムで検証

性別カラムを使用します。
男女の2種類しか値が無く、カーディナリティの低いカラムです。

フルスキャンを指示

WS000004.jpg

コストが104です。

続いて索引スキャンを指示

WS000005.jpg

コストが544に跳ね上がりました。

フルスキャンに比べ、索引スキャンしたほうが約5倍も遅くなっています。

さらに続いて都道府県も索引スキャンを指示

WS000006.jpg

コストが344と性別に比べると多少減っていますが、フルスキャンにはかないません。

ビットマップインデックスで検証

都道府県カラムの索引をビットマップインデックスに変更してみます。

ビットマップインデックスカーディナリティが低い場合に有効とされています。

WS000007.jpg

コストが93フルスキャンの104に比べ多少早くなりました。

まとめ

・カーディナリティが高い場合は、B-Treeインデックスが非常に有効。
・カーディナリティが低い場合は、ビットマップインデックスが多少は有効。

ビットマップインデックスはちょっと期待はずれな結果でした。
実用化には程遠い感じです。(わたしの知識では)

有効な場面をさらに検証し、またご報告できればこれ幸いです。

がんばるか!

【カテゴリ】データベース

B-Treeインデックスの仕組み

other

今回は、最もポピュラーであり、ほとんどのリレーショナルデータベースで使用することができるB-Treeインデックスを掘り下げてみます。

※ORACLE,MySql等でデフォルトのインデックス。
※B-Treeとは「バランスツリー」のことで検索アルゴリズムの一つとしても有名です。

図で簡単に表現すると、下記のようになります。
(カメルーンを検索する場合)
WS000002.jpg

最上位のヘッダブロックから検索していき、
最下位のリーフブロックにて実表の物理位置(ROWID)をゲットします。

この階層が浅ければ浅いほど検索の速度があがりますが、
B-Treeの特徴としてデータ量が10倍、20倍になっても、
階層の深さはさほど変わりません。

そのため、データ量に関わらず一定の速度を保つことが可能になります。

その他、B-Treeインデックスの特徴として
 ・範囲検索の高速化
 ・ソートの高速化
の2点についても、パフォーマンスに良い結果を得ることができます。

範囲検索の高速化については、最下位のリーフブロックに前後のデータのポインタを
各データにもっているので、前後のデータを連続して検索することができるためです。

ソートの高速化については、B-Treeインデックス内のデータは既にソートされているため、
ソート処理の必要がないためです。

なお、複数の列を使用した複合インデックス(主キーに多い)の場合は注意が必要です。

複合インデックスの一部だけを使った検索では、条件によってインデックス検索がされず
思うような速度が出ない場合があります。

複数列で1つの一意性を保つため、インデックスではデータを特定できないためです。

※oracleの場合はオプティマイザの判断によりインデックス検索されることもある。
※一部のみでも、カーディナリティが高い場合など。

つづく

【カテゴリ】データベース

プロフィール

愛知県名古屋市にあるジャスウィルで働く社員です。

ジャスウィルは大学事業に特化したシステムを提案しています。『大学向け事務・教務統合パッケージ―TriR Campus』を開発しています。


Facebookページでは、イベントや普段の社員の様子を公開中♪
ぜひご覧ください☆

過去一年の月別記事 全て表示