让你快速乘风破浪的 成为专业dba

select /*gtt*/  count(*) '并发数',max(time)'最长执行时间',concat(SUBSTR(REPLACE(REPLACE(REPLACE(info, CHAR(10), ''), CHAR(13), ''),'  ',' ' )FROM 1 FOR 30),'||', REPLACE(REPLACE(substr(info,LOCATE('from',info)+4,30), CHAR(10), ''), CHAR(13), ''))  'sql格式化展示'  from information_schema.PROCESSLIST where info is not null  and id not in( select connection_id() )  group by concat(SUBSTR(REPLACE(REPLACE(REPLACE(info, CHAR(10), ''), CHAR(13), ''),'  ',' ' )FROM 1 FOR 30),'||', REPLACE(REPLACE(substr(info,LOCATE('from',info)+4,30), CHAR(10), ''), CHAR(13), '')) order by 1;

select * from information_schema.PROCESSLIST where info is not null  order by time,info ;

select concat('kill ',id,';') 'kill',USER,host,db,COMMAND,time ,STATE from information_schema.PROCESSLIST where info is not null and TIME>0  and info like '%过滤条件%';

SELECT table_schema,table_name,sum(round( round( data_length + index_length ) / (1024 *1024*1024) , 2 )) total_size_G ,round(sum(DATA_FREE)/1024/1024/1024,2) FROM information_schema.TABLES group by table_schema,table_name  HAVING sum(round( round( data_length + index_length ) / (1024 *1024*1024) , 2 ))>1   order by  total_size_G desc ;

slowlog get N

go get github.com/lanfang/redis-analyzer
cd go/bin/
./redis-analyzer parse-rdb --rdb-file=mess.rdb

git clone https://github.com/sripathikrishnan/redis-rdb-tools
cd redis-rdb-tools

python setup.py install
rdb -c memory 30.rdb > memory2.csv

 python setup.py install
 rdb -c memory 30.rdb > memory2.csv

sqlite3 30.db
create table memory30(database int,type varchar(128),key varchar(128),size_in_bytes int,encoding varchar(128),num_elements int,len_largest_element varchar(128),expiry varchar(128));
.mode csv
.import memory.csv memory2018070420320

select count(*) from memory30;

select sum(size_in_bytes)/1024/1024 from memory30 where key like 'xxx_detail%';

select * from memory20180704203207 order by size_in_bytes desc limit 10;

select * from memory20180704203207 where type='list' and num_elements > 1000 

git clone https://github.com/Instagram/redis-faina.git

redis-cli -h 0.0.0.0 -a passwd -p 6379 MONITOR | head -n 10000 | ./redis-faina.py |more

Overall Stats
========================================
Lines Processed 10000        --采样的数目
Commands/Sec 31718.97    --TPS 单实例tps可以达到8-10w

Top Prefixes                            --热点key前缀
========================================
vivashow 8526 (85.26%)
v 275 (2.75%)
vid 102 (1.02%)
n 70 (0.70%)
vids 10 (0.10%)

Top Keys                                  --热点key 
========================================
vivashow:f:v:i~lock 559 (5.59%)
vivashow:v:i~lock 395 (3.95%)

Top Commands                      --热点命令

========================================
EXISTS 4817 (48.17%)
GET 2943 (29.43%)
SET 368 (3.68%)

Command Time (microsecs)--命令耗时
========================================
Median 20.0
75% 217.25
90% 585.75
99% 1184.0

Heaviest Commands (microsecs)--最重的命令

========================================
GET 100961.5
SET 44970.0
EXISTS 38443.25

Slowest Calls                     --最慢的调用
========================================
1519.75 "EXISTS" "xxxxxx:user:info:36833133"

db.currentOp() ---查看数据库当前正在执行的操作
db.killOp(opid) ---直接终止该操作
db.userInfo.find({name: /mongo/}) —-模糊查询
db.user_message_feed_index.createIndex( { created_at:1}, {expireAfterSeconds:2592000,background: true} ) —-添加索引

db.system.profile.find().limit(10).sort({ ts : -1 }).pretty()

db.system.profile.find( { op: { $ne : 'insert' } }).pretty()

db.system.profile.find( { ns : ‘mydb.test‘ } ).pretty()

db.system.profile.find({ millis : { $gt : 100 } } ).pretty()

sh.enableSharding('xxxashow')

sh.shardCollection("xxx.notification",{"receiverId":"hashed"})

use config
db.changelog.find({what: "split"}).sort({time:-1})
db.changelog.find({what: "moveChunk.commit"}).sort({time:-1})