On Sun, 29 Nov 2015 05:02:58 -0500, "Steve Petrie, P.Eng." <apetrie@xxxxxxxxxxxx> wrote: >I should have clarified, that I use the the term "fuzzy" to refer to the >probability mechanism, that hooks a small burst of session row deletion >activity, to each one of a randomly-selected portion (presently 1/5) of the >HTTP requests that cause a new session row to be INSERTed into the session >table. > >This means that on average, only every 5th HTTP request that creates a new >session row, will also incur the session deletion workload. When the session >row deletion process occurs, its (aggressive) limit for deletion workload is >2X as many expired rows as needed on average, to keep up with the rate of >session row creation (so the 2X DELETE limit presently == 10 rows). > >The idea is to make the process of DELETing expired session rows, >automatically scale its activity, to closely and aggressively match the rate >of session row creation. There's nothing really wrong with that, but I wouldn't do it that way ... I would bulk delete old records from a separate scheduled task. Another way to do it would be to have each new session delete exactly one old session. 1:1 scales perfectly and spreads the delete load evenly across all users. Not that deleting a handful of records is a lengthy process, but it seems unfair to burden some users with it but not others. I would burden (or not) everyone equally. >A heavy burst of new sessions being created will >work proportionately more aggressively to DELETE expired session rows. This >way, the (HTTP request-driven) PHP app will be self-tuning its own session >table space recycling. And there is no process (e.g. cron-driven), >external to the PHP app itself, that is doing session row deletion. > >Based on what I've learned from this forum (but before I have studied >AUTOVACUUM in any detail) my thinking is to include an AUTOVACUUM command >(outside of any SQL transaction block) in the HTTP request-driven PHP app, >immediately following any time the PHP app completes a session row DELETE >command. > >Or maybe the AUTOVACUUM request should occur less frequently? Reducing the frequency will result in a larger table space on disk. Insertions are made at the end of the table so the table keeps growing in size regardless of deletions until (some kind of) vacuum is run. Autovacuum doesn't shrink the table space on disk, it merely compacts the table's live data so that any free space is at the end. If you want to tightly control the growth of the table space, you need to run autovacuum _more_ often, not less. George -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
