database

[YohLee]

https://my.oschina.net/Kenyon/blog/75757 https://yq.aliyun.com/articles/159709 http://blog.51cto.com/jackwxh/1884761 http://pgguide.lxneng.com/performance/explain.html https://dreamer-yzy.github.io/2014/12/23/PostgreSQL%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92%E5%92%8C%E6%88%90%E6%9C%AC%E5%9B%A0%E5%AD%90%E8%AF%A6%E8%A7%A3/ https://fugangqiang.github.io/blog/posts/postgresql/postgresql%E4%B9%8B%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92(Execution%20Plan).html http://www.cnblogs.com/renlipeng/p/5689786.html http://blog.csdn.net/dongyuxia15810857916/article/details/76836506 http://www.stay-stupid.com/?p=417 http://toplchx.iteye.com/blog/2091860

https://www.ibm.com/developerworks/cn/analytics/library/ba-cn-bigdata-hbase/index.html

https://stackoverflow.com/questions/31680518/exporting-data-from-postgres-to-json-and-renaming-the-columns-of-the-fly

[YohLee]

/uCloudlink/mongodb3011/bin/mongoexport -h 10.7.19.23 --port 27019 -u ossuser -p ossuser@2016 -d oss_perflog -c t_term_vsim_connrel_20180223 -f imsi,_id,lac,sigStrength,errType,band,rat,psRegStatus,accessId,latitude,terType,errorTime,mvnoId,rssi,createTime,longitude,transTimes,imei,orgId,sessionId,csRegStatus,mcc,uploadTime,imeiAccessId,cellId,sigQuality,errCode,mnc,userName -q '{"_id":{$gt:ObjectId("5a94d7d1d79d024fc69abd85")}}' --type=csv -o /uCloudlink/dumpdata/data/add/oss_perflog/t_term_vsim_connrel.csv

关键参数说明：

-h,--host ：代表远程连接的数据库地址，默认连接本地Mongo数据库；
--port：代表远程连接的数据库的端口，默认连接的远程端口27017；
-u,--username：代表连接远程数据库的账号，如果设置数据库的认证，需要指定用户账号；
-p,--password：代表连接数据库的账号对应的密码；
-d,--db：代表连接的数据库；
-c,--collection：代表连接数据库中的集合；
-f, --fields：代表集合中的字段，可以根据设置选择导出的字段；
--type：代表导出输出的文件类型，包括csv和json文件；
-o, --out：代表导出的文件名；
-q, --query：代表查询条件；
 --skip：跳过指定数量的数据；
--limit：读取指定数量的数据记录；
--sort：对数据进行排序，可以通过参数指定排序的字段，并使用 1 和 -1 来指定排序的方式，其中 1 为升序排列，而-1是用于降序排列,如sort({KEY:1})。

注意：

　　当查询时同时使用sort,skip,limit，无论位置先后，最先执行顺序 sort再skip再limit。

[YohLee]

Greenplum 1.通过执行计划发现加了索引未走，可通过增加禁止全表扫描，来确定是否走索引。 set session enable_seqscan=false 加了后，性能优化比较明显。 select * from pg_partitions where schemaname='dm' and tablename='dm_trmnl_acc_kpi_all_day'; alter table dm.dm_trmnl_acc_kpi_all_day drop partition dm_trmnl_acc_kpi_all_day201804; alter table dw.dw_trmnl_vsim_acc add partition dw_trmnl_vsim_acc201804 start (date '2018-04-01') inclusive end (date '2018-06-30') exclusive; create index idx_substr_dm_TRMNL_ACC_KPI_ALL_hour on dm.dm_TRMNL_ACC_KPI_ALL_hour((substr(ctl_cyc_time,1,10))) ----创建函数索引 CREATE OR REPLACE FUNCTION dw.dw_trmnl_get_all_kpi_day( begintime character varying, endtime character varying) RETURNS void AS $BODY$ DECLARE

/ PROC_NAME ：dw_trmnl_get_all_kpi_day CREATER : Zhangsong Liyonghong CREATIME : 2018/1/19 DESCRIPTION : 终端指标数据从ods层到dw层同步 MODIFIER : MODIFTIME ： MODIFYREASON : / data_period VARCHAR(10):=to_char(now()+interval '-1 day','yyyymmdd'); proc_name varchar(100):='dw_trmnl_get_all_kpi_day'; step_id NUMERIC; exec_bt TIMESTAMP; exec_et TIMESTAMP; row_cnt NUMERIC; exec_msg varchar(500); step_desc varchar(100);

BEGIN

step_id=1; step_desc='接入成功分析'; exec_bt=CURRENT_TIMESTAMP; --接入成功分析 --error set session enable_seqscan=false; DELETE FROM dw.dw_TRMNL_VSIM_ACC WHERE substr(begintime,1,16)<=datatime and substr(endtime,1,16)>=datatime; GET DIAGNOSTICS row_cnt = ROW_COUNT; perform src.src_etl_monitor_step_log_detail(data_period,proc_name,step_id,1,exec_bt,CURRENT_TIMESTAMP,row_cnt,'OK',step_desc);

END $BODY$ LANGUAGE plpgsql VOLATILE; ALTER FUNCTION dw.dw_trmnl_get_all_kpi_day(character varying, character varying) OWNER TO gpadmin;

12	ALTER TABLE table_name ENGINE=InnoDB;

[YohLee]

1、 使用 InnoDB 存储引擎

如果你还在使用 MyISAM 存储引擎，那么是时候转换到 InnoDB 了。有很多的理由都表明 InnoDB 比 MyISAM 更有优势，如果你关注性能，那么，我们来看一下它们是如何利用物理内存的：

MyISAM：仅在内存中保存索引。
InnoDB：在内存中保存索引和数据。

结论：保存在内存的内容访问速度要比磁盘上的更快。

下面是如何在你的表上去转换存储引擎的命令： ALTER TABLE table_name ENGINE=InnoDB; 1 2

ALTER TABLE table_name ENGINE=InnoDB;

注意：你已经创建了所有合适的索引，对吗？为了更好的性能，创建索引永远是第一优先考虑的事情。 2、 让 InnoDB 使用所有的内存

你可以在 my.cnf 文件中编辑你的 MySQL 配置。使用 innodb_buffer_pool_size 参数去配置在你的服务器上允许 InnoDB 使用物理内存数量。

对此（假设你的服务器仅仅运行 MySQL），公认的“经验法则”是设置为你的服务器物理内存的 80%。在保证操作系统不使用交换分区而正常运行所需要的足够内存之后 ，尽可能多地为 MySQL 分配物理内存。

因此，如果你的服务器物理内存是 32 GB，可以将那个参数设置为多达 25 GB。 innodb_buffer_pool_size = 25600M 1 2

innodb_buffer_pool_size = 25600M

注意：（1）如果你的服务器内存较小并且小于 1 GB。为了适用本文的方法，你应该去升级你的服务器。 （2） 如果你的服务器内存特别大，比如，它有 200 GB，那么，根据一般常识，你也没有必要为操作系统保留多达 40 GB 的内存。 3、 让 InnoDB 多任务运行

如果服务器上的参数 innodb_buffer_pool_size 的配置是大于 1 GB，将根据参数 innodb_buffer_pool_instances 的设置， 将 InnoDB 的缓冲池划分为多个。

拥有多于一个的缓冲池的好处有：

在多线程同时访问缓冲池时可能会遇到瓶颈。你可以通过启用多缓冲池来最小化这种争用情况：

对于缓冲池数量的官方建议是：

为了实现最佳的效果，要综合考虑 innodb_buffer_pool_instances 和 innodb_buffer_pool_size 的设置，以确保每个实例至少有不小于 1 GB 的缓冲池。

因此，在我们的示例中，将参数 innodb_buffer_pool_size 设置为 25 GB 的拥有 32 GB 物理内存的服务器上。一个合适的设置为 25600M / 24 = 1.06 GB innodb_buffer_pool_instances = 24 1 2

innodb_buffer_pool_instances = 24

注意！

在修改了 my.cnf 文件后需要重启 MySQL 才能生效： sudo service mysql restart 1 2

sudo service mysql restart

还有更多更科学的方法来优化这些参数，但是这几点可以作为一个通用准则来应用，将使你的 MySQL 服务器性能更好。

[YohLee]

mysql语句查询时，使用group_concat对字段进行操作后查询出来的字段显示不完全，这是由于group_concat的group_concat_max_len参数的值太小造成的，默认值如下：

show variables like 'group_concat_max_len'

SET SESSION group_concat_max_len = 10240;或者SET GLOBAL group_concat_max_len = 10240;
这条设置语句必须加在每次的查询之前，之后就会失效了

在MySQL配置文件中加上 group_concat_max_len = 102400 #你要的最大长度 可以简单一点，执行语句,可以设置作用范围 SET GLOBAL group_concat_max_len=102400; SET SESSION group_concat_max_len=102400;

[YohLee]

Oracle IO优化心得

当应用管理员向我们通告现在应用很慢、数据库很慢的时侯，我们到数据库时做几个示例的Select也发现同样的问题时，有些时侯我们会无从下手，因为我们认为数据库的各种命种率都是满足Oracle文档的建议。实际上如今的优化己经向优化等待(waits)转型了，实际中性能优化最根本的出现点也都集中在IO，这是影响性能最主要的方面，由系统中的等待去发现Oracle库中的不足、操作系统某些资源利用的不合理是一个比较好的办法，下面把我的一点实践经验与大家分享一下，本文测重于Unix环境。 一、通过操作系统的一些工具检查系统的状态，比如CPU、内存、交换、磁盘的利用率，根据经验或与系统正常时的状态相比对，有时系统表面上看起来看空闲这也可能不是一个正常的状态，因为cpu可能正等待IO的完成。除此之外我们还应观注那些占用系统资源(cpu、内存)的进程。 1、如何检查操作系统是否存在IO的问题？使用的工具有sar,这是一个比较通用的工具。 Rp1#sar -u 2 10 即每隔2秒检察一次，共执行20次，当然这些都由你决定了。 示例返回： HP-UX hpn2 B.11.00 U 9000/800 08/05/03 18:26:32 %usr %sys %wio %idle 注：我在redhat下查看是这种结果，不知%system就是所谓的%wio Linux 2.4.21-20.ELsmp (YY075) 05/19/2005 10:36:07 AM CPU %user %nice %system %idle 10:36:09 AM all 0.00 0.00 0.13 99.87 10:36:11 AM all 0.00 0.00 0.00 100.00 10:36:13 AM all 0.25 0.00 0.25 99.49 10:36:15 AM all 0.13 0.00 0.13 99.75 10:36:17 AM all 0.00 0.00 0.00 100.00 10:36:17 AM CPU %user %nice %system %idle 10:36:19 AM all 0.00 0.00 0.00 100.00 10:36:21 AM all 0.00 0.00 0.00 100.00 10:36:23 AM all 0.00 0.00 0.00 100.00 10:36:25 AM all 0.00 0.00 0.00 100.00 其中的%usr指的是用户进程使用的cpu资源的百分比，%sys指的是系统资源使用cpu资源的百分比，%wio指的是等待io完成的百分比，这是值得我们观注的一项，%idle即空闲的百分比。如果wio列的值很大，如在35%以上，说明你的系统的IO存在瓶颈，你的CPU花费了很大的时间去等待IO的完成。Idle很小说明系统CPU很忙。像我的这个示例，可以看到wio平均值为11说明io没什么特别的问题，而我的idle值为零，说明我的cpu已经满负荷运行了。 当你的系统存在IO的问题，可以从以下几个方面解决 ＊联系相应的操作系统的技术支持对这方面进行优化，比如hp-ux在划定卷组时的条带化等方面。 ＊查找Oracle中不合理的sql语句，对其进行优化 ＊对Oracle中访问量频繁的表除合理建索引外，再就是把这些表分表空间存放以免访问上产生热点，再有就是对表合理分区。 2、关注一下内存。 常用的工具便是vmstat，对于hp-unix来说可以用glance,Aix来说可以用topas,当你发现vmstat中pi列非零，memory中的free列的值很小，glance,topas中内存的利用率多于80%时，这时说明你的内存方面应该调节一下了，方法大体有以下几项。 ＊划给Oracle使用的内存不要超过系统内存的1/2,一般保在系统内存的40%为益。 为系统增加内存 ＊如果你的连接特别多，可以使用MTS的方式 ＊打全补丁，防止内存漏洞。 3、如何找到点用系用资源特别大的Oracle的session及其执行的语句。 Hp-unix可以用glance,top IBM AIX可以用topas 此外可以使用ps的命令。 通过这些程序我们可以找到点用系统资源特别大的这些进程的进程号，我们就可以通过以下的sql语句发现这个pid正在执行哪个sql，这个sql最好在pl/sql developer,toad等软件中执行, 把<>中的spid换成你的spid就可以了。 SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text from v$session a,v$process b,v$sqltext c WHERE b.spid='ORCL' AND b.addr=a.paddr AND a.sql_address=c.address(+)order BY c.piece 我们就可以把得到的这个sql分析一下，看一下它的执行计划是否走索引，对其优化避免全表扫描，以减少IO等待，从而加快语句的执行速度。 提示：我在做优化sql时，经常碰到使用in的语句，这时我们一定要用exists把它给换掉，因为Oracle在处理In时是按Or的方式做的，即使使用了索引也会很慢。 比如： SELECT col1,col2,col3 FROM table1 a WHERE a.col1 not in (SELECT col1 FROM table2) 可以换成： SELECT col1,col2,col3 FROM table1 a WHERE not exists (SELECT 'x' FROM table2 b WHERE a.col1=b.col1) 4、另一个有用的脚本：查找前十条性能差的sql. SELECT FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10 二、迅速发现Oracle Server的性能问题的成因，我们可以求助于v$session_wait这个视图，看系统的这些session在等什么，使用了多少的IO。以下是我提供的参考脚本： 脚本说明：查看占io较大的正在运行的session SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE, se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes FROM v$session se, v$session_wait st,v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC 对检索出的结果的几点说明： 1、我是按每个正在等待的session已经发生的物理读排的序，因为它与实际的IO相关。 2、你可以看一下这些等待的进程都在忙什么，语句是否合理？ Select sql_address from v$session where sid=; Select from v$sqltext where address=; 执行以上两个语句便可以得到这个session的语句。 你也以用alter system kill session 'sid,serial#';把这个session杀掉。 3、应观注一下event这列，这是我们调优的关键一列，下面对常出现的event做以简要的说明： a、buffer busy waits，free buffer waits这两个参数所标识是dbwr是否够用的问题，与IO很大相关的，当v$session_wait中的free buffer wait的条目很小或没有的时侯，说明你的系统的dbwr进程决对够用，不用调整；free buffer wait的条目很多，你的系统感觉起来一定很慢，这时说明你的dbwr已经不够用了，它产生的wio已经成为你的数据库性能的瓶颈，这时的解决办法如下： a.1增加写进程，同时要调整db_block_lru_latches参数 示例：修改或添加如下两个参数 db_writer_processes=4 db_block_lru_latches=8 a、2开异步IO，IBM这方面简单得多，hp则麻烦一些，可以与Hp工程师联系。b、db file sequential read，指的是顺序读，即全表扫描，这也是我们应该尽量减少的部分，解决方法就是使用索引、sql调优，同时可以增大db_file_multiblock_read_count这个参数。 c、db file scattered read,这个参数指的是通过索引来读取，同样可以通过增加db_file_multiblock_read_count这个参数来提高性能。 d、latch free,与栓相关的了，需要专门调节。 e、其他参数可以不特别观注。 结篇：匆忙之中写下了这篇文章，希望能抛砖引玉，能为你的Oracle调优实践带来帮助

[YohLee]

gpload 导数时报line too long,可用以下方法解决 此ERROR的意思是csv文件中存在这样一行记录，此记录的大小超过了gpfdist一行记录大小的默认值32KB，可在启动gpfdist服务时添加-m参数解决此问题 1.3.解决办法 //在此设置了gpfdist一行记录大小为10MB gpfdist -d /home/gpadmin -p 8081 -l /root/gpAdminLogs/gpload.log -m 10485760 & 同时需要在yml文件添加此参数 - MAX_LINE_LENGTH: 10485760 VERSION: 1.0.0.1 DATABASE: bigdata USER: gpadmin PASSWORD: 1q2w3e4r HOST: 127.0.0.1 PORT: 5432 GPLOAD: INPUT:

• SOURCE: LOCAL_HOSTNAME:
  • 10.1.1.1 PORT: 8081 FILE:
  • /home/gpadmin/data//test.csv
• MAX_LINE_LENGTH: 10485760
• FORMAT: csv
• DELIMITER: ','
• QUOTE: '"'
• HEADER: true
• ERROR_LIMIT: 25
• ERROR_TABLE: src.member_err OUTPUT:
• TABLE: src.src_oss_system_t_bam_version_scheduler
• MODE: INSERT PRELOAD:
• TRUNCATE: true
• REUSE_TABLES: false

1.4.参考资料 //gpfdist介绍 http://gpdb.docs.pivotal.io/4330/utility_guide/admin_utilities/gpfdist.html https://community.emc.com/thread/191963?start=0&tstart=0 参看以下博文 https://blog.csdn.net/jc_xian/article/details/78238057

[YohLee]

通过以下步骤可以进入greenplum,postgresql 命令窗口 从root用户切换到gpadmin用户 su gpadmin psql -d bidata -h 127.0.0.1 即可进入命令窗口进行操作 \d+ ods.ods_oss_perflog_t_term_vsim_reestsucc; \timing \h create index

set session enable_seqscan=false; 通过以上命令可以强制gp走索引 -----查看分区-------- select from pg_partitions where schemaname='dm' and tablename='dm_trmnl_acc_kpi_all_day'; ----------查看索引----------- select from pg_indexes where tablename like 'dm_trmnl_acc_kpi_all_hour%';

通过标准输入输出实现Greenplum文件导入导出 -----导出 psql -h 192.168.0.1 -U gpadmin bigdata -c "copy(select * from src.dis_geocoding_celllocation) to STDOUT;" >/root/dis_geocoding_celllocation.txt -----导入 cat /mytemp/test.txt |psql -h 192.168.0.1 -U gpadmin bigdata -c 'copy src.dis_geocoding_celllocation from stdin;'

gp,postgresql 优化loop join,hash join,merge join http://www.jasongj.com/2015/03/07/Join1/

https://blog.csdn.net/u012564911/article/details/60959744 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VER FROM TAB_COLUMNS WHERE VERSION = 604);

[YohLee]

greenplum的查询处理 查询计划器（query planner），意指在各个节点上分步骤的获取最后结果的过程，比如表扫描、join、聚集和排序操作。有的查询涉及到motion操作，即数据在segments之间迁移。对于一次查询任务，planner会把它切割成分片（slice），对应到各个segment上去，也就是说slice之间是独立的，但一个slice可能运行在多个segment上，比如一次motion操作肯定对应两边segment上的同一个slice任务。大多数的查询任务可能最后还要将segments上产生的结果通过gather motion操作发送到master上去展现给用户，记为一次slice；也有例外，比如CREATE TABLE X AS SELECT，没有gather motion操作，不需要将数据发送到master。 并行查询执行（parallel query execution），对于一次查询来说，首先master上建立查询分派进程（query dispatcher，QD），负责创建和分派query planner，以及收集和展现最终结果；在segment上的工作进程叫query executor（QE)，负责完成自身任务以及和其他QE传输中间结果；对于每一个slice的执行，都是多进程作业的。query planner的每一个步骤叫做一个gangs，也就是说所有segments上的相同slice是一个gangs，gangs可能包含segments之间的数据流通信，再换句话说，一个并行查询执行，就是一个gangs接着一个gangs来完成的

[YohLee]

Mysql 自增序列

select @num:=@num+1 from tb_datasource,
(select @num:=-1) num limit 365;

[YohLee]

select sc.cid as 课程id,avg(sc.score) as 平均成绩, 100*sum(case when sc.score>=60 then 1 else 0 end)/count(1)||'%' as 及格率 from sc group by sc.cid order by avg(sc.score), sum(case when sc.score>=60 then 1 else 0 end)/count(1) desc;

[YohLee]

hive创建表，用复杂分隔符

CREATE TABLE student7( id int, name string, sex string, age int, department string)ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' WITH SERDEPROPERTIES ("field.delim"="$@|.|@$") STORED AS TEXTFILE;

[YohLee]

PostgreSQL 容灾

也可以使用pg_waldump -t 指定查看日志里面的时间线 pg_relation_filepath(); pg_database 里面的oid 表空间 数据库 对戏那个 表空间 数据库 对象 base 1下 pg_tablespace select oid from pg_tablespace where oid = ; select oid from pg_database where oid = ; select oid,relname from pg_class where oid = 1247;

pg的逻辑备份就是pg_dump 和pg_dumpall，这种方式不支持坏块检测，并且pg_dump这种方式就是文本SQL，不会涉及到块。

[YohLee]

Oracle数据库表授权

查询所有表

SELECT s.owner||'.'||u.table_name AS table_name,u.column_name AS column_name,u.data_type AS column_type,u.column_id AS ordinal_position FROM user_tab_columns u LEFT JOIN sys.dba_tables s ON u.table_name = s.table_name

----授予表select权限 grant select on user_tab_columns to test; grant select on dba_tables to test;

----收回表select权限 REVOKE SELECT ON user_tab_columns FROM scott; REVOKE SELECT ON dba_tables FROM scott;

----授予用户DBA权限 grant dba TO scott;

----收回用户DBA权限 revoke dba from scott;