Fork me on GitHub

查看数据信息常用sql整理

库内存命中率 SELECT 'index hit rate' AS name, (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio FROM pg_statio_user_indexes UNION ALL SELECT 'table hit rate' AS name, sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio FROM pg_statio_user_tables; 表内存命中率 SELECT relname AS relation, heap_blks_read AS heap_read, heap_blks_hit AS heap_hit, ((heap_blks_hit*100) / NULLIF((heap_blks_hit + heap_blks_read), 0)) AS ratio FROM pg_statio_user_tables order by ratio; 读IO内存占比 磁盘中读取和在内存中直接读取之间的数字和比 SELECT relname AS "relation", heap_blks_read AS heap_read, heap_blks_hit AS heap_hit, ( (heap_blks_hit*100) / NULLIF((heap_blks_hit + heap_blks_read), 0)) AS ratioFROM pg_statio_user_tables; 表中索引命中率 SELECT relname, CASE idx_scan WHEN 0 THEN NULL ELSE round(100.0 * idx_scan / (seq_scan + idx_scan), 5) END……

阅读全文

数据库实时运行信息查看

介绍 类似Linux top 命令 查看数据实时运行情况 https://github.com/dalibo/pg_activity 安装 测试环境centos7 postgresql10 查看已安装的PG版本 如果有9.2 版本,清理,如果没有postgresql10-devel 需要安装 yum list installed | grep postgres 设置环境变量 export PG_HOME=/usr/pgsql-10 export PATH=$PATH:$PG_HOME/bin/ 安装pg_activity python3 -m pip install pg_activity psycopg2-binary 使用 与psql 连接方式相同 pg_activity -U xxx -p xxx 更多……

阅读全文

快速删除大量文件

生成大量文件 mkdir lostfiles cd lostfiles cat create_files.sh for i in $(seq 1 1500000) do echo test >>$i.txt done time sh create_files.sh real 3m44.841s user 0m13.208s sys 3m14.523s 快速删除方式对比 time rsync --delete-before -d /tmp/null/ lostfiles/ real 1m14.937s user 0m1.769s sys 0m54.957s time rm lostfiles/ -rf real 1m4.221s user 0m0.776s sys 0m40.334s time find ./lostfiles/ -type f -delete real 1m0.695s user 0m0.851s sys 0m41.294s time perl -e 'for(<*>){((stat)[9]<(unlink))}' real 1m9.959s user 0m2.955s sys 0m50.202s 应用 快速清除minio bucket 数据 清除bucket数据 time find ./bucket001/ -type f -delete 清除bucket元数据 time find .minio.sys/buckets/bucket001 -type f -delete……

阅读全文

perf linux 性能分析

收集数据 sudo perf record -F 99 -p 13204 -g -- sleep 30 -F 99表示每秒99次, -p 13204是进程号 -g表示记录调用栈 sleep 30则是持续30秒 数据解析 perf script -i perf.data &> perf.unfold 数据展现 git clone https://github.com/brendangregg/FlameGraph.git 将perf.unfold中的符号进行折叠 ./stackcollapse-perf.pl perf.unfold &> perf.folded 生成svg图 ./flamegraph.pl perf.folded > perf.svg 用浏览器打开查看 结果解读 https://www.ruanyifeng.com/blog/2017/09/flame-graph.html……

阅读全文

高压缩比工具 XZ

压缩比 xz >biz2 > gzip 安装 默认系统自带 yum install epel-release yum install xz 解压缩 xz -d --threads=`nproc` -k -v hits_v1.tsv.xz 压缩 xz -z --threads=`nproc` -k -v hits_v1.tsv 参数说明 # xz --help Usage: xz [OPTION]... [FILE]... Compress or decompress FILEs in the .xz format. -z, --compress force compression -d, --decompress force decompression -t, --test test compressed file integrity -l, --list list information about .xz files -k, --keep keep (don't delete) input files -f, --force force overwrite of output file and (de)compress links -c, --stdout write to standard output and don't delete input files -0 ... -9 compression preset; default is 6; take compressor *and* decompressor memory usage into account before using 7-9! -e, --extreme try to improve compression ratio by using more CPU time; does not affect……

阅读全文

ClickHouse

调研总结 不适用于多个大表的join操作 没有事务保障机制 merage最终一致性 不适合财务相关系统 对数据精度要求不高,实时性有要求的场景比较适合。……

阅读全文

数据库的json类型

json 与 jsonb json 保持原始格式, jsonb是解析输入后保存的二进制,在解析时会过滤掉不必要的空格和重复的健。 SELECT '{"name": "zhangsan", "age": 17, "sex":"m","age":17.5}'::json; json ------------------------------------------------------------ {"name": "zhangsan", "age": 17, "sex":"m","age":17.5} SELECT '{"name": "zhangsan", "age": 17, "sex":"m","age":17.5}'::jsonb; jsonb ----------------------------------------------- {"age": 17.5, "sex": "m", "name": "zhangsan"} json 插入可能会更快些,jsonb的读取更快 操作符 json ,jsonb 操作符 操作符 右操作数类型 描述 示例 结果 -> int 获取JSON数组元素(索引从0开始) select……

阅读全文

python 简单示例

快速启动一个本地服务 可临时充当文件下载服务 # python2 python -m SimpleHTTPServer 8888 # python3 python3 -m http.server 8888 flask 服务 from flask import Flask app = Flask(__name__) @app.route("/") def hello_world(): result = find_result() return result @app.route('/login', methods=['GET', 'POST']) def login(): if request.method == 'POST': pass else: pass if __name__ == '__main__': app.run(host='0.0.0.0',port='8000') python app.py gunicorn -w 1 -b 127.0.0.1:4000 app:app 操作PG数据库 import psycopg2 import time conn = None try: conn = psycopg2.connect(database="postgres", user="postgres", host="192.168.*.*", port="5432",password='*****') print("Opened database successfully") cur = conn.cursor() cur.execute("select * from api") rows = cur.fetchall() result = '' all_field = cur.description for filed in all_field: print(filed[0]) for row in rows: result += str(row[0]) result += "\n" print(result) print("Operation done successfully") conn.close() except Exception as e:……

阅读全文

长轮询

长轮询 实现原理: 与传统的轮询方式不同的是,当服务端接收到客户端的请求的时候,如果没有最新消息时不是立刻返回请求,而是等待一个最大超时时间。如果等待期间有最新消息则立刻返回。 相对于暴力的轮询,长轮询能够很大程度的减少客户端与服务端的连接进而缓解服务端的压力。 利用长轮询模拟推送服务,……

阅读全文

联想搜索

Redis 联想搜索实现 基于redis ZSET 例子: 当输入n时显示所有n开头的数据,当输入nb时显示所有nb开头的数据。 # 数据录入 127.0.0.1:6379> ZADD ss 0 'n' (integer) 1 127.0.0.1:6379> ZADD ss 0 'nb' (integer) 1 127.0.0.1:6379> ZADD ss 0 'nba' (integer) 1 # 搜索n 127.0.0.1:6379> ZRANK ss 'n' (integer) 0 127.0.0.1:6379> ZRANGE ss 0 -1 1) "n" 2) "nb" 3) "nba" # 搜索nb 127.0.0.1:6379> ZRANK ss 'nb' (integer) 1 127.0.0.1:6379> ZRANGE ss 1 -1 1) "nb" 2) "nba"……

阅读全文