隨手筆記

2014年6月16日星期一

MySQL Performance Tunning

MyISAM

1. Table Row_Format

http://dev.mysql.com/doc/refman/5.1/en/static-format.html

show table status

Row_format = Fixed / Dynamic

Fixed is better for select , but the table can't contain variable-length columns (VARCHAR, VARBINARY, BLOB, TEXT)

ALTER TABLE DBNAME.TABLENAME ROW_FORMAT=Fixed

Static-format tables have these characteristics:

CHAR and VARCHAR columns are space-padded to the specified column width, although the column type is not altered. BINARY and VARBINARY columns are padded with 0x00 bytes to the column width.
Very quick.
Easy to cache.
Easy to reconstruct after a crash, because rows are located in fixed positions.
Reorganization is unnecessary unless you delete a huge number of rows and want to return free disk space to the operating system. To do this, use OPTIMIZE TABLE or myisamchk -r.
Usually require more disk space than dynamic-format tables.

2. OPTIMIZE TABLE
http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or ARCHIVE table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

For MyISAM tables, OPTIMIZE TABLE works as follows:

If the table has deleted or split rows, repair the table.
If the index pages are not sorted, sort them.
If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

By default, the server writes OPTIMIZE TABLE statements to the binary log so that they replicate to replication slaves. To suppress logging, specify the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

2014年6月15日星期日

MySQL Status

1. Dispaly Table locks / cache hit Info

2. Display Command (Insert/Update etc Count)

mysql> show global status like 'Com_%';
select * from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME like 'Com_%';
select * from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME IN ('COM_UPDATE','COM_INSERT','COM_SELECT','COM_DELETE');
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| COM_DELETE    | 120892         |
| COM_INSERT    | 202796         |
| COM_SELECT    | 60755185       |
| COM_UPDATE    | 37150062       |
+---------------+----------------+

3. Show Process List from a Host

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE HOST like '192.168.1.203%';

4. Show Global Query Cache status

mysql> show global status like 'Qca%';

5. set slow_query_log on/off

set global slow_query_log = 'on';
set global slow_query_log = 'off';

7. Show Index status from Table;

mysql> show index from DBNAME.TABLENAME;

8. show global variables;

show global variables;

2014年6月14日星期六

mysqldumpslow

Usage :

mysqldumpslow slowlog

mysqldumpslow slowlog -s t
sort by total query time

mysqldumpslow slowlog -g "TEST"
regular expression match TEST

to display more digital in time

vim mysqldumpslow

167c167
< printf "Count: %d Time=%.2fs (%ds) Lock=%.2fs (%ds) Rows=%.1f (%d), $user\@$host\n%s\n\n",
---
> printf "Count: %d Time=%.6fs (%.2fs) Lock=%.6fs (%.2fs) Rows=%.1f (%d), $user\@$host\n%s\n\n",

Awk Samples

1.

awk -v COLUMN=2 '{ sum += $COLUMN } END { print sum}'

set 2nd to COLUMN and sum ,
after process all lines , print the sum

2.

awk -F"[()s]" -v Q=3 '{qSum += $Q} END { print qSum}'

set separater to ( , ) , s

set 3nd to Q and sum it,
after process all lines , print the sum

3.

awk -F"[()s]" '{qSum += $3; lSum += $7; rSum += $11} END { print qSum,lSum,rSum}'
set separater to ( , ) , s
sum 3 fileds
print them

4.

#!/usr/bin/awk -f
NR%4 == 0{
        print ARGC
        for ( k=0 ; k < ARGC ; k++) {
                print "ARGV[" k "] = [" ARGV[k] "]"
        }
}

5. regular expression

EX1 :

test.awk
#!/usr/bin/gawk -f
$0 ~ /Time:/{
        #print $0;
        match($0, /Time: ([0-9]+)/, arr)
        if( arr[1] != "")
                print arr[1];
#               print NR;
}

Search String "Time:"

Match the   Time: 140613 22:26:16   to arr
print arr[1]

Output
140613
140613
140613

#!/usr/bin/gawk -f
$0 ~ /Time: [0-9]+ [0-9][0-9]:[0-9][0-9]/{
        print $0
}

Seach String " Time: [d]+ dd:dd:dd"

Output :

# Time: 140613 22:26:16
# Time: 140613 22:26:17
# Time: 140613 22:26:18
# Time: 140613 22:26:19
# Time: 140613 22:26:20
# Time: 140613 22:26:21

2014年5月16日星期五

MySQL InnoDB Count(*) Performance

轉錄自 http://blog.chinaunix.net/uid-26602509-id-4113113.html

1、为什么用 secondary index 扫描反而比 primary key 扫描来的要快呢？我们就需要了解innodb的clustered index 和 secondary index 之间的区别了。

innodb 的 clustered index 是把 primary key 以及 row data 保存在一起的，而 secondary index 则是单独存放，然后有个指针指向 primary key。因此，需要进行 count(*) 统计表记录总数时，利用 secondary index 扫描起来，显然更快。而primary key则主要在扫描索引，同时要返回结果记录时的作用较大。

2、可以看到以上where条件有主键和普通索引用and连接的话，实际用到的索引是主键PRIMARY。

3、可以看到以上where条件有主键和普通索引用 or 连接的话，实际用到的索引是普通索引 sex。

弄了一天的mysql索引，通过自己的实践来记录一些事实吧：

一、首先，建表：

CREATE TABLE `sbtest` (

`aid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`id` int(10) unsigned NOT NULL DEFAULT '0',

`name` varchar(255) NOT NULL DEFAULT '',

`passwd` varchar(100) NOT NULL DEFAULT '',

`pad` char(60) NOT NULL DEFAULT '',

`sex` int(11) NOT NULL DEFAULT '1',

PRIMARY KEY (`aid`),

) ENGINE=InnoDB AUTO_INCREMENT=6000001 DEFAULT CHARSET=utf8，

这里只有一个主键索引aid，

二、存储过程插入6百万条数据：

delimiter $$

create procedure insertt()

begin

set @a=0;

set @e=1;

while @a<6000000 div="" do="" nbsp="">

set @b='name2 for procedure';

set @c='pass2 for procecdure';

set @d='test2 for procecdure';

insert into sbtest values('',@e,@b,@c,@d);

set @a=@a+1;

set @e=@e+1;

end while;

end$$

delimiter ;

三、测试count的执行效率

测试之前需要把mysql缓存关闭掉，修改mysql的配置my.cnf

query_cache_size = 0

query_cache_type = 0

重启mysql

mysql> show variables like '%query_cache%';

+------------------------------+---------+

| Variable_name | Value |

+------------------------------+---------+

| have_query_cache | YES |

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 0 |

| query_cache_type | OFF |

| query_cache_wlock_invalidate | OFF |

+------------------------------+---------+

查看这两项：

| query_cache_size | 0 |

| query_cache_type | OFF |

证明mysql缓存已经关闭。接下来继续.....

mysql> explain select count(*) from sbtest;

+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+

+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+

+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+

1 row in set (0.00 sec)

可以看到所查询的数据记录数为 5995867，默认用的是主键索引。

四、此时用php脚本验证一下执行时间：

set_time_limit(0);

function getmicrotime(){

list($usec, $sec) = explode(" ",microtime());

return ((float)$usec + (float)$sec);

}

$con = mysql_connect('localhost','root','') or die('can not connect mysql!');

mysql_select_db('ljyun_512_merchant',$con) or die('select error!');

echo 'start time = '.$startTimt = getmicrotime().'
';

$sql = "select count(*) from sbtest ";

$query = mysql_query($sql);

$res = mysql_fetch_assoc($query);

echo 'end time = '.$endTime = getmicrotime().'
';

$queryTime = $endTime - $startTimt;

echo 'query time is:'.$queryTime.' 秒
';

unset($queryTime,$startTimt,$endTime);

var_dump($res);

以上php程序输出结果为：

start time = 1358934178.7986
end time = 1358934181.8649
query time is:3.0663001537323 秒
array(1) { ["count(*)"]=> string(7) "6000000" }

可以看到程序执行时间为3.0663001537323 秒。

五、添加普通索引key：

mysql> alter table sbtest add key sex(`sex`);

mysql> explain select count(*) from sbtest;

+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+

+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+

+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+

1 row in set (0.00 sec)

可以看到所查询的数据记录数为 5995867，默认用的是普通索引key sex。

此时执行以上php脚本，结果是：

start time = 1358934526.6436
end time = 1358934527.2778
query time is:0.63420009613037 秒
array(1) { ["count(*)"]=> string(7) "6000000" }

可以看到程序执行时间为0.63420009613037 秒。

以上主键索引和（主键索引+普遍索引）6百万条数据，两种情况执行的时间相差大概5倍。当然这两种情况都是在sql语句不加where条件的前提下。

如果sql语句加上where条件会是如何呢？请看下面测试结果：

六、sql中有where条件的情况：

mysql> explain select count(*) from sbtest where aid>=0;

+----+-------------+--------+-------+---------------+---------+---------+------+---------+--------------------------+

+----+-------------+--------+-------+---------------+---------+---------+------+---------+--------------------------+

+----+-------------+--------+-------+---------------+---------+---------+------+---------+--------------------------+

可以看到所查询的数据记录数为 2997933，用的是主键索引 PRIMARY。

执行php脚本结果为：（执行前要把php中的sql语句加上where：select count(*) from sbtest where aid>=0）

start time = 1358934968.3334
end time = 1358934971.4169
query time is:3.0834999084473 秒
array(1) { ["count(*)"]=> string(7) "6000000" }

可以看到程序执行时间为3.0834999084473 秒。

改变where条件：

mysql> explain select count(*) from sbtest where sex>=0;

+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+

+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+

| 1 | SIMPLE | sbtest | range | sex | sex | 4 | NULL | 2997933 | Using where; Using index |

+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+

1 row in set (0.00 sec)

可以看到所查询的数据记录数为 2997933，查询条数和以上情况一致，用的是普通索引 sex。

php执行结果：（select count(*) from sbtest where sex>=0）：

start time = 1358935193.4689
end time = 1358935194.6322
query time is:1.163300037384 秒
array(1) { ["count(*)"]=> string(7) "6000000"

可以看到程序执行时间为1.163300037384 秒。这里的where条件sex>=0要比主键where条件aid>=0快上将近2秒多。

显然，在Innodb中使用count()函数普通索引要比主键索引快，原因我在百度中找到的结果：

为什么用 secondary index 扫描反而比 primary key 扫描来的要快呢？我们就需要了解innodb的clustered index 和 secondary index 之间的区别了。

七、此时我把where条件在更改一下

mysql> explain select count(*) from sbtest where sex>=0 and id>=0;

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

可以看到以上并没有用到索引，

程序执行时间为：

query time is:2.9929001331329 秒

mysql> explain select count(*) from sbtest where sex>=0 and aid>=0;

+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+

+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+

+----+-------------+--------+-------+---------------+---------+---------+------+---------+-------------+

1 row in set (0.00 sec)

可以看到以上where条件有主键和普通索引用and连接的话，实际用到的索引是主键PRIMARY。

程序执行时间为：

query time is:2.9929001331329 秒

mysql> explain select count(*) from sbtest where sex>=0 or aid>=0;

+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+

+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+

+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+

1 row in set (0.01 sec)

可以看到以上where条件有主键和普通索引用 or 连接的话，实际用到的索引是普通索引 sex。

程序执行时间为

query time is:1.1176002025604 秒

此时给sbtest表在添加一个普通索引id

mysql>alter table sbtest add key id(`id`)

mysql> explain select count(*) from sbtest where sex>=0 and id>=0;

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

1 row in set (0.00 sec)

可以看到以上，实际用到的索引是普通索引 NULl,没有用到任何索引。

程序执行时间为

query time is:3.1594998836517 秒

mysql> explain select count(*) from sbtest where sex>=0 and id>=0 and aid>=0;

+----+-------------+--------+-------+----------------+---------+---------+------+---------+-------------+

+----+-------------+--------+-------+----------------+---------+---------+------+---------+-------------+

+----+-------------+--------+-------+----------------+---------+---------+------+---------+-------------+

1 row in set (0.00 sec)

可以看到以上，实际用到的索引是主键索引 PRIMARY。

程序执行时间为

query time is:3.8204998970032 秒。

mysql> explain select count(*) from sbtest where sex>=0 or aid>=0 or id>=0;

+----+-------------+--------+------+----------------+------+---------+------+---------+-------------+

+----+-------------+--------+------+----------------+------+---------+------+---------+-------------+

+----+-------------+--------+------+----------------+------+---------+------+---------+-------------+

1 row in set (0.00 sec)

可以看到以上，实际用到的索引 NULL,没有用到任何索引。

程序执行时间为 query time is:3.034900188446 秒

2014年5月11日星期日

Android Programming - Disable Soft Keyboard On Button Click

import android.view.inputmethod.InputMethodManager;
import android.content.Context;

public void onClick(View v) {

InputMethodManager imm = (InputMethodManager) getSystemService(Context.INPUT_METHOD_SERVICE);

imm.hideSoftInputFromWindow(calcButton.getWindowToken(), 0);

}

2014年3月3日星期一

CentOS 6.4 PHP mssql UTF8

1. 修改 /etc/freetds.conf

[10.10.64.6]
        host = 10.10.64.6
        port = 1433
        tds version = 8.0
        client charset = UTF-8

2. 修改 /etc/php.ini

mssql.charset = "UTF-8"

3. 不確定是否必要
yum install freetds-devel php-odbc

4.使用 /usr/bin/tsql測試連線
tsql -H 10.10.64.6 -p 1433 -U username
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"

1> use dbname
2> GO
1> select * from tablename
2> GO

觀察是否為 UTF8 資料

5. 在 php insert db 中，要使用 mssql 的 N' ' 語法 (指定 unicode)

訂閱：文章 (Atom)

隨手筆記

2014年6月16日星期一

MySQL Performance Tunning

MyISAM

1. Table Row_Format

2014年6月15日星期日

MySQL Status

1. Dispaly Table locks / cache hit Info

2. Display Command (Insert/Update etc Count)

3. Show Process List from a Host

4. Show Global Query Cache status

5. set slow_query_log on/off

7. Show Index status from Table;

8. show global variables;

2014年6月14日星期六

mysqldumpslow

Awk Samples

1.

2.

3.

4.

5. regular expression

2014年5月16日星期五

MySQL InnoDB Count(*) Performance

2014年5月11日星期日

Android Programming - Disable Soft Keyboard On Button Click

2014年3月3日星期一

CentOS 6.4 PHP mssql UTF8

標籤

搜尋此網誌

總網頁瀏覽量

追蹤者

2014年6月16日 星期一

MyISAM

1. Table Row_Format

2014年6月15日 星期日

1. Dispaly Table locks / cache hit Info

2. Display Command (Insert/Update etc Count)

3. Show Process List from a Host

4. Show Global Query Cache status

5. set slow_query_log on/off

7. Show Index status from Table;

8. show global variables;

2014年6月14日 星期六

1.

2.

3.

4.

5. regular expression

2014年5月16日 星期五

2014年5月11日 星期日

2014年3月3日 星期一

2014年6月16日星期一

2014年6月15日星期日

2014年6月14日星期六

2014年5月16日星期五

2014年5月11日星期日

2014年3月3日星期一