{"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":29,"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=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\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\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/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":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","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":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"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\/8d08e9bd996a89bd75c0286cbabf3c66"},"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\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_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\/29"}],"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}]}}