hostgroups, query rules and temporary tables #941

Open
ethaniel opened this Issue Mar 6, 2017 · 11 comments

Comments

Projects
None yet
2 participants
@ethaniel

ethaniel commented Mar 6, 2017

I have 2 hostgroups: 500 for WRITE, 501 for READ.

I have the following rules:

INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(200,'',500,1,3,'^SELECT.*FOR UPDATE',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(201,'',501,1,3,'^SELECT ',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(202,'',501,1,3,'^CREATE TEMPORARY TABLE ',1);

As you can see, temporary tables are created in the READ group. But I can't find a way how to insert into the temporary table, because my INSERTs and UPDATEs go into the WRITE group.

Perhaps, a modification to proxysql is needed - where it detects temporary table creation and then directs all reads and writes to it through the backend where it was created?

@ethaniel

This comment has been minimized.

Show comment
Hide comment
@ethaniel

ethaniel Mar 6, 2017

Worth mentioning, that if I create the temporary table in the WRITE group, then I won't be able to do SELECTs to it, because they will be directed to the READ group.

ethaniel commented Mar 6, 2017

Worth mentioning, that if I create the temporary table in the WRITE group, then I won't be able to do SELECTs to it, because they will be directed to the READ group.

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Mar 6, 2017

Contributor

I think the problem is in these two rules:

INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(200,'',500,1,3,'^SELECT.*FOR UPDATE',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(201,'',501,1,3,'^SELECT ',1);

The first time I wrote them was to show how routing works, and of course examples should be easy to understand.
The problem is that examples has become a standard for routing, and this should not be the case.

The common configuration procedure (or guideline) should be the following:

  • configure ProxySQL to send all the traffic to the master
  • check in stats_mysql_query_digest which are the most expensive SELECT statements;
  • determine which expensive statements should be moved to reader nodes;
  • configure mysql_query_rules to send only expensive SELECT to the readers.

The idea is therefore quite simple: send to slaves/readers only what you want to send to slaves, not just any SELECT.

Contributor

renecannao commented Mar 6, 2017

I think the problem is in these two rules:

INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(200,'',500,1,3,'^SELECT.*FOR UPDATE',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(201,'',501,1,3,'^SELECT ',1);

The first time I wrote them was to show how routing works, and of course examples should be easy to understand.
The problem is that examples has become a standard for routing, and this should not be the case.

The common configuration procedure (or guideline) should be the following:

  • configure ProxySQL to send all the traffic to the master
  • check in stats_mysql_query_digest which are the most expensive SELECT statements;
  • determine which expensive statements should be moved to reader nodes;
  • configure mysql_query_rules to send only expensive SELECT to the readers.

The idea is therefore quite simple: send to slaves/readers only what you want to send to slaves, not just any SELECT.

@ethaniel

This comment has been minimized.

Show comment
Hide comment
@ethaniel

ethaniel Mar 6, 2017

I have tons of heavy queries and my Percona cluster is in full sync mode (wsrep_sync_wait=1). My system automatically replays deadlock queries (and I have probably only 1 deadlock query once per week). Proxysql helped me even out the load. So I prefer SELECTs on all servers.

Anyways.

I found a dirty hack. It sends all queries that start with a comment /* TEMP */ to the READ hostgroup.

INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(202,'',501,1,3,'^/* TEMP */',1);

I prepend all my queries that have to work with temporary tables with /* TEMP */. And told Proxysql to direct such requests to the READ group.

So now my queries look like this:

/* TEMP */ CREATE TEMPORARY TABLE `upd` (`user_id`....
/* TEMP */ INSERT INTO `upd` (user_id, .....
/* TEMP */ SELECT count(DISTINCT `user_id`) FROM upd;

Let's keep this ticket alive for anyone googling this same problem.

But still, it would be awesome if Proxysql could detect temporary tables automatically and lock all queries to them to a specific backend.

ethaniel commented Mar 6, 2017

I have tons of heavy queries and my Percona cluster is in full sync mode (wsrep_sync_wait=1). My system automatically replays deadlock queries (and I have probably only 1 deadlock query once per week). Proxysql helped me even out the load. So I prefer SELECTs on all servers.

Anyways.

I found a dirty hack. It sends all queries that start with a comment /* TEMP */ to the READ hostgroup.

INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(202,'',501,1,3,'^/* TEMP */',1);

I prepend all my queries that have to work with temporary tables with /* TEMP */. And told Proxysql to direct such requests to the READ group.

So now my queries look like this:

/* TEMP */ CREATE TEMPORARY TABLE `upd` (`user_id`....
/* TEMP */ INSERT INTO `upd` (user_id, .....
/* TEMP */ SELECT count(DISTINCT `user_id`) FROM upd;

Let's keep this ticket alive for anyone googling this same problem.

But still, it would be awesome if Proxysql could detect temporary tables automatically and lock all queries to them to a specific backend.

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Mar 6, 2017

Contributor

Not sure if I would call it a dirty hack, of the right approach :)
In another ticket you mentioned a temporary table upd : what about sending all queries for that table to a node (master or slave) creating rules that much any of the follow?

CREATE TEMPORARY TABLE `upd`
INSERT INTO `upd`
SELECT .* FROM upd
Contributor

renecannao commented Mar 6, 2017

Not sure if I would call it a dirty hack, of the right approach :)
In another ticket you mentioned a temporary table upd : what about sending all queries for that table to a node (master or slave) creating rules that much any of the follow?

CREATE TEMPORARY TABLE `upd`
INSERT INTO `upd`
SELECT .* FROM upd
@ethaniel

This comment has been minimized.

Show comment
Hide comment
@ethaniel

ethaniel Mar 6, 2017

True, but using /* TEMP */ approach is easier because if I have many temporary tables, I wouldn't need to create rules for each of them.

ethaniel commented Mar 6, 2017

True, but using /* TEMP */ approach is easier because if I have many temporary tables, I wouldn't need to create rules for each of them.

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Mar 6, 2017

Contributor

it would be awesome if Proxysql could detect temporary tables automatically and lock all queries to them to a specific backend.

For this to happen, ProxySQL needs to fully parse the query. This is not in the short term roadmap, but it will happen

Contributor

renecannao commented Mar 6, 2017

it would be awesome if Proxysql could detect temporary tables automatically and lock all queries to them to a specific backend.

For this to happen, ProxySQL needs to fully parse the query. This is not in the short term roadmap, but it will happen

@ethaniel

This comment has been minimized.

Show comment
Hide comment
@ethaniel

ethaniel Mar 6, 2017

Thank you for the quick responses. You're awesome :)

ethaniel commented Mar 6, 2017

Thank you for the quick responses. You're awesome :)

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Mar 6, 2017

Contributor

Thanks :)

Contributor

renecannao commented Mar 6, 2017

Thanks :)

@ethaniel

This comment has been minimized.

Show comment
Hide comment
@ethaniel

ethaniel Mar 7, 2017

Weird. Looks like I can CREATE a table, INSERT into it, but not SELECT.

/* TEMP */ CREATE TEMPORARY TABLE `upd` (`user_id` ... - OK
/* TEMP */ INSERT INTO `upd` (user_id ... - OK
/* TEMP */ SELECT * FROM `upd`; - Table 'gallery.upd' doesn't exist
/* TEMP */ INSERT INTO `upd` (user_id ... - OK

Here are my query rules:

DELETE FROM mysql_query_rules;
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(200,'',500,1,3,'^SELECT.*FOR UPDATE',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(201,'',501,1,3,'^SELECT ',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(202,'',501,1,3,'^/* TEMP */',1);

Any ideas what could be wrong?

ethaniel commented Mar 7, 2017

Weird. Looks like I can CREATE a table, INSERT into it, but not SELECT.

/* TEMP */ CREATE TEMPORARY TABLE `upd` (`user_id` ... - OK
/* TEMP */ INSERT INTO `upd` (user_id ... - OK
/* TEMP */ SELECT * FROM `upd`; - Table 'gallery.upd' doesn't exist
/* TEMP */ INSERT INTO `upd` (user_id ... - OK

Here are my query rules:

DELETE FROM mysql_query_rules;
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(200,'',500,1,3,'^SELECT.*FOR UPDATE',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(201,'',501,1,3,'^SELECT ',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) VALUES(202,'',501,1,3,'^/* TEMP */',1);

Any ideas what could be wrong?

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Mar 7, 2017

Contributor

Comments are stripped from the digest, therefore you should use match_pattern instead of match_digest.
Try this

Contributor

renecannao commented Mar 7, 2017

Comments are stripped from the digest, therefore you should use match_pattern instead of match_digest.
Try this

@ethaniel

This comment has been minimized.

Show comment
Hide comment
@ethaniel

ethaniel Mar 7, 2017

It works!!!

Here are my new query rules.

DELETE FROM mysql_query_rules;
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_pattern,apply) VALUES (200,'',500,1,3,'^SELECT.*FOR UPDATE',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_pattern,apply) VALUES (201,'',501,1,3,'^SELECT ',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_pattern,apply) VALUES (202,'',501,1,3,'^/* TEMP */',1);

Thank you!

ethaniel commented Mar 7, 2017

It works!!!

Here are my new query rules.

DELETE FROM mysql_query_rules;
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_pattern,apply) VALUES (200,'',500,1,3,'^SELECT.*FOR UPDATE',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_pattern,apply) VALUES (201,'',501,1,3,'^SELECT ',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_pattern,apply) VALUES (202,'',501,1,3,'^/* TEMP */',1);

Thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment