VirtualBox 虛擬機跑MySQL

寫下我最近用VirtualBox跑測試環境過慢的問題

虛擬機
ubuntu 14.04
mysql  Ver 14.14 Distrib 5.5.41, for debian-linux-gnu (x86_64) using readline 6.3

問題一
靜態網頁phpinfo()跑很慢
因為我的虛擬機測試環境已經很髒了，之前在這篇 haproxy使用心得 設定了load balance，所以
先用telnet xxx 80 查連不連得到 => 連不到
用kill把nginx進程（process）全殺了
# killall nginx
再重啟
# /etc/init.d/nginx start
robert：可能是進程被咬死，用service nginx restart 是起不了作用的

問題二
開有連虛擬機上資料庫的網頁非常慢，但設定改為連其他機器時，速度正常
在mysql用show processlist檢查後發現
mysql> show processlist;
...
| 10 | unauthenticated user | 192.168.169.147:51391 | NULL | Connect | NULL | login          | NULL

這個連線在卡
解法：
http://stackoverflow.com/questions/8034706/access-mysql-server-on-linux-throu-virtualbox-windows7-laggs
在 /etc/mysql/my.cnf 的 [mysqld] 中新增
skip-name-resolve

然後重啟mysql
# service mysql restart

問題三
phpmyadmin 編輯欄位速度過慢
mysql> show processlist;
...
| 19 | root | 192.168.169.140:51359 | progmate_test | Query   |   39 | NULL  | SELECT `column_name`, `table_name`, `table_schema`, `referenced_column_name` FROM `information_schem |
卡在SELECT information_schema這個表

簡單的測試
https://www.percona.com/blog/2011/12/23/solving-information_schema-slowness/  Solving INFORMATION_SCHEMA slowness

mysql> select count(*),sum(data_length) from information_schema.tables;
+----------+------------------+
| count(*) | sum(data_length) |
+----------+------------------+
|      769 |       5880654489 |
+----------+------------------+
1 row in set (24.77 sec)

mysql> set global innodb_stats_on_metadata=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*),sum(data_length) from information_schema.tables;
+----------+------------------+
| count(*) | sum(data_length) |
+----------+------------------+
|      769 |       5880654489 |
+----------+------------------+
1 row in set (0.07 sec)

解法：
http://stackoverflow.com/questions/7283915/poor-performance-of-information-schema-key-column-usage-in-mysql  Poor performance of INFORMATION_SCHEMA.key_column_usage in MySQL
在 /etc/mysql/my.cnf 的 [mysqld] 中新增
innodb_stats_on_metadata=0

然後重啟mysql
# service mysql restart

什麼是innodb_stats_on_metadata？
https://www.percona.com/blog/2013/12/03/innodb_stats_on_metadata-slow-queries-information_schema/  innodb_stats_on_metadata and slow queries on INFORMATION_SCHEMA
When the option is set to ON, InnoDB index statistics are updated when running SHOW TABLE STATUS, SHOW INDEX or when querying INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS. These statistics include the cardinality and the number of entries, they are used by the optimizer to find an optimal execution plan.
當這個選項被設置為ON時。當執行 SHOW TABLE STATUS、SHOW INDEX、操作INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.STATISTICS表時， InnoDB的索引統計正被更新。這個統計包括基數( cardinality ) 和 entry數，他們用於優化找到一個最佳的執行計畫
So even if SELECT statements cannot change the real statistics, MySQL updates the statistics for InnoDB tables. This is counterintuitive.
因此，如果SELECT語句不能改變真正的統計（MySQL更新InnoDB表的統計）。這是違反直覺的。
Is it useful? Not really, because InnoDB will always compute statistics when you open a table for the first time and when significant portions of the table have been modified (and when you run ANALYZE TABLE).
它有用嗎？不是，因為InnoDB總是計算統計當你第一次打開一個表和當一個表顯著的部分被修改了（和當你運行ANALYZE TABLE）
The problem was magnified in the example because the whole dataset was not fitting in memory, the number of tables was high and the I/O subsystem was not very powerful.
該問題是被放大的例子，因為整個dataset未嵌合在記憶體中，表的數量很大而子系統的I/O不夠強

值得提出來的是，這個值在 MySQL 5.6 內的 InnoDB 預設是關閉的，在 5.1 與 5.5 則是開啟的。

參考資料：
http://sourceforge.net/p/phpmyadmin/bugs/4592/  #4592 (ok 4.4) Editing columns (tbl_structure.php) is incredibly slow
https://blog.gslin.org/archives/2013/12/09/3918/innodb_stats_on_metadata-%E5%B0%8D%E6%95%88%E8%83%BD%E7%9A%84%E5%BD%B1%E9%9F%BF/  innodb_stats_on_metadata 對效能的影響...