Emergency

Using per-query variable statements in Percona Server


Posted on:

|

By:


PREVIOUS POST
NEXT POST
Share Button

Percona Server has implemented per-query variable statement support in version 5.6.14-62.0. This feature provides the ability to set variable values only for a certain query, after execution of which the previous values will be restored. Per-query variable values can be set up with the following command:

Example:

If we want to increase the sort_buffer_size value just for one specific sort query we can do it like this:

Using the per-query variable statements with Statement Timeout feature

Percona Server has implemented a statement timeout feature (This feature with some changes was ported from Twitter MySQL patches). This feature can be used to limit the query execution time by specifying the timeout value in the max_statement_time variable. After the specified number of miliseconds is reached the server will attempt to abort the statement and return the following error to the client:

Following example shows how to limit the execution time for a specific query by using per-query variable statements with Statement Timeout feature:

This feature isn’t just limited to specifying one variable, we can provide more than one if we want to. Following example will both increase the sort_buffer_size value and limit the execution time:

Share Button
PREVIOUS POST
NEXT POST


Tags:

,

Categories:
MySQL, Percona Server


Comments

  • Is their anyway to limit the value that can be set for a per-query variable?

  • What about MySQL? is it implemented on new version or not?

  • HI,

    It is not quite clear to me how this works. Will the new set statement execute the query, or will it just tell the server to set the variable each time (or only the first?) the specified statement is executed?

  • Hrvoje Matijakovic Post author

    @Michael
    There is no way to limit the per-query variable setting.

    @Saeid
    At the moment MySQL doesn’t have this implemented.

    @Øystein
    New statement will execute the query with the variables set to the specified values for the duration of that statement. Think of such statements as plain SELECT name FROM name … that happen to have some local modifiers to that statement.

  • Oystein,

    I agree with you syntax is a bit confusing. I would rather see something like SELECT … USING VARIABLES … but it was easier from parser standpoint to implement it this way. If you implement similar feature in MySQL with different syntax I will not be offended.

    Michael,

    Currently limits are not enforced though it would be right to have them as we support minimum and maximum values for variables as set by users
    https://www.percona.com/doc/percona-server/5.6/management/expanded_program_option_modifiers.html

Leave a Reply

Your email address will not be published. Required fields are marked *