Postgres optimization

[crashza]

Hi Guys this is most probably not a heplify server issue but i get much better performance using homer5 DB schema using mysql than homer7 schema on postgres.

I have been trying to move over my prod traffic to Homer7 but I kept having a huge disk IO bottleneck which was causing some packets to not be written to DB, I tried all postgres tuning to try and get more out of it but all attempts failed.

I ran a test with my same heplify-server (heplify-server-1.32.0-1.x86_64) using homer5 schema and homer7 schema

I used hammerhep to load test it at 10 000 pps using postgres I get 100 disk usage on iostat using mysql and homer5 schema I get around 40% with the same load test.

I understand this is not really a homer issue but the underlying DB bottleneck but if postgres is the default for homer 7 I would hope there would be some rock solid config provided that will at lease perform on par with older homer5 schema and mysql.

To conduct the load tests hammerhep was used as follows.

./hammerhep -transport TCP -rate 10000

DB versions postgresql-server-12.1-2.module_el8.1.0+273+979c16e6.x86_64 mariadb-server-10.3.17-1.module_el8.1.0

Any help would be appreciated thanks.

[andycol]

Strange as postgresql should perform a lot better than mysql

take a look at the below and maybe tweak your settings to see if you get an improvement https://pgtune.leopard.in.ua/#/

[adubovikov]

can you please share the configs of mysql and postgress ? also, please provide the ammount of RAM, HDD (hdparam, dd perfomance managment)

to see the config of heplify-server will be nice to have.

I hope you do these tests on the same box ?

[adubovikov]

also, if we are talking about disk IO, i would like to recomend to tune shared_buffers params in the pgsql config.

http://wiki.postgresql.org/wiki/Performance_Optimization

and please provide iostats of MYSQL and PGSQL:

sudo iostat -k 1

[crashza]

Hi The tests where on the same server and partition, I have unfortunately had to go back to homer 5 on this server as its now in prduction, But I'm happy to setup a VM and test these issues on a platform that is not needed in production my mysql/postgresql setting where default, I did post versions above but here they are again.

heplify-server-1.32.0-1.x86_64 postgresql-server-12.1-2.module_el8.1.0+273+979c16e6.x86_64 mariadb-server-10.3.17-1.module_el8.1.0

heplify server configs where default for both.

Give me a day or so i will setup a test platform and provide above iostats's etc.

Thanks

[adubovikov]

don't think so that with default mysql settings you can handle 10K PPS and 40% load :-) even with mariadb.

https://serverfault.com/questions/284799/postgres-disk-io-very-high-what-can-i-do-to-reduce-it-immediately https://lifehacker.com/speed-up-linux-hard-drives-by-disabling-atime-5074959

[crashza]

Ok i built a VM and tested this as follows Vanilla install of Centos8 Mem 10G HDD 200G

only one DB was running at a time Load tests where done as follows

./hammerhep -address 127.0.0.1 -transport TCP -rate 10000

iostat was started at the same time as hammerhep I then continued to run hammerhep for 1 minute before stopping it I then continued to run iostat for 1 more minute to make sure heplify completed sending data to DB's

Versions of all packages

heplify-server-1.32.0-1.x86_64 mariadb-server-10.3.17-1.module_el8.1.0+257+48736ea6.x86_64 postgresql-server-12.1-2.module_el8.1.0+273+979c16e6.x86_64

configs for DB's and heplify can be seen below. as well as iostat results

https://nextcloud.webon.co.za/index.php/s/XKjym7Nqgtn47YL

Please let me know if you need any further info.

Thanks

[adubovikov]

so, on the first look postgres write a bit more data and more packets. Maybe it depeends on JSON schema, need to investigate

[crashza]

Is there a way I can test homer7 schema on a mysql backend, I have tried all the tuning and FS options like noatime/nodiratime without much success.

I would like to confirm if its the new schema or psql thats hampering performance.

[lmangani]

@crashza there is no mysql compatibility planned at this time for 7.x

[adubovikov]

@crashza so, i see definitive more IOwait on Postgress 's test. I sugest do optimization that I wrote about IOWait:

1. shared_buffers= 20% of your RAM
2. max_wal_size = 30% of your RAM (maybe more)

please play around with: https://pgtune.leopard.in.ua/#/

[adubovikov]

also good article: https://www.percona.com/blog/2020/05/29/removing-postgresql-bottlenecks-caused-by-high-traffic/

try follow their recomendation

[crashza]

@adubovikov Thanks I have already tried this the results are probably slightly better but still not even close to a stock mariadb setup.

My current config looks as follows,

root@homer7test data] :-) $ mount | grep root /dev/mapper/cl-root on / type xfs (rw,noatime,nodiratime,seclabel,attr2,inode64,noquota) [root@homer7test data] :-) $

[root@homer7test data] :-) $ cat postgresql.conf # DB Version: 12 # OS Type: linux # DB Type: dw # Total Memory (RAM): 10 GB # CPUs num: 6 # Data Storage: hdd

max_connections = 40 shared_buffers = 2560MB effective_cache_size = 7680MB maintenance_work_mem = 1280MB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 500 random_page_cost = 4 effective_io_concurrency = 2 work_mem = 10922kB min_wal_size = 4GB max_wal_size = 16GB max_worker_processes = 6 max_parallel_workers_per_gather = 3 max_parallel_workers = 6 max_parallel_maintenance_workers = 3

[crashza]

I installed up another machine with HW Raid 10 (15K SAS drives) 6 of them, This setup is still struggling to do around 6k pps. At least now its not dropping any packets but IO is still massively high.

Can you guys advise what sort of hardware I would need to process 10k pps this is the HW I have

Manufacturer: Dell Inc. Product Name: PowerEdge R510 CPU: 8 Core Intel(R) Xeon(R) CPU E5620 @ 2.40GHz Memory: 16 Gig Raid Config Root Volume (Raid1) 2x 300GB 15K SAS drives for OS Data Volume (Raid10) 6x 600GB 15K SAS drives for DB storage

My config is as per my previose post as well as heplify-server has default configs

iostat -x 10 10 shows my data drive is utilised 99% of the time.

Im really surprised I cant handle my load with these specs but if anyone can advise how to spec a machine for 10kpps I would be grateful.

Thanks

[adubovikov]

very very strange, because we did stess test before we can handle more than 100K pps and it was R320 server with similar configurations.

I will do some test today and let you know.

[adubovikov]

Can you change these parameteres:

effective_io_concurrency = 25 shared_buffers = 8GB

and try one more time ?

[crashza]

@adubovikov Thanks I have changed the parameters

Load on the drive is still high above 90%

postgres= SHOW effective_io_concurrency; effective_io_concurrency -------------------------- 25 (1 row)

postgres= SHOW shared_buffers; shared_buffers ---------------- 8GB (1 row)

postgres=

[crashza]

Couple of disk test not sure if they valid but i have confirmed with other servers on raid 5 that this is quite a bit faster especially my DD test

[root@monvoice psql] :-) $ hdparm -Tt /dev/sdb1

/dev/sdb1: Timing cached reads: 14474 MB in 1.99 seconds = 7261.97 MB/sec Timing buffered disk reads: 1694 MB in 3.00 seconds = 564.25 MB/sec

[root@monvoice psql] :-) $ sync; dd if=/dev/zero of=/srv/tempfile oflag=direct bs=128k count=16k ; time sync 16384+0 records in 16384+0 records out 2147483648 bytes (2.1 GB) copied, 4.16254 s, 516 MB/s

real 0m0.002s user 0m0.002s sys 0m0.000s [root@monvoice psql] :-) $

[adubovikov]

ubuntu 20 LTS, my local workstation:

model name : Intel(R) Core(TM) i7-8700K CPU @ 3.70GHz 32 RAM

psql (12.5 (Ubuntu 12.5-0ubuntu0.20.04.1))

/dev/sdc2:
 Timing cached reads:   36776 MB in  1.99 seconds = 18498.04 MB/sec
 Timing buffered disk reads: 1184 MB in  3.00 seconds = 394.04 MB/sec
root@home-ubuntu:/home/shurik/HAMMER# hdparm -Tt /dev/sdc2 

sync; dd if=/dev/zero of=/srv/tempfile oflag=direct bs=128k count=16k ; time sync
16384+0 records in
16384+0 records out
2147483648 bytes (2,1 GB, 2,0 GiB) copied, 6,46683 s, 332 MB/s

real    0m0,006s
user    0m0,000s
sys 0m0,003s

time ./hammerhep -address 127.0.0.1 -transport TCP -rate 10000 Hammer down HEP at 127.0.0.1:9060 over TCP with 10000 pps ^C

real 5m4,485s user 1m18,055s sys 2m5,719s

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   wrqm/s  %wrqm w_await wareq-sz     d/s     dkB/s   drqm/s  %drqm d_await dareq-sz  aqu-sz  %util
sdc              0,10      0,40     0,00   0,00    2,00     4,00  217,50  25050,40     9,80   4,31    1,09   115,17    0,00      0,00     0,00   0,00    0,00     0,00    0,04  24,44

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           7,51    0,00    3,69    1,09    0,00   87,71

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   wrqm/s  %wrqm w_await wareq-sz     d/s     dkB/s   drqm/s  %drqm d_await dareq-sz  aqu-sz  %util
sdc              0,10      0,40     0,00   0,00   11,00     4,00 1300,90  62961,60    75,40   5,48    0,90    48,40    0,00      0,00     0,00   0,00    0,00     0,00    0,48  31,84

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          19,70    0,00    7,88    2,88    0,00   69,54

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   wrqm/s  %wrqm w_await wareq-sz     d/s     dkB/s   drqm/s  %drqm d_await dareq-sz  aqu-sz  %util
sdc            463,50  15987,60   156,60  25,25    0,55    34,49 2193,20  76544,00   128,80   5,55    0,83    34,90    0,00      0,00     0,00   0,00    0,00     0,00    0,69  68,92

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           8,83    0,00    8,15    0,66    0,00   82,36

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   wrqm/s  %wrqm w_await wareq-sz     d/s     dkB/s   drqm/s  %drqm d_await dareq-sz  aqu-sz  %util
sdc              0,20      0,80     0,00   0,00    0,00     4,00  279,80  35297,60    34,80  11,06    1,38   126,15    0,00      0,00     0,00   0,00    0,00     0,00    0,13  23,60

homer_data=# select count(*) from hep_proto_1_call limit 1; count

1013465 (1 row)

default postgress.conf:

data_directory = '/var/lib/postgresql/12/main'      # use data in another directory
hba_file = '/etc/postgresql/12/main/pg_hba.conf'    # host-based authentication file
ident_file = '/etc/postgresql/12/main/pg_ident.conf'    # ident configuration file
external_pid_file = '/var/run/postgresql/12-main.pid'           # write an extra PID file
port = 5432             # (change requires restart)
max_connections = 100           # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
shared_buffers = 128MB          # min 128kB
dynamic_shared_memory_type = posix  # the default is the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_line_prefix = '%m [%p] %q%u@%d '        # special values:
log_timezone = 'Europe/Berlin'
cluster_name = '12/main'            # added to process titles if nonempty
stats_temp_directory = '/var/run/postgresql/12-main.pg_stat_tmp'
datestyle = 'iso, dmy'
timezone = 'Europe/Berlin'
lc_messages = 'en_US.UTF-8'         # locale for system error message
lc_monetary = 'de_DE.UTF-8'         # locale for monetary formatting
lc_numeric = 'de_DE.UTF-8'          # locale for number formatting
lc_time = 'de_DE.UTF-8'             # locale for time formatting
default_text_search_config = 'pg_catalog.english'
include_dir = 'conf.d'          # include files ending in '.conf' from

as you see - load average is minimum and I really have no clue why your system so bad overloaded.

can you please update to pgsql 12.5 and use my config or just take default config of the ubuntu's package

[crashza]

@adubovikov Thanks so much for taking the time to run this scenario, I have made some changes to my psql config as per above, I am already on psql12.5, is there anything in conf.d i should be aware of??

I am getting similar results to you when using hammerhep as can be seen below, However when routing production traffic earlier today load was much higher with fewer PPS, I see obviously there is no filtered packets with hammerhep as all packets are valid this cant be the cause as this is processed and filtered in mem? Also hammerhep packets sizes if anything seem larger than my average sizes so now im not sure if the DB changes have made a diff or is it that hammerhep hep packets are just easier on IO I will know more in the morning when production traffic peaks again, Thanks @adubovikov for taking the time to look into my issue I will update tomorrow with further results.

Heplify stats prod vs hammerhep

Hammerhep 2020/12/03 20:32:46.662346 server.go:242: INFO stats since last 5 minutes. PPS: 8100, HEP: 2430144, Filtered: 0, Error: 0 2020/12/03 20:37:46.677514 server.go:242: INFO stats since last 5 minutes. PPS: 15636, HEP: 4691097, Filtered: 0, Error: 0 2020/12/03 20:42:46.662354 server.go:242: INFO stats since last 5 minutes. PPS: 19427, HEP: 5828142, Filtered: 0, Error: 0

Production 2020/12/03 12:31:37.685111 server.go:242: INFO stats since last 5 minutes. PPS: 6035, HEP: 1801573, Filtered: 9181, Error: 1 2020/12/03 12:36:37.671573 server.go:242: INFO stats since last 5 minutes. PPS: 5990, HEP: 1789303, Filtered: 7835, Error: 1 2020/12/03 12:41:37.678169 server.go:242: INFO stats since last 5 minutes. PPS: 5965, HEP: 1780185, Filtered: 9531, Error: 1

iostat results 1 Min Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sdb 0.00 1380.30 0.10 403.20 0.40 25803.60 127.96 0.28 0.69 20.00 0.68 0.07 2.93

2 Min Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sdb 0.00 1032.60 4.20 656.50 99.60 51079.20 154.92 20.29 30.80 101.76 30.35 0.55 36.44

3 Min Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sdb 0.00 42.70 0.00 636.20 0.00 51177.20 160.88 7.70 12.10 0.00 12.10 0.19 12.16

4 Min Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sdb 0.00 672.00 0.40 1147.70 1.60 35432.80 61.73 90.30 79.13 298.25 79.05 0.46 53.05

5 Min Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sdb 0.00 959.20 0.00 677.10 0.00 50634.80 149.56 10.20 15.06 0.00 15.06 0.18 11.90

[adubovikov]

/etc/postgresql/12/main/conf.d$ ls -l
total 0

let check why you have Filtered and Errors. Probably you should also tune udp/tcp buffer in the sysctl.conf, but since you are saying that IO-Wait is an issue, we shoudl definitly check this direction.

[crashza]

The Errors are logged and are very few as can be seen above around 1 every 5 minutes. The following is logged for error packets

WARN ParseMsg: err parsing msg no SIP eof found

Is there any way to check/log filtered packets I assume these are other protocols as traffic is sent through a TAP device so HTTP/SSH etc will also be sent through to heplify-server.

What should I be tuning in sysctl? current settings below

[root@monvoice ~] :-( $ sysctl -a | grep udp sysctl: reading key "net.ipv6.conf.all.stable_secret" net.ipv4.udp_mem = 380379 507174 760758 net.ipv4.udp_rmem_min = 4096 net.ipv4.udp_wmem_min = 4096 sysctl: reading key "net.ipv6.conf.default.stable_secret" sysctl: reading key "net.ipv6.conf.em1.stable_secret" sysctl: reading key "net.ipv6.conf.em2.stable_secret" sysctl: reading key "net.ipv6.conf.lo.stable_secret" net.netfilter.nf_conntrack_udp_timeout = 30 net.netfilter.nf_conntrack_udp_timeout_stream = 180 [root@monvoice ~] :-) $

[adubovikov]

https://gist.github.com/voluntas/bc54c60aaa7ad6856e6f6a928b79ab6c

[adubovikov]

you can also start the captagent and filter only traffic that you need. I.e. drop options before they go to heplify-server, btw same you can do with heplify as well...

  -dim  Discard uninteresting SIP packets by CSeq [OPTIONS,NOTIFY]

[crashza]

Thanks Ive set sysctl as advised let me see what the io looks like tomorrow running live traffic, Currently using heplify as I prefer its simplicity over captagent and just works out the box flawlessly.

[crashza]

So again this morning with live production traffic disk io is through the roof.

avg-cpu: %user %nice %system %iowait %steal %idle 14.02 0.00 8.55 30.05 0.00 47.38

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sda 0.30 0.00 0.40 0.80 4.00 7.20 18.67 0.01 12.00 5.50 15.25 4.08 0.49 sdb 0.00 1581.10 35.40 1987.90 491.20 40272.40 40.29 196.79 97.79 75.44 98.18 0.49 100.00 dm-0 0.00 0.00 0.30 0.70 2.40 7.20 19.20 0.01 13.50 4.33 17.43 4.00 0.40 dm-1 0.00 0.00 0.40 0.00 1.60 0.00 8.00 0.00 9.00 9.00 0.00 2.25 0.09 dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

I ran a pcap of the hep encapsulated traffic in prod and a test with hammerhep of 1 minute each the hammerhep file was bigger so its not due to amount of data being written. I have a suspicion postgres is way more efficient with hammerhep due to the same 4 packets being sent and prod traffic is more random,

I will keep looking into the filtered packets on heplify not sure what that is but will have a look at the code.

For now i think I am just going to throw some SSD drives at the problem.

[adubovikov]

@crashza lets do another way. Can you just send "CALL" messages and drop OPTIONS/REGISTRATION? Lets see if it exactly the issue if you do "switching" between tables.

[adubovikov]

another way:

https://github.com/sipcapture/heplify-server/blob/master/example/homer7_config/heplify-server.toml#L41

DiscardMethod = [REGISTER,SUBSCRIBE,OPTIONS,NOTIFY]

[crashza]

Hi @adubovikov I have made the changes unfortunately its now off peak time and there is no load, Our next peak period will be monday I will report back then Cheers

[adubovikov]

@crashza do you have any updates on this ?

[crashza]

Hi @adubovikov apologies for the late reply, It did make a small difference in diskio although i assume this is purely to the amount of data not being written to DB as the REGISTRATIONS take up a big portion of the traffic, We have decided to replace the HDD's with SSD's to cater for future growth of the network, Thanks for you effort on this.

[adubovikov]

sure, don't forget to star us: https://github.com/sipcapture/homer/stargazers