PostgreSQL testing tools with benchmark archive

prefect

<span class="Apple-style-span" style="color: rgb(25, 65, 117); font-family: Arial, Helvetica, sans-serif; font-size: 15px; line-height: normal; "><div class="asset-header" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; "><span class="Apple-style-span" style="font-size: 15px; font-weight: normal; "><br></span><span class="Apple-style-span" style="font-size: 15px; font-weight: normal; ">By&nbsp;<a class="fn url" href="http://www.2ndquadrant.us/" style="background-color: transparent; color: rgb(18, 18, 18); ">Greg Smith</a>,&nbsp;January 17, 2011 2:41 AM&nbsp;</span></div><div class="asset-content entry-content" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; "><div class="asset-body" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; ">I maintain a number of project whose purpose in life is to make testing portions of PostgreSQL easier.&nbsp; All of these got a decent upgrade over this last week.<br><br><a href="https://github.com/gregs1104/stream-scaling" style="background-color: transparent; color: rgb(18, 18, 18); " target="_blank" target="_blank"><b>stream-scaling</b></a>&nbsp;tests how memory speed increases on servers as more cores are brought into play.&nbsp; It's fascinating data, enough of it there to start seeing some real trends.&nbsp; It now works correctly on systems with large amounts of CPU cache, because they have many cores.&nbsp; It was possible before for it to be so aggressive with sizing the test set to avoid cache impact that it used more memory than could be allocated with the current design of the stream code.&nbsp; That's been scaled back.&nbsp; If you have a 48 core server or larger, I could use some more testing of this new code to see if the new way I handle this make sense.<br><br><a href="https://github.com/gregs1104/peg" style="background-color: transparent; color: rgb(18, 18, 18); " target="_blank" target="_blank"><b>peg</b></a>&nbsp;is a script I wrote to make it easier to build PostgreSQL from source, typically for developer work or for temporarily trying a newer version on a production system.&nbsp; It was very easy to get confused with switch between projects and their associated git branches before; the documentation in this area is much improved.&nbsp;&nbsp;<br><br><a href="https://github.com/gregs1104/pgbench-tools" style="background-color: transparent; color: rgb(18, 18, 18); " target="_blank" target="_blank"><b>pgbench-tools</b></a>&nbsp;is my performance testing workhouse, allowing you to queue up days worth of benchmark mark and then have enough analysis tools available to make sense of it.&nbsp; The program now tracks the recently introduced pg_stat_bgwriter.buffers_backend_fsync parameter if you have a version that supports it (currently only a recent soure build--which brings us back to why peg is useful).&nbsp; You can also tell it to run each test for a fixed amount of time, which makes testing at wildly varying client/size values far easier.<br><br>As far as what you can do with pgbench-tools...as of today I am now<b>&nbsp;<a href="http://www.2ndquadrant.us/pgbench-results/index.htm" style="background-color: transparent; color: rgb(18, 18, 18); " target="_blank" target="_blank">sharing the benchmarking tests</a></b>&nbsp;I am doing on PostgreSQL 9.1 on the most powerful server I have unlimited use of.&nbsp; 8 cores, 16GB RAM, 3 disk RAID-0 database drives, 1 disk WAL volume, Areca Battery-backed cache.&nbsp; You can see the results.&nbsp; Runs are organized into test sets, each of which represents some sort of change to configuration.&nbsp; For example, #1 in this data is only running SELECT, #2 is running TPC-B-like but with 8GB of RAM and ealrier code, while the hot stuff is #3, running TPC-B with 16GB of RAM and code that tracks buffers_backend_fsyncs.<br><br>There are several patches in the PostgreSQL 9.1 queue related to performance in the areas highlighted by these reults--that Linux can have extremely high worst-case latency on write-heavy database loads.&nbsp; A good averagish example is&nbsp;<a href="http://www.2ndquadrant.us/pgbench-results/215/index.html" style="background-color: transparent; color: rgb(18, 18, 18); " target="_blank" target="_blank"><b>test 215</b></a>:&nbsp; scale of 1000, 32 clients, 365 TPS.,&nbsp; But the worst-case latency is 43 seconds, and you can see the dead spots in the TPS graph.&nbsp; That's just terrible, and there are a few concepts floating around for how to do just that.<br><br>If anyone reading this has a powerful server available for a few weeks to run tests like this on, I'd be happy to help you replicate this environment and see what kind of results you see.&nbsp; The only magic I've got is some practice at how to set the scaling and client loads so you don't loose a lot of time to unproductive tests.&nbsp; The rest of my process is all free and documented.&nbsp;&nbsp;<br></div></div><div class="asset-footer" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; "><div class="entry-categories" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; "><br></div></div></span>