1 of 17

MariaDB MaxScale 21.06 Filters

Filters are powerful modules that intercept and process database traffic in MaxScale. Use them to log, transform, block, or reroute queries to add control, security, and monitoring.

MaxScale 21.06 Binlog Filter

This filter was introduced in MariaDB MaxScale 2.3.0.

MaxScale 21.06 Consistent Critical Read Filter

This filter was introduced in MariaDB MaxScale 2.1.

MaxScale 21.06 Hintfilter

This filter adds routing hints to a service. The filter has no parameters.

MaxScale 21.06 Insert Stream Filter

This filter was introduced in MariaDB MaxScale 2.1.

Insert Stream Filter

Overview

The insertstream filter converts bulk inserts into CSV data streams that are consumed by the backend server via the LOAD DATA LOCAL INFILE mechanism. This leverages the speed advantage of LOAD DATA LOCAL INFILE over regular inserts while also reducing the overall network traffic by condensing the inserted values into CSV.

Note: This is an experimental filter module

Filter Parameters

This filter has no parameters.

Details of Operation

The filter translates all INSERT statements done inside an explicit transaction into LOAD DATA LOCAL INFILE streams. The file name used in the request will always be maxscale.data.

The following example is translated into a LOAD DATA LOCAL INFILE request followed by two CSV rows.

Multiple inserts to the same table are combined into a single stream. This allows for efficient bulk loading with simple insert statements.

The following example will use only one LOAD DATA LOCAL INFILE request followed by four CSV rows.

Non-INSERT statements executed inside the transaction will close the streaming of the data. Avoid interleaving SELECT statements with INSERT statements inside transactions.

The following example has to use two LOAD DATA LOCAL INFILE requests, each followed by two CSV rows.

Note: Avoid doing this!

Estimating Network Bandwidth Reduction

The more inserts that are streamed, the more efficient this filter is. The saving in network bandwidth in bytes can be estimated with the following formula:

Positive values indicate savings in network bandwidth usage.

Example Configuration

The filter has no parameters so it is extremely simple to configure. The following example shows the required filter configuration.

_{This page is licensed: CC BY-SA / Gnu FDL}

MaxScale 21.06 Maxrows

This filter was introduced in MariaDB MaxScale 2.1.

Maxrows

Overview

The Maxrows filter is capable of restricting the amount of rows that a SELECT, a prepared statement or stored procedure could return to the client application.

If a resultset from a backend server has more rows than the configured limit or the resultset size exceeds the configured size, an empty result will be sent to the client.

Configuration

The Maxrows filter is easy to configure and to add to any existing service.

Filter Parameters

The Maxrows filter has no mandatory parameters. Optional parameters are:

max_resultset_rows

Type: number
Mandatory: No
Dynamic: Yes
Default: (no limit)

Specifies the maximum number of rows a resultset can have in order to be returned to the user.

If a resultset is larger than this an empty result will be sent instead.

max_resultset_size

Type:
Mandatory: No
Dynamic: Yes
Default: 64Ki

Specifies the maximum size a resultset can have in order to be sent to the client. A resultset larger than this, will not be sent: an empty resultset will be sent instead.

max_resultset_return

Type:
Mandatory: No
Dynamic: Yes
Values: empty

Specifies what the filter sends to the client when the rows or size limit is hit, possible values:

an empty result set
an error packet with input SQL
an OK packet

Example output with ERR packet:

debug

Type: number
Mandatory: No
Dynamic: Yes
Default: 0

An integer value, using which the level of debug logging made by the Maxrows filter can be controlled. The value is actually a bitfield with different bits denoting different logging.

0 (0b00000) No logging is made.
1 (0b00001) A decision to handle data form server is logged.

To log everything, give debug a value of 3.

Example Configuration

Here is an example of filter configuration where the maximum number of returned rows is 10000 and maximum allowed resultset size is 256KB

_{This page is licensed: CC BY-SA / Gnu FDL}

MaxScale 21.06 Named Server Filter

MaxScale 21.06 Regex Filter

Regex Filter

Overview

The Regex filter is a filter module for MariaDB MaxScale that is able to rewrite query content using regular expression matches and text substitution. The regular expressions use the .

PCRE2 library uses a different syntax than POSIX to refer to capture groups in the replacement string. The main difference is the usage of the dollar character instead of the backslash character for references e.g. $1 instead of\1. For more details about the replacement string differences, please read the chapter in the PCRE2 manual.

Configuration

The following demonstrates a minimal configuration.

Filter Parameters

The Regex filter has two mandatory parameters: match and replace.

`match`

Type:
Mandatory: Yes
Dynamic: Yes

Defines the text in the SQL statements that is replaced.

`options`

Type:
Mandatory: No
Dynamic: Yes
Values: ignorecase

The options-parameter affects how the patterns are compiled as .

`replace`

Type: string
Mandatory: Yes
Dynamic: Yes

This is the text that should replace the part of the SQL-query matching the pattern defined in match.

`source`

Type: string
Mandatory: No
Dynamic: Yes
Default: None

The optional source parameter defines an address that is used to match against the address from which the client connection to MariaDB MaxScale originates. Only sessions that originate from this address will have the match and replacement applied to them.

`user`

Type: string
Mandatory: No
Dynamic: Yes
Default: None

The optional user parameter defines a username that is used to match against the user from which the client connection to MariaDB MaxScale originates. Only sessions that are connected using this username will have the match and replacement applied to them.

`log_file`

Type: string
Mandatory: No
Dynamic: Yes
Default: None

The optional log_file parameter defines a log file in which the filter writes all queries that are not matched and matching queries with their replacement queries. All sessions will log to this file so this should only be used for diagnostic purposes.

`log_trace`

Type: string
Mandatory: No
Dynamic: Yes
Default: None

The optional log_trace parameter toggles the logging of non-matching and matching queries with their replacements into the log file on the info level. This is the preferred method of diagnosing the matching of queries since the log level can be changed at runtime. For more details about logging levels and session specific logging, please read the .

Examples

Example 1 - Replace MySQL 5.1 create table syntax with that for later versions

MySQL 5.1 used the parameter TYPE = to set the storage engine that should be used for a table. In later versions this changed to be ENGINE =. Imagine you have an application that you can not change for some reason, but you wish to migrate to a newer version of MySQL. The regexfilter can be used to transform the create table statements into the form that could be used by MySQL 5.5

_{This page is licensed: CC BY-SA / Gnu FDL}

MaxScale 21.06 Throttle

This filter was added in MariaDB MaxScale 2.3

Throttle
- Overview
- Configuration

Overview

The throttle filter replaces and extends on the limit_queries functionality of .

The throttle filter is used to limit the maximum query frequency (QPS - queries per second) of a database session to a configurable value. The main use cases are to prevent a rogue session (client side error) and a DoS attack from overloading the system.

The throttling is dynamic. The query frequency is not limited to an absolute value. Depending on the configuration the throttle will allow some amount of high frequency queries, or especially short bursts with no frequency limitation.

Configuration

Basic Configuration

This configuration states that the query frequency will be throttled to around 500 qps, and that the time limit a query is allowed to stay at the maximum frequency is 60 seconds. All values involving time are configured in milliseconds. With the basic configuration the throttling will be nearly immediate, i.e. a session will only be allowed very short bursts of high frequency querying.

When a session has been continuously throttled for throttling_duration milliseconds, or 60 seconds in this example, MaxScale will disconnect the session.

Allowing high frequency bursts

The two parameters max_qps and sampling_duration together define how a session is throttled.

Suppose max qps is 400 qps and sampling duration is 10 seconds. Since QPS is not an instantaneous measure, but one could say it has a granularity of 10 seconds, we see that over the 10 seconds 10*400 = 4000 queries are allowed before throttling kicks in.

With these values, a fresh session can start off with a speed of 2000 qps, and maintain that speed for 2 seconds before throttling starts.

If the client continues to query at high speed and throttling duration is set to 10 seconds, Maxscale will disconnect the session 12 seconds after it started.

Filter Parameters

max_qps

Type: number
Mandatory: Yes
Dynamic: Yes

Maximum queries per second.

This is the frequency to which a session will be limited over a given time period. QPS is not measured as an instantaneous value but over a configurable sampling duration (see sampling_duration).

throttling_duration

Type:
Mandatory: Yes
Dynamic: Yes

This defines how long a session is allowed to be throttled before MaxScale disconnects the session.

`sampling_duration`

Type:
Mandatory: No
Dynamic: Yes
Default: 250ms

Sampling duration defines the window of time over which QPS is measured. This parameter directly affects the amount of time that high frequency queries are allowed before throttling kicks in.

The lower this value is, the more strict throttling becomes. Conversely, the longer this time is, the longer bursts of high frequency querying is allowed.

`continuous_duration`

Type:
Mandatory: No
Dynamic: Yes
Default: 2s

This value defines what continuous throttling means. Continuous throttling starts as soon as the filter throttles the frequency. Continuous throttling ends when no throttling has been performed in the past continuous_duration time.

_{This page is licensed: CC BY-SA / Gnu FDL}

MaxScale 21.06 Transaction Performance Monitoring Filter

Note: This module is experimental and must be built from source. The module is deprecated in MaxScale 23.08 and might be removed in a future release.

Transaction Performance Monitoring Filter

Overview

The Transaction Performance Monitoring (TPM) filter is a filter module for MaxScale that monitors every SQL statement that passes through the filter. The filter groups a series of SQL statements into a transaction by detecting 'commit' or 'rollback' statements. It logs all committed transactions with necessary information, such as timestamp, client, SQL statements, latency, etc., which can be used later for transaction performance analysis.

Configuration

The configuration block for the TPM filter requires the minimal filter options in it's section within the maxscale.cnf file, stored in /etc/maxscale.cnf.

The TPM filter does not support any filter options currently.

Filter Parameters

The TPM filter accepts a number of optional parameters.

Filename

The name of the output file created for performance logging. The default filename is tpm.log.

Source

The optional source parameter defines an address that is used to match against the address from which the client connection to MaxScale originates. Only sessions that originate from this address will be logged.

User

The optional user parameter defines a user name that is used to match against the user from which the client connection to MaxScale originates. Only sessions that are connected using this username are logged.

Delimiter

The optional delimiter parameter defines a delimiter that is used to distinguish columns in the log. The default delimiter is :::.

Query_delimiter

The optional query_delimiter defines a delimiter that is used to distinguish different SQL statements in a transaction. The default query delimiter is @@@.

Named_pipe

named_pipe is the path to a named pipe, which TPM filter uses to communicate with 3rd-party applications (e.g., ). Logging is enabled when the router receives the character '1' and logging is disabled when the router receives the character '0' from this named pipe. The default named pipe is /tmp/tpmfilter and logging is disabled by default.

For example, the following command enables the logging:

Similarly, the following command disables the logging:

Log Output Format

For each transaction, the TPM filter prints its log in the following format:

Examples

Example 1 - Log Transactions for Performance Analysis

You want to log every transaction with its SQL statements and latency for future transaction performance analysis.

Add a filter with the following definition:

After the filter reads the character '1' from its named pipe, the following is an example log that is generated from the above TPM filter with the above configuration:

Note that 3 and 6 are latencies of each transaction in milliseconds, while 0.165 and 0.123 are latencies of the first statement of each transaction in milliseconds.

_{This page is licensed: CC BY-SA / Gnu FDL}

1484086477::::server1::::root::::3::::0.165@@@@0.108@@@@0.102@@@@0.092@@@@0.121@@@@0.122@@@@0.110@@@@2.081::::UPDATE WAREHOUSE SET W_YTD = W_YTD + 3630.48 WHERE W_ID = 2 @@@@SELECT W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP, W_NAME FROM WAREHOUSE WHERE W_ID = 2@@@@UPDATE DISTRICT SET D_YTD = D_YTD + 3630.48 WHERE D_W_ID = 2 AND D_ID = 9@@@@SELECT D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP, D_NAME FROM DISTRICT WHERE D_W_ID = 2 AND D_ID = 9@@@@SELECT C_FIRST, C_MIDDLE, C_LAST, C_STREET_1, C_STREET_2, C_CITY, C_STATE, C_ZIP, C_PHONE, C_CREDIT, C_CREDIT_LIM, C_DISCOUNT, C_BALANCE, C_YTD_PAYMENT, C_PAYMENT_CNT, C_SINCE FROM CUSTOMER WHERE C_W_ID = 2 AND C_D_ID = 9 AND C_ID = 1025@@@@UPDATE CUSTOMER SET C_BALANCE = 1007749.25, C_YTD_PAYMENT = 465215.47, C_PAYMENT_CNT = 203 WHERE C_W_ID = 2 AND C_D_ID = 9 AND C_ID = 1025@@@@INSERT INTO HISTORY (H_C_D_ID, H_C_W_ID, H_C_ID, H_D_ID, H_W_ID, H_DATE, H_AMOUNT, H_DATA) VALUES (9,2,1025,9,2,'2017-01-10 17:14:37',3630.48,'locfljbe xtnfqn') 1484086477::::server1::::root::::6::::0.123@@@@0.087@@@@0.091@@@@0.098@@@@0.078@@@@0.106@@@@0.094@@@@0.074@@@@0.089@@@@0.073@@@@0.098@@@@0.073@@@@0.088@@@@0.072@@@@0.087@@@@0.071@@@@0.085@@@@0.078@@@@0.088@@@@0.098@@@@0.081@@@@0.076@@@@0.082@@@@0.073@@@@0.077@@@@0.070@@@@0.105@@@@0.093@@@@0.088@@@@0.089@@@@0.087@@@@0.087@@@@0.086@@@@1.883::::SELECT C_DISCOUNT, C_LAST, C_CREDIT, W_TAX FROM CUSTOMER, WAREHOUSE WHERE W_ID = 2 AND C_W_ID = 2 AND C_D_ID = 10 AND C_ID = 1267@@@@SELECT D_NEXT_O_ID, D_TAX FROM DISTRICT WHERE D_W_ID = 2 AND D_ID = 10 FOR UPDATE@@@@UPDATE DISTRICT SET D_NEXT_O_ID = D_NEXT_O_ID + 1 WHERE D_W_ID = 2 AND D_ID = 10@@@@INSERT INTO OORDER (O_ID, O_D_ID, O_W_ID, O_C_ID, O_ENTRY_D, O_OL_CNT, O_ALL_LOCAL) VALUES (286871, 10, 2, 1267, '2017-01-10 17:14:37', 7, 1)@@@@INSERT INTO NEW_ORDER (NO_O_ID, NO_D_ID, NO_W_ID) VALUES ( 286871, 10, 2)@@@@SELECT I_PRICE, I_NAME , I_DATA FROM ITEM WHERE I_ID = 24167@@@@SELECT S_QUANTITY, S_DATA, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10 FROM STOCK WHERE S_I_ID = 24167 AND S_W_ID = 2 FOR UPDATE@@@@SELECT I_PRICE, I_NAME , I_DATA FROM ITEM WHERE I_ID = 96982@@@@SELECT S_QUANTITY, S_DATA, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10 FROM STOCK WHERE S_I_ID = 96982 AND S_W_ID = 2 FOR UPDATE@@@@SELECT I_PRICE, I_NAME , I_DATA FROM ITEM WHERE I_ID = 40679@@@@SELECT S_QUANTITY, S_DATA, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10 FROM STOCK WHERE S_I_ID = 40679 AND S_W_ID = 2 FOR UPDATE@@@@SELECT I_PRICE, I_NAME , I_DATA FROM ITEM WHERE I_ID = 31459@@@@SELECT S_QUANTITY, S_DATA, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10 FROM STOCK WHERE S_I_ID = 31459 AND S_W_ID = 2 FOR UPDATE@@@@SELECT I_PRICE, I_NAME , I_DATA FROM ITEM WHERE I_ID = 6143@@@@SELECT S_QUANTITY, S_DATA, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10 FROM STOCK WHERE S_I_ID = 6143 AND S_W_ID = 2 FOR UPDATE@@@@SELECT I_PRICE, I_NAME , I_DATA FROM ITEM WHERE I_ID = 12001@@@@SELECT S_QUANTITY, S_DATA, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10 FROM STOCK WHERE S_I_ID = 12001 AND S_W_ID = 2 FOR UPDATE@@@@SELECT I_PRICE, I_NAME , I_DATA FROM ITEM WHERE I_ID = 40407@@@@SELECT S_QUANTITY, S_DATA, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05, S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10 FROM STOCK WHERE S_I_ID = 40407 AND S_W_ID = 2 FOR UPDATE@@@@INSERT INTO ORDER_LINE (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO) VALUES (286871,10,2,1,24167,2,7,348.31998,'btdyjesowlpzjwnmxdcsion')@@@@INSERT INTO ORDER_LINE (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO) VALUES (286871,10,2,2,96982,2,1,4.46,'kudpnktydxbrbxibbsyvdiw')@@@@INSERT INTO ORDER_LINE (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO) VALUES (286871,10,2,3,40679,2,7,528.43,'nhcixumgmosxlwgabvsrcnu')@@@@INSERT INTO ORDER_LINE (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO) VALUES (286871,10,2,4,31459,2,9,341.82,'qbglbdleljyfzdpfbyziiea')@@@@INSERT INTO ORDER_LINE (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO) VALUES (286871,10,2,5,6143,2,3,152.67,'tmtnuupaviimdmnvmetmcrc')@@@@INSERT INTO ORDER_LINE (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO) VALUES (286871,10,2,6,12001,2,5,304.3,'ufytqwvkqxtmalhenrssfon')@@@@INSERT INTO ORDER_LINE (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO) VALUES (286871,10,2,7,40407,2,1,30.32,'hvclpfnblxchbyluumetcqn')@@@@UPDATE STOCK SET S_QUANTITY = 65 , S_YTD = S_YTD + 7, S_ORDER_CNT = S_ORDER_CNT + 1, S_REMOTE_CNT = S_REMOTE_CNT + 0 WHERE S_I_ID = 24167 AND S_W_ID = 2@@@@UPDATE STOCK SET S_QUANTITY = 97 , S_YTD = S_YTD + 1, S_ORDER_CNT = S_ORDER_CNT + 1, S_REMOTE_CNT = S_REMOTE_CNT + 0 WHERE S_I_ID = 96982 AND S_W_ID = 2@@@@UPDATE STOCK SET S_QUANTITY = 58 , S_YTD = S_YTD + 7, S_ORDER_CNT = S_ORDER_CNT + 1, S_REMOTE_CNT = S_REMOTE_CNT + 0 WHERE S_I_ID = 40679 AND S_W_ID = 2@@@@UPDATE STOCK SET S_QUANTITY = 28 , S_YTD = S_YTD + 9, S_ORDER_CNT = S_ORDER_CNT + 1, S_REMOTE_CNT = S_REMOTE_CNT + 0 WHERE S_I_ID = 31459 AND S_W_ID = 2@@@@UPDATE STOCK SET S_QUANTITY = 86 , S_YTD = S_YTD + 3, S_ORDER_CNT = S_ORDER_CNT + 1, S_REMOTE_CNT = S_REMOTE_CNT + 0 WHERE S_I_ID = 6143 AND S_W_ID = 2@@@@UPDATE STOCK SET S_QUANTITY = 13 , S_YTD = S_YTD + 5, S_ORDER_CNT = S_ORDER_CNT + 1, S_REMOTE_CNT = S_REMOTE_CNT + 0 WHERE S_I_ID = 12001 AND S_W_ID = 2@@@@UPDATE STOCK SET S_QUANTITY = 44 , S_YTD = S_YTD + 1, S_ORDER_CNT = S_ORDER_CNT + 1, S_REMOTE_CNT = S_REMOTE_CNT + 0 WHERE S_I_ID = 40407 AND S_W_ID = 2 ...

-bash-4.1$ cat /var/logs/top/Employees-top-10.137 Top 10 longest running queries in session. ========================================== Time (sec) | Query -----------+----------------------------------------------------------------- 22.985 | select sum(salary), year(from_date) from salaries s, (select distinct year(from_date) as y1 from salaries) y where (makedate(y.y1, 1) between s.from_date and s.to_date) group by y.y1 5.304 | select d.dept_name as "Department", y.y1 as "Year", count(*) as "Count" from departments d, dept_emp de, (select distinct year(from_date) as y1 from dept_emp order by 1) y where d.dept_no = de.dept_no and (makedate(y.y1, 1) between de.from_date and de.to_date) group by y.y1, d.dept_name order by 1, 2 2.896 | select year(now()) - year(birth_date) as age, gender, avg(salary) as "Average Salary" from employees e, salaries s where e.emp_no = s.emp_no and ("1988-08-01" between from_date AND to_date) group by year(now()) - year(birth_date), gender order by 1,2 2.160 | select dept_name as "Department", sum(salary) / 12 as "Salary Bill" from employees e, departments d, dept_emp de, salaries s where e.emp_no = de.emp_no and de.dept_no = d.dept_no and ("1988-08-01" between de.from_date AND de.to_date) and ("1988-08-01" between s.from_date AND s.to_date) and s.emp_no = e.emp_no group by dept_name order by 1 0.845 | select dept_name as "Department", avg(year(now()) - year(birth_date)) as "Average Age", gender from employees e, departments d, dept_emp de where e.emp_no = de.emp_no and de.dept_no = d.dept_no and ("1988-08-01" between from_date AND to_date) group by dept_name, gender 0.668 | select year(hire_date) as "Hired", d.dept_name, count(*) as "Count" from employees e, departments d, dept_emp de where de.emp_no = e.emp_no and de.dept_no = d.dept_no group by d.dept_name, year(hire_date) 0.249 | select moves.n_depts As "No. of Departments", count(moves.emp_no) as "No. of Employees" from (select de1.emp_no as emp_no, count(de1.emp_no) as n_depts from dept_emp de1 group by de1.emp_no) as moves group by moves.n_depts order by 1 0.245 | select year(now()) - year(birth_date) as age, gender, count(*) as "Count" from employees group by year(now()) - year(birth_date), gender order by 1,2 0.179 | select year(hire_date) as "Hired", count(*) as "Count" from employees group by year(hire_date) 0.160 | select year(hire_date) - year(birth_date) as "Age", count(*) as Count from employees group by year(hire_date) - year(birth_date) order by 1 -----------+----------------------------------------------------------------- Session started Wed Jun 18 18:41:03 2014 Connection from 127.0.0.1 Username massi Total of 24 statements executed. Total statement execution time 35.701 seconds Average statement execution time 1.488 seconds Total connection time 46.500 seconds -bash-4.1$

MaxScale 21.06 Tee Filter

Tee Filter

Overview

The tee filter is a "plumbing" fitting in the MariaDB MaxScale filter toolkit. It can be used in a filter pipeline of a service to make copies of requests from the client and send the copies to another service within MariaDB MaxScale.

Please Note: Starting with MaxScale 2.2.0, any client that connects to a service which uses a tee filter will require a grant for the loopback address, i.e. 127.0.0.1.

Configuration

The configuration block for the TEE filter requires the minimal filter parameters in its section within the MaxScale configuration file. The service to send the duplicates to must be defined.

Filter Parameters

The tee filter requires a mandatory parameter to define the service to replicate statements to and accepts a number of optional parameters.

`target`

Type: target
Mandatory: No
Dynamic: Yes
Default: none

The target where the filter will duplicate all queries. The target can be either a service or a server. The duplicate connection that is created to this target will be referred to as the "branch target" in this document.

`service`

Type: service
Mandatory: No
Dynamic: Yes
Default: none

The service where the filter will duplicate all queries. This parameter is deprecated in favor of the target parameter and will be removed in a future release. Both target and service cannot be defined.

`match`

Type:
Mandatory: No
Dynamic: Yes
Default: None

What queries should be included.

`exclude`

Type:
Mandatory: No
Dynamic: Yes
Default: None

What queries should be excluded.

`options`

Type:
Mandatory: No
Dynamic: Yes
Values: ignorecase

How regular expressions should be interpreted.

`source`

Type: string
Mandatory: No
Dynamic: Yes
Default: None

`user`

Type: string
Mandatory: No
Dynamic: Yes
Default: None

The optional user parameter defines a user name that is used to match against the user from which the client connection to MariaDB MaxScale originates. Only sessions that are connected using this username are replicated.

`sync`

Type:
Mandatory: No
Dynamic: Yes
Default: false

Enable synchronous routing mode. When configured with sync=true, the filter will queue new queries until the response from both the main and the branch target has been received. This means that for n executed queries, n - 1 queries are guaranteed to be synchronized. Adding one extra statement (e.g. SELECT 1) to a batch of statements guarantees that all previous SQL statements have been successfully executed on both targets.

In the synchronous routing mode, a failure of the branch target will cause the client session to be closed.

Limitations

All statements that are executed on the branch target are done in an asynchronous manner. This means that when the client receives the response there is no guarantee that the statement has completed on the branch target. The sync feature provides some synchronization guarantees that can be used to verify successful execution on both targets.
Any errors on the branch target will cause the connection to it to be closed. If target is a service, it is up to the router to decide whether the connection is closed. For direct connections to servers, any network errors cause the connection to be closed. When the connection is closed, no new queries will be routed to the branch target.

With sync=true, a failure of the branch target will cause the whole session to be closed.

Module commands

Read documentation for details about module commands.

The tee filter supports the following module commands.

`tee disable [FILTER]`

This command disables a tee filter instance. A disabled tee filter will not send any queries to the target service.

`tee enable [FILTER]`

Enable a disabled tee filter. This resumes the sending of queries to the target service.

Examples

Example 1 - Replicate all inserts into the orders table

Assume an order processing system that has a table called orders. You also have another database server, the datamart server, that requires all inserts into orders to be replicated to it. Deletes and updates are not, however, required.

Set up a service in MariaDB MaxScale, called Orders, to communicate with the order processing system with the tee filter applied to it. Also set up a service to talk to the datamart server, using the DataMart service. The tee filter would have as its service entry the DataMart service, by adding a match parameter of "insert into orders" would then result in all requests being sent to the order processing system, and insert statements that include the orders table being additionally sent to the datamart server.

_{This page is licensed: CC BY-SA / Gnu FDL}

MaxScale 21.06 Lua Filter

The luafilter is a filter that calls a set of functions in a Lua script.

Read the Lua language documentation for information on how to write Lua scripts.

Note: This module is experimental and must be built from source. The module is deprecated in MaxScale 23.08 and might be removed in a future release.

Lua Filter

Filter Parameters

The luafilter has two parameters. They control which scripts will be called by the filter. Both parameters are optional but at least one should be defined. If both global_script and session_script are defined, the entry points in both scripts will be called.

`global_script`

The global Lua script. The parameter value is a path to a readable Lua script which will be executed.

This script will always be called with the same global Lua state and it can be used to build a global view of the whole service.

`session_script`

The session level Lua script. The parameter value is a path to a readable Lua script which will be executed once for each session.

Each session will have its own Lua state meaning that each session can have a unique Lua environment. Use this script to do session specific tasks.

Lua Script Calling Convention

The entry points for the Lua script expect the following signatures:

nil createInstance() - global script only, called when MaxScale is started
- The global script will be loaded in this function and executed once on a global level before calling the createInstance function in the Lua script.
nil newSession(string, string) - new session is created

These functions, if found in the script, will be called whenever a call to the matching entry point is made.

Script Template

Here is a script template that can be used to try out the luafilter. Copy it into a file and add global_script=<path to script> into the filter configuration. Make sure the file is readable by the maxscale user.

Functions Exposed by the Luafilter

The luafilter exposes three functions that can be called from the Lua script.

string lua_qc_get_type_mask()
Returns the type of the current query being executed as a string. The values are the string versions of the query types defined in query_classifier.h are separated by vertical bars (|). This function can only be called from the routeQuery entry point.

Example Configuration and Script

Here is a minimal configuration entry for a luafilter definition.

And here is a script that opens a file in /tmp/ and logs output to it.

_{This page is licensed: CC BY-SA / Gnu FDL}

MaxScale 21.06 Cache

This filter was introduced in MariaDB MaxScale 2.1.

Cache

Overview

From MaxScale version 2.2.11 onwards, the cache filter is no longer considered experimental. The following changes to the default behaviour have also been made:

The default value of cached_data is now thread_specific (used to beshared).
The default value of selects is now assume_cacheable (used to beverify_cacheable).

The cache filter is a simple cache that is capable of caching the result of SELECTs, so that subsequent identical SELECTs are served directly by MaxScale, without the queries being routed to any server.

By default the cache will be used and populated in the following circumstances:

There is no explicit transaction active, that is, autocommit is used,
there is an explicitly read-only transaction (that is,START TRANSACTION READ ONLY) active, or
there is a transaction active and no statement that modifies the database has been performed.

In practice, the last bullet point basically means that if a transaction has been started with BEGIN, START TRANSACTION or START TRANSACTION READ WRITE, then the cache will be used and populated until the first UPDATE,INSERT or DELETE statement is encountered.

That is, in default mode the cache effectively causes the system to behave as if the isolation level would be READ COMMITTED, irrespective of what the isolation level of the backends actually is.

The default behaviour can be altered using the configuration parameter .

By default it is assumed that all SELECT statements are cacheable, which means that also statements like SELECT LOCALTIME are cached. Please check for how to change the default behaviour.

Limitations

All of these limitations may be addressed in forthcoming releases.

Prepared Statements

Resultsets of prepared statements are not cached.

Multi-statements

Multi-statements are always sent to the backend and their result isnot cached.

Security

The cache is not aware of grants.

The implication is that unless the cache has been explicitly configured who the caching should apply to, the presence of the cache may provide a user with access to data he should not have access to.

Please read the section for more detailed information.

However, from 2.5 onwards it is possible to configure the cache to cache the data of each user separately, which effectively means that there can be no unintended sharing. Please see for how to change the default behaviour.

`information_schema`

When is enabled, SELECTs targeting tables in information_schema are not cached. The reason is that as the content of the tables changes as the side-effect of something else, the cache would not know when to invalidate the cache-entries.

Invalidation

Since MaxScale 2.5, the cache is capable of invalidating entries in the cache when a modification (UPDATE, INSERT or DELETE) that may affect those entries is made.

The cache invalidation works on the table-level, that is, a modification made to a particular table will cause all cache entries that refer to that table to be invalidated, irrespective of whether the modification actually has an impact on the cache entries or not. For instance, suppose the result of the following SELECT has been cached

An insert like

will cause the cache entry containing the result of that SELECT to be invalidated even if the INSERT actually does not affect it. Please see for how to enable the invalidation.

When invalidation has been enabled MaxScale must be able to completely parse a SELECT statement for its results to be stored in the cache. The reason is that in order to be able to invalidate cache entries, MaxScale must know what tables a SELECT statement depends upon. Consequently, if (and only if) invalidation has been enabled and MaxScale fails to parse a statement, the result of that particular statement will not be cached.

When invalidation has been enabled, MaxScale will also parse all UPDATE, INSERT and DELETE statements, in order to find out what tables are modified. If that parsing fails, MaxScale will by default clear the entire cache. The reason is that unless MaxScale can completely parse the statement it cannot know what tables are modified and hence not what cache entries should be invalidated. Consequently, to prevent stale data from being returned, the entire cache is cleared. The default behaviour can be changed using the configuration parameter .

Note that what threading approach is used has a big impact on the invalidation. Please see for how the threading approach affects the invalidation.

Note also that since the invalidation may not, depending on how the cache has been configured, be visible to all sessions of all users, it is still important to configure a reasonable and TTL.

Best Efforts

The invalidation offered by the MaxScale cache can be said to be of_best efforts_ quality. The reason is that in order to ensure that the cache in all circumstances reflects the state in the actual database, would require that the operations involving the cache and the MariaDB server are synchronized, which would cause an unacceptable overhead.

What best efforts means in this context is best illustrated using an example.

Suppose a client executes the statement SELECT * FROM tbl and that the result is cached. Next time that or any other client executes the same statement, the result is returned from the cache and the MariaDB server will not be accessed at all.

If a client now executes the statement INSERT INTO tbl VALUES (...), the cached value for the SELECT statement above and all other statements that are dependent upon tbl will be invalidated. That is, the next time someone executes the statement SELECT * FROM tbl the result will again be fetched from the MariaDB server and stored to the cache.

However, suppose some client executes the statement SELECT COUNT(*) FROM tbl at the same time someone else executes the INSERT ... statement. A possible chain of events is as follows:

That is, the SELECT is performed in the database server before theINSERT. However, since the timelines are proceeding independently of each other, the events may be re-ordered as far as the cache is concerned.

That is, the cached value for SELECT COUNT(*) FROM tbl will reflect the situation before the insert and will thus not be correct.

The stale result will be returned until the value has reached its time-to-live or its invalidation is caused by some update operation.

Configuration

The cache is simple to add to any existing service. However, some experimentation may be required in order to find the configuration settings that provide the maximum benefit.

Each configured cache filter uses a storage of its own. That is, if there are two services, each configured with a specific cache filter, then, even if queries target the very same servers the cached data will not be shared.

Two services can use the same cache filter, but then either the services should use the very same servers or a completely different set of servers, where the used table names are different. Otherwise there can be unintended sharing.

Filter Parameters

The cache filter has no mandatory parameters but a range of optional ones. Note that it is advisable to specify max_size to prevent the cache from using up all memory there is, in case there is very little overlap among the queries.

storage

Type: string
Mandatory: No
Dynamic: No
Default: storage_inmemory

The name of the module that provides the storage for the cache. That module will be loaded and provided with the value of storage_options as argument. For instance:

See for what storage modules are available.

storage_options

Type: string
Mandatory: No
Dynamic: No
Default:

A string that is provided verbatim to the storage module specified in storage, when the module is loaded. Note that the needed arguments and their format depend upon the specific module.

hard_ttl

Type:
Mandatory: No
Dynamic: No
Default: 0s

Hard time to live; the maximum amount of time the cached result is used before it is discarded and the result is fetched from the backend (and cached). See also soft_ttl below.

soft_ttl

Type:
Mandatory: No
Dynamic: No
Default: 0s

Soft time to live; the amount of time - in seconds - the cached result is used before it is refreshed from the server. When soft_ttl has passed, the result will be refreshed when the first client requests the value.

However, as long as hard_ttl has not passed, all other clients requesting the same value will use the result from the cache while it is being fetched from the backend. That is, as long as soft_ttl but not hard_ttl has passed, even if several clients request the same value at the same time, there will be just one request to the backend.

The default value is 0, which means no limit. If the value of soft_ttl is larger than hard_ttl it will be adjusted down to the same value.

max_resultset_rows

Type: count
Mandatory: No
Dynamic: No
Default: 0

Specifies the maximum number of rows a resultset can have in order to be stored in the cache. A resultset larger than this, will not be stored.

max_resultset_size

Type:
Mandatory: No
Dynamic: No
Default: 0

Specifies the maximum size of a resultset, for it to be stored in the cache. A resultset larger than this, will not be stored. The size can be specified as described .

Note that the value of max_resultset_size should not be larger than the value of max_size.

max_count

Type: count
Mandatory: No
Dynamic: No
Default: 0

The maximum number of items the cache may contain. If the limit has been reached and a new item should be stored, then an older item will be evicted.

Note that if cached_data is thread_specific then this limit will be applied to each cache separately. That is, if a thread specific cache is used, then the total number of cached items is #threads * the value of max_count.

max_size

Type:
Mandatory: No
Dynamic: No
Default: 0

The maximum size the cache may occupy. If the limit has been reached and a new item should be stored, then some older item(s) will be evicted to make space. The size can be specified as described .

Note that if cached_data is thread_specific then this limit will be applied to each cache separately. That is, if a thread specific cache is used, then the total size is #threads * the value of max_size.

rules

Type: path
Mandatory: No
Dynamic: No
Default: ""

Specifies the path of the file where the caching rules are stored. A relative path is interpreted relative to the data directory of MariaDB MaxScale.

cached_data

Type:
Mandatory: No
Dynamic: No
Values: shared

An enumeration option specifying how data is shared between threads. The allowed values are:

shared: The cached data is shared between threads. On the one hand it implies that there will be synchronization between threads, on the other hand that all threads will use data fetched by any thread.
thread_specific: The cached data is specific to a thread. On the one hand it implies that no synchronization is needed between threads, on the other hand that the very same data may be fetched and stored multiple times.

Default is thread_specific. See max_count and max_size what implication changing this setting to shared has.

selects

Type:
Mandatory: No
Dynamic: No
Values: assume_cacheable

An enumeration option specifying what approach the cache should take with respect to SELECT statements. The allowed values are:

assume_cacheable: The cache can assume that all SELECT statements, without exceptions, are cacheable.
verify_cacheable: The cache can not assume that all SELECT statements are cacheable, but must verify that.

Default is assume_cacheable. In this case, all SELECT statements are assumed to be cacheable and will be parsed only if some specific rule requires that.

If verify_cacheable is specified, then all SELECT statements will be parsed and only those that are safe for caching - e.g. do not call any non-cacheable functions or access any non-cacheable variables - will be subject to caching.

If verify_cacheable has been specified, the cache will not be used in the following circumstances:

The SELECT uses any of the following functions: BENCHMARK,CONNECTION_ID, CONVERT_TZ, CURDATE, CURRENT_DATE, CURRENT_TIMESTAMP,CURTIME, DATABASE, ENCRYPT

Note that parsing all SELECT statements carries a performance cost. Please read for more details.

cache_in_transactions

Type:
Mandatory: No
Dynamic: No
Values: never

An enumeration option specifying how the cache should behave when there are active transactions:

never: When there is an active transaction, no data will be returned from the cache, but all requests will always be sent to the backend. The cache will be populated inside explicitly read-only transactions. Inside transactions that are not explicitly read-only, the cache will be populated until the first non-SELECT statement.
read_only_transactions: The cache will be used and populated inside explicitly read-only transactions. Inside transactions that are not explicitly read-only, the cache will be populated, but not used until the first non-SELECT statement.

Default is all_transactions.

The values read_only_transactions and all_transactions have roughly the same effect as changing the isolation level of the backend to read_committed.

debug

Type: number
Mandatory: No
Dynamic: No
Default: 0

An integer value, using which the level of debug logging made by the cache can be controlled. The value is actually a bitfield with different bits denoting different logging.

0 (0b00000) No logging is made.
1 (0b00001) A matching rule is logged.

Default is 0. To log everything, give debug a value of 31.

enabled

Type:
Mandatory: No
Dynamic: No
Default: true

Specifies whether the cache is initially enabled or disabled.

Default is true.

The value affects the initial state of the MaxScale user variables using which the behaviour of the cache can be modified at runtime. Please see for details.

invalidate

Type:
Mandatory: No
Dynamic: No
Values: never

An enumeration option specifying how the cache should invalidate cache entries.

The effect of current depends upon the value of cached_data. If the value is shared, that is, all threads share the same cache, then the effect of an invalidation is immediately visible to all sessions, as there is just one cache. However, if the value is thread_specific, then an invalidation will affect only the cache that the session happens to be using.

If it is important and sufficient that an application immediately sees a change that it itself has caused, then a combination of invalidate=current and cached_data=thread_specific can be used.

If it is important that an application immediately sees all changes, irrespective of who has caused them, then a combination of invalidate=current and cached_data=shared must be used.

clear_cache_on_parse_errors

Type:
Mandatory: No
Dynamic: No
Default: true

This boolean option specifies how the cache should behave in case of parsing errors when invalidation has been enabled.

true: If the cache fails to parse an UPDATE/INSERT/DELETE statement then all cached data will be cleared.
false: A failure to parse an UPDATE/INSERT/DELETE statement is ignored and no invalidation will take place due that statement.

The default value is true.

Changing the value to false may mean that stale data is returned from the cache, if an UPDATE/INSERT/DELETE cannot be parsed and the statement affects entries in the cache.

users

Type:
Mandatory: No
Dynamic: No
Values: mixed

An enumeration option specifying how the cache should cache data for different users.

Note that if isolated has been specified, then each user will conceptually have a cache of his own, which is populated independently from each other. That is, if two users make the same query, then the data will be fetched twice and also stored twice. So, a isolated cache will in general use more memory and cause more traffic to the backend compared to a mixed cache.

timeout

Type:
Mandatory: No
Dynamic: No
Default: 5s

The timeout used when performing operations to distributed storages such as redis or memcached.

The default value is 5000ms, that is 5 seconds.

The duration can be specified as explained .

Runtime Configuration

The cache filter can be configured at runtime by executing SQL commands. If there is more than one cache filter in a service, only the first cache filter will be able to process the variables. The remaining filters will not see them and thus configuring them at runtime is not possible.

@maxscale.cache.populate

Using the variable @maxscale.cache.populate it is possible to specify at runtime whether the cache should be populated or not. Its initial value is the value of the configuration parameter enabled. That is, by default the value is true.

The purpose of this variable is make it possible for an application to decide statement by statement whether the cache should be populated.

In the example above, the first SELECT will always be sent to the server and the result will be cached, provided the actual cache rules specifies that it should be. The second SELECT may be served from the cache, depending on the value of @maxscale.cache.use (and the cache rules).

The value of @maxscale.cache.populate can be queried

but only after it has been explicitly set once.

@maxscale.cache.use

Using the variable @maxscale.cache.use it is possible to specify at runtime whether the cache should be used or not. Its initial value is the value of the configuration parameter enabled. That is, by default the value is true.

The purpose of this variable is make it possible for an application to decide statement by statement whether the cache should be used.

The first SELECT will be served from the cache, providing the rules specify that the statement should be cached, the cache indeed contains the result and the date is not stale (as specified by the TTL).

If the data is stale, the SELECT will be sent to the server and the cache entry will be updated, irrespective of the value of@maxscale.cache.populate.

If @maxscale.cache.use is true but the result is not found in the cache, and the result is subsequently fetched from the server, the result will not be added to the cache, unless@maxscale.cache.populate is also true.

The value of @maxscale.cache.use can be queried

but only after it has explicitly been set once.

@maxscale.cache.soft_ttl

Using the variable @maxscale.cache.soft_ttl it is possible at runtime to specify in seconds what soft ttl should be applied. Its initial value is the value of the configuration parameter soft_ttl. That is, by default the value is 0.

The purpose of this variable is make it possible for an application to decide statement by statement what soft ttl should be applied.

When data is SELECTed from the unimportant table unimportant, the data will be returned from the cache provided it is no older than 10 minutes, but when data is SELECTed from the important table important, the data will be returned from the cache provided it is no older than 1 minute.

Note that @maxscale.cache.hard_ttl overrules @maxscale.cache.soft_ttl in the sense that if the former is less that the latter, then soft ttl will, when used, be adjusted down to the value of hard ttl.

The value of @maxscale.cache.soft_ttl can be queried

but only after it has explicitly been set once.

@maxscale.cache.hard_ttl

Using the variable @maxscale.cache.hard_ttl it is possible at runtime to specify in seconds what hard ttl should be applied. Its initial value is the value of the configuration parameter hard_ttl. That is, by default the value is 0.

The purpose of this variable is make it possible for an application to decide statement by statement what hard ttl should be applied.

Note that as @maxscale.cache.hard_ttl overrules @maxscale.cache.soft_ttl, is is important to ensure that the former is at least as large as the latter and for best overall performance that it is larger.

The value of @maxscale.cache.hard_ttl can be queried

but only after it has explicitly been set once.

Client Driven Caching

With @maxscale.cache.populate and @maxscale.cache.use is it possible to make the caching completely client driven.

Provide no rules file, which means that all SELECT statements are subject to caching and that all users receive data from the cache. Set the startup mode of the cache to disabled.

Now, in order to mark statements that should be cached, set@maxscale.cache.populate to true, and perform those SELECTs.

Note that those SELECTs must return something in order for the statement to be marked for caching.

After this, the value of @maxscale.cache.use will decide whether or not the cache is considered.

With @maxscale.cache.use being true, the cache is considered and the result returned from there, if not stale. If it is stale, the result is fetched from the server and the cached entry is updated.

By setting a very long TTL it is possible to prevent the cache from ever considering an entry to be stale and instead manually cause the cache to be updated when needed.

Threads, Users and Invalidation

What caching approach is used and how different users are treated has a significant impact on the behaviour of the cache. In the following the implication of different combinations is explained.

cached_data/users

mixed

isolated

Invalidation

Invalidation takes place only in the current cache, so how visible the invalidation is, depends upon the configuration value ofcached_data.

cached_data=thread_specific

The invalidation is visible only to the sessions that are handled by the same worker thread where the invalidation occurred. Sessions of the same or other users that are handled by different worker threads will not see the new value before the TTL causes the value to be refreshed.

cache_data=shared

The invalidation is immediately visible to all sessions of all users.

Rules

The caching rules are expressed as a JSON object or as an array of JSON objects.

There are two decisions to be made regarding the caching; in what circumstances should data be stored to the cache and in what circumstances should the data in the cache be used.

Expressed in JSON this looks as follows

or, in case an array is used, as

The store field specifies in what circumstances data should be stored to the cache and the use field specifies in what circumstances the data in the cache should be used. In both cases, the value is a JSON array containing objects.

If an array of rule objects is specified, then, when looking for a rule that matches, the store field of each object are evaluated in sequential order until a match is found. Then, the use field of that object is used when deciding whether data in the cache should be used.

When to Store

By default, if no rules file have been provided or if the store field is missing from the object, the results of all queries will be stored to the cache, subject to max_resultset_rows and max_resultset_size cache filter parameters.

By providing a store field in the JSON object, the decision whether to store the result of a particular query to the cache can be controlled in a more detailed manner. The decision to cache the results of a query can depend upon

the database,
the table,
the column, or
the query itself.

Each entry in the store array is an object containing three fields,

where,

the attribute can be database, table, column or query,
the op can be =, !=, like

If op is = or != then value is used as a string; if it is like or unlike, then value is interpreted as a pcre2 regular expression. Note though that if attribute is database, table or column, then the string is interpreted as a name, where a dot . denotes qualification or scoping.

The objects in the store array are processed in order. If the result of a comparison is true, no further processing will be made and the result of the query in question will be stored to the cache.

If the result of the comparison is false, then the next object is processed. The process continues until the array is exhausted. If there is no match, then the result of the query is not stored to the cache.

Note that as the query itself is used as the key, although the following queries

and

target the same table and produce the same results, they will be cached separately. The same holds for queries like

and

as well. Although they conceptually are identical, there will be two cache entries.

Note that if a column has been specified in a rule, then a statement will match irrespective of where that particular column appears. For instance, if a rule specifies that the result of statements referring to the column a should be cached, then the following statement will match

and so will

Qualified Names

When using = or != in the rule object in conjunction with database,table and column, the provided string is interpreted as a name, that is, dot (.) denotes qualification or scope.

In practice that means that if attribute is database then value may not contain a dot, if attribute is table then value may contain one dot, used for separating the database and table names respectively, and if attribute is column then value may contain one or two dots, used for separating table and column names, or database, table and column names.

Note that if a qualified name is used as a value, then all parts of the name must be available for a match. Currently Maria DB MaxScale may not always be capable of deducing in what table a particular column is. If that is the case, then a value like tbl.field may not necessarily be a match even if the field is field and the table actually is tbl.

Implication of the default database

If the rules concerns the database, then only if the statement refers to no specific database, will the default database be considered.

Regexp Matching

The string used for matching the regular expression contains as much information as there is available. For instance, in a situation like

the string matched against the regular expression will be somedb.tbl.fld.

Examples

Cache all queries targeting a particular database.

Cache all queries not targeting a particular table

That will exclude queries targeting table tbl1 irrespective of which database it is in. To exclude a table in a particular database, specify the table name using a qualified name.

Cache all queries containing a WHERE clause

Note that will actually cause all queries that contain WHERE anywhere, to be cached.

When to Use

By default, if no rules file have been provided or if the use field is missing from the object, all users may be returned data from the cache.

By providing a use field in the JSON object, the decision whether to use data from the cache can be controlled in a more detailed manner. The decision to use data from the cache can depend upon

the user.

Each entry in the use array is an object containing three fields,

where,

the attribute can be user,
the op can be =, !=, like or unlike, and

If op is = or != then value is interpreted as a MariaDB account string, that is, % means indicates wildcard, but if op is like orunlike it is simply assumed value is a pcre2 regular expression.

For instance, the following are equivalent:

Note that if op is = or != then the usual assumptions apply, that is, a value of bob is equivalent with 'bob'@'%'. If like or unlike is used, then no assumptions apply, but the string is used verbatim as a regular expression.

The objects in the use array are processed in order. If the result of a comparison is true, no further processing will be made and the data in the cache will be used, subject to the value of ttl.

If the result of the comparison is false, then the next object is processed. The process continues until the array is exhausted. If there is no match, then data in the cache will not be used.

Note that use is relevant only if the query is subject to caching, that is, if all queries are cached or if a query matches a particular rule in the store array.

Examples

Use data from the cache for all users except admin (actually 'admin'@'%'), regardless of what host the admin user comes from.

Security

As the cache is not aware of grants, unless the cache has been explicitly configured who the caching should apply to, the presence of the cache may provide a user with access to data he should not have access to. Note that the following applies only if users=mixed has been configured. If users=isolated has been configured, then there can never be any unintended sharing between users.

Suppose there is a table access that the user alice has access to, but the user bob does not. If bob tries to access the table, he will get an error as reply:

If we now setup caching for the table, using the simplest possible rules file, bob will get access to data from the table, provided he executes a select identical with one alice has executed.

For instance, suppose the rules look as follows:

If alice now queries the table, she will get the result, which also will be cached:

If bob now executes the very same query, and the result is still in the cache, it will be returned to him.

That can be prevented, by explicitly declaring in the rules that the caching should be applied to alice only.

With these rules in place, bob is again denied access, since queries targeting the table access will in his case not be served from the cache.

Storage

There are two types of storages that can be used; local and shared.

The only local storage implementation is storage_inmemory that simply stores the cache values in memory. The storage is not persistent and is destroyed when MaxScale terminates. Since the storage exists in the MaxScale process, it is very fast and provides almost always a performance benefit.

Currently there are two shared storages; storage_memcached andstorage_redis that are implemented using and respectively.

The shared storages are accessed across the network and consequently it is_not_ self-evident that their use will provide any performance benefit. Namely, irrespective of whether the data is fetched from the cache or from the server there will be a network hop and often that network hop is, as far as the performance goes, what costs the most.

The presence of a shared cache may provide a performance benefit_if the network between MaxScale and the storage server (memcached or_ &#xNAN;Redis) is faster than the network between MaxScale and the database &#xNAN;server, if the used SELECT statements are heavy (that is, take a significant amount of time) to process for the database server, or

if the presence of the cache reduces the overall load of an otherwise overloaded database server.

As a general rule a shared storage should not be used without first assessing its value using a realistic workload.

`storage_inmemory`

This simple storage module uses the standard memory allocator for storing the cached data.

This storage module takes no arguments.

`storage_memcached`

This storage module uses for storing the cached data.

Multiple MaxScale instances can share the same memcached server and items cached by one MaxScale instance will be used by the other. Note that all MaxScale instances should have exactly the same configuration, as otherwise there can be unintended sharing.

storage_memcache has the following mandatory arguments:

server using which the location of the server is specified as host[:port]. If no port is provided, the default Memcached port of 11211 is used.

storage_memcached has the following optional arguments:

max_value_size using which the maximum size of a cached value is specified. By default, the maximum size of a value stored to memcached is 1MB, but that configured to be something else. The value of max_value_size will be used for capping max_resultset_size, that is, unless memcached is configured to allow larger values that 1M and max_value_size has been set accordingly, only resultsets up to 1MB in size will be cached. The value can be specified as documented .

Example:

Limitations

Invalidation is not supported.
Configuration values given to max_size and max_count are ignored.

Security

Neither the data in the memcached server nor the traffic between MaxScale and the memcached server is encrypted. Consequently, anybody with access to the memcached server or to the network have access to the cached data.

`storage_redis`

This storage module uses for storing the cached data.

Multiple MaxScale instances can share the same redis server and items cached by one MaxScale instance will be used by the other. Note that all MaxScale instances should have exactly the same configuration, as otherwise there can be unintended sharing.

If storage_redis cannot connect to the Redis server, caching will silently be disabled and a connection attempt will be made after a interval.

If a timeout error occurs during an operation, reconnecting will be attempted after a delay, which will be an increasing multiple of timeout. For example, if timeout is the default 5 seconds, then reconnection attempts will first be made after 10 seconds, then after 15 seconds, then 20 and so on. However, once 60 seconds have been reached, the delay will no longer be increased but the delay will stay at one minute. Note that each time a reconnection attempt is made, unless the reason for the timeout has disappeared, the client will be stalled for timeout seconds.

storage_redis has the following mandatory arguments:

server using which the location of the server is specified as host[:port]. If no port is provided, the default Redis port of 6379 is used.

Example:

Note that Redis should be configured with no idle timeout or with a timeout that is very large. Otherwise MaxScale may have to repeatedly connect to Redis, which will hurt both the functionality and the performance.

Limitations

There is no distinction between soft and hard ttl, but only hard ttl is used.
Configuration values given to max_size and max_count are ignored.

Invalidation

storage_redis supports invalidation, but the caveats documented are of greater significance since also the communication between the cache and the cache storage is asynchronous and takes place over the network.

NOTE If invalidation is turned on after caching has been used (in non-invalidation mode), redis must be flushed as otherwise there will be entries in the cache that will not be affected by the invalidation.

Security

Neither the data in the redis server nor the traffic between MaxScale and the redis server is encrypted. Consequently, anybody with access to the redis server or to the network have access to the cached data.

Example

In the following we define a cache MyCache that uses the cache storage modulestorage_inmemory and whose soft ttl is 30 seconds and whose hard ttl is45 seconds. The cached data is shared between all threads and the maximum size of the cached data is 50 mebibytes. The rules for the cache are in the filecache_rules.json.

Configuration

`cache_rules.json`

The rules specify that the data of the table sbtest should be cached.

Performance

When the cache filter was introduced, the most significant factor affecting the performance of the cache was whether the statements needed to be parsed. Initially, all statements were parsed in order to exclude SELECT statements that use non-cacheable functions, access non-cacheable variables or refer to system or user variables. Later, the default value of the selects parameter was changed to assume_cacheable, to maximize the default performance.

With the default configuration, the cache itself will not cause the statements to be parsed. However, even with assume_cacheable configured, a rule referring specifically to a database, table or column will still cause the statement to be parsed.

For instance, a simple rule like

cannot be fulfilled without parsing the statement.

If the rule is instead expressed using a regular expression

then the statement will not be parsed.

However, when the was introduced, the parsing cost was significantly reduced and currently the cost for parsing and regular expression matching is roughly the same.

In the following is a table with numbers giving a rough picture of the relative cost of different approaches.

In the table, regexp match means that the cacheable statements were picked out using a rule like

while exact match means that the cacheable statements were picked out using a rule like

The exact match rule requires all statements to be parsed.

As the purpose of the test is to illustrate the overhead of different approaches, the rules were formulated so that all SELECT statements would match.

Note that these figures were obtained by running sysbench, MaxScale and the server in the same computer, so they are only indicative.

selects

Rule

qps

For comparison, without caching, the qps is 33.

As can be seen, due to the query classifier cache there is no difference between exact and regex based matching.

Summary

For maximum performance:

Arrange the situation so that the default selects=assume_cacheable can be used, and use no rules.

Otherwise it is mostly a personal preference whether exact or regex based rules are used. However, one should always test with real data and real queries before choosing one over the other.

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB MaxScale 21.06 Filters

MaxScale 21.06 Binlog Filter

MaxScale 21.06 Consistent Critical Read Filter

MaxScale 21.06 Hintfilter

MaxScale 21.06 Insert Stream Filter

hashtagOverview

hashtagFilter Parameters

hashtagDetails of Operation

hashtagEstimating Network Bandwidth Reduction

hashtagExample Configuration

MaxScale 21.06 Maxrows

hashtagOverview

hashtagConfiguration

hashtagFilter Parameters

hashtagExample Configuration

MaxScale 21.06 Named Server Filter

MaxScale 21.06 Regex Filter

hashtagOverview

hashtagConfiguration

hashtagFilter Parameters

hashtagmatch

hashtagoptions

hashtagreplace

hashtagsource

hashtaguser

hashtaglog_file

hashtaglog_trace

hashtagExamples

hashtagExample 1 - Replace MySQL 5.1 create table syntax with that for later versions

MaxScale 21.06 Throttle

hashtagOverview

hashtagConfiguration

hashtagBasic Configuration

hashtagAllowing high frequency bursts

hashtagFilter Parameters

hashtagsampling_duration

hashtagcontinuous_duration

MaxScale 21.06 Transaction Performance Monitoring Filter

hashtagOverview

hashtagConfiguration

hashtagFilter Options

hashtagFilter Parameters

hashtagFilename

hashtagSource

hashtagUser

hashtagDelimiter

hashtagQuery_delimiter

hashtagNamed_pipe

hashtagLog Output Format

hashtagExamples

hashtagExample 1 - Log Transactions for Performance Analysis

MariaDB MaxScale 21.06 Filters

MaxScale 21.06 Insert Stream Filter

hashtagOverview

hashtagFilter Parameters

hashtagDetails of Operation

hashtagEstimating Network Bandwidth Reduction

hashtagExample Configuration

MaxScale 21.06 Binlog Filter

hashtagOverview

hashtagConfiguration

hashtagmatch

hashtagexclude

hashtagrewrite_src

hashtagrewrite_dest

hashtagExample Configuration

MaxScale 21.06 Consistent Critical Read Filter

hashtagOverview

hashtagControlling the Filter with SQL Comments

hashtagFilter Parameters

hashtagtime

hashtagcount

hashtagmatch, ignore

hashtagoptions

hashtagglobal

hashtagExample Configuration

MaxScale 21.06 Hintfilter

hashtagHint Syntax

hashtagComments and comment types

hashtagHint body

Overview

Filter Parameters

Details of Operation

Estimating Network Bandwidth Reduction

Example Configuration

Overview

Configuration

Filter Parameters

Example Configuration

Overview

Configuration

Filter Parameters

`match`

`options`

`replace`

`source`

`user`

`log_file`

`log_trace`

Examples

Example 1 - Replace MySQL 5.1 create table syntax with that for later versions

Overview

Configuration

Basic Configuration

Allowing high frequency bursts

Filter Parameters

`sampling_duration`

`continuous_duration`

Overview

Configuration

Filter Options

Filter Parameters

Filename

Source

User

Delimiter

Query_delimiter

Named_pipe

Log Output Format

Examples

Example 1 - Log Transactions for Performance Analysis

Overview

Filter Parameters

Details of Operation

Estimating Network Bandwidth Reduction

Example Configuration

Overview

Configuration

`match`

`exclude`

`rewrite_src`

`rewrite_dest`

Example Configuration

Overview

Controlling the Filter with SQL Comments

Filter Parameters

`time`

`count`

`match`, `ignore`

`options`

`global`

Example Configuration

Hint Syntax

Comments and comment types

Hint body

Routing destination hints

Hint stack

Prepared Statements

Binary Protocol

Text Protocol

Examples

Routing `SELECT` queries to master

Overview

Configuration

Filter Parameters

Example Configuration

Overview

Configuration

Basic Configuration

Allowing high frequency bursts