xu22-pg01:
Архитектура: x86_64
CPU(s): 8
ID прроизводителя: GenuineIntel
Имя модели: Intel(R) Xeon(R) CPU E5-2680 v4 @ 2.40GHz
Virtualization features:
Разработчик гипервизора: KVM
Тип виртуализации: полный
Caches (sum of all):
L1d: 256 KiB (8 instances)
L1i: 256 KiB (8 instances)
L2: 2 MiB (8 instances)
L3: 280 MiB (8 instances)
NUMA:
NUMA node(s): 1
NUMA node0 CPU(s): 0-7
RAM:
32Gb
sudo vi /etc/systemd/system/disable-thp.service
[Unit]
Description=Disable Transparent Huge Pages
[Service]
Type=oneshot
ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/enabled'
ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/defrag'
[Install]
WantedBy=multi-user.target
# Execute the following commands in the terminal:
sudo systemctl daemon-reload
sudo systemctl enable disable-thp.service
sudo systemctl start disable-thp.service
cat /sys/kernel/mm/transparent_hugepage/enabled
cat /sys/kernel/mm/transparent_hugepage/defrag
# Both commands should output never, indicating that Transparent Huge Pages are disabled.
# Conclusion
# By following these steps, you’ve created a systemd service to disable Transparent Huge Pages permanently on Ubuntu 22.04. This change will persist across reboots, ensuring that THP is disabled each time the system starts. This method is preferred for systems where THP may interfere with the performance of certain applications, especially databases like MongoDB, Redis, etc., that recommend disabling THP for better performance and efficiency.
- sysctl vm.swappiness=1
sudo vi /etc/sysctl.conf
vm.swappiness=1
- Install PostgreSQL 17 on Ubuntu
sudo apt update && sudo DEBIAN_FRONTEND=noninteractive apt upgrade -y \
&& sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' \
&& wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - \
&& sudo apt-get update \
&& sudo DEBIAN_FRONTEND=noninteractive apt -y install postgresql-17 unzip atop htop nmon
- How To Install and Use PostgreSQL on Ubuntu 22.04
- How To Move a PostgreSQL Data Directory to a New Location on Ubuntu 22.04
################################################################ # Note: changing PGDATA will typically require adjusting SELinux # configuration as well. # Note: do not use a PGDATA pathname containing spaces, or you will # break postgresql-setup. [Unit] Description=PostgreSQL database server After=syslog.target After=network.target [Service] Type=forking User=postgres Group=postgres # Note: avoid inserting whitespace in these Environment= lines, or you may # break postgresql-setup. # Location of database directory Environment=PGDATA=/database/replica/ # Where to send early-startup messages from the server (before the logging # options of postgresql.conf take effect) # This is normally controlled by the global default set by systemd # StandardOutput=syslog # Disable OOM kill on the postmaster OOMScoreAdjust=-1000 #ExecStartPre=/usr/local/pgsql/bin/postgresql95-check-db-dir ${PGDATA} ExecStart=/usr/lib/postgresql/14/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 ExecStop=/usr/lib/postgresql/14/bin/pg_ctl stop -D ${PGDATA} -s -m fast ExecReload=/usr/lib/postgresql/14/bin/pg_ctl reload -D ${PGDATA} -s # Give a reasonable amount of time for the server to start up/shut down TimeoutSec=300 [Install] WantedBy=multi-user.target
sudo -u postgres psql
SHOW data_directory;
#Output
# data_directory
#-----------------------------
# /var/lib/postgresql/14/main
#(1 row)
# This output confirms that PostgreSQL is configured to use the default data directory,
# `/var/lib/postgresql/14/main`, so that’s the directory you need to move. Once you’ve confirmed
# the directory on your system, you can close the psql prompt by running the `\q` meta-command:
``\q``
sudo systemctl stop postgresql; sudo systemctl status postgresql
# Output
# ○ postgresql.service - PostgreSQL RDBMS
# Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor>
# Active: inactive (dead) since Thu 2022-06-30 18:46:35 UTC; 27s ago
# Process: 4588 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
# Main PID: 4588 (code=exited, status=0/SUCCESS)
# CPU: 1ms
# Now that the PostgreSQL server is no longer running, copy the existing database directory to the
# new location with rsync. Using the `-a` flag preserves the permissions and other directory properties,
# while `-v` provides verbose output to help you follow the progress. You’re going to start the rsync
# from the postgresql directory in order to mimic the original directory structure in the new location.
# By creating that postgresql directory within the mount-point directory and retaining ownership by the
# PostgreSQL user, you can avoid permissions problems for future upgrades.
# Note: Be sure there is no trailing slash on the directory, which may be added if you use TAB completion.
# If you do include a trailing slash, rsync will dump the contents of the directory into the mount point instead
# of copying over the directory itself.
# The version directory, 14, isn’t strictly necessary since you’ve defined the location explicitly
# in the `postgresql.conf` file, but following the project convention certainly won’t hurt,
# especially if there’s a need in the future to run multiple versions of PostgreSQL:
sudo rsync -av /var/lib/postgresql /mnt/volume_nyc1_01
# Once the copy is complete, rename the current folder with a `.bak` extension and keep it until you’ve
# confirmed that the move was successful. This will help to avoid confusion that could arise from having
# similarly-named directories in both the new and the old location:
sudo mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main.bak
# Now you’re ready to configure PostgreSQL to access the data directory in its new location.sudo vi /etc/postgresql/14/main/postgresql.conf
# Find the line that begins with data_directory and change the path which follows to reflect the new location. In the context of this tutorial, the updated directive will be written as:
``/etc/postgresql/14/main/postgresql.conf``
#
# . . .
# data_directory = '/mnt/volume_nyc1_01/postgresql/14/main'
# . . .
Save and close the file. This is all you need to do to configure PostgreSQL to use the new data directory location. All that’s left at this point is to start the PostgreSQL service again and check that it is indeed pointing to the correct data directory.# After changing the data-directory directive in the postgresql.conf file, go ahead and start the PostgreSQL server using systemctl:
sudo systemctl start postgresql; sudo systemctl status postgresql
# Output
# ● postgresql.service - PostgreSQL RDBMS
# Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor>
# Active: active (exited) since Thu 2022-06-30 18:50:18 UTC; 3s ago
# Process: 4852 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
# Main PID: 4852 (code=exited, status=0/SUCCESS)
# CPU: 1ms
# Lastly, to make sure that the new data directory is indeed in use, open the PostgreSQL command prompt:
sudo -u postgres psql
# Check the value for the data directory again:
SHOW data_directory;
Output
data_directory
----------------------------------------
/mnt/volume_nyc1_01/postgresql/14/main
(1 row)
# This confirms that PostgreSQL is using the new data directory location. Following this, take a moment to ensure that you’re able to access your database as well as interact with the data within. Once you’ve verified the integrity of any existing data, you can remove the backup data directory:
sudo rm -Rf /var/lib/postgresql/14/main.bak
# With that, you have successfully moved your PostgreSQL data directory to a new location.#!/usr/bin/env bash
[ -z "$PGDATA" ] && echo "PGDATA is not defined!" && exit 1
[ ! -e $PGDATA/postmaster.pid ] && echo "Cannot find $PGDATA/postmaster.pid is PostgreSQL server running?" && exit 1
PG_PID=$(head -1 $PGDATA/postmaster.pid)
PG_MEM_U=$(grep ^VmPeak /proc/$PG_PID/status | awk '{print $3}')
HP_MEM_U=$(grep ^Hugepagesize /proc/meminfo | awk '{print $3}')
[ "$PG_MEM_U" != "$HP_MEM_U" ] && echo "The units differ please calculate the Huge Pages manually" && exit 1
PG_MEM=$(grep ^VmPeak /proc/$PG_PID/status | awk '{print $2}')
HP_MEM=$(grep ^Hugepagesize /proc/meminfo | awk '{print $2}')
echo "Advised number of HugePages $((PG_MEM/HP_MEM))"
echo "You can use: sysctl -w vm.nr_hugepages=$((PG_MEM/HP_MEM))"# sysctl -w vm.nr_hugepages=153
### /etc/sysctl.conf file.
vm.nr_hugepages = 153- setup utils
sudo apt-get install libhugetlbfs-bin numactl
# numastat -p $$
Per-node process memory usage (in MBs) for PID 6638 (bash)
Node 0 Total
--------------- ---------------
Huge 0.00 0.00
Heap 0.65 0.65
Stack 0.11 0.11
Private 4.03 4.03
---------------- --------------- ---------------
Total 4.78 4.78
root@xu22-pg01:/home/pilot/PG-OPT31# sudo hugeadm --page-sizes-all
2097152
root@xu22-pg01:/home/pilot/PG-OPT31# sudo hugeadm --pool-list
Size Minimum Current Maximum Default
2097152 153 153 153 *pilot@xu22-pg01:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
17 main 5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
pilot@xu22-pg01:~$ sudo env su
root@xu22-pg01:/home/pilot# ll /var/lib/postgresql/17/main/
итого 92
drwx------ 19 postgres postgres 4096 янв 26 20:03 ./
drwxr-xr-x 3 postgres postgres 4096 янв 26 19:50 ../
drwx------ 5 postgres postgres 4096 янв 26 19:50 base/
drwx------ 2 postgres postgres 4096 янв 26 20:03 global/
drwx------ 2 postgres postgres 4096 янв 26 19:50 pg_commit_ts/
drwx------ 2 postgres postgres 4096 янв 26 19:50 pg_dynshmem/
drwx------ 4 postgres postgres 4096 янв 26 20:02 pg_logical/
drwx------ 4 postgres postgres 4096 янв 26 19:50 pg_multixact/
drwx------ 2 postgres postgres 4096 янв 26 19:50 pg_notify/
drwx------ 2 postgres postgres 4096 янв 26 19:50 pg_replslot/
drwx------ 2 postgres postgres 4096 янв 26 19:50 pg_serial/
drwx------ 2 postgres postgres 4096 янв 26 19:50 pg_snapshots/
drwx------ 2 postgres postgres 4096 янв 26 20:03 pg_stat/
drwx------ 2 postgres postgres 4096 янв 26 19:50 pg_stat_tmp/
drwx------ 2 postgres postgres 4096 янв 26 19:50 pg_subtrans/
drwx------ 2 postgres postgres 4096 янв 26 19:50 pg_tblspc/
drwx------ 2 postgres postgres 4096 янв 26 19:50 pg_twophase/
-rw------- 1 postgres postgres 3 янв 26 19:50 PG_VERSION
drwx------ 4 postgres postgres 4096 янв 26 19:50 pg_wal/
drwx------ 2 postgres postgres 4096 янв 26 19:50 pg_xact/
-rw------- 1 postgres postgres 88 янв 26 19:50 postgresql.auto.conf
-rw------- 1 postgres postgres 130 янв 26 20:03 postmaster.opts
-rw------- 1 postgres postgres 107 янв 26 20:03 postmaster.pid
root@xu22-pg01:/home/pilot/PG-OPT31# export PGDATA=/var/lib/postgresql/17/main/
root@xu22-pg01:/home/pilot/PG-OPT31# ./calculate_hugepages.sh
Advised number of HugePages 109
You can use: sysctl -w vm.nr_hugepages=109
root@xu22-pg01:/home/pilot/PG-OPT31# sysctl -w vm.nr_hugepages=109
vm.nr_hugepages = 109
pilot@xu22-pg01:~$ sudo su postgres
postgres@xu22-pg01:/home/pilot$ psql
psql (17.2 (Ubuntu 17.2-1.pgdg22.04+1))
Введите "help", чтобы получить справку.
postgres=# \dt+
Отношения не найдены.
postgres=# \du
Список ролей
Имя роли | Атрибуты
----------+-------------------------------------------------------------------------
postgres | Суперпользователь, Создаёт роли, Создаёт БД, Репликация, Пропускать RLS
postgres=# \q
postgres@xu22-pg01:/home/pilot$ pgbench -i postgres
dropping old tables...
ЗАМЕЧАНИЕ: таблица "pgbench_accounts" не существует, пропускается
ЗАМЕЧАНИЕ: таблица "pgbench_branches" не существует, пропускается
ЗАМЕЧАНИЕ: таблица "pgbench_history" не существует, пропускается
ЗАМЕЧАНИЕ: таблица "pgbench_tellers" не существует, пропускается
creating tables...
generating data (client-side)...
vacuuming...
creating primary keys...
done in 0.27 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 0.12 s, vacuum 0.05 s, primary keys 0.08 s).
postgres@xu22-pg01:/home/pilot$ pgbench -P 1 -T 10 postgres
pgbench (17.2 (Ubuntu 17.2-1.pgdg22.04+1))
starting vacuum...end.
progress: 1.0 s, 508.0 tps, lat 1.961 ms stddev 0.385, 0 failed
progress: 2.0 s, 528.0 tps, lat 1.893 ms stddev 0.319, 0 failed
progress: 3.0 s, 520.0 tps, lat 1.920 ms stddev 0.290, 0 failed
progress: 4.0 s, 499.0 tps, lat 2.006 ms stddev 0.783, 0 failed
progress: 5.0 s, 521.8 tps, lat 1.913 ms stddev 0.275, 0 failed
progress: 6.0 s, 530.1 tps, lat 1.887 ms stddev 0.328, 0 failed
progress: 7.0 s, 522.1 tps, lat 1.916 ms stddev 0.273, 0 failed
progress: 8.0 s, 520.0 tps, lat 1.923 ms stddev 0.318, 0 failed
progress: 9.0 s, 519.9 tps, lat 1.920 ms stddev 0.278, 0 failed
progress: 10.0 s, 508.1 tps, lat 1.971 ms stddev 0.275, 0 failed
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 5178
number of failed transactions: 0 (0.000%)
latency average = 1.930 ms
latency stddev = 0.381 ms
initial connection time = 3.535 ms
tps = 517.909641 (without initial connection time)
pgbench -P 1 -c 10 -T 10 postgres
postgres@xu22-pg01:/home/pilot$ pgbench -P 1 -c 10 -T 10 postgres
pgbench (17.2 (Ubuntu 17.2-1.pgdg22.04+1))
starting vacuum...end.
progress: 1.0 s, 680.7 tps, lat 13.996 ms stddev 11.257, 0 failed
progress: 2.0 s, 739.2 tps, lat 13.522 ms stddev 9.965, 0 failed
progress: 3.0 s, 721.0 tps, lat 13.880 ms stddev 11.292, 0 failed
progress: 4.0 s, 744.9 tps, lat 13.400 ms stddev 10.213, 0 failed
progress: 5.0 s, 761.1 tps, lat 13.189 ms stddev 9.996, 0 failed
progress: 6.0 s, 772.0 tps, lat 12.925 ms stddev 10.591, 0 failed
progress: 7.0 s, 784.0 tps, lat 12.737 ms stddev 8.676, 0 failed
progress: 8.0 s, 789.7 tps, lat 12.694 ms stddev 10.614, 0 failed
progress: 9.0 s, 745.9 tps, lat 13.284 ms stddev 10.484, 0 failed
progress: 10.0 s, 767.4 tps, lat 13.166 ms stddev 9.367, 0 failed
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 7516
number of failed transactions: 0 (0.000%)
latency average = 13.266 ms
latency stddev = 10.258 ms
initial connection time = 36.165 ms
tps = 752.928165 (without initial connection time)
- 4 потока по числу ядер (8)
postgres@xu22-pg01:/home/pilot$ pgbench -P 1 -c 10 -j 8 -T 10 postgres
pgbench (17.2 (Ubuntu 17.2-1.pgdg22.04+1))
starting vacuum...end.
progress: 1.0 s, 722.9 tps, lat 13.578 ms stddev 10.626, 0 failed
progress: 2.0 s, 748.9 tps, lat 13.353 ms stddev 10.081, 0 failed
progress: 3.0 s, 747.2 tps, lat 13.338 ms stddev 11.267, 0 failed
progress: 4.0 s, 745.8 tps, lat 13.420 ms stddev 10.918, 0 failed
progress: 5.0 s, 751.1 tps, lat 13.252 ms stddev 9.910, 0 failed
progress: 6.0 s, 743.9 tps, lat 13.524 ms stddev 10.475, 0 failed
progress: 7.0 s, 765.0 tps, lat 13.071 ms stddev 8.431, 0 failed
progress: 8.0 s, 755.1 tps, lat 13.247 ms stddev 9.423, 0 failed
progress: 9.0 s, 755.0 tps, lat 13.211 ms stddev 9.308, 0 failed
progress: 10.0 s, 757.9 tps, lat 13.206 ms stddev 9.367, 0 failed
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 8
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 7503
number of failed transactions: 0 (0.000%)
latency average = 13.325 ms
latency stddev = 10.007 ms
initial connection time = 10.374 ms
tps = 749.661513 (without initial connection time)
[x] VM
- 32Gb RAM
- 8 vCPU
- 1 SSD
- 10Gb DATABASE Size
- OLTP
# Connectivity
max_connections = 1000
superuser_reserved_connections = 3
# Memory Settings
shared_buffers = '8192 MB'
work_mem = '32 MB'
maintenance_work_mem = '420 MB'
huge_pages = try # NB! requires also activation of huge pages via kernel params, see here for more: https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-HUGE-PAGES
effective_cache_size = '22 GB'
effective_io_concurrency = 100 # concurrent IO only really activated if OS supports posix_fadvise function
random_page_cost = 1.25 # speed of random disk access relative to sequential access (1.0)
# Monitoring
shared_preload_libraries = 'pg_stat_statements' # per statement resource usage stats
track_io_timing=on # measure exact block IO times
track_functions=pl # track execution times of pl-language procedures if any
# Replication
wal_level = replica # consider using at least 'replica'
max_wal_senders = 0
synchronous_commit = on
# Checkpointing:
checkpoint_timeout = '15 min'
checkpoint_completion_target = 0.9
max_wal_size = '1024 MB'
min_wal_size = '512 MB'
# WAL writing
wal_compression = on
wal_buffers = -1 # auto-tuned by Postgres till maximum of segment size (16MB by default)
wal_writer_delay = 200ms
wal_writer_flush_after = 1MB
# Background writer
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
bgwriter_flush_after = 0
# Parallel queries:
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4
max_parallel_workers = 8
parallel_leader_participation = on
# Advanced features
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
jit = on
max_slot_wal_keep_size = '1000 MB'
track_wal_io_timing = on
maintenance_io_concurrency = 100
wal_recycle = on
synchronous_commit = off
pilot@xu22-pg01:~$ sudo su postgres
postgres@xu22-pg01:/home/pilot$ pgbench -P 1 -c 10 -j 8 -T 10 postgres
pgbench (17.2 (Ubuntu 17.2-1.pgdg22.04+1))
starting vacuum...end.
progress: 1.0 s, 705.8 tps, lat 13.894 ms stddev 9.588, 0 failed
progress: 2.0 s, 733.2 tps, lat 13.587 ms stddev 8.573, 0 failed
progress: 3.0 s, 718.0 tps, lat 14.004 ms stddev 10.585, 0 failed
progress: 4.0 s, 736.0 tps, lat 13.582 ms stddev 9.463, 0 failed
progress: 5.0 s, 729.9 tps, lat 13.673 ms stddev 9.290, 0 failed
progress: 6.0 s, 744.9 tps, lat 13.409 ms stddev 9.963, 0 failed
progress: 7.0 s, 743.2 tps, lat 13.463 ms stddev 9.422, 0 failed
progress: 8.0 s, 742.9 tps, lat 13.501 ms stddev 8.888, 0 failed
progress: 9.0 s, 719.1 tps, lat 13.883 ms stddev 11.266, 0 failed
progress: 10.0 s, 747.8 tps, lat 13.337 ms stddev 9.332, 0 failed
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 8
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 7331
number of failed transactions: 0 (0.000%)
latency average = 13.637 ms
latency stddev = 9.663 ms
initial connection time = 10.147 ms
tps = 732.426534 (without initial connection time)
postgres@xu22-pg01:/home/pilot$ psql -c "ALTER SYSTEM SET synchronous_commit = off;"
ALTER SYSTEM
postgres@xu22-pg01:/home/pilot$ psql -c "SELECT pg_reload_conf();"
pg_reload_conf
----------------
t
postgres@xu22-pg01:/home/pilot$ pgbench -P 1 -c 10 -j 8 -T 10 postgres
pgbench (17.2 (Ubuntu 17.2-1.pgdg22.04+1))
starting vacuum...end.
progress: 1.0 s, 2923.7 tps, lat 3.377 ms stddev 2.182, 0 failed
progress: 2.0 s, 2963.2 tps, lat 3.373 ms stddev 2.437, 0 failed
progress: 3.0 s, 3045.2 tps, lat 3.286 ms stddev 2.295, 0 failed
progress: 4.0 s, 3145.5 tps, lat 3.176 ms stddev 2.253, 0 failed
progress: 5.0 s, 3433.2 tps, lat 2.909 ms stddev 2.198, 0 failed
progress: 6.0 s, 3551.7 tps, lat 2.816 ms stddev 2.113, 0 failed
progress: 7.0 s, 3610.4 tps, lat 2.770 ms stddev 2.077, 0 failed
progress: 8.0 s, 3577.9 tps, lat 2.794 ms stddev 2.056, 0 failed
progress: 9.0 s, 3348.3 tps, lat 2.987 ms stddev 2.068, 0 failed
progress: 10.0 s, 3206.0 tps, lat 3.113 ms stddev 2.210, 0 failed
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 8
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 32813
number of failed transactions: 0 (0.000%)
latency average = 3.045 ms
latency stddev = 2.200 ms
initial connection time = 10.655 ms
tps = 3281.893038 (without initial connection time)
Working with test DB Thai
wget https://storage.googleapis.com/thaibus/thai_small.tar.gz && tar -xf thai_small.tar.gz && psql < thai.sql
pilot@xu22-pg01:~/PG-OPT31$ tar -xf thai_small.tar.gz
pilot@xu22-pg01:~/PG-OPT31$ sudo su postgres
postgres@xu22-pg01:/home/pilot/PG-OPT31$ psql < thai.sql
postgres@xu22-pg01:/home/pilot/PG-OPT31$ psql -d thai
thai=# \dn+
Список схем
Имя | Владелец | Права доступа | Описание
--------+-------------------+----------------------------------------+------------------------
book | postgres | |
public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
| | =U/pg_database_owner |
thai=# \timing
Секундомер включён.
thai=# SELECT count(*) FROM book.tickets;
count
---------
5185505
(1 строка)
Время: 331,513 мс
thai=# SELECT count(1) FROM book.tickets;
count
---------
5185505
(1 строка)
Время: 195,805 мс
thai=# explain SELECT count(1) FROM book.tickets;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=60964.02..60964.03 rows=1 width=8)
-> Gather (cost=60963.60..60964.01 rows=4 width=8)
Workers Planned: 4
-> Partial Aggregate (cost=59963.60..59963.61 rows=1 width=8)
-> Parallel Index Only Scan using tickets_pkey on tickets (cost=0.43..56722.32 rows=1296513 width=0)
(5 строк)
Время: 1,414 мс
thai=# explain (analyze, buffers) SELECT count(1) FROM book.tickets;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=60964.02..60964.03 rows=1 width=8) (actual time=230.817..242.437 rows=1 loops=1)
Buffers: shared hit=14181
-> Gather (cost=60963.60..60964.01 rows=4 width=8) (actual time=230.683..242.428 rows=5 loops=1)
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=14181
-> Partial Aggregate (cost=59963.60..59963.61 rows=1 width=8) (actual time=225.397..225.398 rows=1 loops=5)
Buffers: shared hit=14181
-> Parallel Index Only Scan using tickets_pkey on tickets (cost=0.43..56722.32 rows=1296513 width=0) (actual time=0.102..153.554 rows=1037101 loops=5)
Heap Fetches: 0
Buffers: shared hit=14181
Planning Time: 0.057 ms
Execution Time: 242.468 ms
(13 строк)
Время: 242,947 мс
thai=# \d+ book.tickets
Таблица "book.tickets"
Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию | Хранилище | Сжатие | Цель для статистики | Описание
---------+---------+--------------------+-------------------+------------------------------------------+-----------+--------+---------------------+----------
id | bigint | | not null | nextval('book.tickets_id_seq'::regclass) | plain | | |
fkride | integer | | | | plain | | |
fio | text | | | | extended | | |
contact | jsonb | | | | extended | | |
fkseat | integer | | | | plain | | |
Индексы:
"tickets_pkey" PRIMARY KEY, btree (id)
Ограничения внешнего ключа:
"tickets_fkride_fkey" FOREIGN KEY (fkride) REFERENCES book.ride(id)
"tickets_fkseat_fkey" FOREIGN KEY (fkseat) REFERENCES book.seat(id)
Метод доступа: heap
thai=# SELECT count(id) FROM book.tickets;
count
---------
5185505
(1 строка)
Время: 178,495 мс
thai=# explain SELECT count(id) FROM book.tickets;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=60964.02..60964.03 rows=1 width=8)
-> Gather (cost=60963.60..60964.01 rows=4 width=8)
Workers Planned: 4
-> Partial Aggregate (cost=59963.60..59963.61 rows=1 width=8)
-> Parallel Index Only Scan using tickets_pkey on tickets (cost=0.43..56722.32 rows=1296513 width=8)
(5 строк)
Время: 0,660 мс
thai=# explain (analyze, buffers) SELECT count(id) FROM book.tickets;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=60964.02..60964.03 rows=1 width=8) (actual time=248.133..258.952 rows=1 loops=1)
Buffers: shared hit=14181
-> Gather (cost=60963.60..60964.01 rows=4 width=8) (actual time=248.029..258.943 rows=5 loops=1)
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=14181
-> Partial Aggregate (cost=59963.60..59963.61 rows=1 width=8) (actual time=242.198..242.199 rows=1 loops=5)
Buffers: shared hit=14181
-> Parallel Index Only Scan using tickets_pkey on tickets (cost=0.43..56722.32 rows=1296513 width=8) (actual time=0.107..165.090 rows=1037101 loops=5)
Heap Fetches: 0
Buffers: shared hit=14181
Planning Time: 0.104 ms
Execution Time: 259.000 ms
(13 строк)
Время: 259,678 мс
thai=# vacuum analyze book.tickets;
VACUUM
Время: 419,129 мс
thai=# set random_page_cost = 1;
SET
Время: 0,381 мс
thai=# SELECT relname, pg_size_pretty(pg_relation_size(oid)) FROM pg_class WHERE relname like 'tickets%';
relname | pg_size_pretty
----------------+----------------
tickets | 460 MB
tickets_id_seq | 8192 bytes
tickets_pkey | 111 MB
(3 строки)
Время: 1,147 мс
thai=# vacuum analyze book.tickets;
VACUUM
Время: 385,802 мс
thai=# EXPLAIN SELECT count(id) FROM book.tickets;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=57355.19..57355.20 rows=1 width=8)
-> Gather (cost=57354.77..57355.18 rows=4 width=8)
Workers Planned: 4
-> Partial Aggregate (cost=56354.77..56354.78 rows=1 width=8)
-> Parallel Index Only Scan using tickets_pkey on tickets (cost=0.43..53113.74 rows=1296410 width=8)
(5 строк)
Время: 0,823 мс
thai=# CREATE EXTENSION pg_prewarm;
CREATE EXTENSION
Время: 7,333 мс
thai=# SELECT relname, pg_size_pretty(pg_relation_size(oid)), pg_prewarm(oid) FROM pg_class WHERE relname like 'tickets%';
relname | pg_size_pretty | pg_prewarm
----------------+----------------+------------
tickets | 460 MB | 58926
tickets_id_seq | 8192 bytes | 1
tickets_pkey | 111 MB | 14221
(3 строки)
Время: 34,772 мс
thai=# EXPLAIN (BUFFERS, ANALYZE, TIMING OFF) SELECT count(id) FROM book.tickets;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=57355.19..57355.20 rows=1 width=8) (actual rows=1 loops=1)
Buffers: shared hit=14181
-> Gather (cost=57354.77..57355.18 rows=4 width=8) (actual rows=5 loops=1)
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=14181
-> Partial Aggregate (cost=56354.77..56354.78 rows=1 width=8) (actual rows=1 loops=5)
Buffers: shared hit=14181
-> Parallel Index Only Scan using tickets_pkey on tickets (cost=0.43..53113.74 rows=1296410 width=8) (actual rows=1037101 loops=5)
Heap Fetches: 0
Buffers: shared hit=14181
Planning Time: 0.110 ms
Execution Time: 193.281 ms
(13 строк)
Время: 193,944 мс
thai=# set enable_seqscan = off;
SET
Время: 0,309 мс
thai=# set random_page_cost = 1;
SET
Время: 0,333 мс
thai=# EXPLAIN SELECT count(id) FROM book.tickets;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=57355.19..57355.20 rows=1 width=8)
-> Gather (cost=57354.77..57355.18 rows=4 width=8)
Workers Planned: 4
-> Partial Aggregate (cost=56354.77..56354.78 rows=1 width=8)
-> Parallel Index Only Scan using tickets_pkey on tickets (cost=0.43..53113.74 rows=1296410 width=8)
(5 строк)
Время: 0,590 мс
thai=# EXPLAIN SELECT count(fio) FROM book.tickets;
QUERY PLAN
----------------------------------------------------------------------------------------
Aggregate (cost=10000123746.51..10000123746.52 rows=1 width=8)
-> Seq Scan on tickets (cost=10000000000.00..10000110782.41 rows=5185641 width=15)
JIT:
Functions: 3
Options: Inlining true, Optimization true, Expressions true, Deforming true
(5 строк)
Время: 29,902 мс
thai=# ALTER TABLE book.tickets ALTER COLUMN fio SET NOT NULL;
ОШИБКА: столбец "fio" отношения "tickets" содержит значения NULL
Время: 2,308 мс
thai=# UPDATE book.tickets SET fio = 'no' WHERE fio is NULL;
UPDATE 47607
Время: 11683,063 мс (00:11,683)
thai=# ALTER TABLE book.tickets ALTER COLUMN fio SET NOT NULL;
ALTER TABLE
Время: 0,504 мс