title: Low priority updates on MySQL using JDBC - how to check if they work
body: <p>I have a basic setup where the database is read by multiple web applications, and periodically I have a batch application which does a lot of writing. During the writing, the peroformance of the web-apps degrade heavily (their database reads are very slow).</p>
  
  <p>The env. is MySQL db using MYISAM engine, the batch application is a Java SE app, using spring-batch and SimpleJDBCTemplate to issue SQL commands via JDBC. I found that MySQL has a parameter that lowers the priority of write operations on MYISAM engine: <a href="http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_low-priority-updates" rel="nofollow noreferrer">low_priority_updates</a>. To quote the docs, amongs others, you can "SET LOW_PRIORITY_UPDATES=1 to change the priority in one thread". I opted for this because it's easiest from the config standpoint of my application. What I've done is configured my DataSource such that it exectutes that "SET ..." for each connection it opens, like so:</p>
  
  <pre><code>&lt;bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"&gt;
          &lt;!-- other props omitted --&gt;
          &lt;property name="connectionInitSqls"&gt;
              &lt;list&gt;
                  &lt;value&gt;SET low_priority_updates="ON"&lt;/value&gt;
              &lt;/list&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
  </code></pre>
  
  <p>Now my question is, how do I actually check that an SQL issued via this datasource does actually run with low priority? If I do <code>SHOW FULL PROCESSLIST</code> in MySQL while the inserts are happening it just tell me what SQL they're executing, nothing about the priority:</p>
  
  <p><img src="https://i.stack.imgur.com/gJbtF.png" alt="enter image description here"></p>
  
  <p>If I check the server variables low_priority_updates is "OFF" but that's just the server variable, it sais nothing about the thread-local value.</p>
  
  <p>So again, is there any actual way to check if per query/thread low_priority_updates's values are taken into account?</p>