* Bill Moran (wmoran@xxxxxxxxxxxxxxxxxxxxxxx) wrote: > What I'm fuzzy on is how to discretely know when I'm overflowing > work_mem? Obviously, if work_mem is exhausted by a particular > query, temp files will be created and performance will begin to suck, I don't believe this is necessairly *always* the case. There are instances in PostgreSQL where it will just continue to allocate memory beyond the work_mem setting. This is usually due to poor statistics (you changed the data in the table dramatically and havn't run analyze, or you never ran analyze on the table at all, or the statistics gathering values are set too low to capture enough information about the data, etc). It would nice if it was possible to have this detected and logged, or similar. Additionally, work_mem isn't actually a per-query thing, aiui, it's more like a per-node in the planner thing. That is to say that if you have multiple sorts going on, or a sort and a hash, that *both* of those expect to be able to use up to work_mem amount of memory. Also, another point you might want to consider how to handle is that work_mem has no bearing on libpq and I don't recall there being a way to constrain libpq's memory usage. This has been an issue for me just today when a forgot a couple parameters to a join which caused a cartesean product result and ended up running the box out of memory. Sure, it's my fault, and unlikely to happen in an application, but it still sucks. :) It also managed to run quickly enough that I didn't notice what was happening. :/ Of course, the server side didn't need much memory at all to generate that result. Also, libpq stores everything in *it's* memory before passing it to the client. An example scenario of this being kind of an issue is psql, you need double the memory size of a given result because the result is first completely grabbed and stored in libpq and then sent to your pager (eg: less) which then sucks it all into memory again. In applications (and I guess psql, though I never think of it, and it'd be nice to have as a configurable option if it isn't already...) you can use cursors to limit the amount of memory libpq uses. As these are new things (both the temp file creation logging and the work_mem overflow detection, I believe), this discussion is probably more appropriate for -hackers. > That leads to my other question. Assuming I've got lots of > connections (which I do), how can I determine if work_mem is too > high? Do server processes allocated it even if they don't actually > use it? Is the only way to find out to reduce it and see when it > starts to be a problem? If so, that leads back to my first question: > how can I be sure whether temp files were created or not? Yeah, look for swappiness... It'd be nice to be able to get memory statistics on queries which have been run though... > My goal is to set work_mem as small as is possible for the most > common queries, then force the developers to use "set work_mem to x" > to adjust it for big queries. Sounds like an excellent plan. Be careful though, work_mem settings can affect query plans and they may discover that if set high enough the planner will, for example, do a hashjoin which is much faster than sorting and merge-joining, but takes alot of memory... They may say "hey, I like it being fast" but not consider what happens when alot of those queries run at once.. Thanks! Stephen
Attachment:
signature.asc
Description: Digital signature
