search

zloster / FrameworkBenchmarks

Source code for the framework benchmarking project
http://www.techempower.com/benchmarks/
Other
0 stars 0 forks source link

PostgreSQL tests and table bloat due to updates #28

Closed zloster closed 7 years ago

zloster commented 7 years ago

Investigate what is the table bloat because there are many writes to the World/world table. Autovacuum will run around 200 000 000 writes to the table with the default settings Autovacuum is not enabled. Maybe this will impact some of the projects?

Additional information materials:

  1. https://www.joyent.com/blog/manta-postmortem-7-27-2015 - Joyent had a problem with the vacuum operation on the tables
  2. https://blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres.html via the above Joyent post
zloster commented 7 years ago

More information: https://wiki.postgresql.org/wiki/Show_database_bloat https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql - query for tables https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat.sql - query for index the above two are referred from the PostgreSQL Wiki http://blog.ioguix.net/tag/bloat/

The initial test on Vagrant environment shows some small table bloat, but the table is very small - under 1 MiB. But this is after only under 1 million updates. So maybe it will be different with more updates. I'll adjust the config to use 10 minutes for this test case with servlet-postgres-raw and dropwizard-postgres.

zloster commented 7 years ago

After two runs with 10 minute updates for servlet-postgres-raw and dropwizard-postgres the bloat seems to stay consistent - the whole table is around 1MiB. Also note that there is world and World tables which should be kept in mind if doing similar comparisons.