|
OpenBSD Packages and Ports Installation and upgrading of packages and ports on OpenBSD. |
|
Thread Tools | Display Modes |
|
||||
PostgreSQL-10.5 on OpenBSD-6.4 setup and results
This machine - A Supermicro workstation with a quad-core Xeon, 32GB ECC RAM and an LSI MegaRAID SAS 9266-4i. - http://daemonforums.org/showthread.p...5428#post65428
This is from my notes: mount /dev/sd0j on /var/postgresql type ffs (local, nodev, nosuid) DEFAULT SETUP vi /etc/login.conf Code:
postgresql:\ :openfiles=768:\ :tc=daemon: doas su - _postgresql mkdir /var/postgresql/data initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W exit doas /etc/rc.d/postgresql start psql -U postgres Code:
CREATE USER bench; ALTER USER bench WITH PASSWORD 'blah'; CREATE DATABASE bench OWNER bench; GRANT ALL ON DATABASE bench TO bench; \q pgbench -c 4 -j1 -T 600 -U bench bench Password: starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 70 query mode: simple number of clients: 4 number of threads: 1 duration: 600 s number of transactions actually processed: 90661 latency average = 26.473 ms tps = 151.096835 (including connections establishing) tps = 151.098553 (excluding connections establishing) ################### https://www.postgresql.org/docs/curr...resources.html /etc/sysctl.conf Code:
kern.shminfo.shmmax=33554432 kern.shminfo.shmmin=1 kern.shminfo.shmall=8192 kern.shminfo.shmseg=128 kern.shminfo.shmmni=256 kern.seminfo.semmni=60 kern.seminfo.semmns=1024 kern.seminfo.semmsl=60 doas su - _postgresql cd /var/postgresql/data/ egrep -v "^[[:blank:]]*(#|$)" postgresql.conf Code:
listen_addresses = '127.0.0.1,10.1.10.20' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) superuser_reserved_connections = 5 # (change requires restart) password_encryption = scram-sha-256 # md5 or scram-sha-256 shared_buffers = 8GB # min 128kB work_mem = 64MB # min 64kB maintenance_work_mem = 1GB # min 1MB dynamic_shared_memory_type = posix # the default is the first option checkpoint_timeout = 30min # range 30s-1d max_wal_size = 30GB checkpoint_completion_target = 0.8 # checkpoint target duration, 0.0 - 1.0 random_page_cost = 2.0 # same scale as above effective_cache_size = 16GB default_statistics_target = 500 # range 1-10000 log_line_prefix = '%t:%r:%u@%d:[%p]: ' # special values: log_timezone = 'US/Eastern' datestyle = 'iso, mdy' timezone = 'US/Eastern' lc_messages = 'C' # locale for system error message lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C' # locale for number formatting lc_time = 'C' # locale for time formatting default_text_search_config = 'pg_catalog.english' doas /etc/rc.d/postgresql restart doas vi /etc/rc.conf.local #postgresql_flags= pkg_scripts=postgresql pgbench -c 4 -j1 -T 600 -U bench bench Password: starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 70 query mode: simple number of clients: 4 number of threads: 1 duration: 600 s number of transactions actually processed: 1703654 latency average = 1.409 ms tps = 2839.476987 (including connections establishing) tps = 2839.509728 (excluding connections establishing) doas vi /etc/pf.conf # change the following line to include port 5432 pass in on em0 inet proto tcp from 10.1.10.0/24 to em0 port { 22 80 443 5432 } doas pfctl -f /etc/pf.conf ### From remote machine pgbench -c 75 -j 8 -T 300 -h moria-lan -U bench bench Password: starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 70 query mode: simple number of clients: 75 number of threads: 8 duration: 300 s number of transactions actually processed: 1329039 latency average = 16.930 ms tps = 4429.931786 (including connections establishing) tps = 4430.226344 (excluding connections establishing) pgbench -c 75 -j 8 -T 300 -h moria-lan -S -U bench bench Password: starting vacuum...end. transaction type: <builtin: select only> scaling factor: 70 query mode: simple number of clients: 75 number of threads: 8 duration: 300 s number of transactions actually processed: 11665919 latency average = 1.929 ms tps = 38885.431753 (including connections establishing) tps = 38889.290596 (excluding connections establishing) pgbench -c 75 -j 8 -T 300 -h moria-lan -N -U bench bench Password: starting vacuum...end. transaction type: <builtin: simple update> scaling factor: 70 query mode: simple number of clients: 75 number of threads: 8 duration: 300 s number of transactions actually processed: 1731262 latency average = 12.997 ms tps = 5770.672555 (including connections establishing) tps = 5771.139797 (excluding connections establishing) |
|
|||
Thanks for posting this. I'm using postgres for a couple of small projects, will be taking a closer look at this and checking the performance of my own setup.
|
|
|||
Ouch. In other words, don't use OpenBSD for a database. Those tps and latency numbers are way different.
|
|
||||
Quote:
I suppose a facility like DTrace could be useful in figuring out what is going on with the poor DBMS performance. Maybe the OpenBSD Project, as an organization/enterprise, needs a sociotechnical infrastructure for collaborative reverse engineering and code refactoring. There is a lot of useful stuff out there that could be made better. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Postgresql 9.6.x on Openbsd 6.3 ? | vtypal | OpenBSD Packages and Ports | 2 | 2nd May 2018 06:00 PM |
Issue with PostgreSQL 9.6.2 and Perl on OpenBSD 6.1 | ed.n1n2 | OpenBSD Packages and Ports | 7 | 31st August 2017 12:08 AM |
OpenBSD Multiple VPN Setup | Dr-D | OpenBSD Security | 10 | 7th April 2014 10:50 AM |
OpenBSD VPN Setup | Dr-D | OpenBSD Security | 2 | 4th April 2014 01:23 PM |
how setup arpwatch for OpenBSD | mfaridi | OpenBSD Packages and Ports | 1 | 11th December 2008 05:22 PM |