proxysql killed our dns servers.

[tapuhi]

I recently added 245 mysql servers to 6 proxysql servers . At the moment I did that our dns servers where overloaded and almost inaccessible for other hosts.

Beside using dns caching on each proxysql server is there anything we can do on proxysql end ?

[renecannao]

Other than using IPs instead of hostname, there isn't much to do. Note also that there is an important reason to use IP instead of hostname: while connections to backends is a non blocking call, DNS lookup is a blocking call.

This sounds like a feature request: implement some DNS caching in ProxySQL itself.

[tapuhi]

Meanwhile I am installing a DNS cache on each proxysql server. BUt yes indeed it's a feature request

[tapuhi]

@renecannao BTW, all mysql servers were injected into proxysql using consul-template which I took from orchestrator. #1008

[renecannao]

either 1.4.2 or 1.4.3 will have an experimental feature to add servers in one proxy instance, and all the others will automatically sync. Stay tuned :)

[tapuhi]

In that case Consul template will not be needed.

[tapuhi]

Do you have any more technical information on how that experimental feature is going to work ? As I said I will need to re-consider the consul-template once that feature will not be experimental any more.

Is it going to be a cluster ? What is going to happen if one of the proxysql will not get the update for any technical reason? Will it be able to somehow split load between Proxysql nodes ?

I guess much more question will come up once I will know how exactly it id going to work.

[renecannao]

I am drafting the first version in these days, and I will publish a detailed blog post. In short, all proxies instances will constantly monitor each other. When a proxy get a new configuration, its version will be upgraded. Because all proxies are constantly monitoring the others, they will detect a new configuration version and pull the configuration from an upgraded node. Drawback from this initial implementation is conflict resolution: it can be messy if you re-configure 2 proxies at the same time with different configuration.

A future version will prevent this and allows reconfiguration to only one node at the time (the "master" node)

[tapuhi]

I will definitely stay tuned and wait for that great feature. I am going to mention that feature in my talk in Percona Live in Dublin next month (Proxysql Consul and Orchestrator combination ) . if that's ok for you .

[renecannao]

Absolutely!! The first version will be ready before Percona Live :)

[tapuhi]

THat's great! If I will have time I will test it and comparing between the 2 solutions using yours or consul-template.

[tapuhi]

Added nscd on proxysql servers, solved the issue .

[rtnpro]

@renecannao What's the current status of DNS caching within proxysql?

[ernstae]

One of my major issues with ProxySQL is related to DNS. While it's probably just as easy to add the IP addresses of MySQL back-end servers, it makes diagnosing and debugging issues more challenging.

I've noticed that even if I use systemd-resolved with caching and round-robin failover, that ProxySQL still has an error if/when the primary DNS server is restarted. in my environment. This can lead to downtime for up to 1-2 minutes while Windows AD is restarted.

This is just a +1 for adding a configurable TTL for caching of hostnames, so that each call doesn't cause a lookup. It would ensure a more enthusiastic adoption of ProxySQL within my organization if it were addressed.

[andr-04]

@renecannao, as I see no news for 3 years? I really have no idea why you decided to resolve DNS only once at startup. It looks like you try solving the problem out of your responsibility which is a bad way. If the user uses a DNS name instead of IP, you should resolve it as often as defined in the DNS record -- otherwise, it becomes unexpectable behavior.

I don't know why I'm saying obvious things here. I'm disappointed about ProxySQL: this bug is not the first for me (another one happens more frequently, but it's difficult to reproduce it). 😔

[renecannao]

Hi @andr-04 . I am surprised in reading what you wrote. I mean, you are saying exactly the opposite of how proxysql works, and in fact the reason of this issue if because it doesn't work as you wrote.

I really have no idea why you decided to resolve DNS only once at startup

Completely false, so is everything else.

[andr-04]

This is not false. The following log I saw today:

2020-08-24 00:48:24 [INFO] Using config file /etc/proxysql.cnf
Renaming database file /var/lib/proxysql/proxysql.db
2020-08-24 00:48:24 [INFO] Using OpenSSL version: OpenSSL 1.1.1d  10 Sep 2019
2020-08-24 00:48:24 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
2020-08-24 00:48:25 [INFO] ProxySQL version 2.0.12-38-g58a909a0
2020-08-24 00:48:25 [INFO] Detected OS: Linux pub 4.15.0-112-generic #113-Ubuntu SMP Thu Jul 9 23:41:39 UTC 2020 x86_64
2020-08-24 00:48:25 [INFO] ProxySQL SHA1 checksum: 042c33bcc9485f23b87ec96d4a97240d590e2b27
Standard ProxySQL MySQL Logger rev. 2.0.0714 -- MySQL_Logger.cpp -- Sun May 17 20:24:24 2020
Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Sun May 17 20:24:24 2020
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Sun May 17 20:24:24 2020
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Sun May 17 20:24:24 2020
...
2020-08-24 00:48:25 [INFO] Creating new server in HG 1 : xxx:port , gtid_port=0, weight=10000, status=0
2020-08-24 00:48:25 [INFO] Creating new server in HG 1 : yyy:port , gtid_port=0, weight=1, status=0
2020-08-24 00:48:25 [INFO] New mysql_group_replication_hostgroups table
2020-08-24 00:48:25 [INFO] New mysql_galera_hostgroups table
2020-08-24 00:48:25 [INFO] New mysql_aws_aurora_hostgroups table
2020-08-24 00:48:25 [INFO] MySQL_HostGroups_Manager::commit() locked for 16ms
Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Sun May 17 20:24:24 2020
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Sun May 17 20:24:24 2020
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Sun May 17 20:24:24 2020
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Sun May 17 20:24:24 2020
2020-08-24 00:48:28 mysql_connection.cpp:910:handler(): [ERROR] Failed to mysql_real_connect() on xxx:port , FD (Conn:0 , MyDS:0) , 2005: Unknown MySQL server host 'xxx' (-3).
2020-08-24 00:48:30 mysql_connection.cpp:910:handler(): [ERROR] Failed to mysql_real_connect() on xxx:port , FD (Conn:0 , MyDS:0) , 2005: Unknown MySQL server host 'xxx' (-3).
...
2020-08-24 08:40:42 mysql_connection.cpp:910:handler(): [ERROR] Failed to mysql_real_connect() on yyy:port , FD (Conn:0 , MyDS:0) , 2005: Unknown MySQL server host 'yyy' (-3).
2020-08-24 08:40:44 mysql_connection.cpp:910:handler(): [ERROR] Failed to mysql_real_connect() on yyy:port , FD (Conn:0 , MyDS:0) , 2005: Unknown MySQL server host 'yyy' (-3).
2020-08-24 08:40:46 MySQL_Monitor.cpp:2425:monitor_ping(): [ERROR] Server yyy:port missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
2020-08-24 08:40:46 mysql_connection.cpp:910:handler(): [ERROR] Failed to mysql_real_connect() on yyy:port , FD (Conn:0 , MyDS:0) , 2005: Unknown MySQL server host 'yyy' (-3).

It repeated until I restarted ProxySQL. I suppose, at a startup some problems with DNS/Internet were for a short period. Obviously, resolving DNS records were broken since a startup.

[andr-04]

I take my words back. The problem was on the Docker side (after server restarted, /etc/resolv.conf inside the container didn't contain DNS servers).

But the hint for ProxySQL from here: terminating ProxySQL (as a fatal error) can fix it. But it's not related to ProxySQL directly.

[xo4yecTb]

Hello @renecannao!

Same issue with our ProxySQL 2.3.2 installation, when local dns resolver has been restarted, restart lasts 500ms, we see many connect timeout errors in ProxySQL log:

2022-02-10 12:20:12 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on mysql_pxc-1:3306 : exceeded by 2005886us
2022-02-10 12:20:12 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on mysql_pxc-1:3306 : exceeded by 2006211us
2022-02-10 12:20:12 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on 10.100.0.28:3306 : exceeded by 2005548us
2022-02-10 12:20:12 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on mysql_pxc-1:3306 : exceeded by 2005115us
2022-02-10 12:20:12 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on 10.100.0.28:3306 : exceeded by 2005548us
2022-02-10 12:20:12 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on mysql_pxc-1:3306 : exceeded by 2005115us
2022-02-10 12:20:12 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on mysql_pxc-1:3306 : exceeded by 2005596us
2022-02-10 12:20:12 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on mysql_pxc-1:3306 : exceeded by 2002677us
2022-02-10 12:20:12 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on mysql_pxc-1:3306 : exceeded by 2004805us
2022-02-10 12:20:12 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on mysql_pxc-1:3306 : exceeded by 2004805us
2022-02-10 12:20:13 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on mysql_pxc-1:3306 : exceeded by 2005389us
2022-02-10 12:20:13 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on mysql_pxc-1:3306 : exceeded by 2002872us
2022-02-10 12:20:13 mysql_connection.cpp:686:handler(): [ERROR] Connect timeout on mysql_pxc-1:3306 : exceeded by 2004484us

Also we see connect timeouts error to mysql server which hostname is identified with IP only in this situation