mysqlサーバで利用しているサーバの容量が減ってきたので別サーバに移行することに。
しかし、旧サーバの空き容量がないのでmysqldumpで出力することができない。
限界まで削除して１テーブルごとに出力するか。
すごく非効率。
そこで直接ダンプファイルを新サーバに出力できないか検討してみる。
続きを読む
「MySQL」タグアーカイブ
owncloudを4.5から5にアップデート
プライベートストレージとして、owncloudを使ってるんですがブラウザでログインするとできなかったので、とりあえず５にアップデートしてみることに。
手順としては、
1.既存のファイルをバックアップ。
2.ファイルのダウンロード、解凍後上書きコピー
3.mysqlのデータベースを削除。
これでアップデート完了。
初期画面が表示され、ログイン。
あれ？
やっぱりできない。
ログを見てみると、PHPのsessionフォルダの権限の問題。
権限を設定したらログインできました。
ついでに、クライアントもアップデート。
なぜか、mac版は英語だけど。
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しかないので、設定を変更してください。」と。
とりあえず、指定された設定値を増やして問題は解決しました。
レコードが増加することを当初の設計段階で把握できてなかった私の落ち度でした。
MySQLで処理時間の長いクエリを実行すると・・・
MySQLって軽快な動作で、検索が早いと評判ですがどうしてもレコード数の増加、並び替えの必要な場合など、処理によっては異常に処理時間が長くなることはないですか？
処理時間の長いクエリは、システムとしては欠陥クエリなんですけど、そこは置いておいて。
ORDER BY句で並び替えをした場合のSQL文をExplainで確認してみると、ExtraにUsing filesortという表示が出た場合の話です。
この表示が出た場合、SQLクエリの処理に時間がかかります。レコード数に比例して。
とあるシステムで、検索に失敗することがありました。
開発環境ではレコード数が少ないので異常はなかったのですが。
「Got error 28 from table handler」が表示されます。