[Toolserver-l] improving slow query performance with transaction isolation level

[kate]

hi.

people already familiar with SQL's transaction isolation level and MVCC 
databases can ignore this post.  anyone who doesn't may want to read it.

the problem:
a lot of the queries used on zedler are report queries which examine a lot 
of records (possibly every page, link or revision) to generate the result.  
not only do these take a long time to run, but they slow down the SQL 
server for other uses, since InnoDB queries are transactional, and the 
server must ensure that changes to the database don't affect the query.

in most cases -- like on the production site -- this is what you want, and 
the performance penalty is acceptable.  however, for other uses, you may be 
more interested in the query completing quickly than having entirely 
consistent results (especially when your result is going to be several 
hours out of date anyway).

for this purpose, MySQL provides a command, SET TRANSACTION ISOLATION LEVEL.  
this allows you to control (for the current connection only) how consistent 
queries will be.  this and other related InnoDB issues are explained in 
detail at:
  http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html
(section 14.2.10.3 in particular) but i'll provide a brief summary here.

the default isolation level is "repeatable read".  this means that (mostly) 
everything you do will be consistent relative to the first query you run in 
each transaction.  if the table changes, your transaction will not see the 
changes.

the least consistent isolation level is "read uncommitted".  this does not 
perform any locking or multi-versioning for the query.  the downside of 
this is that you may see dirty data (slightly out of date or uncommitted) 
in the result.  however, the advantage is that the query will run much 
faster.

you can change the current isolation level with the command:

  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

if you're running slow queries on the database, you may like to test this 
and see how much difference it makes.

you may also want to review the manual sections linked above for more 
details.

k.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 185 bytes
Desc: not available
Url : http://lists.wikimedia.org/pipermail/toolserver-l/attachments/20051230/9ccf9236/attachment.pgp