work_mem is maybe the most confounding setting inside Postgres. work_mem is a design inside Postgres that decides how much memory can be utilized during specific tasks. At its surface, the work_mem setting appears to be basic: all things considered, work_mem simply indicates how much memory accessible to be utilized by interior sort activities and hash tables prior to composing information to plate. But, leaving work_mem unconfigured can welcome on a large group of issues. What maybe is seriously alarming, however, is the point at which you get an out of memory mistake on your data set and you bounce in to tune work_mem, just for it to act in an un-natural way.
Setting your default memory
The work_mem esteem defaults to 4MB in Postgres, and that is probable a piece low. This implies that per Postgres action (each join, a few sorts, and so on) can consume 4MB before it begins spilling to plate. At the point when Postgres begins composing temp records to plate, clearly things will be a lot more slow than in memory. You can see whether you’re spilling to plate via looking for brief record inside your PostgreSQL logs when you have log_temp_files empowered. On the off chance that you see transitory document, it tends to merit expanding your work_mem.
It’s not just about the memory for inquiries
We should utilize a guide to investigate how to contemplate streamlining your work_mem setting.
Let’s assume you have a specific measure of memory, say 10 GB. In the event that you have 100 running Postgres questions, and every one of those questions has a 10 MB association above, then, at that point, 100*10 MB (1 GB) of memory is taken up by the 100 associations — which leaves you with 9GB of memory.
With 9 GB of memory remaining, say you give 90 MB to work_mem for the 100 running inquiries. Yet, stand by, it is quite difficult. Why? Indeed, work_mem isn’t set on a for every inquiry premise, rather, it’s set in view of the quantity of sort/hash tasks. Be that as it may, what number of shorts/hashes and joins happen per inquiry? Now that is a confounded inquiry. A convoluted inquiry made more confounded on the off chance that you have different cycles that likewise consume memory, for example, autovacuum.
We should save a little for support errands then, at that point, and for vacuum and we’ll be OK then the same length as we restrict our associations right? Not so quick my companion.
Postgres presently has equal inquiries. In the event that you’re involving Citus for parallelism you’ve had this for some time, however presently you have it on single hub Postgres too. This means on a solitary question you can have various cycles running and performing work. This can bring about a few critical enhancements in speed of questions, yet every one of those running cycles can consume the predefined measure of work_mem. In the model above, with a 64 MB default and 100 associations, we could now have every one of those running a question for each center consuming definitely more memory than we expected.
More work_mem, more issues
So we can see that getting it wonderful is somewhat more work than ideal. We should return a bit and attempt this all the more basically… we can begin work_mem little at say 16 MB and continuously increment work_mem when we see transitory document. In any case, why not give each question as much memory as it could like? Assuming we were to simply say each cycle could consume up to 1 GB of memory what’s the mischief? Well the other outrageous out there is that inquiries start consuming a lot of memory, more than you have accessible on your crate. At the point when that happens you get 100 questions that have 5 different sort tasks and a couple of hash participates in them it’s as a matter of fact entirely conceivable to debilitate all the memory accessible to your data set.
At the point when you consume more memory than is accessible on your machine you can begin to see out of out of memory blunders inside your Postgres logs, or in more awful cases the OOM executioner can begin to kill running cycles to arbitrarily let loose memory. An out of memory blunder in Postgres essentially mistakes on the question you’re running, where as the OOM executioner in linux starts killing running cycles which at times could try and incorporate Postgres itself.
At the point when you see an out of memory blunder you either need to expand the general RAM on the actual machine by moving up to a bigger example OR you need to diminish how much memory that work_mem utilizes. Indeed, you read that right: out-of-memory it’s smarter to diminish work_mem rather than increment since that is how much memory that can be consumed by each cycle and an excessive number of tasks are utilizing up to that much memory.