2014年10月30日 星期四

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

2014年10月29日 星期三

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


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

show variables like "%slow_query_log%

Variable_nameValue
slow_query_logOFF
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 Namelog_queries_not_using_indexes
Variable ScopeGlobal
Dynamic VariableYes
 Permitted Values
Typeboolean
DefaultOFF
Whether queries that do not use indexes are logged to the slow query log.

2014年10月22日 星期三

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

2014年10月14日 星期二

mysql fulltext常用參數...

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

以下為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 Nameft_min_word_len
Variable ScopeGlobal
Dynamic VariableNo
Permitted Values
Typenumeric
Default4
Min Value1
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 Nameft_stopword_file
Variable ScopeGlobal
Dynamic VariableNo
Permitted Values
Typefile 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
Introduced5.6.4
Command-Line Format--innodb_ft_min_token_size=#
System Variable Nameinnodb_ft_min_token_size
Variable ScopeGlobal
Dynamic VariableNo
Permitted Values
Typenumeric
Default3
Min Value0
Max Value16
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
Introduced5.6.4
Command-Line Format--innodb_ft_server_stopword_table=db_name/table_name
System Variable Nameinnodb_ft_server_stopword_table
Variable ScopeGlobal
Dynamic VariableYes
Permitted Values
Typestring
DefaultNULL
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
Introduced5.6.4
Command-Line Format--innodb_ft_enable_stopword=#
System Variable Nameinnodb_ft_enable_stopword
Variable ScopeGlobal
Dynamic VariableYes
Permitted Values
Typeboolean
DefaultON
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

2014年8月25日 星期一

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:
官方說明文件 

2014年8月14日 星期四

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