{"id":812,"date":"2024-01-22T10:58:05","date_gmt":"2024-01-22T10:58:05","guid":{"rendered":"https:\/\/mvermeulen.org\/perf\/?page_id=812"},"modified":"2024-01-24T03:14:13","modified_gmt":"2024-01-24T03:14:13","slug":"pgbench","status":"publish","type":"page","link":"https:\/\/mvermeulen.org\/perf\/workloads\/phoronix\/pgbench\/","title":{"rendered":"pgbench"},"content":{"rendered":"\n<p>A benchmark of PostgresSQL operations. There are many different test configurations, I picked two that are closest to what Phoronix used in their benchmark article.  scaling factor of 100 with 1000 clients. The read operation followed by read\/write. Looks like there are mostly a small number of runnable processes through up to 800 appear at one point.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1280\" height=\"960\" src=\"https:\/\/mvermeulen.org\/perf\/wp-content\/uploads\/sites\/7\/2024\/01\/systemtime-56.png\" alt=\"\" class=\"wp-image-853\" srcset=\"https:\/\/mvermeulen.org\/perf\/wp-content\/uploads\/sites\/7\/2024\/01\/systemtime-56.png 1280w, https:\/\/mvermeulen.org\/perf\/wp-content\/uploads\/sites\/7\/2024\/01\/systemtime-56-1024x768.png 1024w, https:\/\/mvermeulen.org\/perf\/wp-content\/uploads\/sites\/7\/2024\/01\/systemtime-56-768x576.png 768w\" sizes=\"auto, (max-width: 1280px) 100vw, 1280px\" \/><\/figure>\n\n\n\n<p>Topdown overview shows a front-end bound process. The timing looks slightly different in this case than previous one and stretching longer. Perhap additional runs to get stability.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1280\" height=\"960\" src=\"https:\/\/mvermeulen.org\/perf\/wp-content\/uploads\/sites\/7\/2024\/01\/amdtopdown-94.png\" alt=\"\" class=\"wp-image-854\" srcset=\"https:\/\/mvermeulen.org\/perf\/wp-content\/uploads\/sites\/7\/2024\/01\/amdtopdown-94.png 1280w, https:\/\/mvermeulen.org\/perf\/wp-content\/uploads\/sites\/7\/2024\/01\/amdtopdown-94-1024x768.png 1024w, https:\/\/mvermeulen.org\/perf\/wp-content\/uploads\/sites\/7\/2024\/01\/amdtopdown-94-768x576.png 768w\" sizes=\"auto, (max-width: 1280px) 100vw, 1280px\" \/><\/figure>\n\n\n\n<p>AMD metrics show average on-cpu of only 2.5. Some floating point code and many branches. Frontend latency appears to dominate and this would be a good benchmark to dig deeper on latency.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>elapsed              863.433\non_cpu               0.157          # 2.52 \/ 16 cores\nutime                459.890\nstime                1713.587\nnvcsw                12451489       # 8.45%\nnivcsw               134887770      # 91.55%\ninblock              8              # 0.01\/sec\nonblock              16504          # 19.11\/sec\ncpu-clock            11390349332903 # 11390.349 seconds\ntask-clock           11403070781759 # 11403.071 seconds\npage faults          91224025       # 7999.953\/sec\ncontext switches     397659971      # 34873.060\/sec\ncpu migrations       38233072       # 3352.875\/sec\nmajor page faults    799            # 0.070\/sec\nminor page faults    91223226       # 7999.882\/sec\nalignment faults     0              # 0.000\/sec\nemulation faults     0              # 0.000\/sec\nbranches             5355467484433  # 205.853 branches per 1000 inst\nbranch misses        292739782623   # 5.47% branch miss\nconditional          3407698242743  # 130.985 conditional branches per 1000 inst\nindirect             117052096702   # 4.499 indirect branches per 1000 inst\ncpu-cycles           45652716363402 # 3.31 GHz\ninstructions         27213399658895 # 0.60 IPC\nslots                90040636490742 #\nretiring             9410107725001  # 10.5% (11.7%)\n-- ucode             44883771195    #     0.0%\n-- fastpath          9365223953806  #    10.4%\nfrontend             45468404154306 # 50.5% (56.6%)\n-- latency           37635521319684 #    41.8%\n-- bandwidth         7832882834622  #     8.7%\nbackend              24909401939477 # 27.7% (31.0%)\n-- cpu               2220898318464  #     2.5%\n-- memory            22688503621013 #    25.2%\nspeculation          605948968091   #  0.7% ( 0.8%)\n-- branch mispredict 602767658468   #     0.7%\n-- pipeline restart  3181309623     #     0.0%\nsmt-contention       9643199770382  # 10.7% ( 0.0%)\ncpu-cycles           45879696338268 # 3.32 GHz\ninstructions         26657314107701 # 0.58 IPC\ninstructions         8759583463590  # 79.491 l2 access per 1000 inst\nl2 hit from l1       611600487555   # 22.86% l2 miss\nl2 miss from l1      107286230319   #\nl2 hit from l2 pf    32790526002    #\nl3 hit from l2 pf    23400169289    #\nl3 miss from l2 pf   28515861814    #\ninstructions         8759544138410  # 80.986 float per 1000 inst\nfloat 512            70             # 0.000 AVX-512 per 1000 inst\nfloat 256            586            # 0.000 AVX-256 per 1000 inst\nfloat 128            709400728133   # 80.986 AVX-128 per 1000 inst\nfloat MMX            0              # 0.000 MMX per 1000 inst\nfloat scalar         0              # 0.000 scalar per 1000 inst\n<\/code><\/pre>\n\n\n\n<p>Intel metrics<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>elapsed              2291.272\non_cpu               0.139          # 2.22 \/ 16 cores\nutime                1836.851\nstime                3255.948\nnvcsw                7011767        # 1.36%\nnivcsw               507821164      # 98.64%\ninblock              7648           # 3.34\/sec\nonblock              5576           # 2.43\/sec\ncpu-clock            27441549967843 # 27441.550 seconds\ntask-clock           27449294125546 # 27449.294 seconds\npage faults          237069157      # 8636.621\/sec\ncontext switches     1078148357     # 39277.817\/sec\ncpu migrations       23548431       # 857.888\/sec\nmajor page faults    2234           # 0.081\/sec\nminor page faults    237066923      # 8636.540\/sec\nalignment faults     0              # 0.000\/sec\nemulation faults     0              # 0.000\/sec\nbranches             9692774844607  # 191.453 branches per 1000 inst\nbranch misses        57955491662    # 0.60% branch miss\nconditional          9692775358495  # 191.453 conditional branches per 1000 inst\nindirect             2345563682863  # 46.330 indirect branches per 1000 inst\nslots                124996051720466 #\nretiring             32187185782664 # 25.8% (25.8%)\n-- ucode             4181245792684  #     3.3%\n-- fastpath          28005939989980 #    22.4%\nfrontend             62982740884535 # 50.4% (50.4%)\n-- latency           43903284077682 #    35.1%\n-- bandwidth         19079456806853 #    15.3%\nbackend              26797147178785 # 21.4% (21.4%)\n-- cpu               4765353392012  #     3.8%\n-- memory            22031793786773 #    17.6%\nspeculation          3176735760047  #  2.5% ( 2.5%)\n-- branch mispredict 2355089638929  #     1.9%\n-- pipeline restart  821646121118   #     0.7%\nsmt-contention       0              #  0.0% ( 0.0%)\ncpu-cycles           59299293721852 # 1.62 GHz\ninstructions         45253336959405 # 0.76 IPC\nl2 access            2944393364322  # 93.519 l2 access per 1000 inst\nl2 miss              859430772105   # 29.19% l2 miss\n<\/code><\/pre>\n\n\n\n<p>Process overview shows the many processes launched as clients to run pgbench along with the postgres benchmark. Here would also be useful to separate out topdown metrics for either.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>6595 processes\n\t120 pgbench               7466.61 25775.90\n\t6086 postgres              6313.98  2805.83\n\t 68 clinfo                  18.19     5.99\n\t 38 vulkaninfo               1.32     0.96\n\t  6 glxinfo:gdrv0            0.15     0.08\n\t  4 vulkani:disk$0           0.14     0.10\n\t  6 php                      0.07     0.17\n\t  2 llvmpipe-0               0.07     0.05\n\t  2 llvmpipe-1               0.07     0.05\n\t  2 llvmpipe-10              0.07     0.05\n\t  2 llvmpipe-11              0.07     0.05\n\t  2 llvmpipe-12              0.07     0.05\n\t  2 llvmpipe-13              0.07     0.05\n\t  2 llvmpipe-14              0.07     0.05\n\t  2 llvmpipe-15              0.07     0.05\n\t  2 llvmpipe-2               0.07     0.05\n\t  2 llvmpipe-3               0.07     0.05\n\t  2 llvmpipe-4               0.07     0.05\n\t  2 llvmpipe-5               0.07     0.05\n\t  2 llvmpipe-6               0.07     0.05\n\t  2 llvmpipe-7               0.07     0.05\n\t  2 llvmpipe-8               0.07     0.05\n\t  2 llvmpipe-9               0.07     0.05\n\t  2 glxinfo                  0.07     0.04\n\t  2 glxinfo:cs0              0.07     0.04\n\t  2 glxinfo:disk$0           0.07     0.04\n\t  2 glxinfo:sh0              0.07     0.04\n\t  2 glxinfo:shlo0            0.07     0.04\n\t  6 clang                    0.04     0.08\n\t  3 rocminfo                 0.03     0.00\n\t  1 lspci                    0.00     0.03\n\t  1 ps                       0.00     0.01\n\t 90 sh                       0.00     0.00\n\t 13 gcc                      0.00     0.00\n\t 12 pg_ctl                   0.00     0.00\n\t 10 gsettings                0.00     0.00\n\t  8 stat                     0.00     0.00\n\t  8 systemd-detect-          0.00     0.00\n\t  6 bc                       0.00     0.00\n\t  6 createdb                 0.00     0.00\n\t  6 llvm-link                0.00     0.00\n\t  6 sleep                    0.00     0.00\n\t  5 phoronix-test-s          0.00     0.00\n\t  4 dropdb                   0.00     0.00\n\t  4 gmain                    0.00     0.00\n\t  2 cc                       0.00     0.00\n\t  2 dconf worker             0.00     0.00\n\t  2 lscpu                    0.00     0.00\n\t  2 uname                    0.00     0.00\n\t  2 which                    0.00     0.00\n\t  2 xset                     0.00     0.00\n\t  1 date                     0.00     0.00\n\t  1 dirname                  0.00     0.00\n\t  1 dmesg                    0.00     0.00\n\t  1 dmidecode                0.00     0.00\n\t  1 grep                     0.00     0.00\n\t  1 ifconfig                 0.00     0.00\n\t  1 ip                       0.00     0.00\n\t  1 lsmod                    0.00     0.00\n\t  1 mktemp                   0.00     0.00\n\t  1 qdbus                    0.00     0.00\n\t  1 readlink                 0.00     0.00\n\t  1 realpath                 0.00     0.00\n\t  1 sed                      0.00     0.00\n\t  1 sort                     0.00     0.00\n\t  1 stty                     0.00     0.00\n\t  1 systemctl                0.00     0.00\n\t  1 template.sh              0.00     0.00\n\t  1 wc                       0.00     0.00\n\t  1 xrandr                   0.00     0.00\n5 processes running\n1037 maximum processes\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>A benchmark of PostgresSQL operations. There are many different test configurations, I picked two that are closest to what Phoronix used in their benchmark article. scaling factor of 100 with 1000 clients. The read operation followed by read\/write. Looks like <span class=\"excerpt-dots\">&hellip;<\/span> <a class=\"more-link\" href=\"https:\/\/mvermeulen.org\/perf\/workloads\/phoronix\/pgbench\/\"><span class=\"more-msg\">Continue reading &rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":58,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-812","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/mvermeulen.org\/perf\/wp-json\/wp\/v2\/pages\/812","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mvermeulen.org\/perf\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/mvermeulen.org\/perf\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/mvermeulen.org\/perf\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mvermeulen.org\/perf\/wp-json\/wp\/v2\/comments?post=812"}],"version-history":[{"count":2,"href":"https:\/\/mvermeulen.org\/perf\/wp-json\/wp\/v2\/pages\/812\/revisions"}],"predecessor-version":[{"id":855,"href":"https:\/\/mvermeulen.org\/perf\/wp-json\/wp\/v2\/pages\/812\/revisions\/855"}],"up":[{"embeddable":true,"href":"https:\/\/mvermeulen.org\/perf\/wp-json\/wp\/v2\/pages\/58"}],"wp:attachment":[{"href":"https:\/\/mvermeulen.org\/perf\/wp-json\/wp\/v2\/media?parent=812"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}