Speeding up pgbench using COPY FREEZE

 

Photo by Florian Kurz

 What is pgbench

Pgbench is a simple tool to perform performance tests on PostgreSQL. Since pgbench comes with PostgreSQL distribution and it's easy to use, many users and developers regularly use pgbench.  The original version of pgbench was written by me in 1999 and then it was contributed to PostgreSQL. Since then many people have enhanced the tool.

Loading data takes long time

Pgbench has a feature to load initial data into the benchmark tables (-i or --initialize option). By specifying scaling factor (-s option) users can create data as large as he/she wants. For example scaling factor 100 will create 10 millions rows. When I execute pgbench command with the scaling factor 100, it took 70 seconds on my laptop. You can see how long  each part of the process took:

 done in 70.78 s (drop tables 0.21 s, create tables 0.02 s, client-side generate 12.42 s, vacuum 51.11 s, primary keys 7.02 s).

  As you can see, most of the time was spent in vacuum: 51.11 seconds.

 After some testing, I realized these numbers  were too specific. I mean, I did the test under the environment where PostgreSQL servers are configured as streaming replication system on my laptop. When primary server does lots of write while pgbench is running, WAL is sent to standby server and it does lots of write too. This means under the environment the effect of write is amplified. 

After doing pgbench -i without the streaming replication, I got this number with standard  pgbench:

done in 16.20 s (drop tables 0.24 s, create tables 0.01 s, client-side generate 10.05 s, vacuum 2.13 s, primary keys 3.77 s).

So now standard pgbench runs much faster.  So I have changed some words and graphs according to the result in the rest of this article.

 Why vacuum is slow?

Ok, why we need vacuum then? One of the reasons is to create statistic data for tables. Without the statistic data, PostgreSQL's planner cannot create good plans to execute queries. But actually this does not take so long time because for this task vacuum just reads the table and adds small data to certain system catalogs. But vacuum does other tasks: updating hint bits in each tuple. Hint bits are a"cache" of transactions status that is stored in pg_xact and pg_subtrans. See wiki  for details about hint bits.  Unfortunately this results in whole rewriting of the table, which generates massive I/O. This is the reason why vacuum is slow.

COPY FREEZE

Is there anyway to avoid the slowness of vacuum? COPY FREEZE is one of the answer to this. COPY is used to populate the main benchmark table "pgbench_accounts". By adding "FREEZE" option to COPY, COPY sets the hint bits while processing and subsequent vacuum does not need to change them. Thus it will not generate massive I/O.

COPY FREEZE will be enhanced in PostgreSQL 14

Unfortunately  pre PostgreSQL 14's COPY FREEZE does not do the all the necessary tasks:

1.  Update hint bits in each tuple
2.  Update visibility map (bit map indicating whether corresponding table tuple in a page is visible to all transactions)
   
3.  Update freeze map (bit map indicating whether corresponding table tuple in a page is all frozen)
4.  Update PD_ALL_VISIBLE flag in each page

It only does 1, but does not do 2, 3 and 4. Especially 4 is important because if COPY does not do it, subsequent vacuum will update the flag in a page and it writes whole pages of the table, which in turn slows down vacuum. 2 and 3 are not necessary to prevent the massive write in vacuum but it is convenient for SELECT which wants to do index only scan. Index only scan requires the visibility map to be set. With a fix patch PostgreSQL 14's COPY FREEZE does all 1 to 4.

PostgreSQL 14's COPY FREEZE helps pgbench

Now that COPY FREEZE does the right thing, now is the time to use COPY FREEZE in pgbench -i. I proposed a small patch for pgbench. Here is a graph to compare the time spent in "pgbench -i" with scaling factor 100 on my Ubuntu 18 Laptop.

With the patch total time drops from 16.20 seconds (left) to 14.30 seconds (right), that is 11.7% faster. This is mainly because vacuum (green part) takes only 0.25 seconds while unpatched pgbench takes 2.13 seconds, which is 8 times slower.

Conclusion

By using enhanced COPY FREEZE in PostgreSQL 14, performance of pgbench -i  is significantly enhanced. The patch is in commit festa and I expect it would become a part of PostgreSQL 15, which is supposed to be released in the third quarter of 2022. Of course you could use the enhanced COPY FREEZE for any purposes other than pgbench once PostgreSQL 14 is released.