|
|
Welcome to the nancy wiki! |
|
|
# More on Database Experiments
|
|
|
|
|
|
The definition of **database experiments** and description of 4 its parts is provided here: https://gitlab.com/postgres-ai-team/nancy#what-is-a-database-experiment
|
|
|
|
|
|
Here we discuss what we usually are interested in when we conduct a database experiment.
|
|
|
|
|
|
## Checkpoints
|
|
|
|
|
|
How many checkpoints happened? How many of them were forced? What was their frequency? How much data was written by the checkpointer process, by bgwriter, by backends? What were the average rates of writing?
|
|
|
|
|
|
- it looks like we covered this, getting the snapshot of `pg_stat_bgwriter` (and resetting statistics, including `pg_stat_reset_shared('bgwriter')`: https://gitlab.com/postgres-ai-team/nancy/blob/master/nancy_run.sh#L1502)
|
|
|
|
|
|
- additionally, we might want to analyze logs, getting actual information from there (the "workload" log is among artifacts already).
|
|
|
|
|
|
## WAL
|
|
|
|
|
|
How many segments were generated? How many per second / minute / hour? How much data (bytes)? How much per second / minute / hour?
|
|
|
|
|
|
:railroad\_track: This is not yet covered!!
|
|
|
|
|
|
# Heap and Index Bloat
|
|
|
|
|
|
How our workload affected heap and index bloat?
|
|
|
|
|
|
:railroad\_track: This is not yet covered!!
|
|
|
|
|
|
## Autovacuum
|
|
|
|
|
|
How many times each table was autovacuumed? What was performance impact of it?
|
|
|
|
|
|
- not covered directly, although we can analyze it from logs (with custom analysis or using existing pgBadger capabilities)
|
|
|
|
|
|
# Query Performance
|
|
|
|
|
|
- pgBadger, or
|
|
|
|
|
|
- [TODO] K*** reports of postgres-checkup |