Tiago Oliveira de Freitas's questions

我无法在挂载目录中创建新表空间

psql -U sysuser -d postgres -c "CREATE TABLESPACE dadospostgres LOCATION '/media/tiago/Dados/Postgres/Bancos';"
ERROR:  could not set permissions on directory "/media/tiago/Dados/Postgres/Bancos": Permission denied

psql -U sysuser -d postgres -c "CREATE TABLESPACE dadospostgres LOCATION '/media/tiago/Dados/Postgres';"
ERROR:  could not set permissions on directory "/media/tiago/Dados/Postgres": Permission denied

文件夹权限：

root@tiago:/media/tiago/Dados# ls -l /media/
total 4
drwxr-x---+ 4 root root 4096 out 14 11:29 tiago

root@tiago:/media/tiago/Dados# ls -l /media/tiago/ | grep Dados
drwxrwxrwx 1 tiago tiago 8192 out 14 10:52 Dados

root@tiago:/media/tiago/Dados# ls -l /media/tiago/Dados/ | grep Postgres
drwx------ 1 postgres postgres         144 out 14 11:33 Postgres

root@tiago:/media/tiago/Dados# ls -l /media/tiago/Dados/Postgres/
total 0
drwx------ 1 postgres postgres 0 out 14 11:33 Bancos

挂载磁盘

root@tiago:/media/tiago/Dados# lsblk -O
└─nvme1n1p2
     nvme1n1p2
           /dev/nvme1n1p2
                259:9    188,9G 953,9G ntfs     765G    80% /media/tia Dados 56836A8F5F3C7018                     3af9eb40-7c61-4a6f-a748-180335979b27 dos    ebd0a0a2-b9e5-4433-87c0-68b6b72699c7           01418683-fa44-448e-92c1-234d67229098           128  0  0       0              953,9G       root  disk  brw-rw----         0    512      0     512     512    0 none      255 part        0      512B       2T         0    0B uuid.df8e86d6-8a47-9916-2e43-8e8b6efdb7cd    0 nvme1n1

root@tiago:/media/tiago/Dados# cat /etc/fstab
UUID=56836A8F5F3C7018  /media/tiago/Dados       ntfs-3g auto,users,permissions 0 0

我需要这个已安装磁盘中的可用空间。

我在使用 Ubuntu、320 GB SSD、6 核和 16GB Ram 的服务器上工作，但是 Postgres 遇到了一些需要很长时间才能运行的查询以及并行查询，这大大增加了服务器负载。

一些 Postgres 配置：

max_connections = 150
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 109MB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
effective_io_concurrency = 200

seq_page_cost = 1
random_page_cost = 1.1
cpu_index_tuple_cost = 0.030
cpu_operator_cost = 0.0150
cpu_tuple_cost = 0.06

#parallel_tuple_cost = 0.1      # same scale as above
#parallel_setup_cost = 1000.0   # same scale as above
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
max_worker_processes = 6
max_parallel_workers_per_gather = 3
max_parallel_workers = 6

服务器在 Linode 上，我得到一些信息：

#更大的bs
$ dd bs=16k count=10240 iflag=direct if=./test_file of=/dev/null;
已复制 167772160 字节（168 MB，160 MiB），1,01442 秒，165 MB/秒

#较小的bs
$ dd bs=2048 count=80000 iflag=direct if=./arquivo_teste of=/dev/null;
已复制 163840000 字节（164 MB，156 MiB），9,91975 秒，16,5 MB/秒

lsblk -o NAME,MOUNTPOINT,MODEL,ROTA
sda / QEMU HARDDISK 1

Linux myClientName 5.7.6-x86_64-linode136 #1 SMP PREEMPT Wed Jun 24 15:41:07 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux

不确定 dd 测试是否会有所帮助，但我发现小 BS 测试和大 BS 测试之间的差异有多大很有趣。

如果我没看错 QEMU 是虚拟化系统，尽管 ROTA = 1，HD 可能仍然是 SSD

• 有什么方法可以确定我可以在 Postgres 服务器上拥有多少并行工作人员？

数据库有一个复制服务器，我需要撤销用户“user_bi”的权限，但允许同一用户访问复制服务器！

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user_bi; -- on master.

这会将撤销复制到从站。

GRANT SELECT ON ALL TABLES IN SCHEMA public FROM user_bi; -- on the slave.

这将返回错误“SQL 错误 [25006]：错误：无法在只读事务中执行 GRANT”。

• 我可以只撤销一个数据库的权限吗？

• 什么是 autovacuum 对该表起作用的合适设置？

我们有一个“sequencia”表，它做了sequencepostgres 应该做的事情，但也许通过使用其他数据库，开发选择了这种方法，即使用数据库中的表来存储插入到其他表中的最后一个 id。

表“sequencia”的一个例子：

 id_entidade                      no_sequencia    
 -------------------------------  --------------- 
 CLIENTEGESTAO                    33325146        
 VEICULOREVISAO                   14541831        
 DADOSBOOK                        11627492        
 HISTORICOQUILOMETRAGEM           9992701         
 RESULTADOINTEGRACAO              4089476         
 CAMPOSREGISTROINTERFACE          2892807         
 REGISTROINTEGRACAO               1731981         
 ACAO                             1585661         
 ENCAMINHAMENTO                   1263190         
 REQUISICAOOS                     948967          
 MOTIVOEVENTO                     916872          
 EVENTO                           818115          
 VEICULO                          -35041          
 CLIENTE                          -64414

数据库中任何记录的下一个 ID 将是“no_sequencia”+ 1，因此该表收到大量更新，每分钟约 100 次，很少有一些 INSERT，每年一次可能更少，我的疑问仍然存在autovacuum，今天我使用以下配置：

`autovacuum_vacuum_scale_factor` = 0.20
`autovacuum_vacuum_threshold` = 50

当达到 51 个更新的元组时，如果 autovacuum 可用，将清除表，在更一般的上下文中，如果我们忽略此表，autovacuum 每 30 分钟左右运行一次：

 relname                    n_live_tup   % live   n_dead_tup    Tamanho     Registros    Configuracao                                                                                                                               last_autovacuum      autovacuum_count  
 -------------------------  -----------  -------  ------------  ----------  -----------  -----------------------------------------------------------------------------------------------------------------------------------------  -------------------  ------------------
 sequencia                  191          65       99            752 KB      191          {autovacuum_vacuum_scale_factor=0.20,autovacuum_vacuum_threshold=50}                                                                       17/01/2018 19:41:31  13806             
 campanha                   3541         98       67            3400 KB     3821         {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 19:40:49  194               
 sms                        2071256      99       1026          773160 KB   2071070      {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 19:24:31  20                
 campanhaexecucao           1948         96       61            136 KB      1980         {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 19:05:48  10                
 telefonema                 8957384      99       1792          1086424 KB  8957050      {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 18:22:45  17                
 email                      1059515      99       3776          945368 KB   1056970      {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 18:11:53  3                 
 oportunidade               698662       99       317           105992 KB   698440       {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 18:06:54  1                 
 interesse                  698254       99       550           93936 KB    698043       {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 18:06:46  1                 
 encaminhamento             8277408      99       1998          1187264 KB  8277230      {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 18:03:24  39                
 evento                     3689441      99       5672          2376448 KB  3688780      {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 17:06:11  36                
 sessao                     1736         94       102           216 KB      1736         {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 16:22:44  18                
 acao                       14931180     99       6635          4250392 KB  14924700     {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=25000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=50000}  17/01/2018 15:25:32  10                
 parametroempresa           349          81       77            48 KB       348          {autovacuum_vacuum_scale_factor=0.20,autovacuum_vacuum_threshold=50}                                                                       17/01/2018 14:22:41  22                
 tipoevento                 4565         99       19            3656 KB     4565         {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 14:08:40  3                 
 usuariotipoevento          64254        98       992           4544 KB     64270        {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 14:03:41  5                 
 registrointerface          4050719      99       1194          549800 KB   4049100      {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=10000,autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=20000}  17/01/2018 13:30:35  2                 
 regraleadmontadora         117          90       13            48 KB       117          {autovacuum_vacuum_scale_factor=0.20,autovacuum_vacuum_threshold=50}                                                                       17/01/2018 11:58:37  6                 
 usuarioformacontato        78593        98       1389          5944 KB     79230        {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 11:22:39  3                 
 arquivointegracao          1843         93       134           296 KB      1787         {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_threshold=100}                                                                      17/01/2018 02:00:50  12                

在上面的示例中，我们看到n_dead_tup已经过了 51，autovacuum 正在休眠 60 秒。

 name ---------------------------------- setting ----- unit   
 autovacuum                              on             
 autovacuum_analyze_scale_factor         0.1            
 autovacuum_analyze_threshold            50             
 autovacuum_freeze_max_age               200000000      
 autovacuum_max_workers                  3              
 autovacuum_multixact_freeze_max_age     400000000      
 autovacuum_naptime                      60            s      
 autovacuum_vacuum_cost_delay            20            ms     
 autovacuum_vacuum_cost_limit            -1             
 autovacuum_vacuum_scale_factor          0.2            
 autovacuum_vacuum_threshold             50             
 autovacuum_work_mem                     -1            kB     

• 什么是 autovacuum 对该表起作用的合适设置？

该过程运行良好，这只是预防措施，如果您需要更多信息，请告知。

要获取estimated cost查询，我使用EXPLAIN SELECT column FROM table;，获取current cost查询，我正在使用EXPLAIN ANALYSE SELECT column FROM table;，我的问题是如何cost自动获取查询，而不必explain为每个查询手动运行。

我需要这样的东西：

DECLARE cost integer;
DECLARE highercost integer;
DECLARE query text;

highercost := 0;
i := 0;
query = '';

WHILE i < array_length( queryarray ,1) LOOP

    cost := explain analyse queryarray[i];

    IF cost > highercost THEN

        highercost := cost;
        query := queryarray[i];

    END IF;

    i := i+1;

END LOOP;

这个想法是创建一个脚本来检查 a 中的查询log并运行psql，或者将查询复制log到数据库中的表并运行plain SQL以验证最昂贵的查询，目前正是我所寻求的，无需担心关于cost查询的实际情况（“成本”X“每分钟执行的次数”），成本INSERT，UPDATE等等DELETE。

我希望这是可能的，如果不能，还有另一种方法可以搜索昂贵的查询而不用逐一检查吗？

我知道数据库可以将最常用的数据存储在 RAM 内存中以便快速访问，但是什么时候，什么数据？

1- 在此表中多次执行查询

id, name, gender
-- ----- -------
1 , Bill, Male.
2 , Todd, Male.

SELECT * FROM people WHERE gender = 'Male';

• 它可以在内存中保存的最小值是多少（列、行、页、表）？
• 在可以保存时进行 PK 或 FK 有什么区别？
• 如果我UPDATE是表或行，必须再次读取内存？

2 -JOIN在这样的表中进行查询？

people
id, name, gender_id
-- ----- -------
1 , Bill, 2
2 , Todd, 2

people_gender
id, gender
-- -------
1 , Female.
2 , Male.

SELECT id, name, gender FROM people p JOIN people_gender pg ON p.gender_id = pg.id

• 数据库读取一次并保存 的值people_gender.id，然后它只能读取 4 个字节 + 2 个字节 in people.gender_id= 6 个字节或者它必须 JOIN 并读取每一行，即使值是众所周知的 (1+4+1+4 = 10）？
• 它的JOIN价值还是第一种情况对性能更好？

真正的基础可以是Sql Server高达 16 GB RAM 或Postgresql低至 1 GB RAM，它有一个保存事件状态的列，如 WORKING，WAITING，STOPPED 和 CANCELED（实际上不是英语，五个状态，8-10 个字符每个），它是如此常见的更新和/或返回表中的数据Event，我从这里测试性能开始。

我在一个软件中工作，该软件在过去几年中增长了很多，现在正面临性能问题，这是为 DBA 工作的……但我们没有 :D。

我在 dba meta 中的第一个问题，很抱歉有任何错误，欢迎任何提示。

先感谢您。