まだ重たいCMSをお使いですか？
毎秒1000リクエスト を捌く超高速CMS「adiary」
毎秒1000リクエスト を捌く超高速CMS「adiary」
2023/08/11(金)Perl DBI と prepare_cached() の罠
Webアプリケーションで、prepare_cached() を使うと色々問題があったのでメモ。
問題あり
prepare_cached() を使用すると、キャッシュヒット時（同じSQL文実行時）に$sthを再生成せずに再利用され、データ取得を含めた実行速度が220usが200usになるぐらいの効果があります。
しかし、prepare_cached() された状態でテーブルのカラムが変更されると問題が起こることが分かりました。
2012/12/19(水)MySQL設定メモ
InnoDBでMySQLを運用することを前提に書きます。
my.cnfの設定
MySQL 5.5以降ならばInnoDBの新しいエンジンが入っているが、5.1.xの場合はプラグインとなっているのでそれを有効化する。
[mysqld] ignore-builtin-innodb plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
また同時に、デフォルトエンジンとしてInnoDBの設定、及びutf8を設定する。
[client] default-character-set=utf8 [mysqld] character-set-server=utf8 default-storage-engine=InnoDB
mysqlコマンド
コマンドライン書式の例。
# mysql -u root -p # mysql -u user -pPASS dbname # mysql -h db-server.dummy.xxx -P 3306 -u user -pPASS dbname
DBとユーザーの作成
CREATE DATABASE dbname DEFAULT CHARSET=utf8; GRANT all privileges ON dbname.* TO dbuser@localhost IDENTIFIED BY 'PASSWORD'; GRANT SELECT,INSERT,UPDATE,DELETE ON privileges ON dbname.* TO dbuser@localhost IDENTIFIED BY 'PASSWORD';
IPアドレス等を指定する場合は次のようにする。
| 書式 | 意味 |
|---|---|
| uname@localhost | UNIXドメインソケット通信を許可 |
| uname@127.0.0.1 | ローカルホスト(TCP/IP)を許可 |
| uname@'%' | 全てのホストを許可 |
| uname@192.168.1.12 | IPアドレス指定 |
| uname@'192.168.%' | IPアドレスが192.168.* のホストを許可 |
この他、Firawall(iptables)を設定するのはもちろんのこと、MySQLはhosts.allowも参照する。
mysqld: ALL
ユーザー情報の確認
use mysql; select host,user,password from user;
パスワード以外は直接書き換えれば良い。
パスワードは次のようにする。
SET PASSWORD FOR user@localhost=PASSWORD('password');
SET PASSWORD FOR user@your.host=PASSWORD('password');
文字コードの確認
show variables like 'char%';
2010/01/27(水)PostgreSQL関連メモ
あとで適当に追記。
共有メモリ設定
共有メモリの確認
# ipcs -m # cat /proc/sys/kernel/shmmax
共有メモリの変更
1G # echo 1099511627776 >/proc/sys/kernel/shmmax 1.5G # echo 1649267441664 >/proc/sys/kernel/shmmax
起動時に設定するには。
# /etc/sysctl.conf kernel.shmmax = 1649267441664
制約やカラムの操作
Ver8.3で確認。テーブル名、カラム名が変更されていても作成時のものとなるので注意。
| 制約の種類 | 制約名 |
|---|---|
| PRIMARY KEY | [table]_pkey |
| UNIQUE | [table]_[col]_key |
| REFERENCES | [table]_[col]_fkey |
ALTER TABLE table ADD CONSTRAINT table_[col]_fkey FOREIGN KEY ([col]) REFERENCES usr([ref_col]); ALTER TABLE table DROP CONSTRAINT table_[col]_key;
| 目的 | SQL |
|---|---|
| NOT NULLを付ける | ALTER TABLE table ALTER COLUMN [col] SET NOT NULL |
| NOT NULLを外す | ALTER TABLE table ALTER COLUMN [col] DROP NOT NULL |
| DEFAULTを付ける | ALTER TABLE table ALTER COLUMN [col] SET DEFAULT [value] |
| DEFAULTを外す | ALTER TABLE table ALTER COLUMN [col] DROP DEFAULT |
| カラム名を変える | ALTER TABLE table RENAME [old_col] TO [new_col] |
| テーブル名を変える | ALTER TABLE table RENAME TO table2 |
2009/09/04(金)MySQL/PostgreSQLでのシリアル値まとめ
adiaryではすべてのテーブルに pkey というシリアル値（PRIMARY KEY）を設定しています。その扱いについて。
特にMySQLで特別な加工をせず、安全にシリアル値を取得する方法。
PostgreSQLの場合
PostgreSQLではそのままserial型というものがあり、
CREATE TABLE test(pkey SERIAL PRIMARY KEY, x INT); INSERT INTO test(x) VALUES(10);
とすることで、pkeyをプライマリキーとして自動的に生成することができます。
PostgreSQLではシーケンス操作関数というものがあり、SERIAL型を定義すると自動的に作成されます。
例えば、現在の値を取得したければ
SELECT currval(pg_catalog.pg_get_serial_sequence('test', 'pkey'))
とします。pkeyに値を設定して INSERT したとき、シーケンス関数は自動的に再定義されないため次の関数で再設定する必要があります。
SELECT setval(pg_catalog.pg_get_serial_sequence('test', 'pkey'), (SELECT max(pkey) FROM test))
現在の値（最後に生成された値）を取得するには次のようにします。
SELECT currval(pg_catalog.pg_get_serial_sequence('test', 'pkey'), (SELECT max(pkey) FROM test))
同じセッション内でテーブル問わず最後に生成された値を取得する場合は次で済みます。
SELECT lastval()
安全に（他のセッションともかぶらない唯一無二な）次のシリアル値を取得するには、次のようにします。
SELECT nextval(pg_catalog.pg_get_serial_sequence('test', 'pkey'))
MySQLの場合
MySQLにも5.1以降（それより前から？）SERIAL型があります。
CREATE TABLE test(pkey SERIAL PRIMARY KEY, x INT); INSERT INTO test(x) VALUES(10);
これによりpkeyに自動的にシリアル値を設定することができます。
- SERIALは「BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE」のエイリアスです。
- AUTO_INCREMENTは１つのテーブルに１カラムしか指定できない。
- AUTO_INCREMENTを指定したカラムに、例えばpkeyを指定してINSERTしても、AUTO_INCREMENTがよきにはからってくれる（PostgreSQLのように値を再設定する必要なし）。
現在の値（最後に生成された値）を取得するには次のようにします。
SELECT LAST_INSERT_ID();
ただしC API等では mysql_insert_id()というものがあるため、こちらを使用するほうが効率が良いようです。例えばPerl DBIならば $sth->{mysql_insertid}。
追記。
SELECT * FROM test WHERE pkey IS NULL;
以前はこの方法でも取得が可能でしたが、いつの間にか使えなくなっているようなので注意しましょう。
値を取得、値の変更
SHOW TABLE STATUS WHERE NAME = 'test';
で得られたテーブル情報の中からAuto_incrementカラムの値を参照します。得られるのは次に挿入される値です。*1
何かの都合で値を設定するときは次のようにします。
ALTER TABLE test AUTO_INCREMENT=1;
安全に次の値を取得
MySQLでもっとも厄介なのはこれです。auto_incrementには安全に次の値を得る方法が提供されていません。別にシーケンステーブルを作る方法などがありますが、面倒です。
INSERTでしか得られないならINSERTしてしまえばいいやという単純な方法です。
INSERT INTO test() VALUES(); DELETE FROM test WHERE pkey=LAST_INSERT_ID(); SELECT LAST_INSERT_ID();
NOT NULL制約等が付いているとINSERTが失敗するので、CREATE TABLE等であらかじめ制約を満たすDEFAULT値を設定しておく必要があります。MyISAMではトランザクションが使えないため、NOT NULLかつUNIQUE制約が付いていると、複数のプロセスが同時に"INSERT INTO test() VALUES();"を実行したとき一方が失敗します。
MySQLの場合はINSERTしてUPDATEした方がスマートなのはたしかなのですが、PostgreSQLではシーケンス型の次の値を安全に取得できるし、INSERTに成功してUPDATEに失敗した場合を考えるとややこしいので。