Re: using a postgres table as a multi-writer multi-updater queue
От | Steve Petrie, P.Eng. |
---|---|
Тема | Re: using a postgres table as a multi-writer multi-updater queue |
Дата | |
Msg-id | DA25EAB207CD4B2FA6CF2BD58DBC40A4@Dell обсуждение исходный текст |
Ответ на | using a postgres table as a multi-writer multi-updater queue (Chris Withers <chris@simplistix.co.uk>) |
Список | pgsql-general |
Thanks to George for the helpful comments. My remarks are below. "George Neuner" <gneuner2@comcast.net> wrote in message news:gvad5bllba9slstdhkn6ql2jbplgd78p98@4ax.com... > On Wed, 25 Nov 2015 23:39:39 -0500, "Steve Petrie, P.Eng." > <apetrie@aspetrie.net> wrote: > > > >>My plan was always, to avoid eventual exhaustion of the SERIAL >>sequence number integer value series, by swapping in during the >>periodic app shutdown, a freshly truncated postgres >> <eto_sql_tb_session_www> table. > > Is there a logical problem with letting the sequence wrap around? > I can't think of any reason why letting the sequence wrap would be a logical problem, but for operational simplicity I prefer to reset the sequence number back to one, during each brief daily postgres server shutdown window. I don't relish the challenge of mentally grappling with gigantic session row sequence numbers, when I'm investigating some future operational anomaly. > > I'm not sure I understand the reason for "fuzzy" deletion. There are > a number of timestamps in your data ... is it not possible to delete > deterministically based on one of them? > You are correct. The timestamps are there in the session row, and the PHP app's session row deletion process does use them to select rows for deletion. There is also a decrementing integer "hit count" limit column (presently initialized == 25) that kills a session when its "budget" of HTTP requests is used up. 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. 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? > > Hope this helps, > George > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Steve Petrie, P.Eng. ITS-ETO Consortium Oakville, Ontario, Canada apetrie@aspetrie.net
В списке pgsql-general по дате отправления:
Предыдущее
От: "Steve Petrie, P.Eng."Дата:
Сообщение: Re: using a postgres table as a multi-writer multi-updater queue
Следующее
От: "Steve Petrie, P.Eng."Дата:
Сообщение: Re: using a postgres table as a multi-writer multi-updater queue