今天突然有这样一个想法,proxy SQL docker化部署,data目录当然要外挂,可否启用多个docker容器,共用一个数据目录呢?这样能否达到一个集群的效果呢?带着这个想法做了一些尝试。

docker 镜像的entry point 使用front进程启动proxysql,这样不会生产pid文件,而且proxysql挂了,容器就退出了,然后自动重启,依靠容器实现一定程度的高可用:

1
2
3
4
5
#!/bin/bash

config=/etc/proxysql.cnf

proxysql -f -c $config

Docker容器启动:

1
2
3
4
5
6
7
8
9
10
docker run -d --name proxysql1 \
-v /dbfiles/proxysql_volume:/var/lib/proxysql \
-m 8g \
-p 3307:3306 -p 7032:6032 proxysql:1.4.12

启动第二个:
docker run -d --name proxysql2 \
-v /dbfiles/proxysql_volume:/var/lib/proxysql \
-m 8g \
-p 3308:3306 -p 8032:6032 proxysql:1.4.12

第二个只改映射端口和容器名,然后确认容器启动

1
2
3
4
 docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
c587df3484ba proxysql:1.4.12 "/entry_point.sh" 2 seconds ago Up 1 second 0.0.0.0:3308->3306/tcp, 0.0.0.0:8032->6032/tcp proxysql2
a0d9c84a33c5 proxysql:1.4.12 "/entry_point.sh" 9 minutes ago Up 9 minutes 0.0.0.0:3307->3306/tcp, 0.0.0.0:7032->6032/tcp proxysql1

两个容器都能正常启动,登陆也没有问题。
经测试在一个proxysql中创建账号,save mysql users to disk后,在另一个proxysql中是可以通过load mysql users to mem/run,启用生效的.

查看外挂的data目录,发现有个文件可能需要频繁写盘proxysql_stats.db,它是统计SQL执行信息的库,当在两个proxysql上并行执行SQL时,这个统计信息入库会不会有冲突?

1
2
3
4
-rw------- 1 root root 122880 11月 24 10:23 proxysql.db
-rw------- 1 root root 3517 11月 22 11:30 proxysql.log
-rw-r--r-- 1 root root 3 11月 22 11:30 proxysql.pid
-rw------- 1 root root 524288 11月 24 10:25 proxysql_stats.db

stats统计信息可以使用一些变量来控制,如:admin-stats_mysql_connections,
admin-stats_system_cpu设定了采集周期是60秒。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show variables like '%stat%'
-> ;
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| mysql-client_multi_statements | true |
| mysql-servers_stats | true |
| mysql-stats_time_backend_query | false |
| mysql-stats_time_query_processor | false |
| admin-stats_credentials | stats:stats |
| admin-stats_mysql_connections | 60 |
| admin-stats_mysql_connection_pool | 60 |
| admin-stats_mysql_query_cache | 60 |
| admin-stats_system_cpu | 60 |
| admin-stats_system_memory | 60 |
| admin-telnet_stats_ifaces | (null) |
| admin-cluster_check_status_frequency | 10 |
| mysql-commands_stats | true |
+--------------------------------------+-------------+

如果两个proxsql共用同一个数据目录,会否造成统计信息不准确,然而这在docker环境下是不是无所谓呢?
经过对两个ProxySQL同时压测发现,stats库中的表stats_mysql_query_digest的SSQL统计信息也不写盘。使用sysbench 在一个proxysql 上prepare时,在另一个proxysql的表stats_mysql_query_digest中并没有查到prrepare所涉及的insert语句,虽然它们共享一个数据目录。看了下官方文档中对stats库的解释:

This database contains metrics gathered by ProxySQL with respect to its internal functioning. Here you will find information on how often certain counters get triggered and the execution times of the queries that pass through ProxySQL.

A user that connects to Admin with admin-stats_credentials credentials can only access this schema.

Generally, the tables from this database are populated on the fly when the SQL query against them is execute, by examining in-memory data structures.

stats库还是有一些玄机。根据这个comment的描述,只有对表stats_mysql_query_digest查询时,数据才会从内存中dump出来存到SQLite的内存中以供查询。每次查询都是这样一个流程,以便获得最新的数据。这也解释了上面在不同的proxysql实例中得到不同的数据。 这样的话不会影响两个proxysql共享一个data目录。

在ProxySQL的管理台,执行shou databases可以看到:

1
2
3
4
5
6
7
8
9
10
mysql> show databases;
+-----+---------------+------------------------------------------+
| seq | name | file |
+-----+---------------+------------------------------------------+
| 0 | main | |
| 2 | disk | /dbfiles/proxysql/data/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /dbfiles/proxysql/data/proxysql_stats.db |
+-----+---------------+------------------------------------------+

里面有5个数据库,只有两个是需要写盘的,即disk和stats_histroy。其中stats库中的表只驻留内存并不写盘。而stats_history库中的表只是一些关于connection,query_cache,system_cpu and system memory的统计信息。他们都是周期化采集然后写入磁盘的,采集周期由上面的变量控制。多个proxysql共用一个数据目录,这个库的写入可能会有冲突。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show tables from stats_history;
+------------------------+
| tables |
+------------------------+
| mysql_connections |
| mysql_connections_day |
| mysql_connections_hour |
| mysql_query_cache |
| mysql_query_cache_day |
| mysql_query_cache_hour |
| system_cpu |
| system_cpu_day |
| system_cpu_hour |
| system_memory |
| system_memory_day |
| system_memory_hour |
+------------------------+
12 rows in set (0.00 sec)

为了更加深入的了解stats_history库,我用sqlite3连上stats_history数据库文件,发现里面有这几张表,与proxysql中显示的是一致的:

1
2
3
4
5
sqlite> .tables
mysql_connections mysql_query_cache_day system_cpu_hour
mysql_connections_day mysql_query_cache_hour system_memory
mysql_connections_hour system_cpu system_memory_day
mysql_query_cache system_cpu_day system_memory_hour

这个库的作用是为ProxySQL内置的web server提供监控数据。web server默认是关闭,即使是关闭状态,这个库的信息还是会被采集。

在多个proxysql共享数据目录的日志中,确实看到了一些冲突。因为sqlite的隔离级别是Serializable。同一时刻只能有一个事务执行。多个proxysql并行存储所采集的数据,可能会有冲突,这些信息也在日志中打出来了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2018-11-26 17:16:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM mysql_connections WHERE timestamp < 1542618994
2018-11-26 17:16:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM mysql_connections_hour WHERE timestamp < 1511687794
2018-11-26 17:16:35 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- INSERT INTO mysql_query_cache_hour SELECT timestamp/3600*3600 , MAX(count_GET), MAX(count_GET_OK), MAX(count_SET), MAX(bytes_IN), MAX(bytes_OUT), MAX(Entries_Purged), AVG(Entries_In_Cache), AVG(Memory_bytes) FROM mysql_query_cache WHERE timestamp >= 1543222800 AND timestamp < 1543222800 GROUP BY timestamp/3600
2018-11-26 17:16:35 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM mysql_query_cache WHERE timestamp < 1542618995
2018-11-26 17:16:35 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM mysql_query_cache_hour WHERE timestamp < 1511687795
2018-11-26 17:18:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- INSERT INTO mysql_connections_hour SELECT timestamp/3600*3600 , MAX(Client_Connections_aborted), AVG(Client_Connections_connected), MAX(Client_Connections_created), MAX(Server_Connections_aborted), AVG(Server_Connections_connected), MAX(Server_Connections_created), MAX(ConnPool_get_conn_failure), MAX(ConnPool_get_conn_immediate), MAX(ConnPool_get_conn_success), MAX(Questions), MAX(Slow_queries) FROM mysql_connections WHERE timestamp >= 1543222800 AND timestamp < 1543222800 GROUP BY timestamp/3600
2018-11-26 17:18:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM mysql_connections WHERE timestamp < 1542619114
2018-11-26 17:18:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM mysql_connections_hour WHERE timestamp < 1511687914
2018-11-26 17:18:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- INSERT INTO mysql_query_cache_hour SELECT timestamp/3600*3600 , MAX(count_GET), MAX(count_GET_OK), MAX(count_SET), MAX(bytes_IN), MAX(bytes_OUT), MAX(Entries_Purged), AVG(Entries_In_Cache), AVG(Memory_bytes) FROM mysql_query_cache WHERE timestamp >= 1543222800 AND timestamp < 1543222800 GROUP BY timestamp/3600
2018-11-26 17:18:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM mysql_query_cache WHERE timestamp < 1542619114
2018-11-26 17:18:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM mysql_query_cache_hour WHERE timestamp < 1511687914
2018-11-26 17:18:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- INSERT INTO system_cpu_hour SELECT timestamp/3600*3600 , SUM(tms_utime), SUM(tms_stime) FROM system_cpu WHERE timestamp >= 1543222800 AND timestamp < 1543222800 GROUP BY timestamp/3600
2018-11-26 17:18:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM system_cpu WHERE timestamp < 1542619114
2018-11-26 17:18:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM system_cpu_hour WHERE timestamp < 1511687914
2018-11-26 17:18:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- INSERT INTO system_memory_hour SELECT timestamp/3600*3600 , AVG(allocated), AVG(resident), AVG(active), AVG(mapped), AVG(metadata), AVG(retained) FROM system_memory WHERE timestamp >= 1543222800 AND timestamp < 1543222800 GROUP BY timestamp/3600
2018-11-26 17:18:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM system_memory WHERE timestamp < 1542619114
2018-11-26 17:28:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- INSERT INTO mysql_connections_hour SELECT timestamp/3600*3600 , MAX(Client_Connections_aborted), AVG(Client_Connections_connected), MAX(Client_Connections_created), MAX(Server_Connections_aborted), AVG(Server_Connections_connected), MAX(Server_Connections_created), MAX(ConnPool_get_conn_failure), MAX(ConnPool_get_conn_immediate), MAX(ConnPool_get_conn_success), MAX(Questions), MAX(Slow_queries) FROM mysql_connections WHERE timestamp >= 1543222800 AND timestamp < 1543222800 GROUP BY timestamp/3600
2018-11-26 17:28:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM mysql_connections WHERE timestamp < 1542619714
2018-11-26 17:28:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM mysql_connections_hour WHERE timestamp < 1511688514
2018-11-26 17:28:34 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- INSERT INTO mysql_query_cache_hour SELECT timestamp/3600*3600 , MAX(count_GET), MAX(count_GET_OK), MAX(count_SET), MAX(bytes_IN), MAX(bytes_OUT), MAX(Entries_Purged), AVG(Entries_In_Cache), AVG(Memory_bytes) FROM mysql_query_cache WHERE timestamp >= 1543222800 AND timestamp < 1543222800 GROUP BY timestamp/3600

从打出的错误日志可以看到, proxysql会删除一定时期以前的数据,保证proxysql_stats.db不会无限增长。

在一篇官方博客的问答中,提到了类似的问题:

Q. Right, data directory management … though you could technically have both proxysql01 and proxysql02 share the same proxysql.db file? Admin them both separately, tell each to SAVE BLAH TO DISK, one config database, but then you are overwriting the db file so better be careful you know what you are doing!
A. Exactly. They can share the same proxysql.db, but using different database files makes things easier.

看来多个ProxySQL共享一个数据目录时,需要确保只在一个ProxySQL中修改,然后save … to disk; 在其它的proxysql中执行load … to mem;load .. to run;即可生效。唯一的问题是stats_history库的写入冲突,有错误日志打出,(但proxysql 以-f 参数启动,日志打到控制台,不会打到日志文件),ProxySQL内置的web server展示信息不准确,但貌似这些不影响使用。另外可否单独给这个库指定数据目录呢?

参考:
https://proxysql.com/blog/how-to-run-multiple-proxysql-instances
https://proxysql.com/blog/proxysql-http-server-initial-release