I happened to be looking in the PostgreSQL logs (8.4.10, x86_64, ScientificLinux 6.1) and noticed that an app was doing some sorting (group by, order by, index creation) that ended up on disk rather than staying in memory. So I enabled trace_sort and restarted the app. What followed confused me. I know that the app is setting the work_mem and maintenance_work_mem to 1GB, at the start of the session, with the following calls: select set_config(work_mem, 1GB, False); select set_config(maintenance_work_mem, 1GB, False); By timestamps, I know that these statements take place before the next log items, generated by PostgreSQL (note: I also log the PID of the backend and all of these are from the same PID): LOG: 00000: begin tuple sort: nkeys = 2, workMem = 1048576, randomAccess = f LOG: 00000: begin tuple sort: nkeys = 1, workMem = 1048576, randomAccess = f LOG: 00000: begin tuple sort: nkeys = 1, workMem = 1048576, randomAccess = f LOG: 00000: begin tuple sort: nkeys = 1, workMem = 1048576, randomAccess = f LOG: 00000: begin tuple sort: nkeys = 2, workMem = 1048576, randomAccess = f ^ these make sense LOG: 00000: begin tuple sort: nkeys = 2, workMem = 131072, randomAccess = f LOG: 00000: begin tuple sort: nkeys = 1, workMem = 131072, randomAccess = f LOG: 00000: begin tuple sort: nkeys = 1, workMem = 131072, randomAccess = f .... ^^ these do not (but 128MB is the globally-configured work_mem value) LOG: 00000: begin index sort: unique = t, workMem = 2097152, randomAccess = f ^ this kinda does (2GB is the globally-configured maintenance_work_mem value) LOG: 00000: begin index sort: unique = f, workMem = 131072, randomAccess = f LOG: 00000: begin tuple sort: nkeys = 2, workMem = 131072, randomAccess = f .. The config shows 128MB for work_mem and 2GB for maintenance_work_mem. Why does PostgreSQL /sometimes/ use the globally-configured values and sometimes use the values that come from the connection? Am I wrong in misunderstanding what 'session' variables are? I thought that session (versus transaction) config items were set for /all/ transactions in a given backend, until changed or until that backend terminates. Is that not so? If I reconfigure the app to call out to set_config(item, value, True) after each 'BEGIN' statement then workMem seems to be correct (at least more of the time -- the process takes some time to run and I haven't done an exhaustive check as yet). -- Jon -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
