[UPHPU] MySQL Locking Question
uphpu at sollis.net
Tue Aug 7 14:04:11 MDT 2007
The process actually only clears out the sessions on "garbage collection"
which is still occuring too frequently. I like your suggestion of having
the job run once or twice a day, and remove it from the garbage collection
On 8/7/07, Lonnie Olson <olsonl at fungus.sosstaffing.com> wrote:
> On Tue, 2007-08-07 at 11:44 -0600, Chad Sollis wrote:
> > SELECT session_id, session_array FROM session_table WHERE
> > session_key='keygoeshere' limit 0,1
> > DELETE FROM session_table WHERE
> > (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(session_date_created))>86400
> > Can I reconstruct these queries to not lock the table. I understand
> > creating/updating the table it needs to lock, but I am under the
> > that for select and delete it does not need to lock?
> I suggest taking a different approach. Instead of running the DELETE
> query at every single session lookup, just add the date expiration to
> the WHERE clause in the SELECT. This way the table will have old items,
> but you can clean them out as maintenance once every so often.
> Write locks caused by the DELETE only occur during maintenance.
> Increased performance.
> Lookups for sessions will be slightly slower, but can be helped by an
> index on session_date_created.
> Requires separate maintenance to be performed, but could be automated.
chad at sollis.net
More information about the UPHPU