4 ms·

The work_mem section is incorrect. The article says the configuration is per worker, but it's actually per operation which requires work_mem... any given worke

The work_mem section is incorrect. The article says the configuration is per worker, but it's actually per operation which requires work_mem... any given worker can have multiple operations running in parallel. So actually getting a good, but safe, value is more sensitive than the article suggests. https://www.postgresql.org/docs/9.5/static/runtime-config-resource.html#GUC-WORK-MEM https://www.postgresql.org/docs/9.5/static/runtime-config-re... work_mem (integer) Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value defaults to four megabytes (4MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.

pgaddict 10y ago
It's not the only section that's incorrect. The effective_cache_size is wrong too, shared_buffers is misleading (e.g. new PostgreSQL versions are not subject to SysV kernel limits), and so on. The recommendation to set low checkpoint_segments during restore is a bit strange. Also, not a single word about checkpoint_timeout (when the configuration should aim for timed checkpoints in the first place). Haven't read further.
- irrational 10y ago
  Do you have a recommendation of a similar type of post that has correct information? I'm just getting into PostgreSQL so I don't know what the best resources are.
  - sbuttgereit 10y ago
    I would say this is my favorite resource. https://www.packtpub.com/big-data-and-business-intelligence/postgresql-90-high-performance https://www.packtpub.com/big-data-and-business-intelligence/... Note that, as published, it's a bit dated, but by and large much still is applicable even today. Once you learn the basics the PostgreSQL docs and release notes can catch you up to what's different.
- omarish 10y ago
  Hey there! Thanks for the feedback. Could you clarify what's incorrect about effective_cache_size? Would love to correct it.
  - pgaddict 10y ago
    Well, I'm a bit drunk at the moment, but I'll try explaining anyway ... The documentation says this about effective_cache_size [https://www.postgresql.org/docs/9.5/static/runtime-config-query.html https://www.postgresql.org/docs/9.5/static/runtime-config-qu...]: Sets the planner's assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. The important part here is "to a single query". Essentially when computing the cost of an index scan, we have to ask what fraction of blocks (accessed randomly) will be served from a cache (either shared buffers or page cache), and how many will have to access storage. And that depends on how much RAM will be available for a single query, or rather per index scan. Which is why the statement that effective_cache_size is "the total amount of memory you think Postgres will be able to use" is misleading, as it often leads people to se set effective_cache_size to ~75% of RAM, while in fact they should set it to (75% of RAM)/N where N is the number of concurrently running queries. Actually, I seem to remember that it's actually "per index scan", but that would only make the difference even more significant. Edit: Nope, I've checked the code and it's actually per query.
    - anarazel 10y ago
      > Which is why the statement that effective_cache_size is "the total amount of memory you think Postgres will be able to use" is misleading, as it often leads people to se set effective_cache_size to ~75% of RAM, while in fact they should set it to (75% of RAM)/N where N is the number of concurrently running queries. Meh. From a practical perspective you usually have inter-backend caching effects. And in my experience a too small e_c_s is much more likely to hurt than a too high one.
      - 10y ago
omarish 10y ago
Great catch. I actually wasn't aware of that. Thanks for the correction!

pgaddict 10y ago

It's not the only section that's incorrect. The effective_cache_size is wrong too, shared_buffers is misleading (e.g. new PostgreSQL versions are not subject to SysV kernel limits), and so on. The recommendation to set low checkpoint_segments during restore is a bit strange. Also, not a single word about checkpoint_timeout (when the configuration should aim for timed checkpoints in the first place). Haven't read further.

irrational 10y ago
Do you have a recommendation of a similar type of post that has correct information? I'm just getting into PostgreSQL so I don't know what the best resources are.
- sbuttgereit 10y ago
  I would say this is my favorite resource. https://www.packtpub.com/big-data-and-business-intelligence/postgresql-90-high-performance https://www.packtpub.com/big-data-and-business-intelligence/... Note that, as published, it's a bit dated, but by and large much still is applicable even today. Once you learn the basics the PostgreSQL docs and release notes can catch you up to what's different.
omarish 10y ago
Hey there! Thanks for the feedback. Could you clarify what's incorrect about effective_cache_size? Would love to correct it.
- pgaddict 10y ago
  Well, I'm a bit drunk at the moment, but I'll try explaining anyway ... The documentation says this about effective_cache_size [https://www.postgresql.org/docs/9.5/static/runtime-config-query.html https://www.postgresql.org/docs/9.5/static/runtime-config-qu...]: Sets the planner's assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. The important part here is "to a single query". Essentially when computing the cost of an index scan, we have to ask what fraction of blocks (accessed randomly) will be served from a cache (either shared buffers or page cache), and how many will have to access storage. And that depends on how much RAM will be available for a single query, or rather per index scan. Which is why the statement that effective_cache_size is "the total amount of memory you think Postgres will be able to use" is misleading, as it often leads people to se set effective_cache_size to ~75% of RAM, while in fact they should set it to (75% of RAM)/N where N is the number of concurrently running queries. Actually, I seem to remember that it's actually "per index scan", but that would only make the difference even more significant. Edit: Nope, I've checked the code and it's actually per query.
  - anarazel 10y ago
    > Which is why the statement that effective_cache_size is "the total amount of memory you think Postgres will be able to use" is misleading, as it often leads people to se set effective_cache_size to ~75% of RAM, while in fact they should set it to (75% of RAM)/N where N is the number of concurrently running queries. Meh. From a practical perspective you usually have inter-backend caching effects. And in my experience a too small e_c_s is much more likely to hurt than a too high one.
    - 10y ago

omarish 10y ago

Great catch. I actually wasn't aware of that. Thanks for the correction!