Search code examples
postgresqlperformancememorymemory-managementpostgresql-performance

is a reduction in free disk space a good overall indicator of a `work_mem` setting that is too low?

As I understand it (after a fair amount of searching online)...

1- If a component of a query (sort, join, etc.) uses more RAM/memory than my work_mem setting or the total memory used by all current operations on the server exceeds available OS memory, the query will start writing to disk.

Is that true?

2- Postgres (and many other good DB engines) use memory to cache a lot so queries go faster; therefore, the server should indicate low free memory even if the server isn't really starved for memory. So low free memory doesn't really indicate anything other than a good DB engine and healthy utilization.

Is that true?

3- If both #1 and #2 above are true, holding everything else content, if I want a board indicator of a work_mem setting that is too low or not enough overall OS memory, I should look to see if the server free disk space is going down?

Am I thinking about this correctly?

links:

https://www.postgresql.org/docs/current/static/runtime-config-resource.html

http://patshaughnessy.net/2016/1/22/is-your-postgres-query-starved-for-memory

https://www.enterprisedb.com/monitor-cpu-and-memory-percentage-used-each-process-postgresqlppas-9

https://dba.stackexchange.com/questions/18484/tuning-postgresql-for-large-amounts-of-ram

I know I can set log_temp_files and look at individual temp files to tune the work_mem setting, but I wanted an overall gauge I could use to determine if possibly work_mem is too low before I start digging around looking at temp file sizes that exceed my work_mem setting.

I have PostgreSQL 10.

Solution

  • Processing a query takes a number of steps:

    1. generate (all)possible plans
    2. estimate the cost of execution of these plans (in terms of resources: disk I/O,buffers,memory,CPU), based on tuning constants and statistics.
    3. pick the "optimal" plan , based on tuning constants
    4. execute the chosen plan.

    In most cases, a plan that is expected (step2) to need more work_mem than your work_mem setting will not be chosen in step3. (because "spilling to disk" is considered very expensive) Once step4 detects that it is needing more work_mem, its only choice is to spill to disk. Shit happens... At least this doesn't rely on the OS's page-swapping the the overcommitted memory.)

    The rules are very simple:

    • hash-joins are often optimal but will cost memory
    • don't try to use more memory than you have
    • if there is a difference between expected(step2) and observed(step4) memory, your statistics are wrong. You will be punished by spill-to-disk.
    • a lack of usable indexes will cause hash joins or seqscans.
    • sorting uses work_mem, too. The mechanism is similar :bad estimates yield bad plans.
    • CTE's are often/allways(?) materialized. This will splill to disk once your bufferspace overflows.
    • CTE's don't have statistics, and don't have indices.

    A few guidelines/advice:

    • use a correct data model (and don't denormalize)
    • use the correct PK/FK's and secundary indices.
    • run ANALYZE the_table_name; to gather fresh statistics after huge modifications to the table's structure or data.

    Monitoring:

    • check the Postgres logfile
    • check the query plan, compare observed <--> expected
    • monitor the system resource usage (on Linux: via top/vmstat/iostat)