{"id":9962,"date":"2017-04-25T07:39:06","date_gmt":"2017-04-25T05:39:06","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/"},"modified":"2017-04-25T07:39:06","modified_gmt":"2017-04-25T05:39:06","slug":"does-the-wal-segment-size-matter-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/","title":{"rendered":"Does the wal segment size matter in PostgreSQL?"},"content":{"rendered":"

In PostgreSQL you configure the size of the wal (write ahead log) segments when you compile from source. If you use an installer or if you use the packages provided by your OS distribution the size of the wal segments is usually 16MB<\/a>. Although 16MB seems very low you don’t need to worry about that in most of the cases, it just works fine. However there are cases where you might want to adjust this, e.g. when you have an application that generates thousands of transactions in a very short time and therefore forces PostgreSQL to generate huge amounts of wal segments. In this post we’ll look at a specific case: Usually you want to archive the wal segments for being able to do point in time recovery in case your severs crashes for some reason. Does the size of the wal segments matter for archiving?<\/p>\n

<\/p>\n

Archiving of wal segments in PostgreSQL is done by specifying an archive_command<\/a>. Whatever you put there will be executed by PostgreSQL once a new wal segment is completed. Usually you’ll find something like this in archive_command (from the documentation):<\/p>\n

\narchive_command = 'test ! -f \/mnt\/server\/archivedir\/%f && cp %p \/mnt\/server\/archivedir\/%f'  # Unix\narchive_command = 'copy \"%p\" \"C:\\server\\archivedir\\%f\"'  # Windows\n<\/pre>\n
Or something like this:<\/p>\n
\narchive_command = 'rsync -a %p postgres@[SOME_OTHER_HOST]:\/path\/to\/wal_archive\/%f'\n<\/pre>\n
Or:<\/p>\n
\narchive_command ='scp %p postgres@[SOME_OTHER_HOST]:\/path\/to\/wal_archive\/%f'\n<\/pre>\n
Lets test how the size of wal segments impact the three ways of archiving outlined above. To begin with lets create 100 files each 16MB (the same as the default wal segment size in PostgreSQL) and 25 files 64MB each:<\/p>\n
\nrm -rf \/var\/tmp\/test16mb\nmkdir \/var\/tmp\/test16mb\nfor i in {1..100}; do\n   dd if=\/dev\/zero of=\/var\/tmp\/test16mb\/${i} bs=1M count=16\ndone\nls -la \/var\/tmp\/test16mb\nrm -rf \/var\/tmp\/test64mb\nmkdir \/var\/tmp\/test64mb\nfor i in {1..25}; do\n   dd if=\/dev\/zero of=\/var\/tmp\/test64mb\/${i} bs=1M count=64\ndone\nls -la \/var\/tmp\/test64mb\ndu -sh \/var\/tmp\/test16mb\ndu -sh \/var\/tmp\/test64mb\n<\/pre>\n
This will give us a total size of 1.6GB for each of the wal sizes (16MB and 64MB). Lets start by testing the “cp” way:<\/p>\n
\necho 3 > \/proc\/sys\/vm\/drop_caches\nmkdir -p \/var\/tmp\/target\nrm -rf \/var\/tmp\/target\/*\ntime for i in `ls \/var\/tmp\/test16mb`; do\n    cp \/var\/tmp\/test16mb\/${i} \/var\/tmp\/target\/\ndone\n<\/pre>\n
My result (on a VM local on my notebook):<\/p>\n
\nreal\t0m17.444s\nuser\t0m0.275s\nsys\t0m8.569s\n<\/pre>\n
The same test for the 64MB files:<\/p>\n
\necho 3 > \/proc\/sys\/vm\/drop_caches\nmkdir -p \/var\/tmp\/target\nrm -rf \/var\/tmp\/target\/*\ntime for i in `ls \/var\/tmp\/test64mb`; do\n    cp \/var\/tmp\/test16mb\/${i} \/var\/tmp\/target\/\ndone\n<\/pre>\n
It is almost 3 times as fast to copy the large files than to copy the smaller files:<\/p>\n
\nreal\t0m5.365s\nuser\t0m0.065s\nsys\t0m1.835s\n<\/pre>\n
Of course, for production systems, you would copy the files not locally but rather to e.g. NFS mount and then the numbers will change.<\/p>\n
What are the numbers for scp? For the smaller files:<\/p>\n
\necho 3 > \/proc\/sys\/vm\/drop_caches\nmkdir -p \/var\/tmp\/target\nrm -rf \/var\/tmp\/target\/*\ntime for i in `ls \/var\/tmp\/test16mb`; do\n    scp \/var\/tmp\/test16mb\/${i} root@localhost:\/var\/tmp\/target\/\ndone\n<\/pre>\n
The result:<\/p>\n
\nreal\t2m51.708s\nuser\t0m14.136s\nsys\t0m35.292s\n<\/pre>\n
Quite a huge overhead. What is the result with the 64MB files?:<\/p>\n
\necho 3 > \/proc\/sys\/vm\/drop_caches\nmkdir -p \/var\/tmp\/target\nrm -rf \/var\/tmp\/target\/*\ntime for i in `ls \/var\/tmp\/test64mb`; do\n    scp \/var\/tmp\/test64mb\/${i} root@localhost:\/var\/tmp\/target\/\ndone\n<\/pre>\n
Approximately double as fast:<\/p>\n
\nreal\t1m23.326s\nuser\t0m10.353s\nsys\t0m30.814s\n<\/pre>\n
And finally rsync, for the smaller files:<\/p>\n
\necho 3 > \/proc\/sys\/vm\/drop_caches\nmkdir -p \/var\/tmp\/target\nrm -rf \/var\/tmp\/target\/*\ntime for i in `ls \/var\/tmp\/test16mb`; do\n    rsync -a \/var\/tmp\/test16mb\/${i} root@localhost:\/var\/tmp\/target\/${i}\ndone\n<\/pre>\n
The result:<\/p>\n
\nreal\t0m51.624s\nuser\t0m4.488s\nsys\t0m10.247s\n<\/pre>\n
For the larger ones:<\/p>\n
\necho 3 > \/proc\/sys\/vm\/drop_caches\nmkdir -p \/var\/tmp\/target\nrm -rf \/var\/tmp\/target\/*\ntime for i in `ls \/var\/tmp\/test64mb`; do\n    rsync -a \/var\/tmp\/test64mb\/${i} root@localhost:\/var\/tmp\/target\/${i}\ndone\n<\/pre>\n
The result:<\/p>\n
\nreal\t0m34.342s\nuser\t0m3.623s\nsys\t0m9.685s\n<\/pre>\n
Conclusion: When you have applications with high transaction rates it can make sense to increase the default wal segment size as archiving will usually be much faster when you use bigger segments. Of course you’ll need to test this on your specific hardware and for your specific workload. In a next post we’ll look at how bigger segments affect performance of PostgreSQL.<\/p>\n","protected":false},"excerpt":{"rendered":"
In PostgreSQL you configure the size of the wal (write ahead log) segments when you compile from source. If you use an installer or if you use the packages provided by your OS distribution the size of the wal segments is usually 16MB. Although 16MB seems very low you don’t need to worry about that […]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-9962","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nDoes the wal segment size matter in PostgreSQL? - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Does the wal segment size matter in PostgreSQL?\" \/>\n<meta property=\"og:description\" content=\"In PostgreSQL you configure the size of the wal (write ahead log) segments when you compile from source. If you use an installer or if you use the packages provided by your OS distribution the size of the wal segments is usually 16MB. Although 16MB seems very low you don’t need to worry about that […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-04-25T05:39:06+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/does-the-wal-segment-size-matter-in-postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/does-the-wal-segment-size-matter-in-postgresql\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Does the wal segment size matter in PostgreSQL?\",\"datePublished\":\"2017-04-25T05:39:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/does-the-wal-segment-size-matter-in-postgresql\\\/\"},\"wordCount\":432,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/does-the-wal-segment-size-matter-in-postgresql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/does-the-wal-segment-size-matter-in-postgresql\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/does-the-wal-segment-size-matter-in-postgresql\\\/\",\"name\":\"Does the wal segment size matter in PostgreSQL? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2017-04-25T05:39:06+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/does-the-wal-segment-size-matter-in-postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/does-the-wal-segment-size-matter-in-postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/does-the-wal-segment-size-matter-in-postgresql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Does the wal segment size matter in PostgreSQL?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Does the wal segment size matter in PostgreSQL? - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Does the wal segment size matter in PostgreSQL?","og_description":"In PostgreSQL you configure the size of the wal (write ahead log) segments when you compile from source. If you use an installer or if you use the packages provided by your OS distribution the size of the wal segments is usually 16MB. Although 16MB seems very low you don’t need to worry about that […]","og_url":"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2017-04-25T05:39:06+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Does the wal segment size matter in PostgreSQL?","datePublished":"2017-04-25T05:39:06+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/"},"wordCount":432,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/","name":"Does the wal segment size matter in PostgreSQL? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-04-25T05:39:06+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/does-the-wal-segment-size-matter-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Does the wal segment size matter in PostgreSQL?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9962","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9962"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9962\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9962"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9962"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9962"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9962"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}