「備忘録（MySQL）」カテゴリーアーカイブ

MySQLの接続が遅い

とあるActive directyドメインネットワーク内のMySQLサーバーとドメインネットワーク外のWebサーバー(Apache＋PHP)との接続があるタイミングから遅くなってしまった。
元々、ドメインネットワーク内に属していたWebサーバーでしたが、DMZに入れることになりドメインネットワークから切り離したタイミングでMySQLの接続が遅い現象が発生。
DMZとのネットワーク関係を疑っていたのだけど、解決方法がわからず。

そんな中、LinuxOSで構築したWebサーバーからも同様に接続が遅い。
接続ができないわけでなく、遅い。
接続に５秒から２０秒かかる。

逆引きができていない場合は同様の現象が発生するらしく、MySQLサーバーをホスト名ではなくIPアドレスで指定すると解決するとか。
しかし、MySQLサーバーとの接続はIPアドレス指定。

結論として、名前解決がうまくいっていないため、ログイン認証に時間がかかっているだけでした。

MySQLサーバー側のhostsファイルに接続が遅いPCのIPアドレスとコンピュータ名を追記することで解決。

また、localhostとMySQLサーバーを指定していたホスト名を127.0.0.1で指定すると１秒程度かかることがあった接続が１秒以下になりました。

MySQLで容量の大きいバイナリデータを保存する場合

画像などのバイナリデータをMySQLに格納するために、BLOB型やMEDIUMBLOB型を利用することがありました。

BLOB型でサイズの大きいデータをMySQLに格納しようとするとMySQLのログにエラーログが出力されています。

100906 00:00:00 InnoDB: ERROR: the age of the last checkpoint is 9440228,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
調べたところ、MySQLのログファイルのサイズが小さいために発生している模様。

MySQLは、テーブルスペースの更新に時間がかかるため、直接テーブルスペースを更新するのではなく、最初にログファイルに全ての更新を書き込み、そのあと非同期でテーブルスペースを更新するという仕組みになっています。

このログファイル・サイズはデフォルトで5MBが設定されていますが、ログファイルの合計サイズは、 innodb_log_files_in_group という設定値(デフォルト2)と、innodb_log_file_size との積で、トータル10MBとなります。

そのため、10MB以上のファイルをDB(MEDIUMBLOB/BLOB型のカラム)に格納しようとするとログファイルサイズをオーバーしてしまい、エラーとなるため、ログファイルのサイズを拡張しなければなりません。

変更方法は以下のとおりです。
続きを読む →

mysqlデータを別サーバに移行

mysqlサーバで利用しているサーバの容量が減ってきたので別サーバに移行することに。
しかし、旧サーバの空き容量がないのでmysqldumpで出力することができない。
限界まで削除して１テーブルごとに出力するか。
すごく非効率。

そこで直接ダンプファイルを新サーバに出力できないか検討してみる。
続きを読む →

MySQL max_join_size は意外に重要な設定

先日、とあるレンタルサーバーで特定の処理が動かないというトラブルが発生しました。

以前から、トラブルが続いていたレンタルサーバーだったのですが、今回は以前とは違う種類のトラブルでした。
前回は、MySQLサーバーへの接続に失敗するというトラブルだったのですが、今回は特定のSQL文を実行するとエラーとなってしまうトラブルでした。
そのSQL文とは、「join」を含むSQL文です。

さて、原因は分かったけど解決方法は？

全部の「join」を書き直すとなると、相当な労力だ。
どうにかならないものか。

とりあえず、データベースの設計を見直すことに。

なぜか、このシステムのデータベースにはインデックスの設定がされていない。
PRIMERY　KEYは設定してあるんですが。
とりあえず、気休めでもいいからインデックスを設定しておこう。
このインデックスの設定が正解かどうかは分からないが、「join」を含むSQL文でも正常に処理が行われるようになった。
んー、インデックスを設定することで参照レコードが少なくなった？？

何はともあれ、システム無事復旧。

私は、あんまり「join」を使わないのですが今回のことで「join」を使いこなせると便利だが、諸刃の剣では？と思うようになった。
それから、インデックスは必ずDB設計する時には設定をする。

普段から、1､000万単位のDBを扱っているのでインデックスにはシビアに考えているので私自身は問題ないんですけどね。

ちなみに、レンタルサーバー会社にこのことを問い合わせてみると
共用サーバーなので、他の誰かが無茶なSQLを流しているみたいで制限をかけた。
とのこと。
そういう大事なことは、事前に伝えないといけないと思うんですが。

MySQLバージョンアップについて

とある事情でMySQLを4.0.26から5.1.4にバージョンアップするこになったのですが、問題が山積みでこまっています。
同じMySQLだから、互換性はあるだろうとたかをくくっていいたので、出鼻をくじかれっぱなし。

その中の一つを紹介します。
MySQL4.0.26に格納されいるデータを移行しようとしたのですが、エラーが出てどうしても挿入できないということでした。
エラー内容は、Field ‘%s’ doesn’t have a default value　というようなエラーで、ググッてみるとすぐに解答は見つかりました。
ただ、根本的な解決になるかは不明ですが。
my.iniの【sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”】という項目をコメントアウトすることで解消。

他にも、MySQL5.1系は日付のチェックをしてくれる親切設計なのですが、既存データには2007-02-29などのありえない日付が格納されている。これも丁寧にエラーとしてはじかれます。
ただ、ユーザ側でMySQLのエラーを表示するわけにもいかないので、事前にPHP側で日付の整合性をとることにして解決。

それにしても、速度的には4.0.26のほうが快速だと個人的には思うのですが、テーブル毎のファイル分割をしたいがために、こんなに手間になるなんて。

とある、メーリングリストに4.0.26、4.1系、5.1系の中でどれか軽快で速度が速いですがという質問をしたのですが、「最新版にしなさい」と言われorz
質問に対しての答えではない。
今後のこともあるので、最新版をチョイスして開発をすることに。

あー、バージョンアップなんてめんどくさいって思うことで、SEやってるんだなって実感を覚えました。

MySQLで大量のデータをINSERTするには

MySQLでデータを挿入するときには、INSERT文を使っていると思います。
１レコードや１０レコードぐらいのデータをINSERTするときには、そんなに気にならないのですが、数万～数百万単位のデータをINSERTする場合、非常に時間がかかります。
レコード数が多くなれば時間がかかるのはしょうがないことなのですが、できれば処理時間は短くしたいものです。

そこで、バルクINSERT文というものを使えば処理時間は劇的に短縮できます。
私の場合、通常のINSERT文は[INSERT INTO {table_name} SET {カラム名} = {値}]と記述しています。
これを[INSERT INTO {table_name} (カラム名、カラム名) VALUES(値１,値２),(値３,値４)]のようにある程度のレコードをまとめてSQL文を生成して、一括でクエリを実行すると処理は劇的に短縮されます。

MySQLバージョンアップの際の注意　予約語

とあるシステムでMysqlのバージョンをあげることになりました。
現在のバージョンは4.0.26で5.0.45に。
とりあえずシステム的に大きな修正はないはずと踏んでいたのですが、いきなりつまずいてしまいました。

まず、MySQLには予約語というものが存在しています。
この予約語はカラム名や変数に割り当ててはいけません。
しかも、この予約語はバージョンごとに違うのです。
今回問題になったのが、カラム名として使っていた[condition]という文字列。

4.0.26では予約語ではなかったので、問題なかったのですが、5.0.45ではしっかりと予約語として登録されていました。

なんだか不安な展開orz

< Mysql4.1系の予約語 >
< Mysql5.1系の予約語 >

MySQL　InnoDBエンジンでのibdata分割

とあるシステムが運用していたのですが、データ数の肥大化に伴い設計を見直すことになりました。
そこで、現在１つのDBで運用しているものを複数のDBに分割して構築することになりました。

以前から気になっていた、MySQLデータフォルダ内に格納してあるibdataのファイルサイズの肥大化も
解消されるのかと思いきやどうも複数のDBであっても１つのibdataファイルを共有するようなのです。

唯一の方法として、InnoDBエンジンのテーブル毎ibdataの分割という手段があったのでとりあえずこの方法で
設計をすることにしました。

テーブル毎のibdata分割の方法については、my.cnf内の[mysqld]の項目内に「innodb_file_per_table」と記載するだけです。

ちなみに、この方法はMySQL4.1系以降から利用できるみたいです。
私の場合は、MySQL4.0.26なのでMySQLのバージョンから見直しですorz

この設定に変更した場合であってもテーブルスペースは必要です。
テーブルスペースにはテーブル毎のデータやインデックスの値だけでなくInnoDBに関する情報やREDOログなどが含まれるからです。 

MySQLのストレージエンジン

MySQLには複数のストレージエンジンが選択できます。
その中で代表的なものが「MyISAM」「InnoDB」「HEAP」の三つだと思います。

それぞれに特徴があります。
まず、「MyISAM」について。
MySQL独自の形式であり、MySQLの特徴である軽快で高速な動作をするという所以のエンジンです。
ただ、速度を重視するためトランザクションをサポートしていません。
一般的には、参照テーブルなどに利用されていると思います。

次に、「InnoDB」。
このエンジンはMyISAMとは異なり、トランザクション、参照整合性制約、行レベルロック、インスタンス障害時の自動リカバリなどを
サポートしています。Oracleの表と似ています。
ただし、速度的には低速になります。
また、ディスクの容量についても、MyISAMより多くの容量を必要とします。

最後に、「HEAP]ですが、私もあまり使わないエンジンなので語弊があるかもしれません。
最大の特徴は、すべてのデータがメモリ上でのみ扱われます。その為、非常に高速です。
そのかわり、TEXT型やBLOB型などの列長の長いデータは使用できませんし、トランザクションもサポートしていません。
また、メモリ上での扱いと言う点からインスタンスの停止によってデータが消失します。

それぞれのエンジンに特徴があるので、データベース設計時には必要なエンジンを選択する必要があります。
※ここで言うエンジンの高速・低速という表現は一般的な表現ですが、システムの構築・データによっては逆になる場合があります。

MySQLで、既存テーブルにカラム追加できない？

MySQLでとあるシステムを構築、運用していたときのことです。
要望があり、項目追加の為テーブルにカラムを追加することがありました。
「ALTER　TABLE・・・」でカラムを追加しようとしたのですが、MySQLのサービスが停止してしまいました。

既存テーブルにはレコードが９００万件近くあり、なぜ？？と頭をひねっていました。

とりあえずOS,Apache、MySQL全てのログを採取して洗い出したところ原因が判明しました。

原因はMySQL側にあり、ログには「Innodb_buffer_pool_sizeが８Mしかないので、設定を変更してください。」と。

とりあえず、指定された設定値を増やして問題は解決しました。

レコードが増加することを当初の設計段階で把握できてなかった私の落ち度でした。