[Deploying Sakai] [Building Sakai] mysql redundant statements (SQL_SELECT_LIMIT)

Stephen Marquard stephen.marquard at uct.ac.za
Wed Apr 25 01:31:47 PDT 2012


Hi all,

Everyone using mysql in production interested in performance tuning
(especially in Tests & Quizzes) should pay attention to this.

Load-testing and examination of the mysql logs showed a large number of
these statements:

SET OPTION SQL_SELECT_LIMIT=DEFAULT

We subsequently enabled graphing on our production cluster of the mysql
counter for SET OPTION statements (with show global status like
'Com_set_option') and found the number of SET OPTION statements exceeded
the number of actual queries being run, and at peak was over 6000 of
these per second. While these statement in themselves do nothing (as
that option is already set to the default), the network latency and CPU
overhead in dealing with them at this volume are a significant
performance issue.

The mysql connector only starts issuing these statements when at least
one prepared statement has set this to a non-default value, e.g.

SET OPTION SQL_SELECT_LIMIT=5

and looking at mysql logs shows that only quartz (used by jobscheduler)
does this. So changing quartz to not use this prevents the
mysql-connector from issuing the statement to set it to the default
value for every subsequent query.

The change to quartz to do this is simple and will not affect its
functionality (as far as we've seen) nor does it significantly increase
the volume of data returned to quartz (in our instance, the query
returns 10 rows rather than 5). So we applied this patch to quartz:

http://source.cet.uct.ac.za/svn/sakai/patches/trunk/quartz-remove-maxrows.diff


and deployed a custom quartz jar with the patch rather than the
standard quartz-1.6.6 jar in shared/lib/:

http://source.cet.uct.ac.za/svn/sakai/quartz/quartz-1.6.6-UCT.jar

Running a load-test before and after this change showed dramatic
results - the test with the modified quartz ran in 50% of the time.

So if you are running into performance limits of your mysql database
under heavy load, you may wish to try this simple change.

This is obviously a somewhat brittle fix, as if any other code was
introduced into any Sakai app or contrib tool that used the JDBC
setMaxRows() function, the problem would return. The right place to fix
this is in the mysql-connector-java code, so that it remembers the state
of the SET OPTION SQL_SELECT_LIMIT setting for the connection and does
not issue the statement unless necessary but we haven't got as far as
filing a bug report with Oracle for this yet or creating a reproducible
test case to demonstrate the behaviour.

A further note is that this behaviour is influenced by the
mysql-connector option settings. We are using:

url at javax.sql.BaseDataSource=jdbc:mysql://dbname:3306/vula?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=4096&prepStmtCacheSqlLimit=4096

with mysql-connector-java-5.1.19 (the latest version).

If you're not using server-side prepared statements or caching prepared
statements, then performance may be even worse.

Regards
Stephen

Stephen Marquard, Acting Director 
Centre for Educational Technology, University of Cape Town
http://www.cet.uct.ac.za
Email/IM/XMPP: stephen.marquard at uct.ac.za 
Phone: +27-21-650-5037 Cell: +27-83-500-5290 
 


>>> Lance Speelmon <lance at rsmart.com> 4/20/2012 4:25 PM >>>
Sounds like it could be this [1]:

 1342   					if (this.hasLimitClause)
{
 1343   						rowLimit =
this.maxRows;
 1344   					} else {
 1345   						if (this.maxRows
<= 0) {
 1346  
							executeSimpleNonQuery(locallyScopedConn,
 1347  
									"SET
OPTION SQL_SELECT_LIMIT=DEFAULT");

[1]
http://www.docjar.com/html/api/com/mysql/jdbc/PreparedStatement.java.html


Maybe setting maxRows to a sufficiently high number might prevent it?


On Apr 20, 2012, at 5:19 AM, Stephen Marquard wrote:

> Hi all,
> 
> I wonder if anyone has come across unusual behaviour with mysql and
> this statement:
> 
> SET OPTION SQL_SELECT_LIMIT=DEFAULT
> 
> If we enable query logging on a test server, there are thousands of
> these, mostly redundant in about a 10:1 ratio to real queries. For
> example this will get issued over and over again for one connection
> (e.g. 1675 below):
> 
>                 1675 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1677 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1675 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1677 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1675 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1677 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1675 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1677 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1675 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1677 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1675 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1677 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1675 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1677 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1675 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1675 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1677 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1675 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1678 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1677 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
>                 1675 Query     SET OPTION SQL_SELECT_LIMIT=DEFAULT
> 
> The source of this is the mysql connector-j and I've seen the same
> behaviour on the latest version (5.1.19) and an older version (5.0.8)
so
> it doesn't seem to be a recent change. It could be a consequence of
some
> other change in Sakai or Hibernate though.
> 
> We think this is a contributing factor to some performance issues,
so
> are keen to resolve it.
> 
> Regards
> Stephen
> 
> 
> Stephen Marquard, Acting Director 
> Centre for Educational Technology, University of Cape Town
> http://www.cet.uct.ac.za 
> Email/IM/XMPP: stephen.marquard at uct.ac.za 
> Phone: +27-21-650-5037 Cell: +27-83-500-5290 
> 
> 
> 
> 
> 
> 
> ###
> 
> UNIVERSITY OF CAPE TOWN 
> 
> This e-mail is subject to the UCT ICT policies and e-mail disclaimer
> published on our website at
> http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable
from
> +27 21 650 9111. This e-mail is intended only for the person(s) to
whom
> it is addressed. If the e-mail has reached you in error, please
notify
> the author. If you are not the intended recipient of the e-mail you
may
> not use, disclose, copy, redirect or print the content. If this
e-mail
> is not related to the business of UCT it is sent by the sender in
the
> sender's individual capacity.
> 
> ###
> 
> 
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org 
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev 
> 
> TO UNSUBSCRIBE: send email to
sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
"unsubscribe"






###

UNIVERSITY OF CAPE TOWN 

This e-mail is subject to the UCT ICT policies and e-mail disclaimer
published on our website at
http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from
+27 21 650 9111. This e-mail is intended only for the person(s) to whom
it is addressed. If the e-mail has reached you in error, please notify
the author. If you are not the intended recipient of the e-mail you may
not use, disclose, copy, redirect or print the content. If this e-mail
is not related to the business of UCT it is sent by the sender in the
sender's individual capacity.

###
 



More information about the production mailing list