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 <dschu...@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@lists.koha.org > http://lists.koha.org/mailman/listinfo/koha-devel > _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org http://lists.koha.org/mailman/listinfo/koha-devel
