Postgres insert slow after snapshot restore but not after restart

user_12309386 0
Viewed 34

My setup

  • Postgres 11 running on an AWS EC2 t4g.xlarge instance (4 vCPU, 16GB) running Amazon Linux.
  • Set up to take a nightly disk snapshot (my workload doesn't require high reliability).
  • Database has table xtc_table_1 with ~6.3 million rows, about 3.2GB.

Scenario

To test some new data processing code, I created a new test AWS instance from the nightly snapshot of my production instance. I create a new UNLOGGED table, and populate it with INSERT INTO holding_table_1 SELECT * FROM xtc_table_1;

It takes around 2 min 24 sec for the CREATE statement to execute.

I truncate holding_table_1 and run the CREATE statement again, and it completes in 30 sec. The ~30 second timing is consistent for successive truncates and creates of the table.

I think this may be because of some caching of data. I tried restarting Postgres service, then rebooting the AWS instance (after stopping postgres with sudo service postgresql stop), then stopping and starting the AWS instance. However, it's still ~30 sec to create the table.

If I rebuild a new instance from the snapshot, the first time I run the CREATE statement it's back to the ~2m+ time.

Similar behavior for other tables xtc_table_2, xtc_table_3.

Hypothesis

After researching and finding this answer, I wonder if what's happening is that the disk snapshot contains some WAL data that is being replayed the first time I do anything with xtc_table_n. And that subsequently, because Postgres was shut down "nicely" there is no WAL to playback.

Does this sound plausible?

I don't know enough about Postgres internals to be sure. I would have imagined that any WAL playback would happen on starting up postgres, but maybe it happens at the individual table level the first time a table is touched?

Knowing the reason is more than just theoretical; I'm using the test instance to do some tuning on some processing code, and need to be confident in having a consistent baseline to measure from.

Let me know if more information is needed about my setup or what I'm doing.

amazon-ec2 postgresql
1 Answers

@jellycsc's suggestion was correct; adding more info here in case it's helpful to anyone else.

The problem I was encountering was not a postgres issue at all, but because of the way AWS handles volumes and snapshots.
From this page:

For volumes that were created from snapshots, the storage blocks must be pulled down from Amazon S3 and written to the volume before you can access them. This preliminary action takes time and can cause a significant increase in the latency of I/O operations the first time each block is accessed. Volume performance is achieved after all blocks have been downloaded and written to the volume.

I used the fio utility as described in the linked AWS page to initialize the restored volume, and first-time performance was consistent with subsequent query times.

user_123093860
Related