The first tutorial is a high level overview of how to install ProxySQL , and how to quickly configure and use it.

Install and compile ProxySQL

Installing and compiling ProxySQL is very straightforward:

1) download and uncompress the source code from GitHub

2) compile

Note:

The following packages are needed in order to compile:

- libssl and ibssl-dev (Ubuntu) or openssl and openssl-devel (Centos)

- libglib2 and libglib2.0-dev (Ubuntu) or glib2 and glib2-devel (Centos)

- libmysqlclient and libmysqlclient-dev (Ubuntu) or mysql-libs and mysql-devel (Centos)

Below an example:

vegaicm@voyager:~/proxysql$ wget -q https://github.com/renecannao/proxysql/archive/master.zip -O proxysql.zip

vegaicm@voyager:~/proxysql$ unzip -q proxysql.zip

vegaicm@voyager:~/proxysql$ cd proxysql-master/src/

vegaicm@voyager:~/proxysql/proxysql-master/src$ make

...

gcc -o proxysql obj/main.o obj/free_pkts.o obj/mem.o obj/debug.o obj/sqlite3.o obj/fundadb_hash.o obj/global_variables.o obj/mysql_connpool.o obj/mysql_protocol.o obj/mysql_handler.o obj/network.o obj/queue.o obj/threads.o obj/admin_sqlite.o -I../include -lpthread -ggdb -rdynamic -lcrypto `mysql_config --libs_r --cflags` `pkg-config --libs --cflags gthread-2.0` -DDEBUG -lm

Configure ProxySQL to run against your local MySQL instance

ProxySQL is under active development, evolving day by day thus some of the options and switches may change from version to version.

To simplify the configuration and to make it easy to try, ProxySQL is shipped with an interactive script that should help its configuration : proxysql_interactive_config.pl

Launch the script, and it will guide you step by step through a basic configuration.

The script will propose several default values, making the configuration very simple.

Following is an example, where we are going to use many of the defaults and configure only server and usernames:

vegaicm@voyager:~/proxysql/proxysql-master/src$ ./proxysql_interactive_config.pl

Interactive script to configure ProxySQL,

High Performance and High Availability proxy for MySQL

Generic options:

- core_dump_file_size : maximum size of core dump in case of crash

- stack_size : stack size allocated for each thread

- error_log : log file for error messages (not implemented yet)

core_dump_file_size [0]:

stack_size (65536-8388608) [524288]:

Clients can communicate with ProxySQL through 2 different sockets:

- proxy_mysql_port : TCP socket for MySQL traffic : default is 6033

- mysql_socket : Unix Domanin socket : default is /tmp/proxysql.sock

proxy_mysql_port [6033]:

mysql_socket [/tmp/proxysql.sock]:

ProxySQL uses an admin interface for runtime configuration and to export statistics.

Such interface uses the MySQL protocol and can be used by any MySQL client.

Options:

- proxy_admin_port : TCP socket for Administration : default is proxy_mysql_port-1 (6032)

- proxy_admin_user : username for authentication ( this is not a mysql user )

- proxy_admin_password : password for the user specified in proxy_admin_user

proxy_admin_port [6032]:

proxy_admin_user [admin]:

proxy_admin_password [admin]:

ProxySQL allows to cache SELECT statements executed by the application.

Query cache is configured through:

- mysql_query_cache_partitions : defines the number of partitions, reducing contention

- mysql_query_cache_default_timeout : defaults TTL for queries without explicit TTL

- mysql_query_cache_size : total amount of memory allocable for query cache

mysql_query_cache_partitions (1-64) [16]:

mysql_query_cache_default_timeout (0-315360000) [1]: 30

mysql_query_cache_size (1048576-10737418240) [67108864]:

Several options define the network behaviour of ProxySQL:

- mysql_threads : defines how many threads will process MySQL traffic

- mysql_poll_timeout : poll() timeout (millisecond)

- mysql_max_query_size : maximum length of a query to be analyzed

- mysql_max_resultset_size : maximum size of resultset for caching and buffering

- net_buffer_size : internal buffer for network I/O

- backlog : listen() backlog

mysql_threads (1-128) [8]: 4

mysql_poll_timeout (100-1000000) [10000]:

mysql_max_query_size (1-16777210) [1048576]:

mysql_max_resultset_size (1-1073741824) [1048576]:

net_buffer_size (1024-16777216) [8192]:

backlog (50-10000) [2000]:

ProxySQL implements an internal connection pool. Configurable with:

- mysql_connection_pool_enabled : enables the connection pool if set to 1

- mysql_wait_timeout : timeout to drop unused connections

mysql_connection_pool_enabled (0-1) [1]:

mysql_wait_timeout (1-31536000) [28800]:

ProxySQL connects to various mysqld instances that form the backend.

- mysql_servers : list of mysqld servers in the format host:port;host:port;...

Hostname[:port] of backend#1 : 127.0.0.1:3306

Would you like to add another backend server (Y-N) [N]: N

Few options specify how to connect to the backend:

- mysql_usage_user : user used by ProxySQL to connect to the backend to verify its status

- mysql_usage_password : password for user specified in mysql_usage_user

Note:

the user specified in mysql_usage_user needs only USAGE privilege, and you can create the user with GRANT USAGE

mysql_usage_user [proxy]:

mysql_usage_password [proxy]:

Note (again!):

The user specified in mysql_usage_user needs only USAGE privilege

You can create the user with GRANT USAGE ON *.* TO 'proxy'@'<proxysqlip>' IDENTIFIED BY 'proxy';

ProxySQL authenticates clients' connections, and then uses the same credentials to connect to the backends.

ProxySQL needs to know clients' usernames and passwords because a single client connection can generate multiple connections to the backend.

Username for user#1 : testuser

Password for user msandbox : testpassword

Would you like to add another user (Y-N) [N]: N

Basic configuration completed!

If you compiled ProxySQL with debug information (enabled by default) you can enable debug verbosity.

Would you like to enable debug verbosity? (Y-N) [N]: N

# ProxySQL config file

# Generated using proxysql_interactive_config.pl

[global]

core_dump_file_size=0

debug=0

stack_size=524288

proxy_admin_port=6032

proxy_admin_user=admin

proxy_admin_password=admin

net_buffer_size=8192

backlog=2000

[mysql]

mysql_threads=4

proxy_mysql_port=6033

mysql_socket=/tmp/proxysql.sock

mysql_query_cache_partitions=16

mysql_query_cache_default_timeout=30

mysql_query_cache_size=67108864

mysql_poll_timeout=10000

mysql_max_query_size=1048576

mysql_max_resultset_size=1048576

mysql_connection_pool_enabled=1

mysql_wait_timeout=28800

mysql_servers=127.0.0.1:3306

mysql_usage_user=proxy

mysql_usage_password=proxy

[mysql users]

testuser=testpassword

[debug]

debug_generic=0

debug_net=0

debug_pkt_array=0

debug_memory=0

debug_mysql_com=0

debug_mysql_connection=0

debug_mysql_server=0

debug_admin=0

debug_mysql_auth=0

Would you like to write a configuration file? (Y-N) [Y]:

config filename [proxysql.cnf]:

Configuration completed!

Quit

Note on debugging: do not enable debug at this stage. I will write an article just on this topic.

Create users in your local MySQL instance to allow ProxySQL to connect:

GRANT USAGE ON *.* TO 'proxy'@'127.0.0.1' IDENTIFIED BY 'proxy';

CREATE USER 'testuser'@'127.0.0.1' IDENTIFIED BY 'testpassword';

GRANT ALL PRIVILEGES ON test.* TO 'testuser'@'127.0.0.1';

GRANT ALL PRIVILEGES ON sbtest.* TO 'testuser'@'127.0.0.1';

Run ProxySQL

Starting ProxySQL is very straightforward, just execute the following in your prompt :

vegaicm@voyager:~/proxysql/proxysql-master/src$ ./proxysql

Note that currently it has some intentional limitations due the fact that is under development:

- no daemon support, only foreground;

- no logging facilities included, only stdout;

- the working directory must be writable .

Connect to MySQL through ProxySQL

Let’s make a simple test to connect to ProxySQL using tcp port 6033 :

vegaicm@voyager:~$ mysql -u testuser -ptestpassword -h 127.0.0.1 -P6033

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3055020864

Server version: 5.1.30 (Ubuntu)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW GRANTS;

+-----------------------------------------------------------------------------------------------------------------+

| Grants for testuser@127.0.0.1 |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'testuser'@'127.0.0.1' IDENTIFIED BY PASSWORD '*9F69E47E519D9CA02116BF5796684F7D0D45F8FA' |

| GRANT ALL PRIVILEGES ON `test`.* TO 'testuser'@'127.0.0.1' |

| GRANT ALL PRIVILEGES ON `sbtest`.* TO 'testuser'@'127.0.0.1' |

+-----------------------------------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)

mysql>

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

mysql> CREATE TABLE proxytest (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=INNODB;

Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO proxytest VALUES(NULL);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO proxytest SELECT NULL FROM proxytest;

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> INSERT INTO proxytest SELECT NULL FROM proxytest;

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM proxytest;

+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

| 4 |

+----+

4 rows in set (0.00 sec)

mysql> \q

Bye

Now let’s try to connect to ProxySQL through socket /tmp/proxysql.sock :

vegaicm@voyager:~$ mysql -u testuser -ptestpassword -S /tmp/proxysql.sock test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3054492480

Server version: 5.1.30 (Ubuntu)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM proxytest;

+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

| 4 |

+----+

4 rows in set (0.00 sec)

mysql> \q

Bye

Simple queries using ProxySQL seems to give us the results we were expecting.

Using mysqlslap we can quickly test how ProxySQL performs compared to MySQL running the very simple query “SELECT * FROM proxytest”. We will run MySQL with and without MySQL query cache enabled.

vegaicm@voyager:~$ for i in `seq 1 20000` ; do echo "SELECT * FROM proxytest;" ; done > /tmp/select_proxytest.sql

vegaicm@voyager:~$ mysql -u root -e "SET GLOBAL query_cache_size=0;" ; mysqlslap -u testuser -ptestpassword --create-schema=test -c 8,16,32,64,128,256 -q /tmp/select_proxytest.sql -S /var/run/mysqld/mysqld.sock | grep "Average number of seconds"

Average number of seconds to run all queries: 3.284 seconds

Average number of seconds to run all queries: 6.591 seconds

Average number of seconds to run all queries: 13.530 seconds

Average number of seconds to run all queries: 29.546 seconds

Average number of seconds to run all queries: 66.452 seconds

Average number of seconds to run all queries: 138.742 seconds

vegaicm@voyager:~$ mysql -u root -e "SET GLOBAL query_cache_size=1024*1024;" ; mysqlslap -u testuser -ptestpassword --create-schema=test -c 8,16,32,64,128,256 -q /tmp/select_proxytest.sql -S /var/run/mysqld/mysqld.sock | grep "Average number of seconds"

Average number of seconds to run all queries: 1.632 seconds

Average number of seconds to run all queries: 4.528 seconds

Average number of seconds to run all queries: 7.533 seconds

Average number of seconds to run all queries: 17.182 seconds

Average number of seconds to run all queries: 41.967 seconds

Average number of seconds to run all queries: 91.434 seconds

vegaicm@voyager:~$ mysqlslap -u testuser -ptestpassword --create-schema=test -c 8,16,32,64,128,256 -q /tmp/select_proxytest.sql -S /tmp/proxysql.sock | grep "Average number of seconds"

Average number of seconds to run all queries: 1.135 seconds

Average number of seconds to run all queries: 1.908 seconds

Average number of seconds to run all queries: 4.575 seconds

Average number of seconds to run all queries: 9.451 seconds

Average number of seconds to run all queries: 18.843 seconds

Average number of seconds to run all queries: 36.143 seconds

A picture is worth a thousands words:

Build a small cluster to run ProxySQL

While it is possible to use ProxySQL on a setup with a single MySQL server, to use many of its features (load balancing, queries routing, read/write split) it is required to have several MySQL backends, configured in various architectures.

To build a small cluster with several MySQL backends we will create a simple replication setup using MySQL Sandbox .

If you are not familiar with MySQL Sandbox, it is “a tool for installing one or more MySQL servers in isolation, without affecting other servers” that you can download from https://launchpad.net/mysql-sandbox .

vegaicm@voyager:~$ make_replication_sandbox mysql_binaries/mysql-5.5.34-linux2.6-i686.tar.gz
installing and starting master
installing slave 1
installing slave 2
starting slave 1
.... sandbox server started
starting slave 2
.... sandbox server started
initializing slave 1
initializing slave 2
replication directory installed in $HOME/sandboxes/rsandbox_mysql-5_5_34

Now that the cluster is installed, verify on which ports are listening the various mysqld processes:

vegaicm@voyager:~$ cd sandboxes/rsandbox_mysql-5_5_34
vegaicm@voyager:~/sandboxes/rsandbox_mysql-5_5_34$ cat default_connection.json
{
"master":
   {
       "host":     "127.0.0.1",
       "port":     "23389",
       "socket":   "/tmp/mysql_sandbox23389.sock",
       "username": "msandbox@127.%",
       "password": "msandbox"
   }
,
"node1":
   {
       "host":     "127.0.0.1",
       "port":     "23390",
       "socket":   "/tmp/mysql_sandbox23390.sock",
       "username": "msandbox@127.%",
       "password": "msandbox"
   }
,
"node2":
   {
       "host":     "127.0.0.1",
       "port":     "23391",
       "socket":   "/tmp/mysql_sandbox23391.sock",
       "username": "msandbox@127.%",
       "password": "msandbox"
   }
}

The mysqld processes are listening on port 23389 (master) and 23390 and 23391 (slaves).

Important note:

Unless configured differently (details in another tutorial), ProxySQL considers a writable master any server that is configured with read_only=OFF . Because MySQL Sandbox doesn’t configure the slaves with read_only=ON , ProxySQL will consider any node as a master : that is obviously incorrect and needs to be addressed before starting ProxySQL.

Change read_only setting on the two slaves:

vegaicm@voyager:~$ mysql -u msandbox -pmsandbox -h 127.0.0.1 -P23390 -e "SET GLOBAL read_only=ON"

vegaicm@voyager:~$ mysql -u msandbox -pmsandbox -h 127.0.0.1 -P23391 -e "SET GLOBAL read_only=ON"

Reconfigure ProxySQL to use the test cluster

It is possible to reconfigure ProxySQL without restarting it, but it is a long process that will be explained in another tutorial . For now, in order to reconfigure proxysql , you should :

- stop proxysql

- delete the proxysql.db* files

- edit proxysql.cnf or relaunch proxysql_interactive_config.pl

Note: proxysql.db* files are the database files that ProxySQL uses to store configurations after processing proxysql.cnf , and the configuration stored in these files have higher priority over the same configuration values stored in proxysql.cnf . That is the reason why you should delete proxysql.db* files if you edit proxysql.cnf , or the new settings in proxysql.cnf won’t have any effect.

In the next article we will describe the content of these database files and how to reconfigure ProxySQL without deleting any file and without restarting the process.

For semplicity I will rerun proxysql_interactive_config.pl and highlight the few differences:

vegaicm@voyager:~/proxysql/proxysql-master/src$ ./proxysql

vegaicm@voyager:~/proxysql/proxysql-master/src$ rm -f proxysql.db*

vegaicm@voyager:~/proxysql/proxysql-master/src$ rm proxysql.cnf

vegaicm@voyager:~/proxysql/proxysql-master/src$ ./proxysql_interactive_config.pl

Interactive script to configure ProxySQL,

High Performance and High Availability proxy for MySQL

…

...

Hostname[:port] of backend#1 : 127.0.0.1:23389

Would you like to add another backend server (Y-N) [N]: Y

Hostname[:port] of backend#2 : 127.0.0.1:23390

Would you like to add another backend server (Y-N) [N]: y

Hostname[:port] of backend#3 : 127.0.0.1:23391

Would you like to add another backend server (Y-N) [N]: N

…

mysql_usage_user [proxy]: msandbox

mysql_usage_password [proxy]: msandbox

Note (again!):

The user specified in mysql_usage_user needs only USAGE privilege

You can create the user with GRANT USAGE ON *.* TO 'msandbox'@'<proxysqlip>' IDENTIFIED BY 'msandbox';

ProxySQL authenticates clients' connections, and then uses the same credentials to connect to the backends.

ProxySQL needs to know clients' usernames and passwords because a single client connection can generate multiple connections to the backend.

Username for user#1 : msandbox

Password for user msandbox : msandbox

Would you like to add another user (Y-N) [N]: N

…

Your new proxysql.cnf should look like this .

Connect to the various MySQL backends through ProxySQL

Restart ProxySQL :

vegaicm@voyager:~/proxysql/proxysql-master/src$ ./proxysql

Once again , let’s make a simple test to connect to ProxySQL :

vegaicm@voyager:~$ mysql -u msandbox -pmsandbox -h 127.0.0.1 -P6033

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3048201024

Server version: 5.1.30 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT 1;

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

mysql> \q

All looks good so far. But to which backend are we connected?

Let make a simple test:

vegaicm@voyager:~$ mysql -u msandbox -pmsandbox -h 127.0.0.1 -P6033 -e "SELECT @@hostname, @@port"

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| voyager | 23390 |

+------------+--------+

vegaicm@voyager:~$ mysql -u msandbox -pmsandbox -h 127.0.0.1 -P6033 -e "SELECT @@hostname, @@port"

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| voyager | 23391 |

+------------+--------+

vegaicm@voyager:~$ mysql -u msandbox -pmsandbox -h 127.0.0.1 -P6033 -e "SELECT @@hostname, @@port"

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| voyager | 23390 |

+------------+--------+

vegaicm@voyager:~$ mysql -u msandbox -pmsandbox -h 127.0.0.1 -P6033 -e "SELECT @@hostname, @@port"

+------------+--------+

| @@hostname | @@port |

+------------+--------+

| voyager | 23389 |

+------------+--------+

It seems we are randomly connected to one of the backends.

Let make a different test now: we will send the same identical query over a very small period of time: