mysql


php + mysql のデータマイニングを高速化したときの覚書

mysql のデータベース内に入っているデータをphpを使ってデータマイニングした際、時間がかかったのでしたこと。

何も対策せずにすると35日ほどかかった。

もう一度する必要でてきたのでそのときにしたこと。

ブラウザを複数立ち上げて同時に処理していった。

このとき同じブラウザ(IE)でやると、それぞれのブラウザで処理が倍になってトータル時間が変わらなかった。

なので、IE、chrome、opera の三つで同時に行った。

単純に三倍速くはならなかったけど、2.5倍ほど早くなったように思う。

【mysql】Row size too large (> 8126) のエラー対処法

mysql で update を実行した際、以下のエラーが出た

-------------------------------------------------
Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
-------------------------------------------------

ROW_FORMATが「Compact」のときに、8126バイト以上のレコードを入力しようとすると出るエラーらしい。

ターミナルの mysql から現在の ROW_FORMAT を確認

-------------------------------------------------
use データベース名 ← データベースを選択

SHOW TABLE STATUS LIKE 'テーブル名'\G
-------------------------------------------------

出力結果
*************************** 1. row ***************************
Name: テーブル名
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 35155
Avg_row_length: 4583
Data_length: 161136640
Max_data_length: 0
Index_length: 0
Data_free: 6291456
Auto_increment: 46319
Create_time: 2018-03-09 16:18:39
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)

Row_format の Compact の部分を、DYNAMIC か COMPRESSED に変更すれば消えるらしい。で、変更するには innodb_file_format が Barracuda になっていないといけない。

innodb_file_format を確認

-------------------------------------------------
mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_file_%';
-------------------------------------------------

出力結果
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
+--------------------------+-----------+
4 rows in set (0.00 sec)

innodb_file_format が Antelope なので Barracuda に変更する必要がある。

my.cnfを編集
-------------------------------------------------
vim /etc/my.cnf
-------------------------------------------------

[mysqld]以下に追記
-------------------------------------------------
[mysqld]
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
-------------------------------------------------

service mysqld restart で再起動

変更を確認
mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_file_%';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
+--------------------------+-----------+
4 rows in set (0.00 sec)

↑一度 mysqld を再起動しても反映されていない時があった。必ず反映していることを確認すること。

ここまでで、Row_format を Compact → DYNAMIC (or COMPRESSED)に変更する下準備ができたことになる。

再度確認
-------------------------------------------------
use データベース名 ← データベースを選択

SHOW TABLE STATUS LIKE 'テーブル名'\G
-------------------------------------------------

まだ Row_format は Compact のまま

今回は ROW_FORMAT を DYNAMIC にするので以下を実行
-------------------------------------------------
mysql> ALTER TABLE `テーブル名` ROW_FORMAT=DYNAMIC;
-------------------------------------------------

無事に変更されていることを確認する
-------------------------------------------------
use データベース名 ← データベースを選択

SHOW TABLE STATUS LIKE 'テーブル名'\G
-------------------------------------------------

phpmyadminでデータベースをエクスポートしたらphpの実行時間オーバー

phpmyadminからデータベースをエクスポートしたらphpの実行時間オーバーでできなかった。

解決方法1
ターミナルでサーバーにログインしてダウンロードする

phpの実行時間オーバーなので php を使わずにエクスポートする。

////////////////////////////////////////
mysqldump -u root -p database_name > ダウンロード後のファイル名.sql
////////////////////////////////////////

上記は mysql はログインせずに実行する。ダウンロード先はカレントディレクトリになるので、不都合ならパスを指定する。

解決方法2
php の実行時間を延長させる。

【mysql】データベースの照合順序を変更する

データベースの照合順序を変更する
---------------------------------------------------------
ALTER DATABASE データベース名 COLLATE utf8mb4_general_ci
---------------------------------------------------------

テーブルの照合順序を変更する
---------------------------------------------------------
ALTER TABLE テーブル名 COLLATE utf8mb4_general_ci
---------------------------------------------------------

さくらVPSで外部ホストからmysqlに接続する

さくらVPSで外部ホストからmysqlに接続するには、接続を許可するユーザーを作ればいい。

参考サイト
MySQLに外部ホストから接続できるように設定する

接続を許可するユーザーを作るSQL
-------------------------------------------------
grant all privileges on 接続を許可するDB名.* to new_user_name@"接続元のホスト" identified by 'password' with grant option;
-------------------------------------------------

grant → 付与。許諾する。mysqlで権限を付与する命令。

all privileges → 全ての権限を与える。

privileges → プリビレッジ。権限の意。

sourceを使ってsqlファイルをインポートする

sourceを使ってsqlファイルをインポートするスクリプト

-------------------------------------------------
use データベース名;
source /var/www/html/file.sql;
-------------------------------------------------

【PHP】mysqlでselectしたデータの出力時に使う変数名に変数を使う

$get_datas['カラム名'];

↓↓↓

$get_datas[$culumn_name];

変数名をそのまま記述すればOK。可変変数は不要。

KAGOYAのデータベースプランにターミナルでアクセス

KAGOYA のデータベースプラン(SSD 共有)の DB サーバーにアクセスする方法、覚書。

1.kagoya のサーバーコントロールにログインして、データベースをつくる。そのデータベースのアクセス許可にアクセス元の IPアドレス を登録する。自分の場合はさくら共有サーバーのIPアドレス。

2.ターミナルからさくらサーバーにアクセス。さくらサーバーからさくらの DB にアクセスするように kagoya の DB にアクセスする。

コマンド(さくらサーバーにログイン後)
----------------------------------------------------
% mysql -h mysql*****.kagoya.net -u ユーザーID -p --default-character-set=utf8
----------------------------------------------------

以後、さくらの DB を使っているのと同じように kagoya の DB を使用できる。

【mysql】さくらでRow size too large エラーの対処法

mysqlでupdateをしてデータを増やそうとしたらエラーが出た

エラー内容
---------------------------------------------------------------
Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
---------------------------------------------------------------

これは「InnoDBの最大行長(最大行サイズ)は約8000バイト」なので発生したエラー。
最大行サイズとは、一行ごとに格納できるデータの最大容量。

VARCHAR(255)やTEXTは最大で800バイト弱を使用するので、そういったカラムが11より多い発生する可能性が出てくる。

InnoDBの最大行長8000バイトの壁は、innodb_file_format を Antelope から別のものに変更すればいいらしいが、my.cnf の設定をいじる必要がある。しかし、さくらの共用サーバープランではいじれないので諦める。

不要なカラムを削除して対応した。

さくらのmysqlで大きなファイルをインポートしたら後半のデータが文字化けした

サクラインターネットのレンタルサーバーをプラン変更したとき、古いデータベースサーバーから新しいデータベースサーバーにデータを移行する必要があった。古いデータを mysqladmin から sql ファイルとしてエクスポートして、新しいデータベースサーバーへはターミナルで接続して source コマンドでエクスポートした。

すると、いくつかあるテーブルの内、後半のテーブル内のデータが文字化けする現象に遭遇した。インポート中のターミナル画面を見ていると気になるエラー?を発見。

--------------------------------------------
Query OK, 870 rows affected (0.02 sec)
Records: 870 Duplicates: 0 Warnings: 0

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...

Query OK, 850 rows affected (0.02 sec)
Records: 850 Duplicates: 0 Warnings: 0
--------------------------------------------

いったんデータベースサーバーへの接続が切断されて、その後再接続が行われている。そして、その再接続後から文字化けが起こっている?

このエラーは、インポートするデータが大きすぎると起こるらしい。

自分でいじれるサーバーであれば、max_allowed_packet の値を上げることで解決できるようだが(参考)、さくらの自分のプランでは、my.cnf をいじることができないため通用せず。

mysql の文字コードを確認してみる。

//コマンド
-------------------------------
mysql> show variables like "chara%";
-------------------------------

+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | ujis |
| character_set_connection | ujis |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | ujis |
| character_set_server | ujis |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+

utf8 のデータだが、設定されている文字コードは ujis が多い。文字コードを変更する最も一般的な方法は my.cnf を書き換えることだが自分のプランではできない。ターミナルで mysqlサーバーへログインするときに 文字コードを指定してみる。

//ターミナルで文字コードを設定
-------------------------------
mysql -u ユーザー名 -p -h ホスト名 --default-character-set=utf8
-------------------------------

//設定後
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | ujis |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+

character_set_database は use でデータベースを選択すると、データベースの文字コードになる。

//最終的にこうなった
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | ujis |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+

この状態で sql ファイルを source で実行したら文字化けは起こらなかった。

1 / 612345...最後 »