API tools faq
paste
galileo_pkm

Is NVMe RAID useless (performance-wise) with PostgreSQL?

galileo_pkm
Jun 12th, 2024 (edited)
75
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.35 KB | None | 0 0
raw download clone embed print report
  1. Hi all,
  2.  
  3. While building a new PostgreSQL server, I realized that the performance with a single disk
  4. is the same or better than with a RAID0 4-disk array.
  5.  
  6. All benchmarks were conducted using pgbench with a scaling factor of 2000.
  7. For a typical run with pgbench -j 4 -c 512 -P 60 -r -T 300 -b tpcb-like, these are the results:
  8.  
  9.  
  10. single disk run:
  11.  
  12. latency average = 17.524 ms
  13. latency stddev = 6.904 ms
  14. tps = 28870
  15.  
  16.  
  17. iostat:
  18.  
  19. avg-cpu: %user %nice %system %iowait %steal %idle
  20. 26.10 0.00 21.02 2.95 0.00 49.93
  21.  
  22. | Device | r/s | rMB/s | rrqm/s | %rrqm | r_await | rareq-sz | w/s | wMB/s | wrqm/s | %wrqm | w_await | wareq-sz | f/s | f_await | aqu-sz | %util |
  23. |---------|:--------:|:------:|:------:|:-----:|:-------:|:--------:|:--------:|:------:|:------:|:-----:|:-------:|:--------:|:------:|:-------:|:------:|:-----:|
  24. | nvme0n1 | 28641.27 | 255.00 | 0.00 | 0.00 | 0.16 | 9.12 | 27665.67 | 458.09 | 0.00 | 0.00 | 0.09 | 16.96 | 251.47 | 1.69 | 7.69 | 98.08 |
  25.  
  26.  
  27.  
  28. RAID0 4 disk, 4K chunk:
  29.  
  30. latency average = 22.269 ms
  31. latency stddev = 10.825 ms
  32. tps = 22742
  33.  
  34.  
  35. avg-cpu: %user %nice %system %iowait %steal %idle
  36. 23.63 0.00 19.63 1.53 0.00 55.21
  37.  
  38. | Device | r/s | rMB/s | rrqm/s | %rrqm | r_await | rareq-sz | w/s | wMB/s | wrqm/s | %wrqm | w_await | wareq-sz | f/s | f_await | aqu-sz | %util |
  39. |---------|:--------:|:------:|:-------:|:-----:|:-------:|:--------:|:---------:|:------:|:------:|:-----:|:-------:|:--------:|:------:|:-------:|:------:|:-----:|
  40. | md127 | 55359.93 | 216.25 | 0.00 | 0.00 | 0.09 | 4.00 | 105629.07 | 412.61 | 0.00 | 0.00 | 0.04 | 4.00 | 0.00 | 0.00 | 9.02 | 93.76 |
  41. | nvme1n1 | 12763.33 | 54.03 | 1067.47 | 7.72 | 0.08 | 4.33 | 26572.07 | 103.31 | 37.33 | 0.14 | 0.05 | 3.98 | 162.53 | 1.74 | 2.67 | 99.18 |
  42. | nvme3n1 | 12753.07 | 53.97 | 1063.87 | 7.70 | 0.08 | 4.33 | 26560.47 | 103.26 | 37.40 | 0.14 | 0.05 | 3.98 | 162.47 | 1.73 | 2.58 | 99.15 |
  43. | nvme4n1 | 12787.27 | 54.10 | 1062.80 | 7.67 | 0.09 | 4.33 | 26492.73 | 102.99 | 35.67 | 0.13 | 0.05 | 3.98 | 162.53 | 1.69 | 2.67 | 99.07 |
  44. | nvme5n1 | 12796.53 | 54.15 | 1065.60 | 7.69 | 0.09 | 4.33 | 26505.67 | 103.04 | 35.73 | 0.13 | 0.05 | 3.98 | 162.53 | 1.66 | 2.56 | 98.95 |
  45.  
  46. BTW, if these tables are mangled in transport or by email clients, I posted this email to <pastebin URL>.
  47.  
  48. A 4K chunk is obviously not optimal, but I should still be getting around a 2x uplift.
  49.  
  50. In the past, when tuning PostgreSQL, I tweaked various RAID parameters like chunk size, stripe_cache_size, etc.
  51. but it never occurred to me to check the performance against a single drive. Hence this email.
  52. I'm not sure if this is expected or if there is something wrong with my setup.
  53. Full system details are at the end of the message.
  54.  
  55. While exploring this, I went deep down the rabbit hole, running hundreds of tests and trying dozens of configurations.
  56. The best I achieved was 31K TPS with a plain RAID0 256KB chunk, nvme poll_queues=4 and io_pool=1.
  57. This resulted in a measly 2% improvement compared to a single disk.
  58.  
  59. A sample of results:
  60.  
  61. Single disk nvme poll_queues=0
  62. pgbench -j4 -c X tps avg latency ms latency stddev ms
  63. 1 477 2.096 0.258
  64. 4 1167 3.426 0.295
  65. 16 4408 3.623 0.545
  66. 64 12533 5.089 0.999
  67. 128 21295 5.979 1.538
  68. 256 28022 9.048 3.014
  69. 512 28870 17.524 6.904
  70.  
  71.  
  72. Single disk nvme poll_queues=4
  73. pgbench -j4 -c X tps avg latency ms latency stddev ms
  74. 128 22284 5.711 1.448
  75. 256 27390 9.240 2.848
  76. 512 30596 16.452 6.090
  77. 1024 26352 38.481 19.513
  78.  
  79.  
  80. 4 disk RAID 0 4KB Chunk nvme poll_queues=0
  81. pgbench -j4 -c X tps avg latency ms latency stddev ms
  82. 128 17614 7.231 2.43
  83. 256 22347 11.37 4.922
  84. 512 22742 22.269 10.825
  85. 1024 20896 48.57 26.975
  86.  
  87.  
  88. 4 disk LVM RAID 0 4KB Chunk nvme poll_queues=4
  89. pgbench -j4 -c X tps avg latency ms latency stddev ms
  90. 128 17423 7.312 1.991
  91. 256 22064 11.521 4.044
  92. 512 24875 20.373 9.421
  93. 1024 21242 47.692 25.843
  94.  
  95.  
  96. 4 disk RAID 0 8KB Chunk nvme poll_queues=4
  97. pgbench -j4 -c X tps avg latency ms latency stddev ms
  98. 128 18907 6.736 2.094
  99. 256 24909 10.184 3.446
  100. 512 24878 20.331 8.448
  101. 1024 20535 49.665 27.462
  102.  
  103.  
  104. 4 disk RAID 0 64KB Chunk nvme poll_queues=4
  105. pgbench -j4 -c X tps avg latency ms latency stddev ms
  106. 128 21393 5.951 2.102
  107. 256 27231 9.293 2.752
  108. 512 30261 16.624 5.986
  109. 1024 25245 40.285 21.317
  110.  
  111.  
  112. 4 disk RAID 0 256KB Chunk nvme poll_queues=4
  113. pgbench -j4 -c X tps avg latency ms latency stddev ms
  114. 128 21055 6.046 1.972
  115. 256 27226 9.289 2.736
  116. 512 31174 16.120 5.858
  117. 1024 27069 37.355 17.797
  118.  
  119.  
  120. 4 disk RAID 5 4KB Chunk nvme poll_queues=4 group_thread_cnt=0 stripe_cache_size=256
  121. pgbench -j4 -c X tps avg latency ms latency stddev ms
  122. 128 15368 8.291 3.527
  123. 256 19110 13.312 6.151
  124. 512 19796 25.667 13.191
  125.  
  126.  
  127. 4 disk RAID 5 4KB Chunk nvme poll_queues=4 group_thread_cnt=4 stripe_cache_size=256
  128. pgbench -j4 -c X tps avg latency ms latency stddev ms
  129. 128 17143 7.431 2.353
  130. 256 21777 11.665 4.175
  131. 512 22602 22.433 9.239
  132. 1024 20291 50.189 26.191
  133.  
  134.  
  135. 4 disk RAID 5 4KB Chunk nvme poll_queues=4 group_thread_cnt=8 stripe_cache_size=256
  136. pgbench -j4 -c X tps avg latency ms latency stddev ms
  137. 128 16767 7.598 2.698
  138. 256 21646 11.733 4.288
  139. 512 22161 22.879 9.811
  140.  
  141. 4 disk RAID 5 256KB Chunk nvme poll_queues=4 group_thread_cnt=8 stripe_cache_size=4096
  142. pgbench -j4 -c X tps avg latency ms latency stddev ms
  143. 128 15997 7.966 2.754
  144. 256
  145. 512 21745 23.293 9.805
  146.  
  147. The obvious conclusion is that there is something broken with Linux MD RAID, so I examined
  148. whether RAID0 scales with the simplest workloads, such as sequential reads.
  149. Unfortunately, I did not reach a definite conclusion.
  150. I posted my tests to the Linux RAID list but received no response (https://lore.kernel.org/linux-raid/[email protected]/T/#u).
  151.  
  152. So my question is this: has anyone else tested whether MD RAID scales with PostgreSQL, and what results did you get?
  153. What is your typical setup when you have a bunch of NVMe drives in a shiny new server?
  154.  
  155.  
  156. System specs:
  157.  
  158.  
  159. Dell PowerEdge R7525, Dual AMD EPYC 7313, 32G DDR4 3200
  160. Disks used for tests: "Samsung SSD 980 PRO with Heatsink 2TB" drives
  161. All drives under test are connected to the same processor.
  162. AlmaLinux release 9.4 (Seafoam Ocelot)
  163. Kernel 5.14.0-427.20.1.el9_4.x86_64 #1 SMP PREEMPT_DYNAMIC
  164. tuned-adm profile postgresql
  165. tweaks: poll_queues=4, io_poll = 1
  166.  
  167. version
  168. --------------------------------------------------------------------------------------------------------------
  169. PostgreSQL 16.1 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 11.4.1 20231218 (Red Hat 11.4.1-3), 64-bit
  170. (1 row)
  171.  
  172. name | current_setting | source
  173. ------------------------------+--------------------+--------------------
  174. application_name | psql | client
  175. checkpoint_completion_target | 0.9 | configuration file
  176. client_encoding | UTF8 | client
  177. DateStyle | ISO, MDY | configuration file
  178. default_statistics_target | 100 | configuration file
  179. default_text_search_config | pg_catalog.english | configuration file
  180. dynamic_shared_memory_type | posix | configuration file
  181. effective_cache_size | 24GB | configuration file
  182. effective_io_concurrency | 200 | configuration file
  183. lc_messages | C.UTF-8 | configuration file
  184. lc_monetary | C.UTF-8 | configuration file
  185. lc_numeric | C.UTF-8 | configuration file
  186. lc_time | C.UTF-8 | configuration file
  187. log_filename | postgresql-%a.log | configuration file
  188. log_rotation_age | 1d | configuration file
  189. log_rotation_size | 0 | configuration file
  190. log_timezone | Europe/Belgrade | configuration file
  191. log_truncate_on_rotation | on | configuration file
  192. logging_collector | on | configuration file
  193. maintenance_work_mem | 2GB | configuration file
  194. max_connections | 1024 | configuration file
  195. max_wal_size | 8GB | configuration file
  196. min_wal_size | 2GB | configuration file
  197. random_page_cost | 1.1 | configuration file
  198. shared_buffers | 8GB | configuration file
  199. TimeZone | Europe/Belgrade | configuration file
  200. wal_buffers | 16MB | configuration file
  201. work_mem | 4MB | configuration file
  202.  
  203.  
  204.  
  205.  
  206.  
  207.  
  208. Thanks
  209. Dragan
  210.  
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!