Key design aspects relevant to proxy

  • Table chunking: we break large tables into pieces, e.g. a 50-billion-row table T might be stored as 20,000 tables: T_0001, T_0002, ... t_20000, across 100s of worker nodes.
  • Distributed system: we have 100s of worker nodes. Table chunks are distributed across the nodes. Each worker node run a single mysqld and is responsible for processing its chunks locally.

mysql-proxy

  • mysql-proxy is used as a front-end between our system and advanced users.
  • The proxy parses each query, and
    • disallows certain queries and return. (INSERT, UPDATE, LOAD, CREATE, ALTER, TRUNCATE, DROP)
    • redirects certain queries to a "local" mysql instance and return result. (SELECT @@VERSION_COMMENT LIMIT, SELECT @@SESSION.AUTO_INCREMENT_INCREMENT, ^SHOW, ^SET,  ^DESCRIBE, ^DESC, ^SELECT CURRENT_USER())
    • bounces certain queries back with "not supported" message (EXPLAIN, GRANT, FLUSH)
    • sends all other queries to "czar" through xmlrpc and handle the queries there. The czar will process a query, and return the result in a table (there is one "result" table for each user query). Note that proxy is detecting special keywords in the WHERE clause, such as "QSERV_AREASPEC_BOX", "OBJECTID=" and sends them along with the query to czar.
  • Proxy can also handle query killing

proxy-czar

  • We use a special "lock" tables (one per user query to communicate with mysql-proxy when the results are available.
  • Relevant code: proxy.py

czar

  • Czar has its own parser (antlr-based). If it detects a query that involves a chunked table, it rewrites such query into per-chunk queries (e.g. SELECT * FROM T submitted by user would become "SELECT * FROM T_0001", "SELECT * FROM T_0002", ...". It will then dispatch these queries to worker nodes. Results from each chunk-query are sent back from each worker to czar, and stored in a single "result" table.
  • When results are collected from all chunk queries, the "result" table is unlocked, and proxy takes over, and returns the result to user.  

Fault-tolerance and proxy

  • Currently, we rely on a single proxy, but we envision having multiple proxies in the future for performance, load-balancing, and fault tolerance purposes
  • We have two type of queries: 
    • interactive - these are very short, if something fails, query dies and user is expected to re-run the query
    • long-running - we plan to return a "globally unique query id", which user can use to check on status, fetch results from known location etc. If a proxy or any other part of our system dies during long-running query, user can still get the query status and results through other proxy.
  • No labels