CODEWITHSUNDEEP
mysqldatabasearchitectureproxy
yegor256
yegor256

Reputation: 105043

proxy in front of mysql for redundancy removal

I'm trying to implement a proxy layer in front of MySQL server, that will catch redundant SQL queries and send them only once to the server. In other words, I have many clients (in PHP, Perl, on different web nodes) that talk to the MySQL and very often repeat the same SELECT queries. When traffic goes up MySQL, very often, goes down.

The question is - are you aware of any open source (or commercial) tool that can help? I tried MySQL Proxy, but looks like it can't help.

Upvotes: 0

Views: 307

Answers (2)

PapaMikeDBA
PapaMikeDBA

Reputation: 31

Two suggestions:

  1. MySQL Proxy This is a front end proxy from MySQL which does what you want as far as I know
  2. vtocc From the vitess project, used in the YouTube mysql environment, also does a similar thing. Query consolidation: The ability to reuse the results of an in-flight query to any subsequent requests that were received while the query was still executing.

Upvotes: 3

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44333

You may want to look into HAProxy and how it works.

Here two additional suggestions

SUGGESTION #1 Setup a Cluster

If your data is all InnoDB, you should try Percona XtraDB Cluster and use HAProxy in conjunction with it. You can load balance across all server in the Cluster including the Write Master.

SUGGESTION #2 Setup a Cluster via MySQL Replication to 1 or more DB Servers

Use HAProxy to load balance your reads across the Read Slaves

If you are on a budget and your data is relatively small, setup multiple MySQL Instances on one server

Upvotes: 0

Related Questions