UPDATED August 2023: Tuning shared_buffers in PostgreSQL is a pretty fundamental thing to do when you set up a high-performance server. In most cases, you add more memory to speed things up considerably. Many tests have shown exactly that. However, there are some corner cases which are not common, and which can cause significant performance issues.
Massive use of
One of those corner cases is the massive use of
DROP TABLE. One might argue that
DROP TABLE is a rare thing to do. However, in many applications it is not.
DROP TABLE is used massively by countless applications and can turn out to be a bit of a showstopper.
Why is it an issue? The problem is that during
DROP TABLE all cached blocks are forced out of the cache. The bigger the cache, the bigger the effort.
The following test shows what this can actually mean in real life.
The test script
SET synchronous_commit TO off; BEGIN; CREATE TABLE x(id int); INSERT INTO x VALUES (1); DROP TABLE x; COMMIT;
To make sure that the entire test does not start to be disk-bound, flushing requirements are relaxed. Then a basic table is created and dropped again.
The test is repeated with various settings for
#/bin/sh DB=test for x in '8 MB' '32 MB' '128 MB' '1 GB' '8 GB' do pg_ctl -D /tmp/db -l /dev/null -o "--shared_buffers='$x'" start sleep 1 echo tps for $x psql -c "SHOW shared_buffers" $DB pgbench --file=run.sql -j 1 -c 1 -T 10 $DB 2> /dev/null pg_ctl -D /tmp/db stop sleep 1 done
The test starts a database instance and runs the test shown before for 10 seconds.
The results are really worth a look:
[hs@laura benchmark]$ ./x.sh | grep tps tps for 8 MB tps = 1390.858135 (including connections establishing) tps = 1391.123414 (excluding connections establishing) tps for 32 MB tps = 1341.244741 (including connections establishing) tps = 1341.745728 (excluding connections establishing) tps for 128 MB tps = 1193.329832 (including connections establishing) tps = 1193.796699 (excluding connections establishing) tps for 1 GB tps = 532.483333 (including connections establishing) tps = 532.702853 (excluding connections establishing) tps for 8 GB tps = 104.238781 (including connections establishing) tps = 104.280531 (excluding connections establishing)
As you can see, the number of transactions per seconds drops dramatically down to around 100 TPS – this is 7.5% of the maximum value reached on this test.
The reason for this horrible drop is that during
DROP TABLE, PostgreSQL will clean out
shared_buffers. The trouble is: The larger the
shared_buffers area actually is, the longer this will take. For most applications this is not a real issue. However, if your application is heavily built on creating and dropping tables for whatever reason, this can be a showstopper and kill performance.
Of course, creating and dropping so many temporary tables should not happen, but from experience we have seen that it actually does.
Take note: vacuum truncation is also affected.
To understand how that works, see the update about vacuum truncation in this blog on table bloat.