Low priority MySQL updates using JDBC - how to check if they work

Question

I have a basic setup in which the database is read by several web applications, and periodically I have a batch application that writes a lot. At the time of writing, the performance of web applications is greatly deteriorating (their reading of the database is very slow).

env. MySQL db using the MYISAM engine, the batch application is a Java SE application using spring-batch and SimpleJDBCTemplate to issue SQL commands through JDBC. I found that MySQL has a parameter that lowers the priority of write operations on the MYISAM engine: low_priority_updates . To quote documents, among others, you can "SET LOW_PRIORITY_UPDATES = 1 change the priority in a single thread." I chose this because it is easiest in terms of the configuration of my application. What I did configured my DataSource so that it exectutes that "SET ..." for each of its connections opens, for example:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <!-- other props omitted --> <property name="connectionInitSqls"> <list> <value>SET low_priority_updates="ON"</value> </list> </property> </bean> 

Now my question is: how do I really verify that SQL released through this data source really works with low priority? If I do SHOW FULL PROCESSLIST in MySQL while inserts occur, just tell me what SQL they execute, nothing about priority:

If I check the server variables, then low_priority_updates is "OFF", but this is only a server variable, it does not say anything about the local value of the stream.

So, is there a real way to check if the request / stream values ​​of low_priority_updates are taken into account?

Answer 1

By issuing the SET LOW_PRIORITY_UPDATES = 1 command, you affect the value of the variable for the session. Therefore, this can be seen by checking the value of the variable in the session.

I know two ways to do this:

1- SHOW SESSIONS VARIABLES AS "low_priority_dapdates"

it shows on / off

2- select @@ session.low_priority_updates

it gives 0/1

Important: the above statements / calls will show the values ​​of the variables in the session in which they are executed. Therefore, you will need to run them using the connections themselves to see the values. I do not know a method in MySQL where you can select values ​​for variables belonging to another session.

If you want to see them as a list, you may need to do the work by creating a table and registering this information yourself. eg:

 CREATE TABLE `mydb`.`my_low_priority_updates` ( `connection_id` INT , `low_priority_updates_value` INT NOT NULL ) ENGINE = MyISAM; 

then you need an instruction that inserts the connection identifier and value into the table:

 insert into my_low_priority_updates(connection_id,low_priority_updates_value) select connection_id(),@@session.low_priority_updates from dual where not exists (select 1 from my_low_priority_updates where connection_id=connection_id()) 

You can put this statement in the procedure and make sure that it is called or added to the trigger of the table that you know is updated / inserted.

then querying the my_low_priority_updates table will later show you the variable values ​​in each connection.