-->

2013-08-18

memo: postgresql, mysql

/etc/mysql/my.cnf /etc/postgresql-9.2/postgresql.conf 変更無し。

$ eix -C dev-db "^(mysql|postgresql-server)$"|grep "Installed versions:"
     Installed versions:  5.1.70(11時28分41秒 2013年08月18日)(community perl ssl -big-tables -cluster -debug -embedded -extraengine -latin1 -max-idx-128 -minimal -pbxt -profiling -selinux -static -test -xtradb)
     Installed versions:  9.2.4(9.2)(12時40分02秒 2013年08月18日)(nls pam perl -doc -kerberos -pg_legacytimestamp -python -selinux -tcl -test -uuid -xml KERNEL="linux" LINGUAS="-af -cs -de -en -es -fa -fr -hr -hu -it -ko -nb -pl -pt_BR -ro -ru -sk -sl -sv -tr -zh_CN -zh_TW" PYTHON_SINGLE_TARGET="python2_7 -python2_5 -python2_6 -python3_1 -python3_2 -python3_3" PYTHON_TARGETS="python2_7 python3_2 -python2_5 -python2_6 -python3_1 -python3_3")


$ sudo su - -c "echo 3 > /proc/sys/vm/drop_caches"
$ sudo /etc/init.d/mysql restart


mysql> create table test004 (id serial, data text) ENGINE=innodb;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table test004\G
*************************** 1. row ***************************
       Table: test004
Create Table: CREATE TABLE `test004` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `data` text,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


$ for cnt in $(seq 1 10); do ab -n2000 -c200 http://192.168.0.110:2000/ 2>&1 | grep -iP "^((Non-2xx responses)|(Failed requests)|(   \\(Connect)|(Complete requests)|(Requests per second)):"; done | sort | uniq -c | sort -k1nr,5n
     10 Complete requests:      2000
     10 Failed requests:        0
      1 Requests per second:    206.60 [#/sec] (mean)
      1 Requests per second:    213.36 [#/sec] (mean)
      1 Requests per second:    221.74 [#/sec] (mean)
      1 Requests per second:    221.75 [#/sec] (mean)
      1 Requests per second:    224.32 [#/sec] (mean)
      1 Requests per second:    226.38 [#/sec] (mean)
      1 Requests per second:    239.60 [#/sec] (mean)
      1 Requests per second:    242.41 [#/sec] (mean)
      1 Requests per second:    242.47 [#/sec] (mean)
      1 Requests per second:    265.60 [#/sec] (mean)


mysql> select * from test004 order by id desc limit 1;
+-------+----------------------------+
| id    | data                       |
+-------+----------------------------+
| 20011 | 2013-08-18 11:59:06.715882 |
+-------+----------------------------+
1 row in set (0.03 sec)


$ sudo su - -c "echo 3 > /proc/sys/vm/drop_caches"
$ sudo /etc/init.d/mysql restart


mysql> drop table test004;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test004 (id serial, data text) ENGINE=myisam;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table test004\G
*************************** 1. row ***************************
       Table: test004
Create Table: CREATE TABLE `test004` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `data` text,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


$ for cnt in $(seq 1 10); do ab -n2000 -c200 http://192.168.0.110:2000/ 2>&1 | grep -iP "^((Non-2xx responses)|(Failed requests)|(   \\(Connect)|(Complete requests)|(Requests per second)):"; done | sort | uniq -c | sort -k1nr,5n
     10 Complete requests:      2000
     10 Failed requests:        0
      1 Requests per second:    2402.95 [#/sec] (mean)
      1 Requests per second:    2435.10 [#/sec] (mean)
      1 Requests per second:    2438.26 [#/sec] (mean)
      1 Requests per second:    2471.78 [#/sec] (mean)
      1 Requests per second:    3124.24 [#/sec] (mean)
      1 Requests per second:    3176.14 [#/sec] (mean)
      1 Requests per second:    3179.68 [#/sec] (mean)
      1 Requests per second:    3180.62 [#/sec] (mean)
      1 Requests per second:    3232.88 [#/sec] (mean)
      1 Requests per second:    3235.57 [#/sec] (mean)


mysql> select * from test004 order by id desc limit 1;
+-------+----------------------------+
| id    | data                       |
+-------+----------------------------+
| 20000 | 2013-08-18 12:01:47.475692 |
+-------+----------------------------+
1 row in set (0.03 sec)


$ sudo su - -c "echo 3 > /proc/sys/vm/drop_caches"
$ sudo /etc/init.d/postgresql-9.2 restart


dbname=# drop table test004;
ERROR:  テーブル"test004"は存在しません
dbname=# create table test004 (id serial, data text);
NOTICE:  CREATE TABLEはシリアル列"test004.id"用に暗黙的なシーケンス"test004_id_seq"を作成します。
CREATE TABLE


$ for cnt in $(seq 1 10); do ab -n2000 -c200 http://192.168.0.110:2000/ 2>&1 | grep -iP "^((Non-2xx responses)|(Failed requests)|(   \\(Connect)|(Complete requests)|(Requests per second)):"; done | sort | uniq -c | sort -k1nr,5n
     10 Complete requests:      2000
     10 Failed requests:        0
      1 Requests per second:    1117.53 [#/sec] (mean)
      1 Requests per second:    1136.16 [#/sec] (mean)
      1 Requests per second:    1144.23 [#/sec] (mean)
      1 Requests per second:    1147.27 [#/sec] (mean)
      1 Requests per second:    1155.13 [#/sec] (mean)
      1 Requests per second:    1155.26 [#/sec] (mean)
      1 Requests per second:    1163.53 [#/sec] (mean)
      1 Requests per second:    1163.71 [#/sec] (mean)
      1 Requests per second:    1164.36 [#/sec] (mean)
      1 Requests per second:    974.07 [#/sec] (mean)


dbname=# select * from test004 order by id desc limit 1;
  id   |            data
-------+----------------------------
 20000 | 2013-08-18 13:05:01.021865
(1 行)

http://www.php.net/manual/ja/libevent.examples.php
https://gist.github.com/joseph-montanez/1122992
http://www.php.net/manual/ja/book.http.php

この結果はチューニングをしてなく内容も単純なので参考でしかありませんが
この中ではトランザクションとSQLが使いたいのであればpostgresqlが良く
SQLが使いたいのであればmysql-myisamが良いです。
またpeclのlibeventはphpからnodejsに変えるか検討している場合には
nodejs以外の選択肢の一つになると思います。
投稿者

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)