[Koha-devel] sessions in mysql

[Clay Fouts]

Hello, David. It's not clear to me why Koha (or CGI::Session, more
likely) uses REPLACE INTO for sessions. It's a performance killer.
Each time it's run it splits up into two or four queries, depending on
conditions. Simply executing an UPDATE, then an INSERT on the
condition that zero rows were updated would be faster.

Over the long term it further degrades performance by heavily
fragmenting the table's structure since the 'sessions' table is
altered more frequently than any other. That is why your "SELECT
count(*) FROM sessions" takes so long to run.

However, seeing a slew of REPLACE INTO statements isn't necessarily
indicative of that being the cause of the hang. They're just the
statement that gets executed first by any Koha script, so if something
has locked the database or if other threads are tying up enough
resources to create contention issues those are the statements you're
most likely to see waiting to be executed. It's likely that other
queries are the culprits. The threads at the very top of the
processlist output are the oldest and most suspect.

The "server shutdown in progress" message is what you'll see when the
query gets killed (e.g. via "mysqladmin kill xyz"). There's a cron job
scheduled to run every minute that kills SELECT queries that have been
running longer than 45 seconds or so. This is to help prevent runaway
queries and keep resources free for other tasks.

Regards,
Clay


On Thu, May 14, 2009 at 2:27 PM, David Schuster <dschust1 at tx.rr.com> wrote:
>
> So we have been having a few issues with locking up servers so I've been
> watching show processlist when this happens and it seems that we have a lot
> of
>
> REPLACE INTO sessions(id, a-session) etc.. lines when this happens and when
> they magically go away everything is running smoothly again.
>
> So what are these "sessions" and how do they play with the system?
>
> If you show columns from sessions you get to columns:
> +-----------+-------------+------+-----+---------+-------+
> | Field     | Type        | Null | Key | Default | Extra |
> +-----------+-------------+------+-----+---------+-------+
> | id        | varchar(32) | NO   | PRI |         |       |
> | a_session | text        | NO   |     |         |       |
> +-----------+-------------+------+-----+---------+-------+
>
> If I do a select count(*) from sessions I get
>
> select count(*) from sessions;
> ERROR 1053 (08S01): Server shutdown in progress
>
> but thankfully the server doesn't actually shutdown!
>
> TIPS hints???  usually by the time I contact my support group about it and
> they get connected 30 minutes to an hour later things are better.
>
> Lately if it hangs it is only for 10-15 minutes but at that the librarians
> are pulling their hair out with 30-40 8 year olds trying to search for a
> book/check in or out a book!!
>
> David Schuster
> Plano ISD
> --
> View this message in context: http://www.nabble.com/sessions-in-mysql-tp23549310p23549310.html
> Sent from the Koha - Dev mailing list archive at Nabble.com.
>
> _______________________________________________
> Koha-devel mailing list
> Koha-devel at lists.koha.org
> http://lists.koha.org/mailman/listinfo/koha-devel
>