526互联

lightdb 练习题

发布时间 2023-06-11 15:39:38作者: winter-loo

lightdb 练习题

  1. 在LightDB/PostgreSQL中，有表a，定义为：

    create table a(id int primary key, rand int, comm varchar(128));

    如何一条语句生成一张1000万记录的表，且满足id从1001万-2000万，rand为0-1000000之间的随机整数，comm为随机生成的UUID?

     insert into a select *, random() * 1000000, gen_random_uuid() from generate_series(10000001, 20000000);

  2. 有两张表a和b，没有索引，数据量分别为1万行和1000万行，要执行下列SQL语句：

    select count(1) from a,b where a.id=b.id

    应该选择哪种执行计划？
    A. hash join
    B. nest loop join
    C. merge join
    为什么？

    选 A.

    • Hash join 需要给 1 万行数据创建 hash table. 然后遍历 1000 万行数据，在 hash table 中查找是否存在对应的 id.
    • Nested loop 需要两重循环，内层循环遍历 1000 万行数据，并把遍历结果缓存，外层循环遍历 1 万行数据。
    • Merge join 需要给 1 万行和 1000 万行数据排序，然后执行 merge.

  3. hash join和哪几个GUC相关？
    work_mem, hash_mem_multipler

  4. 什么情况下hash join无法生效？
    不是等值查询

  5. 并行执行（parallel）是什么意思？
    当 sql 涉及大量数据的查询是，pg 会执行类似 map-reduce 的操作将一个大任务分成多个小任务，每个小任务可以独立同时执行。

  6. 一个加了/*+ Parallel(a 4 hard) */ 优化器提示的SQL未走并行执行计划，通常有哪些原因？

    • 系统资源不足
    • 超过并行度限制参数 max_worker_processes

  7. 如何判断一个函数是否为parallel safe？

    select proname, proparallel from pg_proc where proparallel != 'u'

  8. 在LightDB里，哪几种分页查询语法正确的？

    • 通用法
    prepare page(int, int) as
select * from a order by a.id limit $1 offset ($2 - 1) * $1;
    • pg 特有
    SELECT column1, column2, ...
FROM table
ORDER BY column
OFFSET (page_number - 1) * page_size
FETCH NEXT page_size ROWS ONLY;

  9. 修改分布式LightDB参数的正确步骤

    • 分别修改单实例上的配置文件 lightdb.conf 或 postgresql.conf
    • 根据参数级别确认是重启数据库还是 reload

  10. 修改高可用LightDB参数的正确步骤
    配置文件 ltcluster.conf 内容改变时需要根据节点角色对应执行：

    • ltcluster primary register --force -f /path/to/ltcluster.conf
    • ltcluster standby register --force -f /path/to/ltcluster.conf
    • ltcluster witness register --force -f /path/to/ltcluster.conf -h primary_host

  11. 如何查看LightDB集群的状态

    • select * from pg_dist_node;
    • select * from canopy_tables;
    • select table_name,shardid,shard_name,nodename,nodeport from canopy_shards;
    • ltcluster -f ${LTHOME}/etc/ltcluster/ltcluster.conf service status

  12. LightDB日常采用哪些性能测试工具？
    - https://wiki.postgresql.org/wiki/Profiling_with_perf
    - https://www.2ndquadrant.com/en/blog/tracing-postgresql-perf/

  13. LightDB支持哪些操作系统版本和CPU架构
    TODO

  14. 如何查看一个SQL语句的执行计划
    explain

  15. 如何查看一个SQL语句的实际执行计划
    explain analyze

  16. 哪个命令可以查询包含enable的所有GUC参数?

	SELECT name, current_setting(name) AS value
	FROM pg_settings
	WHERE name ILIKE '%enable%';

  1. 如何查看一张表的大小

    select pg_size_pretty(pg_relation_size('a'));

  2. 如何查看一张分布式表的大小

  3. 如何查看一张表是否已经缓存在共享缓冲（shared_buffers）中

select relname, relpages from pg_class where relpages > 0;
  1. 如何删除表的主键
delete from foo where id = 1;
  1. 如何通过SQL查询到当前lightdb实例的版本
select version();
  1. 如何查询当前lightdb实例的角色（primary/standby）
select pg_is_in_recovery();

  1. Lightdb支持哪些方式导入CSV或文本数据？

    1. 服务端执行的 copy command
    COPY foo FROM '/home/ldd/foo.csv' CSV;
    1. 客户端执行的 \copy command
    \COPY foo FROM './foo.csv' WITH (FORMAT CSV)
    1. 外部表（Foreign Table）

  2. LightDB分布式表支持哪些类型？一般什么情况下建议使用哪种表？

    • distributed table
      需要存储大数据量的表
    • reference table
      与查询语句相关联的数据，如列的类型定义，这种数据很小可以单独存在任何一个节点，这些数据又同时被每一个节点需要。
    • local table
      正常的单实例表，这个表里的数据其他节点不需要用到

  3. 如何查看当前数据库的实时活动、正在执行哪些SQL，当前语句执行了多久？

    SELECT pid, query, now() - pg_stat_activity.query_start AS duration
FROM pg_stat_activity
WHERE state = 'active' AND now() - pg_stat_activity.query_start > interval '5 minutes'
ORDER BY duration DESC;

  4. 如何查看主从节点的滞后延时以及滞后WAL大小（转换为字节数）？
    TODO

  5. internal函数是什么函数？internal如何创建？它和动态加载函数在开发上有什么差别？

    • 一些对外暴露的 C 语言函数，如 C 语言中的数学库函数
    • create function .... language internal strict;
    • 不需要被 LOAD
      38.9 38.10.3

  6. PL过程是LightDB内置的吗？它是如何实现的？
    不是，通过 extension 实现

  7. 自定义PL/pgSQL、PL/oraSQL、SQL函数、过程的内部执行过程
    TODO

  8. LightDB执行c语言编写的函数时，会从哪些路径查找对应的so.

    postgresql 14 documentation 38.10.1

    - 直接用绝对路径指定文件名
- $libdir, 默认值 `lt_config --pkglibdir`
- dynamic_library_path, 默认值 `$libdir`

  9. 新修改的c语言函数的so何时会生效？
    新启动一个会话的时候，每次会话启动访问都会加载，不是实例级别缓存。38.10

  10. 某些函数在BODY最后声明了STRICT，其作用是什么？

    省去在代码中检查参数是否为空的必要了

  11. Datum代表什么？
    代表任意数据类型，在 C 语言层面上是一个指针。

  12. Oid代表什么？
    object identifer. PG 用来唯一标识数据库对象，如：表名，行，索引等。regclass 就代表表或索引的名字， regclass 这个仅仅是 oid 的别名。C 语言实现上 oid 类型是 uint32_t.

  13. LightDB支持哪些事务隔离级别，默认是什么隔离级别？

    • read committed, default
    • repeatable read
    • serializable

  14. LightDB默认自动提交吗？如何关闭？
    - 默认自动提交，每一条 sql 都是一个事务
    - 客户端关闭：\set autocommit off
    - 或者用 begin 手动开启事务

  15. ltsql和libpq是什么关系？
    psql itself is a client application built on top of libpq

  16. 在SQL中，intN的N单位是什么？在c语言中，intN的N单位是什么？

    • SQL: intN 表示 N 个字节
    • C： intN 表示 N bit

  17. C语言编写的函数支持哪些传参类型？

    • by value
    • by reference, 超过 8 字节的类型

  18. tid, cid, xid分别代表什么含义？何时会生成这些id

    • tid: tuple id, 即 row id, 每插入一条数据，都会附带一个 ctid, 格式为 (page_no, offset)
    • cid: command id，用作 cmin/cmax 的类型
    • xid: transaction id, 有 writable transaction 生成的时候

  19. 查看正在执行的sql的执行计划

```sql
select query from pg_stat_activity where state = 'active' limit 1 \gset
explain :query
```

  1. 如何查看一个包含order by的sql语句是否使用了临时文件
    执行计划中出现了 external sort 或 external merge

  2. 执行计划是在什么时候生成的？
    是在 optimizer 根据最小代价原则挑选最优执行路径的时候

  3. 如何查看对象的依赖关系
    查询 pg_depend 表

    SELECT classid::regclass AS dependent_object,
	   refclassid::regclass AS referenced_object
FROM pg_depend
WHERE objid = 'your_object'::regclass;

  4. 一个实例有两个db，如何查看所有表上的锁及被谁占用？

```sql
SELECT pg_class.relname AS table_name, pg_locks.* 
FROM pg_locks
JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE pg_class.relkind = 'r'
AND pg_locks.database = (SELECT oid FROM pg_database WHERE datname = 'your_database');
```

  1. 如何查询lightdb中的所有定时任务及其最近的执行状态

    1. 安装扩展 lt_cron
    2. 执行 sql
      select * from cron.job;

  2. 分布式数据库是实例级别还是db级别？如何确定当前是否为分布式数据库模式

    • db 级别
    • select * from canopy_tables ;

  3. 如何确定一个参数是否可会话级修改、可通过reload加载生效
    查询 pg_settings 表中的 context 字段：

    • user 表示是会话级别
    • sighup 表示修改完 postgresql.conf/lightdb.conf 后需要 reload
    • 还有其他值。。。

  4. lt_hba.conf的作用是什么？
    控制客户端的连接认证

  5. 优化器提示的作用是什么？是哪个extension控制优化器提示的？
    影响 optimizer 选择最优执行计划，pg_hint_plan. PostgreSQL 并不建议使用 hint.

  6. PWR的作用是什么？
    AWR - Automatic Workload Repository
    采集数据库中一些关键指标用于后续分析数据库是否处于最佳运行状态。具体来说，它可以用来统计查询语句的执行时间及资源消耗情况。

  7. PWR的采集频率如何？存储在什么地方？

    • lt_profile 并没有规定采样频率，只是建议一个小时内生成一到两个样本。具体是通过 linux cron 运行 take_sample() 函数控制采样频率。同时，lightdb 提供了 lt_cron 扩展实现定时任务。
    • 所有的数据存放在 historic repository. This repository comprises a set of tables. TODO

  8. PSH的作用是什么？
    ASH - Active Session History
    采集数据库中正在执行的 sql 语句的资源消耗情况。简而言之，AWR 用于历史数据分析，ASH 用于实时数据分析。

  9. PSH的采集频率如何？存储在什么地方？
    TODO

  10. EM agent的作用是什么？
    用于采集数据库和主机的监控数据提供给LightDB-EM做分析、处理、和展示

  11. 如何查看agent中的所有采集任务、频率？
    TODO

  12. 所有的压测，建议最小的表记录数、执行次数、时长分别为多久？
    TODO

  13. Hugepage的作用是什么？如何确定需要多大的hugepage？如何确定hugepage是否对lightdb生效了？

    • 减少 pagetable 所占的空间
    • $nr_hugepages = (shared_buffers \times 1.2) \div hugepage_size$ , more on [[performance#settings#hugepage]]
    • cat /proc/meminfo | grep Huge

  14. agent cpu高如何排查是谁造成？什么工具、如何定位？如何确定是哪个方法、哪个类
    TODO

  15. agent 内存高如何排查，提供示例dump，找出具体对象？
    TODO

  16. 在PL/pgSQL中，哪些方式支持事务？
    存储过程支持事务，函数不支持

  17. lightdb 函数支持哪些特性
    1、返回void 2、call调用 3、支持事务(错误) 4、可以带出参

  18. PL/pgSQL函数&过程和shell一样，也支持按位置定义参数

    CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
	SELECT $1 + $2;
$$ LANGUAGE SQL;

  19. lightdb到23年前最重要是兼容哪个数据库？
    oracle mysql db2 sql server