MySQL Proxy FAQ
Contents |
MySQL Proxy Frequently Asked Questions
Frequently asked questions about MySQL Proxy
Compiling and installing
General features
In load balancing, how can I separate reads from writes?
Q. Is a backend-server a remote mysql machine ? You would think so, but from another post in this forum, it looks like the separation of reads from writes has not been implemented yet, so I am not clear as to the distinction between backend-servers and read-only servers.
$ mysql-proxy \ --proxy-backend-addresses=10.0.1.2:3306 \ --proxy-backend-addresses=10.0.1.3:3306 &
I don't see how this works for writes, as they kind of have to go one place.
A. Read/Write Splitting is currently available as a Proof of Concept. A whole set of issues have to be resolved, before it works good enough.
Command line options
How do I use a socket with MySQL Proxy?
Q. Proxy change logs say added support for UNIX sockets
How to use it? I can't see any --proxy-socket=</path/to/socket> option in `mysql-proxy --help-proxy` nor in sources.
A. just
--proxy-backend-addresses=/path/to/socket
However it appears that --proxy-address=</path/to/socket> does not work on the front end. It would be nice if someone added this feature.
How do I run the Proxy as a daemon?
Q. Can I run the Proxy as a daemon?
A. Starting from version 0.6.0, the Proxy is launched as a daemon by default.
If you want to avoid this, use the -D --no-daemon option
To keep track of the process ID, the daemon can be started with the additional option --pid-file=<file>, to save the PID to a known file name.
On version 0.5.x, the Proxy can't be started natively as a daemon
Questions from MySQL Proxy Webinar -Jul-2008
Q. Will the proxy roadmap involve moving popular features from lua to C? ie. R/W splitting
A. We will keep the high-level parts in the lua-layer to be able to adjust to special situations without a rebuild. R/W splitting sometimes needs external knowledge what may only be available by the DBA.
Q. Do proxy apps run on a separate server? If not, what is the overhead incurred by Proxy on the DB server side?
A. You can run the proxy on the application server, on its own box or on the DB-server depending on the use-case
Q. With load balancing, what happen to transactions ? Are all queries sent to the same server ?
A. without any special customization the whole connection is sent to the same server. That keeps the whole connection state intact.
Q. We've looked at using MySQL Proxy but we're concerned about the alpha status - when do you think the proxy would be considered production ready?
A. we are on the road to the next feature release: 0.7.0. It will improve the performance quite a bit. After that we may be able to enter the beta phase.
Q. Is it possible to use the MySQL proxy w/ updating a Lucene index (or Solr) by making tcp calls to that server to update?
A. yes, but it isn't advised for now.
Q. Is the system context switch expensive, how much overhead does the lua script add?
A. lua is fast and the overhead should be small enough for most applications. The raw packet-overhead is around 400microseconds
Q. Adding a proxy for sure adds latency, how big is it ?
A. in the range of 400microseconds
Q. Do you have make one large script and call at proxy startup, can I change scripts without stopping and restarting (interrupting) the proxy?
A. You can just change the script and the proxy will reload it when a client connects.
Q. if proxy has to live on same machine as mysql, are there any tuning considerations to ensure both perform optimally?
A. It can live on any box: application, db or its own box.
Q. Can mysql-proxy be used on slaves and intercept binlog messages?
A. We are working on that. See http://jan.kneschke.de/2008/5/30/mysql-proxy-rbr-to-sbr-decoding for an example.
Q. I currently use SQL Relay for efficient connection pooling with a number of apache processes connecting to a MySQL server. Can MySQL proxy currently accomplish this. My goal is to minimize connection latency while keeping temporary tables available.
A. yes. that's what my part of the presentation is about
Q. are these reserved function names (e.g., error_result) that get automatically called?
A. only functions and values starting with proxy.* are provided by the proxy. All others are provided by you.
Q. As the script is reread by proxy, does it cache this or is it looking at the filesystem with each request?
A. It looks for the script at client-connect and reads it if it has changed, otherwise it uses the cached version.
Q. Given that there is a connect_server function, can a lua script link up with multiple servers?
A. The proxy provides some tutorials in the source-package, one is about examples/tutorial-keepalive.lua
Q. so in other words the mysql proxy is an API ?
A. The MySQL proxy provides a API allowing you to change its behaviour.
Q. The global namespace variable example being done here with quotas does not persist after a reboot right?
A. right. if you restart the proxy, you lose the results, unless you save them in a file
Q. I have two questions. (1) Can Proxy handle SSL connections? (2) Could proxy be used to capture passwords?
A. no. being the man-in-the-middle, Proxy can't handle encrypted sessions
A. Passwords aren't sent in clear-text as part of the authentication process.
Q. Seems awfully confusing. Are there tools for isolating problems? How can someone figure out if a problem is in the client, in the db or in the proxy?
A. you can set a debug script in the proxy, whih is an exceptionally good tool for this purpose. You can see very clearly which is which, if you set the right breakpoints
Q. Can you explain the status of your work with memcached and mysql-proxy?
A. there are some ideas to integrate proxy and memcache a bit, but no code yet.
Q. i was more trying to understand if the mysql proxy on its own is an API like application interface ...
A. yes/no :)
Q. Isn't what's currently being descrived pretty much what java connection pools are used for?
A. yes, that's true. however we are trying to make it a transparent layer
Q. So the authentication when connection pooling has to be done at every connection? What's the authentication latency?
A. You can skip the round-trip and use the connection as it was added to the pool. ... as long as the application does cleanup temporary tables.
A. The overhead is (as always) around 400microseconds
Q. if you have multiple databases on the same box, can u use proxy to connect to databases on default port 3306?
A. yes. provided that none of the server is using the same port
Q. To John: what about caching the auth info so clients connecting are given back-end connections that were established with identical auth info, thus saving a few more round trips?
A. there is an option --proxy-pool-no-change-user
Q. is there any big web site using it ? for what purpose and what transaction rate
A. gaiaonline is, John is just talking about it ;)
A. FYI, we have tested it and have seen it handle 2400 queries per second going through the proxy
Q. How does MySQL Proxy compare to DBSlayer ?
A. DBSlayer is a REST->MySQL tool, MySQL Proxy is transparent to your application. No change to the application is needed.
Q. I tried using mysql proxy without any lua script to try a round-robin type load balancing. In this case, if the first databsae in the list is down, mysql proxy would not connect the client to the second database in the list.
A. That's fixed in the next release.
Q. is it "safe" to use LuaSocket with proxy scripts?
A. You can, but it is not advised as it may block.
Q. How different the proxy from DBCP (Database connection poolling) for apache in terms of connection poolin
A. Connection Pooling is just one use-case of the MySQL Proxy. You can use it for a lot more and it works in cases where you can't use DBCP (like if you don't have Java).
Q. Will proxy be depreciated WRT connection pooling once MySQL 6.x comes out? Or will 6.x integrate proxy more deeply?
A. The logic about the pooling is controlled by the lua scripts, you can enable and disable it if you like.
Q. proxy can handle about 5000 connections, what is the limit on a mysql server?
A. see your max-connections settings.
A. by default 150, the proxy can handle a lot more.
Q. would the java only connection pooling solution work for multiple web servers? With this, I'd assume you can pool across many web servers at once
A. Yes. But you can also start one proxy on each application server to get a similar behaviour as you have it already.
Q. what is the limit for this parameter
A. around 1024 the MySQL Server may run out of threads it can spawn. Leaving it at around 100 is advised.
Q. Can you dynamically reconfigure the pool of mysql servers mysql proxy will load balance to?
A. not yet, it is on the list. We are working on a admin-interface for that purpose.
Q. In the quick poll, I see "LB: read-write splitting" as an option, so would it be correct to say that there are no scripts written for Proxy yet to do this?
A. There is a proof of concept script for that included. But its far from perfect and may not work for you yet.