postgresql安装及性能测试
1. Postgresql介绍
Postgresql是一款功能强大的开源对象关系型数据库管理系统（ORDBMS），以其稳定性、扩展性和标准的SQL支持而闻名。它支持复杂查询、外键、触发器、视图、事务完整性、多版本并发控制（MVCC）等特性，且具有丰富的扩展能力，可以通过插件扩展其功能。Postgresql适用于各种场景，从小型应用到大规模企业级应用。
2. Postgresql安装
Postgresql可以安装在多种操作系统上，包括Linux、macOS和Windows。以下是不同系统的安装步骤：
2.1 在Linux上安装Postgresql（以Ubuntu为例）
- 更新包列表并安装依赖：
sudo apt-get update
sudo apt-get install wget ca-certificates
- 添加Postgresql仓库：
wget -qO - https://www.Postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.Postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
- 更新包列表并安装Postgresql：
sudo apt-get update
sudo apt-get install Postgresql Postgresql-contrib
- 启动Postgresql服务：
sudo systemctl start Postgresql
- 设置Postgresql在系统启动时自动启动：
sudo systemctl enable Postgresql
2.2 数据库管理操作
- 查看数据库版本:
test@ubuntu-svr:~$ sudo -u postgres psql -c "SELECT version();"
version
----------------------------------------------------------------------------------------------------------------------------------------
Postgresql 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)
- 登录与登出数据库
test@ubuntu-svr:~$ sudo su postgres # 切换到postgres用户
postgres@ubuntu-svr:/home/test$ psql # 通过psql工具进入数据库
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
Type "help" for help.
postgres=# \q # 退出数据库
- 查看数据库与用户
test@ubuntu-svr:~$ sudo su postgres # 切换到postgres用户
postgres@ubuntu-svr:/home/test$ psql # 通过psql工具进入数据库
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
Type "help" for help.
postgres=# \l # 查看数据库列表，默认有三个
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)
postgres=# \du # 查看数据库用户
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
- 创建数据库用户并授权
# 创建用户并指定权限
postgres=# CREATE USER test WITH PASSWORD '123456';
CREATE ROLE
postgres=# ALTER USER test WITH SUPERUSER;
ALTER ROLE
# 删除用户
postgres=# DROP USER xxx;
- 查看数据库配置文件路径
postgres@ubuntu-svr:~$ psql
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
Type "help" for help.
postgres=# show hba_file;
hba_file
-------------------------------------
/etc/postgresql/14/main/pg_hba.conf
(1 row)
postgres=# show config_file;
config_file
-----------------------------------------
/etc/postgresql/14/main/postgresql.conf
(1 row)
- 使用新创建用户登录
# pg数据库默认连接的认证方式是peer。在postgres用户下无法使用test数据库用户连接
postgres@ubuntu-svr:/home/test$ psql -U test -d postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "test"
# 在test系统用户下使用test用户连接数据库
test@ubuntu-svr:~$ psql -U test -d postgres
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
Type "help" for help.
postgres=#
说明:
Peer认证方法的工作原理是：从内核中获取客户的操作系统用户名，并将其作为允许的数据库用户名（可选择用户名映射）。这种方法只支持本地连接。
- 修改认证方式
修改配置文件改为，将认证方式改为md5认证：
# 修改第二条local配置中的method为md5
test@ubuntu-svr:~$ sudo vim /etc/postgresql/14/main/pg_hba.conf
...
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
...
# 重启数据库
test@ubuntu-svr:~$ sudo systemctl restart postgresql
# 再次在postgres系统用户下使用test数据库用户连接成功。
test@ubuntu-svr:~$ sudo su postgres
postgres@ubuntu-svr:/home/test$ psql -U test -d postgres
Password for user test:
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
Type "help" for help.
postgres=#
- 配置远程连接
# 修改pg_hba.conf在最后位置增加如下配置，允许所有远程主机的数据库用户使用md5认证进行连接。
test@ubuntu-svr:~$ sudo vim /etc/postgresql/14/main/pg_hba.conf
...
# Allow all hosts connections via md5:
host all all 0.0.0.0/0 md5
...
# 修改postgresql.conf，修改listen_addresses为*，并取消注释。port和最大连接数根据需求进行自定义。
test@ubuntu-svr:~$ sudo vim /etc/postgresql/14/main/postgresql.conf
...
60 listen_addresses = '*' # what IP address(es) to listen on;
61 # comma-separated list of addresses;
62 # defaults to 'localhost'; use '*' for all
63 # (change requires restart)
64 port = 5432 # (change requires restart)
65 max_connections = 100 # (change requires restart)
...
# 重启数据库
test@ubuntu-svr:~$ sudo systemctl restart postgresql
# 在另外一台机器上测试连接。确认已经关闭数据库所在机器的防火墙或者放行规则。
[root@localhost test]# psql -h 192.168.226.128 -p 5432 -U test -d postgres
Password for user test:
psql (9.2.24, server 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
WARNING: psql version 9.2, server version 14.0.
Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.
2.2 在macOS上安装Postgresql
- 使用Homebrew安装：
brew update
brew install Postgresql
- 启动Postgresql服务：
brew services start Postgresql
2.3 在Windows上安装Postgresql
-
下载Postgresql：从Postgresql官方网站下载Windows版的Postgresql安装包。
-
运行安装程序：按照安装向导的指示进行安装，选择默认设置即可。
-
初始化数据库集群：安装程序会自动初始化数据库集群并启动Postgresql服务。
3. Postgresql性能测试方案
为了测试Postgresql的性能，可以使用内置的基准测试工具pgbench，或者第三方工具如sysbench。
3.1 使用pgbench进行测试
pgbench是Postgresql自带的基准测试工具，可以模拟多种负载来测试数据库性能。
- 初始化测试数据库：
test@ubuntu-svr:~$ sudo -u postgres createdb pgbench
test@ubuntu-svr:~$ sudo -u postgres pgbench -i -s 10 pgbench
dropping old tables...
creating tables...
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 0.47 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.85 s (drop tables 0.02 s, create tables 0.01 s, client-side generate 0.48 s, vacuum 0.18 s, primary keys 0.17 s).
这个命令会创建一个名为pgbench的测试数据库，并初始化测试数据，-s 10表示生成10倍于默认规模的数据。
- 运行基准测试：
test@ubuntu-svr:~$ sudo -u postgres pgbench -c 10 -j 2 -T 60 pgbench
pgbench (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 2
duration: 60 s
number of transactions actually processed: 311888
latency average = 1.924 ms
initial connection time = 9.363 ms
tps = 5198.015078 (without initial connection time)
这个命令会以10个并发连接、2个线程运行测试，持续时间为60秒。
-
查看测试结果：
pgbench会输出每秒事务数（tps）等性能指标。
3.2 使用sysbench进行测试
sysbench是一个多线程基准测试工具，支持多种数据库的性能测试，包括Postgresql。
- 安装sysbench：
sudo apt-get install sysbench
- 准备测试数据：
# 创建sysbench测试库
test@ubuntu-svr:~$ sudo -u postgres createdb testdb
# 生成测试数据，根据环境替换pgsql用户名和密码
sysbench --db-driver=pgsql --pgsql-host=localhost --pgsql-user=test --pgsql-password=123456 --pgsql-db=testdb --tables=10 --table-size=1000000 /usr/share/sysbench/oltp_read_write.lua prepare
这个命令会在名为testdb的数据库中创建10个表，每个表包含1,000,000行数据。
- 运行基准测试：
sysbench --db-driver=pgsql --pgsql-host=localhost --pgsql-user=test --pgsql-password=123456 --pgsql-db=testdb --tables=10 --table-size=1000000 --threads=10 --time=60 /usr/share/sysbench/oltp_read_write.lua run
这个命令会以10个线程运行读写测试，持续时间为60秒。测试结果如下：
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 10
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 1190546
write: 340154
other: 170080
total: 1700780
transactions: 85039 (1417.01 per sec.)
queries: 1700780 (28340.26 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0121s
total number of events: 85039
Latency (ms):
min: 1.05
avg: 7.05
max: 63.49
95th percentile: 13.46
sum: 599901.95
Threads fairness:
events (avg/stddev): 8503.9000/116.42
execution time (avg/stddev): 59.9902/0.00
- 清理测试数据
sysbench --db-driver=pgsql --pgsql-host=localhost --pgsql-user=test --pgsql-password=123456 --pgsql-db=testdb --tables=10 --table-size=1000000 --threads=10 --time=60 /usr/share/sysbench/oltp_read_write.lua cleanup
4. 优化Postgresql性能的建议
-
硬件优化：使用SSD存储、增加内存和高性能CPU以提升I/O和计算能力。
-
配置优化：调整Postgresql配置文件
Postgresql.conf中的参数，如shared_buffers、work_mem、maintenance_work_mem、effective_cache_size、checkpoint_segments等。 -
索引优化：创建合适的索引以加快查询速度，避免过多的索引影响写性能。
-
查询优化：使用EXPLAIN分析查询计划，优化SQL查询以减少不必要的开销。
-
连接池：使用连接池（如PgBouncer）来减少连接创建和销毁的开销，提高并发处理能力。
-
分区：对于大表，可以使用表分区来提高查询性能和管理效率。
-
VACUUM和ANALYZE：定期运行VACUUM和ANALYZE命令以维护数据库统计信息和清理垃圾数据，提高查询性能。
通过合理的配置和优化，可以显著提升Postgresql的性能，满足高并发、低延迟的数据处理需求。
本文由mdnice多平台发布