AWS RDS mysql 慢查詢 (slow query log)

以下為官方文件

The MySQL slow query log and the general log can be written to a file or a database table by setting parameters in your DB parameter group. For information about creating and modifying a DB parameter group, see Working with DB Parameter Groups. You must set these parameters before you can view the slow query log or general log in the Amazon RDS console or by using the Amazon RDS API, Amazon RDS CLI, or AWS SDKs.
You can control MySQL logging by using the parameters in this list:

• slow_query_log: To create the slow query log, set to 1. The default is 0.
  
• general_log: To create the general log, set to 1. The default is 0.
  
• log_output: To write the general and slow query logs to the file system, set to FILE. The default is TABLE, which writes general queries to the mysql.general_log table, and slow queries to the mysql.slow_log table. To disable logging, set to NONE.
  
• long_query_time: To prevent fast-running queries from being logged in the slow query log, specify a value for the shortest query execution time to be logged, in seconds. The default is 10 seconds, the minimum is 0. If log_output = FILE, you can specify a floating point value that goes to microsecond resolution. If log_output = TABLE, you must specify an integer value with second resolution. Only queries whose execution time exceeds the long_query_time value are logged. For example, setting long_query_time to 0.1 prevents any query that runs for less than 100 milliseconds from being logged.
  
• log_queries_not_using_indexes: To log all queries that do not use an index to the slow query log, set to 1. The default is 0. Queries that do not use an index are logged even if their execution time is less than the value of the long_query_time parameter.
  
  
  
  資料來源
• http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.MySQL.html
•  http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html

mac os 10.10 mysql 慢查詢 (slow query log)

1.可由以下query得知相關參數

show variables like "%slow_query_log%

Variable_name	Value
slow_query_log	OFF
slow_query_log_file	/usr/local/mysql/data/server-slow.log

2.開啟慢日誌(slow query log)

vi /usr/local/mysql/my.cnf

加入

slow_query_log=on ＃開啟
slow_query_log_file=mysql-slow #檔案名稱
long_query_time=2 ＃2s

3.慢查詢分析方法

官方：MySQLdumpslow
其他: pt-query-digest

4.log_queries_not_using_indexes

Command-Line Format	--log-queries-not-using-indexes
System Variable Name	log_queries_not_using_indexes
Variable Scope	Global
Dynamic Variable	Yes
	Permitted Values
	Type	boolean
	Default	OFF

Whether queries that do not use indexes are logged to the slow query log.

install apache subversion on mac os 10.10

1.download apache subversion source code

2.build subversion

cd ~/Downloads/subversion-1.8.10

./configure

make

3.複製.so到apache

sudo cp subversion/mod_dav_svn/.libs/mod_dav_svn.so /usr/libexec/apache2

sudo cp subversion/mod_authz_svn/.libs/mod_authz_svn.so /usr/libexec/apache2



Note:

svn的設定檔在以下位置

/Library/Server/Web/Config/apache2/other/svn.conf

mysql fulltext常用參數...

再my.conf加入下列參數，可分別將stopword關掉與min length設成１

以下為MyISAM
ft_min_word_len=1
ft_stopword_file=''


以下為InnoDB

innodb_ft_min_token_size=1
innodb_ft_server_stopword_table = ''
innodb_ft_enable_stopword=FALSE

官方說明

ft_min_word_len

Command-Line Format	--ft_min_word_len=#
System Variable Name	ft_min_word_len
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	numeric
	Default	4
	Min Value	1

The minimum length of the word to be included in a FULLTEXT index.
Note：
FULLTEXT indexes must be rebuilt after changing this variable. Use REPAIR TABLE tbl_name QUICK.


ft_stopword_file

Command-Line Format	--ft_stopword_file=file_name
System Variable Name	ft_stopword_file
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	file name

The file from which to read the list of stopwords for full-text searches. The server looks for the file in the data directory unless an absolute path name is given to specify a different directory. All the words from the file are used; comments are not honored. By default, a built-in list of stopwords is used (as defined in the storage/myisam/ft_static.c file). Setting this variable to the empty string ('') disables stopword filtering. See also Section 12.9.4, “Full-Text Stopwords”.
Note:
FULLTEXT indexes must be rebuilt after changing this variable or the contents of the stopword file. Use REPAIR TABLE tbl_name QUICK.

innodb_ft_min_token_size

Introduced	5.6.4
Command-Line Format	--innodb_ft_min_token_size=#
System Variable Name	innodb_ft_min_token_size
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	numeric
	Default	3
	Min Value	0
	Max Value	16

Minimum length of words that are stored in an InnoDB FULLTEXT index. Increasing this value reduces the size of the index, thus speeding up queries, by omitting common word that are unlikely to be significant in a search context, such as the English words “a” and “to”. For content using a CJK (Chinese, Japanese, Korean) character set, specify a value of 1.

 innodb_ft_server_stopword_table

Introduced	5.6.4
Command-Line Format	--innodb_ft_server_stopword_table=db_name/table_name
System Variable Name	innodb_ft_server_stopword_table
Variable Scope	Global
Dynamic Variable	Yes
	Permitted Values
	Type	string
	Default	NULL

Name of the table containing a list of words to ignore when creating an InnoDB FULLTEXT index, in the format db_name/table_name.
Note:
The stopword table must be an InnoDB table, containing a single VARCHAR column named VALUE. The stopword table must exist before you specify its name in the configuration option value.

 innodb_ft_enable_stopword

Introduced	5.6.4
Command-Line Format	--innodb_ft_enable_stopword=#
System Variable Name	innodb_ft_enable_stopword
Variable Scope	Global
Dynamic Variable	Yes
	Permitted Values
	Type	boolean
	Default	ON

Specifies that a set of stopwords is associated with an InnoDB FULLTEXT index at the time the index is created. If the innodb_ft_user_stopword_table option is set, the stopwords are taken from that table. Else, if the innodb_ft_server_stopword_table option is set, the stopwords are taken from that table. Otherwise, a built-in set of default stopwords is used.

ERROR! The server quit without updating PID file (/usr/local/mysql/data/server.local.pid).

當開啟mysql時遇到以下error

Starting MySQL

.. ERROR! The server quit without updating PID file (/usr/local/mysql/data/server.local.pid).

在網路上查了很多總解法都無效....

後來把ib_logfile0與ib_logfile1砍掉就搞定了

sudo rm -rf /usr/local/mysql/data/ib_logfile0

sudo rm -rf /usr/local/mysql/data/ib_logfile1

mysql upgarde on mac osx 10.9

停止mysql服務

sudo /usr/local/mysql/support-files/mysql.server stop

到mysql官網抓取安裝程式
在此安裝了mysql 5.6.20

點選下載下來的.pkg檔案,安裝MySQL Server

確認/usr/local/mysql是否指定新版的sql

1.ls -al /usr/local/mysql

2.lrwxr-xr-x  1 root  wheel  27  8 24 23:36 /usr/local/mysql -> mysql-5.6.20-osx10.8-x86_64

將舊版本data複製到新版

1.cd /usr/local

2.sudo cp -rf mysql-5.5.27-osx10.6-x86_64/data/  mysql-5.6.20-osx10.8-x86_64/data/

3.確認權限是否正確，如果不對，利用sudo chown -R 更改權限

啟動新版本的MySQL

sudo /usr/local/mysql/support-files/mysql.server start

執行升級程序 

1.sudo /usr/local/mysql/bin/mysql_upgrade

2.sudo /usr/local/mysql/support-files/mysql.server restart 

更新密碼 

/usr/local/mysql/bin/mysqladmin -u root password 'password' 

Note:

官方說明文件 

mac OSX 10.9安裝PHP extension

1.抓取php 5.4.24 source code

http://tw1.php.net/get/php-5.4.24.tar.bz2/from/a/mirror

2.Build the PHP XXX extension

cd ~/Downloads
unzip PHP-5.4.17.zip
cd php-src-PHP-5.4.17/ext/XXX/
/usr/bin/phpize
./configure
make
sudo make install

3.Enable the extension

編輯 /etc/php.ini file 並加入
extension=XXX.so

4.Restart apache