kill sql server2008联接

select 'kill '+ cast(spid as varchar(10)) +';' from sys.sysprocesses where dbid=db_id('Epicor905');

换行符陷阱

昨天帮同事将一组外部数据导入到mysql中,  今天同事反应有一个表的数据有问题,  最后一列的数据后面有特殊字符.

我查看了下, 原来同事给的数据是windows下面生成的, windows的换行符是"\r\n", 但是我是在linux下使用load data infile导入的,  这个语法默认会使用linux的换行符"\n"作为行分隔符, 所以最后一列的数据留下了一个"\r", 就导致了数据显示异常.

而另外几个导入的表都没有问题, 原因是那几个表的最后一列都是数字类型, 导入的时候转换成数字类型会将特殊字符去掉.

看来这还是个比较容易忽视的陷阱啊, 虽然查出这个问题比较容易, 但是确实是很容易犯错的.

当年我刚工作的时候, 就因为不知道这个换行符的差别, 为了写一个linux的shell脚本折腾了一整天的.

MySQL下的db link: FEDERATED

以前经常要跨库查询处理数据, 用sql server的时候有link server, oracle有db link, mysql呢?  最近了解到, 其实mysql也是有这种机制的, mysql的federated引擎就可以达到这个目的.

默认mysql是没有启用这个引擎的, 具体可以用show engines查看一下.

我这边的没有, 但是也只需要在my.cnf文件中添加一行"FEDERATED"然后重启mysql, 就启用了.

有两种方法创建federated表, 一种是直接利用connection string:

CREATE TABLE federated_table (

    id     INT(20) NOT NULL AUTO_INCREMENT,

    name   VARCHAR(32) NOT NULL DEFAULT '',

    other  INT(20) NOT NULL DEFAULT '0',

    PRIMARY KEY  (id),

    INDEX name (name),

    INDEX other_key (other)

)

ENGINE=FEDERATED

DEFAULT CHARSET=latin1

CONNECTION='mysql://username:password@hostname:port/database/tablename';

第二种方法, 利用预先创建好的server link:

CREATE SERVER server_name

    FOREIGN DATA WRAPPER wrapper_name

    OPTIONS (host 'host_name', database 'db_name', user 'user_name', password 'password', socket 'socket_path', owner 'owner', port port_no);

CREATE TABLE test_table (

    id     INT(20) NOT NULL AUTO_INCREMENT,

    name   VARCHAR(32) NOT NULL DEFAULT '',

    other  INT(20) NOT NULL DEFAULT '0',

    PRIMARY KEY  (id),

    INDEX name (name),

    INDEX other_key (other)

)

ENGINE=FEDERATED

DEFAULT CHARSET=latin1

CONNECTION='server_name/table_name';

Tuning MySQL Query Cache

最近一直在研究mysql的query cache, 对于怎样tuning query cache, <High Performance MySQL>上面的一张图基本上能够很清楚的说明了, 但是里面具体怎么做讲的不是很清楚, 我按照我的理解整理了下.

第一步, 查看hit_rate是否可接受.
hit_rate目前给出的公式是qcache_hits/qcache_hits + com_select, 这个是系统启动以来的总体hit_rate,  但我觉得使用某一段时期的hit_rate应该更合理, 因为就我来讲tuning期间我可能会经常对query cache相关参数进行调整, 要查看调整的效果, 当然就不能是看数据库运行期间的整体hit_rate, 而应该是自我调整以来这一段时间的hit_rate.
所以我给出的公式识是qcache_hits_inc/qcache_hits_inc + com_select_inc.
所谓inc, 就是"incremental value", 就是一段时间以来的增量值.
至于是否可接受, 没有一个一定的准则, 要根据你应用的特点来定, 更新比较多的, 就不能期望这个hit_rate太高, 但是更新比较少, 你的hit_rate也很低, 那就有问题了. 我目前的目标系统是一个订单系统, 因为更新频繁, hit_rate是30%到50%, 我觉得已经可以了.
第二步, 是否大部分的query都是uncacheable的.
这个我觉得可以由qcache_not_cached_inc/com_select_inc来决定, 如果得出的数据比较大, 接近1, 那么就符合这个"大部分uncacheable"的命题了.
(不过最近在一个5.5.15版本的mysql上测试, 发现在运行一个很大的查询的时候, Qcache_not_cached增加了2, 而com_select只增加了1, 暂时还不知道这是个什么情况)
query_cache_limit large enough? 这个我觉得也需要根据你应用的特点来决定, 默认的1M, 我个人觉得对一般系统来说, 已经相当大了,  也就是一个sql的结果集要超过1M, 才不会被cache. 我觉得如果sql结果集超过1M, 还需要被cache, 也就是说运行的还比较频繁, 那这个应用肯定是有问题的.
第三部, 是否有很多的validations? 其实我觉得这里应该是invalidation.  Invalidation其实指的就是query cache中的内容被刷掉. 衡量invalidation我觉得可以有两种方法: 1. qcache_insert_inc / com_select_insert  2. query_in_cache_inc / qcache_insert_inc
第一种方法, 如果值接近1, 也就是说几乎每一个新增的select, 都需要新insert进query cache, 那就代表了invalidation比较多
第二种方法, 如果这个值很小,  也就是说, 不断的有大量的query进入query cache, 但是query cache中的数量并没有增多
判断是否有太多碎片, 个人觉得需要从两方面来判断, 一是free block片数量与query cache中query数量的对比, 二是freeblock平均大小与query_cache_min_res_unit大小的对比, 如果小于query_cache_min_res_unit, 则是碎片.
判断是否有太多的low-memory-prunes, 可以根据Qcache_lowmem_prunes_inc / com_select_inc来对比.
判断是否有很多update, 直接根据Com_update_inc与com_select_inc的数量来判断.
判断query cache是否已经warmed up, 这个我觉得直接根据系统运行时间来判断就可以了.

查看当前数据库query cache hit ratio

想弄一个定期查看mysql query cache hit ratio的工具, 前天弄了个store procedure的, 使用的information_schema.global_status中获得status数据, 但是无奈我们目前production环境的版本太低, 还没有这个表, 只能从show global status中获取数据, 废了九牛二虎之力弄了个bash脚本的版本, 下次要弄类似工具还是用python算了, 方便快捷多了.

#!/bin/bash

echo "input password: "

read  -s password

while read line

do

   echo "$line" | grep -q "qcache_hits"

   if [ $? -eq 0 ]

   then

     old_qcache_hits=`echo $line | sed 's/qcache_hits=//'`

   fi

   echo "$line" | grep -q  "com_select"

   if [ $? -eq 0 ]

   then

     old_com_select=`echo $line | sed 's/com_select=//'`

   fi

done < ./last_query_cache.info

new_qcache_hits=`mysql -u root -p$password -s -N -e "show global status like 'Qcache_hits';" | sed 's/Qcache_hits[\t]*//'`

new_com_select=`mysql -u root -p$password -s -N -e "show global status like 'Com_select';" | sed 's/Com_select[\t]*//'`

hit_rate=$(echo "scale=3;$new_qcache_hits/($new_qcache_hits+$new_com_select)"|bc)

inc_hit_rate=$(echo "scale=3;($new_qcache_hits-$old_qcache_hits)/($new_com_select+$new_qcache_hits-$old_qcache_hits-$old_com_select)"|bc)

echo qcache_hits=$new_qcache_hits > last_query_cache.info

echo com_select=$new_com_select >> last_query_cache.info

echo "hit_rate: "$hit_rate

echo "inc_hit_rate: "$inc_hit_rate

mysql锁调度方法

1. 设置 concurrent_insert值

2. 设置low-priority-updates值

误设query_cache_limit参数

刚在检查几个数据库的时候, 发现一个库明明有query cache, 但是show status却显示query cache中一个query也没有, 小兴奋了一下, 还以为遇到了什么bug. 仔细检查才发现, 将query_cache_limit设为了1,  查看配置文件, 应该是想设为1M的, 结果把后面那个M丢了....