problems with set_config, work_mem, maintenance_work_mem, and sorting

From: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: problems with set_config, work_mem, maintenance_work_mem, and sorting
Date: 2012-02-28 19:16:27
Message-ID: CAKuK5J22ZTaVrVSPUerPA2_Fe-+udtOFWxSoOjFi9r9=5gyZgw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2012-02-28 19:28:03 Re: problems with set_config, work_mem, maintenance_work_mem, and sorting
Previous Message David Kerr 2012-02-28 17:06:08 Re: Very long deletion time on a 200 GB database