Size: a a a
kp
YS
SV
kp
SV
A
# explain analyze select * from log;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Seq Scan on log (cost=0.00..1083078.35 rows=6832235 width=1145) (actual time=0.544..43662.580 rows=6511739 loops=1) │
│ Planning Time: 0.039 ms │
│ Execution Time: 43897.532 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(3 rows)
Time: 43897.861 ms (00:43.898)
YS
# explain analyze select * from log;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Seq Scan on log (cost=0.00..1083078.35 rows=6832235 width=1145) (actual time=0.544..43662.580 rows=6511739 loops=1) │
│ Planning Time: 0.039 ms │
│ Execution Time: 43897.532 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(3 rows)
Time: 43897.861 ms (00:43.898)
SELECT name, category, setting, unit, context, short_desc
FROM pg_settings
WHERE category IN ('Resource Usage / Memory', 'Query Tuning / Other Planner Options');
A
A
│ name │ category │ setting │ unit │ context │ short_desc │
│ autovacuum_work_mem │ Resource Usage / Memory │ -1 │ kB │ sighup │ Sets the maximum memory to be used by each autovacuum worker process. │
│ constraint_exclusion │ Query Tuning / Other Planner Options │ partition │ │ user │ Enables the planner to use constraints to optimize queries. │
│ cursor_tuple_fraction │ Query Tuning / Other Planner Options │ 0.1 │ │ user │ Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved. │
│ default_statistics_target │ Query Tuning / Other Planner Options │ 100 │ │ user │ Sets the default statistics target. │
│ dynamic_shared_memory_type │ Resource Usage / Memory │ posix │ │ postmaster │ Selects the dynamic shared memory implementation used. │
│ force_parallel_mode │ Query Tuning / Other Planner Options │ off │ │ user │ Forces use of parallel query facilities. │
│ from_collapse_limit │ Query Tuning / Other Planner Options │ 8 │ │ user │ Sets the FROM-list size beyond which subqueries are not collapsed. │
│ huge_pages │ Resource Usage / Memory │ try │ │ postmaster │ Use of huge pages on Linux or Windows. │
│ jit │ Query Tuning / Other Planner Options │ off │ │ user │ Allow JIT compilation. │
│ join_collapse_limit │ Query Tuning / Other Planner Options │ 8 │ │ user │ Sets the FROM-list size beyond which JOIN constructs are not flattened. │
│ maintenance_work_mem │ Resource Usage / Memory │ 2097152 │ kB │ user │ Sets the maximum memory to be used for maintenance operations. │
│ max_prepared_transactions │ Resource Usage / Memory │ 0 │ │ postmaster │ Sets the maximum number of simultaneously prepared transactions. │
│ max_stack_depth │ Resource Usage / Memory │ 2048 │ kB │ superuser │ Sets the maximum stack depth, in kilobytes. │
│ shared_buffers │ Resource Usage / Memory │ 1048576 │ 8kB │ postmaster │ Sets the number of shared memory buffers used by the server. │
│ temp_buffers │ Resource Usage / Memory │ 1024 │ 8kB │ user │ Sets the maximum number of temporary buffers used by each session. │
│ track_activity_query_size │ Resource Usage / Memory │ 1024 │ B │ postmaster │ Sets the size reserved for pg_stat_activity.query, in bytes. │
│ work_mem │ Resource Usage / Memory │ 20971 │ kB │ user │ Sets the maximum memory to be used for query workspaces. │
YS
YS
│ name │ category │ setting │ unit │ context │ short_desc │
│ autovacuum_work_mem │ Resource Usage / Memory │ -1 │ kB │ sighup │ Sets the maximum memory to be used by each autovacuum worker process. │
│ constraint_exclusion │ Query Tuning / Other Planner Options │ partition │ │ user │ Enables the planner to use constraints to optimize queries. │
│ cursor_tuple_fraction │ Query Tuning / Other Planner Options │ 0.1 │ │ user │ Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved. │
│ default_statistics_target │ Query Tuning / Other Planner Options │ 100 │ │ user │ Sets the default statistics target. │
│ dynamic_shared_memory_type │ Resource Usage / Memory │ posix │ │ postmaster │ Selects the dynamic shared memory implementation used. │
│ force_parallel_mode │ Query Tuning / Other Planner Options │ off │ │ user │ Forces use of parallel query facilities. │
│ from_collapse_limit │ Query Tuning / Other Planner Options │ 8 │ │ user │ Sets the FROM-list size beyond which subqueries are not collapsed. │
│ huge_pages │ Resource Usage / Memory │ try │ │ postmaster │ Use of huge pages on Linux or Windows. │
│ jit │ Query Tuning / Other Planner Options │ off │ │ user │ Allow JIT compilation. │
│ join_collapse_limit │ Query Tuning / Other Planner Options │ 8 │ │ user │ Sets the FROM-list size beyond which JOIN constructs are not flattened. │
│ maintenance_work_mem │ Resource Usage / Memory │ 2097152 │ kB │ user │ Sets the maximum memory to be used for maintenance operations. │
│ max_prepared_transactions │ Resource Usage / Memory │ 0 │ │ postmaster │ Sets the maximum number of simultaneously prepared transactions. │
│ max_stack_depth │ Resource Usage / Memory │ 2048 │ kB │ superuser │ Sets the maximum stack depth, in kilobytes. │
│ shared_buffers │ Resource Usage / Memory │ 1048576 │ 8kB │ postmaster │ Sets the number of shared memory buffers used by the server. │
│ temp_buffers │ Resource Usage / Memory │ 1024 │ 8kB │ user │ Sets the maximum number of temporary buffers used by each session. │
│ track_activity_query_size │ Resource Usage / Memory │ 1024 │ B │ postmaster │ Sets the size reserved for pg_stat_activity.query, in bytes. │
│ work_mem │ Resource Usage / Memory │ 20971 │ kB │ user │ Sets the maximum memory to be used for query workspaces. │
A
$ free -m
total used free shared buff/cache available
Mem: 31149 3095 18508 8456 9546 19151
YS
$ free -m
total used free shared buff/cache available
Mem: 31149 3095 18508 8456 9546 19151
c5.4xlarge 16 32 EBS-Only Up to 10 4,750А диски там какие (в списке не видно, я не стал дальше искать)?
h
A
postgres@db:~$ ps axuwwf |grep -e postgresql.conf -A0 -e SELECT#—————————-
postgres 28256 0.0 0.0 12940 1084 pts/0 S+ 10:18 0:00 | \_ grep -e postgresql.conf -A0 -e SELECT
--
postgres 1358 0.2 0.6 8789252 213432 ? S Jul29 11:46 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
--
postgres 27402 48.3 6.9 8806152 2211584 ? Ds 10:16 0:49 \_ postgres: 11/main: postgres novakid [local] SELECT
Aug 2 10:19:42 db kernel: [306159.019632] [27394] 113 27394 4928905 4901342 9630 21 0 0 psql
Aug 2 10:19:42 db kernel: [306159.019634] [27402] 113 27402 2201655 1271306 4262 12 0 0 postgres
Aug 2 10:19:42 db kernel: [306159.019635] [28951] 113 28951 2197626 2886 104 6 0 0 postgres
Aug 2 10:19:42 db kernel: [306159.019637] Out of memory: Kill process 27394 (psql) score 615 or sacrifice child
Aug 2 10:19:42 db kernel: [306159.023000] Killed process 27394 (psql) total-vm:19715620kB, anon-rss:19604016kB, file-rss:1280kB
#-------------------
ubuntu@db:~$ sudo systemctl status postgresql@11-main.service
● postgresql@11-main.service - PostgreSQL Cluster 11-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2020-07-29 21:16:58 UTC; 3 days ago
Process: 25688 ExecReload=/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i reload (code=exited, status=0/SUCCESS)
Process: 1261 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i start (code=exited, status=0/SUCCESS)
Main PID: 1358 (postgres)
CGroup: /system.slice/system-postgresql.slice/postgresql@11-main.service
├─ 1358 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
...
A
A
┌────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────┤
│ Seq Scan on log (cost=0.00..1084061.36 rows=6838436 width=1145) │
└────────────────────────────────────────────────────────────────────────┘
(1 row)
Time: 1.333 ms
YS
postgres@db:~$ ps axuwwf |grep -e postgresql.conf -A0 -e SELECT#—————————-
postgres 28256 0.0 0.0 12940 1084 pts/0 S+ 10:18 0:00 | \_ grep -e postgresql.conf -A0 -e SELECT
--
postgres 1358 0.2 0.6 8789252 213432 ? S Jul29 11:46 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
--
postgres 27402 48.3 6.9 8806152 2211584 ? Ds 10:16 0:49 \_ postgres: 11/main: postgres novakid [local] SELECT
Aug 2 10:19:42 db kernel: [306159.019632] [27394] 113 27394 4928905 4901342 9630 21 0 0 psql
Aug 2 10:19:42 db kernel: [306159.019634] [27402] 113 27402 2201655 1271306 4262 12 0 0 postgres
Aug 2 10:19:42 db kernel: [306159.019635] [28951] 113 28951 2197626 2886 104 6 0 0 postgres
Aug 2 10:19:42 db kernel: [306159.019637] Out of memory: Kill process 27394 (psql) score 615 or sacrifice child
Aug 2 10:19:42 db kernel: [306159.023000] Killed process 27394 (psql) total-vm:19715620kB, anon-rss:19604016kB, file-rss:1280kB
#-------------------
ubuntu@db:~$ sudo systemctl status postgresql@11-main.service
● postgresql@11-main.service - PostgreSQL Cluster 11-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2020-07-29 21:16:58 UTC; 3 days ago
Process: 25688 ExecReload=/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i reload (code=exited, status=0/SUCCESS)
Process: 1261 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i start (code=exited, status=0/SUCCESS)
Main PID: 1358 (postgres)
CGroup: /system.slice/system-postgresql.slice/postgresql@11-main.service
├─ 1358 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
...
A
AG
