Proxy TCP stream (MySQL and Redis) with Nginx

Question

I read about Nginx Fabric Model and it brings my attention to reconfigure how an application communicates to MySQL and Redis. If local Nginx instance can proxy HTTP traffic efficiently and fast, now it can also proxy TCP without worrying about the network, even using database slave as a master in case of emergency and potentially encapsulate database sharding. All the benefits can simplify application configuration and its logic, network (congestion, latency, timeouts, retries) won't be a focus in features development anymore.

I use latest Docker and set of containers: Nginx, Redis, MySQL. I tried the following configuration:

user  nginx;
worker_processes  1;

error_log  /var/log/nginx/error.log info;
pid        /var/run/nginx.pid;


events {
  worker_connections  1024;
}

stream {
  upstream redis {
    # prefer first server but limit connections
    server 172.17.0.8:6379 weight=2 max_conns=1;
    server 172.17.0.3:6379;
  }

  upstream mysql {
    # use second server in case of failure
    server 172.17.0.4:3306;
    server 172.17.0.5:3306 backup;
  }

  server {
    listen 6379 so_keepalive=on;
    proxy_pass redis;
  }

  server {
    listen 3306 so_keepalive=on;
    proxy_pass mysql;
  }
}

I have some questions:

• logging - how can I know which endpoint is in use, how many times did Nginx retry a particular request?

• real-time stats - is it possible to get throughput for stream module?

• from database sharding perspective - is it possible to dispatch request to a sharded database based on some logic apart from $remote_addr?

The last question is quite important, I found modules ngx_stream_map_module and ngx_stream_split_clients_module but $remote_addr is not suitable for sharding, can we intercept cookie from HTTP section and reuse in-stream section where we don't have any headers? Can we inject the Lua code in the stream section? Is ngx_stream_ssl_preread_module a solution for this problem, how to make it work for connection without encryption?

Answer 1

I do not think Nginx can be used for what you're trying to use it for. While it can be used to proxy tcp streams or load balance them, it's not necessarily knowledgable of the protocol and request structure within them. So to answer your question:

from database sharding perspective - is it possible to dispatch request to sharded database based on some logic apart from $remote_addr?

Not really. It can't route different Redis requests or MySQL queries from a single connection to different servers based on their content, as those things, to nginx, are just a stream which it cannot read. It assigns it a destination during the stream initiation and makes sure that all packets back and forth go to the same destinations, and that's about it.

As an example, in the MySQL sharding article you linked, it executing the mysql command prompt to run a single query and check the node name. Each time it's run, it makes a connection, runs the query, and disconnects. So each time it does this, the new connection gets routed to another server by nginx. However, if you were to run the same query twice over the same mysql command prompt instance, it would give you the same node name. It's also worth noting that this is using MySQL Galera, which is a multi-master configuration of MySQL which handles read and write routing within itself, which allows for arbitrary client-side query routing like this. This would not be true of redis.

Also, this does not inherently get rid of all connection handling for your application either. Errors, such as the downstream being unavailable, may still propagate up to the client in many circumstances.

You may be able to use this to simplify configuration for your application, however, it may also just be pushing the complexity into a local nginx config. So that may or may not be an actual benefit.

---

can we intercept cookie from http section and reuse in stream section where we don't have any headers?

Probably not. HTTP headers are not passed through to database connections as they are fundamentally different protocols, so this would not be available to nginx even if it could read the streams.

---

Can we inject Lua code in the stream section?

This is potentially possible, but would require a Lua module that's able to parse the application protocols, such as Redis' and MySQLs wire protocol. But as I mentioned previously, routing of requests within a connection from (even if you could detect and route them in nginx) these is not necessarily simple from a database / consistency standpoint and I wouldn't really recommend doing that.