T-1000の日記

専門学校の授業中にXAMPP Ver1.7.4をインストールして、PHPとMySQLを使って
授業をしています。
そろそろPHPの授業でデータベース連携の内容に入ろうとした所で
事件が起こりました。

「MySQLに登録したデータ（日本語）がすべて文字化けする。」

ということで、調べてみました。XAMPP Ver1.7.4の時点ではMySQLの標準文字コードはlatin1のようです。（？）

PHPの方は文字コードをUTF-8に変更しているので、PHPMyAdminから
追加、更新すると文字化けした状態で登録されてしまいます。
（mysqlクライアントからも同様の結果になりました。
set names sjisしても文字化けが直りませんでした。。これだと無事更新できそうだけど…？）

結局、MySQLの設定ファイルmy.iniを修正して、デフォルトの文字コードを
変更すれば修正できそうだという情報を見つけたので、以下の記述を追加しました。

[XAMPPインストールフォルダ]¥mysql¥bin¥my.ini
[mysqld]
character-set-server = utf8
skip-character-set-client-handshake

[mysqldump]
default-character-set=utf8

[mysql]
default-character-set=utf8

character-set-serverの設定名は、
古いバージョンだと、default-character-setになるようです。

サーバを再起動すると、無事更新できるようになるかと思ったのですが、
文字コードを指定する前に作成したデータベースに関しては、文字コードが
設定されているようで、この設定では反映されませんでした。
なので、以下のクエリを実行して、デフォルトの文字コードを変更します。

ALTER DATABASE sampledb default character set=utf8;

これでも、すでに登録されたデータに関しては復活しないようなので、
テストデータに関しては諦めるしか無いのかもしれません。。。

昨日１日中時刻の検索SQLに振り回されました。
時刻を検索する時に
時分まで細かく指定するより日ごと（0:00の指定）で検索するほうが相対的に早いようです。

もしかしたら常識なのかもしれないですが。

正直こんなことに１日振り回されたのがショックですね。

恐らく、以下に挙げる内容は「至極当然」の話だと思いますが、
調べてみたので残しておきます。

まずは、クラスタ化インデックスをSectionNoに張り、Noに非クラスタ化一意インデックスを張ったテーブル

USE [DB_TEST]
GO
CREATE TABLE [dbo].[Clustered_Table](
[No] [int] NOT NULL,
[SectionNo] [int] NOT NULL,
[Name] [nvarchar](50) COLLATE Japanese_CI_AS NOT NULL,
[updDate] [datetime] NOT NULL
) ON [PRIMARY]
USE [DB_TEST]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Primary_Table] ON [dbo].[Clustered_Table]
(
[No] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

USE [DB_TEST]
GO
CREATE CLUSTERED INDEX [IX_Clustered_Table] ON [dbo].[Clustered_Table]
(
[SectionNo] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

次にNo列にプライマリキーをつけたもの

USE [DB_TEST]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NonClustered_Table](
[No] [int] NOT NULL,
[SectionNo] [int] NOT NULL,
[Name] [nvarchar](50) COLLATE Japanese_CI_AS NOT NULL,
[updDate] [datetime] NOT NULL,
CONSTRAINT [PK_NonClustered_Table] PRIMARY KEY CLUSTERED
(
[No] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

この２つのテーブルに１００００件のデータをそれぞれ入れて、
SELECT * FROM dbo.Clusterd_Table WHERE SectionNo = 500
SELECT * FROM dbo.NonClusterd_Table WHERE SectionNo = 500

なんていうSQLを実行させて、それぞれ実行プランを取得しました。
すると、コストに以下の差がでました。

非クラスタ化
I/Oコスト：0.0586806
CPUコスト：0.011157
操作コスト：0.0698376

クラスタ化
I/Oコスト：0.003125
CPUコスト：0.000168
操作コスト：0.003293

クラスタで分割された範囲を検索させると劇的な速さが実現されるようです。

ただ、プライマリキーでの検索ではさすがに非クラスタ化の方が早いように思います。
（実際にはそんなに数値的な差は無かったですが、
クラスタ化テーブルの方は、一意インデックスとクラスタ化インデックスの両方を検索していました）

まあ、範囲検索が多いか、キーでの検索が多いかの違いでしょうか。
テーブル設計をしっかりしましょうということで。

（クラスタ分割した時ってインデックスの再構築が必要になるような…？）

最近SQLServerのパフォーマンスが原因による障害が発生しているので
少しSQLServer自体のチューニングができないのかどうか調べてみました。

チューニングではないですが、SQLServerの稼動状況を参照するためのビューが
存在しているようです。

select * from sys.dm_os_perormance_counters -- パフォーマンスカウンタ
select * from sys.dm_io_virtual_file_stats(NULL, NULL) -- ディスクI/O状態（全てのDBの情報を取得）
select * from sys.dm_os_wait_stats -- テーブルのロック状況を取得

まあ、ＤＢごとにパーティションを分けて配置するか、ディスクを分けておくのが
一番ディスクI/Oの速度は速そうですね。（一番いいのはディスクI/O自体を減らす？）

チューニング系の作業は地道だけど、それなりに面白いですよね。私だけかもしれませんが。

少し仕事でSQLServerのエラーに悩まされる事があったので
SQLServer自体が出力するログが無いのかどうかを調べてみました。

すると、（当然ですが）ありました。
詳細は調べていませんが、以下のSQL文をメモとして残しておきます。

-- エラーログを参照
EXEC sp_readErrorlog 5

-- トレース情報を出力（最新のトレースファイルの絶対パスなどが出力されていました）
select * from fn_trace_getinfo(default);

-- トレースファイルの中身を参照。（クエリなのにファイルを直撃のようです。）
select * from fn_trace_gettable(
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_424.trc',
default);
go

さすがにSQLServerの方でエラーが発生しているのにプログラミング言語レベルで
対応するのは難しいものがありますよね。（タイムアウトとか…。ただ、テーブル構成を
見直すとか、データベース自体のチューニングなどができれば良いんですが…）

今日は仕事が終わった後に「研修発表会」が開催されたので
参加してきました。
（と言っても研修を受けてこられた方が内容を説明する簡単なものなんですが）

内容はOracle 11g Bronze Masterレベルの内容らしく、Oracle11gが動作する仕組みを
説明していただきました。

SQLがキャッシュとしてメモリ上に残るしくみや
データ更新の仕組みなどなど。

プライベートでの刺激と同じような刺激を感じ、
とても有意義な時間だったと思います。

IF @iMoney >= 0
BEGIN
-- 現在の値よりも大きく、かつ最小の整数を取得(123.45→124）
SET @iCeiling = CEILING(@iMoney)
END
ELSE
BEGIN
-- 現在の値よりも小さく、かつ最大の整数を取得（-123.45→-124）
SET @iFloor = FLOOR(@iMoney)
END

マイナス数値の時にCEILING()を使うと、-123.45が-123になってしまい、
金額を扱う時には「切り上げ」にならない場合があります。
（って、時と場合に因りますかね？）

ちなみに、SQLServer限定だと思います…。

以前から「ローカルからはつながるけれども、リモートからはつながらない」という現象が
ずっと続いていて、以前の日記に書いたようにWindowsにインストールをしてRuby on Railsを
動作させていました。

しかし気になっていたので、色々とpg_hba.confとpostgresql.confを調べてみました。

すると、tcpip_socketという設定が無い事に気がつき、tcpip_socket = trueに
してみた所、デーモンが起動しなくなってしまいました。

それからまた調べていると、listen_addressesという設定でクライアントからの接続を
監視するという事で設定を見てみると、見事に#がついてコメントになっていました。
（要するに、デフォルト値が適用されてlocalhostのみだったと。）

そこで、範囲指定したかったのでlisten_addresses = '192.168.1.1/24'とした所、
serverlogに以下のように記録されていました。
<2008-02-11 10:21:54 JST 5046>WARNING: could not create listen socket for "192.168.11.1/24"
<2008-02-11 10:21:54 JST 5046>FATAL: could not create any TCP/IP sockets

これも失敗という事ですね。

仕方なく、listen_addresses='*'とした所、無事接続できました。

ドキュメントを見てみると、カンマで区切ってホストを追加する事で複数のホストを許可する事が
できるみたいですが、DHCPを導入しているためそんなことやってられない…。

ということで、現実解はiptablesにて拒否したいアドレスからのパケットを捨てるしかなさそうですね。

久しぶりにPostgreSQLをいじりました。
以前にいじりまわって、多少の知識はあったのですが、
長々と業務で忙しい状態だったので、ほぼ忘れていました。（年のせいなんだろうか？）

やはり、今仕事で触っているSQLServer2005と比べるとやっぱりSQLServer2005の方が
操作性は非常に高いです。（Management Studioを使ってるのと
コマンドラインからの操作を比べる方がおかしいが）
しかし、GUIからの操作ばかりをやっているといざコマンドラインしかないような環境に
出くわすと非常に痛い事になります。（SQLServerをコマンドプロンプトから操作する方法は
私は知らない:-)）

また、Linuxを久しぶりに「本格的に」使ったのでだいぶ感覚が鈍っているような
気がしました。GUIに慣れてしまったと言う事でしょうか。（コマンドラインからの方が
実際に何をしているのかが理解できて良いんですけどね。。。）

これからゆっくりと、Ruby on Railsと一緒に知識を深めていこうと思っています。

昨日、以下のSQLを実行すると予想外の結果が返ってきました。

SELECT * FROM [TestDB].[dbo].[JapaneseTest]
WHERE name = N'あさがお'

結果
----
あさがお
アサガオ

本来は「ひらがな」のデータが取得できる事を想定するのですが、
実際に取得できたのは「ひらがな」と「カタカナ」両方が取得できてしまいました。

しかし、以下の事をしたら、しっかり比較できるようになりました。

SELECT * FROM [TestDB].[dbo].[JapaneseTest]
WHERE name = N'あさがお'　COLLATE Japanese_90_BIN2

結果
----
あさがお

どうやら、デフォルトの状態ではかな、半角全角などを区別しないような
設定になっているようですね。その照合順序をバイナリ比較に変えて
比較させるようにした。ようです。

なお、この照合順序はテーブルのカラムごとに変更が可能なようです。
テーブルのカラムに設定を施すと、１つ目のSQLでも実行が可能になるようです。

テーブル自体にも設定ができるが、その場合は１つ目のSQLの結果は
変わりませんでした。

それから、デフォルトの設定もインストール時に変更すれば良いらしいですが、
そこまでは試せませんでした。

もっと動作を理解しなければいけませんね。