$ grep -nri mysql-proxy /etc/portage/
/etc/portage/package.keywords/x86_64-pc-linux-gnu:16:=dev-db/mysql-proxy-0.8.2 ~amd64
/etc/portage/package.use/x86_64-pc-linux-gnu:14:dev-db/mysql-proxy examples
sudo emerge --oneshot -avt mysql-proxy
$ emerge -pv mysql-proxy
These are the packages that would be merged, in order:
Calculating dependencies... done!
[ebuild R ~] dev-db/mysql-proxy-0.8.2 USE="examples" 0 kB
Total: 1 package (1 reinstall), Size of downloads: 0 kB
$ mysql -uroot -p -hlocalhost -P 4040 --protocol=TCP
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (111)
$ sudo /etc/init.d/mysql-proxy start
$ mysql -uroot -p -hlocalhost -P 4040 --protocol=TCP
Enter password:
ERROR 1105 (HY000): (proxy) all backends are down
$ netstat -an | grep 3306
tcp 0 0 192.168.0.110:3306 0.0.0.0:* LISTEN
tcp 0 0 192.168.0.110:45661 192.168.0.110:3306 TIME_WAIT
$ mysql -uroot -p -h192.168.0.110 -P 3306 --protocol=TCP
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
$ mysql -uroot -p -h192.168.0.110 -P 4040 --protocol=TCP
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.110' (111)
http://docs.oracle.com/cd/E17952_01/refman-5.0-en/mysql-proxy-faq.html#qandaitem-16-7-6-1-2
$ /usr/bin/sudo /usr/bin/rcsdiff /etc/config-archive/etc/mysql/mysql-proxy.cnf,v /etc/mysql/mysql-proxy.cnf
===================================================================
RCS file: /etc/config-archive/etc/mysql/mysql-proxy.cnf,v
retrieving revision 1.1
diff -r1.1 /etc/mysql/mysql-proxy.cnf
33c33
< proxy-backend-addresses = 127.0.0.1:3306
---
> proxy-backend-addresses = 192.168.0.110:3306
$ sudo /etc/init.d/mysql-proxy restart
$ sudo grep proxy-address /etc/mysql/mysql-proxy.cnf
proxy-address = 127.0.0.1:4040
$ mysql -uroot -p -h127.0.0.1 -P 4040 --protocol=TCP
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> GRANT SELECT ON test.* TO test@'%' IDENTIFIED BY 'test';
mysql> FLUSH PRIVILEGES;
$ mysql -utest -ptest -h192.168.0.110 -P 3306 --protocol=TCP
ERROR 1045 (28000): Access denied for user 'test'@'vmware-gentoo1' (using password: YES)
mysql> GRANT SELECT ON test.* TO test@'vmware-gentoo1' IDENTIFIED BY 'test';
mysql> FLUSH PRIVILEGES;
$ mysql -utest -ptest -h192.168.0.110 -P 3306 --protocol=TCP
Welcome to the MySQL monitor. Commands end with ; or \g.
$ mysql -utest -ptest -h127.0.0.1 -P 4040 --protocol=TCP
Welcome to the MySQL monitor. Commands end with ; or \g.
$ php -r 'var_dump(mysql_connect("192.168.0.110","test","test"));'
resource(4) of type (mysql link)
$ php -r 'var_dump(mysql_connect("192.168.0.110","test","testxxx"));'
PHP Warning: mysql_connect(): Access denied for user 'test'@'vmware-gentoo1' (using password: YES) in Command line code on line 1
$ php -r 'var_dump(mysql_connect("127.0.0.1:4040","test","test"));'
resource(4) of type (mysql link)
$ ab -n 1000 -c 100 "http://192.168.0.110/test/ab.php?dsn=2" 2>&1 | grep -iP "^((Complete requests)|(Requests per second)):"
Complete requests: 1000
Requests per second: 2253.65 [#/sec] (mean)
$ ab -n 1000 -c 100 "http://192.168.0.110/test/ab.php?dsn=1" 2>&1 | grep -iP "^((Complete requests)|(Requests per second)):"
Complete requests: 1000
Requests per second: 3483.41 [#/sec] (mean)
$ ulimit -n
1024
$ rcsdiff /etc/config-archive/etc/security/limits.conf,v /etc/security/limits.conf
===================================================================
RCS file: /etc/config-archive/etc/security/limits.conf,v
retrieving revision 1.1
diff -r1.1 /etc/security/limits.conf
48a49,50
> UserNameToRunTheBenchmarksOfApache soft nofile 8192
> UserNameToRunTheBenchmarksOfApache hard nofile 8192
soft => デフォルト値、hard => 最大値。
再度ログインで変更反映。
$ ulimit -n
8192
$ ab -n 50000 -c 5000 "http://192.168.0.110/test/ab.php?dsn=1" 2>&1 | grep -iP "^((Complete requests)|(Requests per second)):"
Complete requests: 50000
Requests per second: 2240.82 [#/sec] (mean)
$ ab -n 50000 -c 5000 "http://192.168.0.110/test/ab.php?dsn=2" 2>&1
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 192.168.0.110 (be patient)
Completed 5000 requests
Completed 10000 requests
apr_socket_recv: Connection reset by peer (104)
Total of 14923 requests completed
$ ab -n 50000 -c 5000 "http://192.168.0.110/test/ab.php?dsn=2" 2>&1
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 192.168.0.110 (be patient)
Completed 5000 requests
Completed 10000 requests
Completed 15000 requests
Completed 20000 requests
apr_socket_recv: Connection reset by peer (104)
Total of 22536 requests completed
http://dev.mysql.com/doc/refman/5.0/en/mysql-proxy-configuration.html#option_mysql-proxy_event-threads
$ /usr/bin/sudo /usr/bin/rcsdiff /etc/config-archive/etc/mysql/mysql-proxy.cnf,v /etc/mysql/mysql-proxy.cnf
===================================================================
RCS file: /etc/config-archive/etc/mysql/mysql-proxy.cnf,v
retrieving revision 1.1
diff -r1.1 /etc/mysql/mysql-proxy.cnf
8c8
< #event-threads = ...
---
> event-threads = 3
16c16
< #max-open-files = ...
---
> max-open-files = 8192
33c33
< proxy-backend-addresses = 127.0.0.1:3306
---
> proxy-backend-addresses = 192.168.0.110:3306
$ ab -n 50000 -c 5000 "http://192.168.0.110/test/ab.php?dsn=2" 2>&1 | grep -iP "^((Complete requests)|(Requests per second)):"
Complete requests: 50000
Requests per second: 1732.69 [#/sec] (mean)
sudo /etc/init.d/mysql restart && sudo /etc/init.d/mysql-proxy restart && sudo /etc/init.d/apache2 restart
(for CNT in $(seq 1 1); do for DSN in 1 2; do for C in 100 1000 5000; do \
N=`expr ${C} \* 10` && DATE=`php -r 'print preg_replace("/^0\.(.*?)0* (.*)$/e","date(\"Y-m-d-H-i-s-\",$2).\"$1\"",microtime());'` && \
echo && echo "===== CNT=${CNT}; DSN=${DSN}; C=${C}; N=${N}; DATE=${DATE};" && \
ab -n ${N} -c ${C} "http://192.168.0.110/test/ab.php?dsn=${DSN}&xxx=${DATE}" 2>&1 | grep -iP "^((Complete requests)|(Requests per second)):" && \
echo && echo "===== CNT=${CNT}; DSN=${DSN}; C=${C}; N=${N}; DATE=${DATE};" && \
grep xxx=${DATE} /var/log/apache2/access_log | awk '{print $9}' | sort | uniq -c && \
echo && echo "===== CNT=${CNT}; DSN=${DSN}; C=${C}; N=${N}; DATE=${DATE};" && \
mysqladmin status && \
echo && echo "===== CNT=${CNT}; DSN=${DSN}; C=${C}; N=${N}; DATE=${DATE};" && \
ps aux|grep -iP "^apache\s" | wc -l; \
done; done; done;) > /tmp/ab.txt
$ cat ab.php | perl -pe 's/([<>&])/{"<","<",">",">","&","&"}->{$1}/ge'
<?php
error_reporting(E_ALL|E_STRICT);
set_time_limit(0);
MAIN();
exit;
function MAIN()
{
$dsn = isset($_GET["dsn"]) ? (int)$_GET["dsn"] : 1;
$server = "";
$username = "";
$password = "";
switch ($dsn)
{
case 2:
$server = "127.0.0.1:4040";
$username = "test";
$password = "test";
break;
case 1:
default:
$server = "192.168.0.110:3306";
$username = "test";
$password = "test";
break;
}
$link = mysql_pconnect($server, $username, $password);
if (!is_resource($link))
{
header('HTTP', true, 504);
trigger_error(sprintf("mysql_pconnect error. [%s] %s", mysql_errno(), mysql_error()), E_USER_ERROR);
}
$result = mysql_query("SHOW FULL PROCESSLIST;", $link);
if (!is_resource($result))
{
header('HTTP', true, 505);
var_dump($result);
trigger_error(sprintf("mysql_query error. [%s] %s", mysql_errno(), mysql_error()), E_USER_ERROR);
}
$num = mysql_num_rows($result);
print "\$server: " . htmlspecialchars($server) . "<br />\n";
print "\$username: " . htmlspecialchars($username) . "<br />\n";
print "\$password: " . htmlspecialchars($password) . "<br />\n";
print "number of lines: " . htmlspecialchars($num) . "<br />\n";
print "<table border=1 style=\"empty-cells: show;\">\n";
$cnt = 0;
while ($row = mysql_fetch_assoc($result))
{
$cnt++;
if ($cnt == 1)
{
print "<tr>\n";
foreach ($row as $key => $val)
{
print "<th>" . htmlspecialchars($key) . "</th>\n";
}
print "</tr>\n";
}
print "<tr>\n";
foreach ($row as $key => $val)
{
print "<td>" . htmlspecialchars($val) . "</td>\n";
}
print "</tr>\n";
}
print "</table>\n";
mysql_free_result($result);
}
$ grep -B1 Threads /tmp/ab.txt
===== CNT=1; DSN=1; C=100; N=1000; DATE=2013-07-27-14-20-29-145301;
Uptime: 10 Threads: 6 Questions: 1001 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 100.100
--
===== CNT=1; DSN=1; C=1000; N=10000; DATE=2013-07-27-14-20-29-547357;
Uptime: 13 Threads: 5 Questions: 11010 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 846.923
--
===== CNT=1; DSN=1; C=5000; N=50000; DATE=2013-07-27-14-20-32-932006;
Uptime: 52 Threads: 251 Questions: 61078 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 1174.576
--
===== CNT=1; DSN=2; C=100; N=1000; DATE=2013-07-27-14-21-11-355416;
Uptime: 54 Threads: 501 Questions: 62079 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 1149.611
--
===== CNT=1; DSN=2; C=1000; N=10000; DATE=2013-07-27-14-21-13-197702;
Uptime: 69 Threads: 501 Questions: 72095 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 1044.855
--
===== CNT=1; DSN=2; C=5000; N=50000; DATE=2013-07-27-14-21-28-597448;
Uptime: 122 Threads: 249 Questions: 122100 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 1000.819
http://dev.mysql.com/doc/refman/4.1/ja/gone-away.html
$ grep -B2 "Requests per second" /tmp/ab.txt
===== CNT=1; DSN=1; C=100; N=1000; DATE=2013-07-27-14-20-29-145301;
Complete requests: 1000
Requests per second: 4871.13 [#/sec] (mean)
--
===== CNT=1; DSN=1; C=1000; N=10000; DATE=2013-07-27-14-20-29-547357;
Complete requests: 10000
Requests per second: 3195.77 [#/sec] (mean)
--
===== CNT=1; DSN=1; C=5000; N=50000; DATE=2013-07-27-14-20-32-932006;
Complete requests: 50000
Requests per second: 1314.47 [#/sec] (mean)
--
===== CNT=1; DSN=2; C=100; N=1000; DATE=2013-07-27-14-21-11-355416;
Complete requests: 1000
Requests per second: 631.75 [#/sec] (mean)
--
===== CNT=1; DSN=2; C=1000; N=10000; DATE=2013-07-27-14-21-13-197702;
Complete requests: 10000
Requests per second: 661.34 [#/sec] (mean)
--
===== CNT=1; DSN=2; C=5000; N=50000; DATE=2013-07-27-14-21-28-597448;
Complete requests: 50000
Requests per second: 953.87 [#/sec] (mean)
$ grep -B1 Threads /tmp/ab.txt
===== CNT=1; DSN=2; C=100; N=1000; DATE=2013-07-27-14-30-51-109835;
Uptime: 16 Threads: 6 Questions: 1001 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 62.562
--
===== CNT=1; DSN=2; C=1000; N=10000; DATE=2013-07-27-14-30-51-680217;
Uptime: 20 Threads: 13 Questions: 11010 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 550.500
--
===== CNT=1; DSN=2; C=5000; N=50000; DATE=2013-07-27-14-30-55-454527;
Uptime: 61 Threads: 251 Questions: 61015 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 1000.245
--
===== CNT=1; DSN=1; C=100; N=1000; DATE=2013-07-27-14-31-36-980396;
Uptime: 63 Threads: 501 Questions: 62030 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 984.603
--
===== CNT=1; DSN=1; C=1000; N=10000; DATE=2013-07-27-14-31-38-493284;
Uptime: 78 Threads: 501 Questions: 72031 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 923.474
--
===== CNT=1; DSN=1; C=5000; N=50000; DATE=2013-07-27-14-31-53-18563;
Uptime: 129 Threads: 251 Questions: 122072 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 946.294
===== CNT=1; DSN=2; C=100; N=1000; DATE=2013-07-27-14-30-51-109835;
Complete requests: 1000
Requests per second: 3054.97 [#/sec] (mean)
--
===== CNT=1; DSN=2; C=1000; N=10000; DATE=2013-07-27-14-30-51-680217;
Complete requests: 10000
Requests per second: 2852.79 [#/sec] (mean)
--
===== CNT=1; DSN=2; C=5000; N=50000; DATE=2013-07-27-14-30-55-454527;
Complete requests: 50000
Requests per second: 1215.63 [#/sec] (mean)
--
===== CNT=1; DSN=1; C=100; N=1000; DATE=2013-07-27-14-31-36-980396;
Complete requests: 1000
Requests per second: 770.72 [#/sec] (mean)
--
===== CNT=1; DSN=1; C=1000; N=10000; DATE=2013-07-27-14-31-38-493284;
Complete requests: 10000
Requests per second: 692.58 [#/sec] (mean)
--
===== CNT=1; DSN=1; C=5000; N=50000; DATE=2013-07-27-14-31-53-18563;
Complete requests: 50000
Requests per second: 988.28 [#/sec] (mean)
間違い。phpで制限をかけるとpconnectを開放するのではなく、持続的接続が1つあるのでエラーになる。
Warning: mysql_pconnect(): Too many open links (1) in /var/www/localhost/htdocs/test/ab.php on line 30
pconnectの場合 DB::close の直前に SET wait_timeout=1; をやるのはあり。
多い時のDBへの接続が1秒辺りmax_connections以下かどうかとかで増やしたり減らしたり。
MySQL server has gone awayへの対処が必要。