DaemonForums  

Go Back   DaemonForums > OpenBSD > OpenBSD Packages and Ports

OpenBSD Packages and Ports Installation and upgrading of packages and ports on OpenBSD.

Reply
 
Thread Tools Display Modes
  #1   (View Single Post)  
Old 1st November 2018
hanzer's Avatar
hanzer hanzer is offline
Real Name: Adam Jensen
just passing through
 
Join Date: Oct 2013
Location: EST USA
Posts: 314
Default 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 cap_mkdb /etc/login.conf

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 -i -s 70 -U bench bench
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
https://wiki.postgresql.org/wiki/Tun...tgreSQL_Server

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'
exit
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)
Reply With Quote
  #2   (View Single Post)  
Old 1st November 2018
ripe's Avatar
ripe ripe is offline
Package Pilot
 
Join Date: Feb 2013
Location: France
Posts: 175
Default

Hi hanzer, this is a guide?
Reply With Quote
  #3   (View Single Post)  
Old 1st November 2018
hanzer's Avatar
hanzer hanzer is offline
Real Name: Adam Jensen
just passing through
 
Join Date: Oct 2013
Location: EST USA
Posts: 314
Default

Quote:
Originally Posted by ripe View Post
Hi hanzer, this is a guide?
Just public notes that are a little like a report that is open for comments; perhaps a proto-guide. It is a very new setup - I put it together last night. The results surprised me, the performance seems to be very good.
Reply With Quote
  #4   (View Single Post)  
Old 2nd November 2018
ripe's Avatar
ripe ripe is offline
Package Pilot
 
Join Date: Feb 2013
Location: France
Posts: 175
Default

Ok. PostgreSQL is nice.
Reply With Quote
  #5   (View Single Post)  
Old 13th November 2018
beavers beavers is offline
Shell Scout
 
Join Date: Nov 2017
Posts: 85
Default

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.
Reply With Quote
  #6   (View Single Post)  
Old 13th November 2018
hanzer's Avatar
hanzer hanzer is offline
Real Name: Adam Jensen
just passing through
 
Join Date: Oct 2013
Location: EST USA
Posts: 314
Default

For comparison, the same hardware running Scientific Linux-7.5 with PostgreSQL-11.1, with the same postgresql.conf as above in this thread, in an LVM partition with the XFS filesystem results in this:

Local pgbench:
Code:
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: 5386462
latency average = 0.446 ms
tps = 8977.429792 (including connections establishing)
tps = 8977.459991 (excluding connections establishing)
Remote pgbench:
Code:
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: 3419012
latency average = 6.583 ms
tps = 11392.941601 (including connections establishing)
tps = 11393.179867 (excluding connections establishing)
Reply With Quote
  #7   (View Single Post)  
Old 14th November 2018
gpatrick gpatrick is offline
Spam Deminer
 
Join Date: Nov 2009
Posts: 245
Default

Ouch. In other words, don't use OpenBSD for a database. Those tps and latency numbers are way different.
Reply With Quote
  #8   (View Single Post)  
Old 14th November 2018
hanzer's Avatar
hanzer hanzer is offline
Real Name: Adam Jensen
just passing through
 
Join Date: Oct 2013
Location: EST USA
Posts: 314
Default

Quote:
Originally Posted by gpatrick View Post
Ouch. In other words, don't use OpenBSD for a database. Those tps and latency numbers are way different.
Yeah, SL seems to make much better use of the machine. I've replaced the OpenBSD installation with Scientific Linux so I can explore OpenAFS more conveniently.

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.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

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


All times are GMT. The time now is 05:20 PM.


Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Content copyright © 2007-2010, the authors
Daemon image copyright ©1988, Marshall Kirk McKusick