mysqldump + ssl

[Jamie McClown]

Hi - I get the feeling that using mysqldump is not recommended via proxy sql. However, I'm asking because we operate a shared server using proxysql and most of our users don't even know it's running - so they expect to just run mysqldump and have it work.

The problem: about 80% of the time we dump a database or table with mysqldump, it hangs indefinitely.

However:

1. When I change the host to dump directly from the MariaDB database server, it works 100% of the time. And...

2. When I add --skip-ssl to the mysqldump command line (or set mysql-have_ssl=false) and go through Proxysql, it also works 100% of the time.

I tested this on one of our older servers running proxysql 2.0.18 (with mysql-have_ssl=true) and also on proxysql 3.0.3 (mysql-have_ssl=true is enabled by default) - exact same results.  Also, enabling or disabling proxysql tls with the backend has no effect.

Does anyone have any suggestions for debugging this problem? Thanks!

jamie

[René Cannaò]

Hi Jamie,

This is not expected.

Can you please share more details on how to reproduce it, and your current observations?

Few questions that come to mind right now:

* what OS are you using?

* what is the size of the database you are making a backup of?

* what is the network latency between client (mysqldump) and proxysql ? (this may sound like a weird question, but in a very old release , perhaps early 2.0 , we had a timing bug related to SSL)

* do you note any anomaly in CPU/memory usage in proxysql ?

* how quickly it hangs? Does it transfer any considerable amount of data? Does it hang at the beginning, at the end, or randomly? What's the size of the incomplete backup?

* what does "SELECT * FROM stats_mysql_processlist" report about that specific session?

* anything relevant on the error log?

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/proxysql/2acacc50-232a-4912-b3b6-865112c51032n%40googlegroups.com.

[Jamie McClown]

Hi - Thanks for the reply.

I've experienced the problem running Proxysql 2.0.4 on Debian Bookworm and also Proxysql 3.0.3 on Debian Trixie.

I've replicated with dumps of varying sizes, even as little as 1MB and as big as 70MB sql files. I've tried dumping the entire database and also just a single table. All with the same effect.

Latency between the proxysql server and the backend database is very low:

64 bytes from 10.9.67.57: icmp_seq=1 ttl=64 time=0.574 ms
64 bytes from 10.9.67.57: icmp_seq=2 ttl=64 time=0.929 ms
64 bytes from 10.9.67.57: icmp_seq=3 ttl=64 time=0.654 ms
64 bytes from 10.9.67.57: icmp_seq=4 ttl=64 time=1.04 ms

The CPU usage of proxysql seems unaffected when it hangs compared with other times - always around 2%.

When it hangs, it hangs within a few seconds. Regardless of the size of the table being dumped, it always gets between 300 and 500 KB before it hangs. Examples of exact amount of bytes dumped: 327680, 344064, 454656. Often it dumps the exact same amount of bytes when I run it over and over on the same table.

SELECT * FROM stats_mysql_processlist:

+----------+-----------+-----------+-----------+-----------+----------+-----------+------------+------------+------------+----------+---------+---------+------+--------------+---------------+
| ThreadID | SessionID | user      | db        | cli_host  | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host   | srv_port | command | time_ms | info | status_flags | extended_info |
+----------+-----------+-----------+-----------+-----------+----------+-----------+------------+------------+------------+----------+---------+---------+------+--------------+---------------+
| 2        | 62        | testjamie | testjamie | localhost | NULL     | 9         | 10.9.67.61 | 39444      | 10.9.67.57 | 3306     | Sleep   | 44051   | NULL | 16           | NULL          |
+----------+-----------+-----------+-----------+-----------+----------+-----------+------------+------------+------------+----------+---------+---------+------+--------------+---------------+

I don't get anything in the logs except:

[WARNING] Closing unhealthy client connection localhost:0

When I ctrl-c to cancel. Although I don't think I'm properly setting a high enough logging level - can you point me in the right direction to get more verbose logging for this?

Thanks for your help.

jamie

[René Cannaò]

Hi Jamie,

I think a very important piece of information just arise.

I asked "what is the network latency between client (mysqldump) and proxysql ?" , but you replied with the latency between proxysql and the backend. I found it odd, until I continued reading ...

From the output of stats_mysql_processlist and "Closing unhealthy client connection localhost:0" we can spot a valuable information: you are connecting to ProxySQL using unix socket domain . Can you please confirm this?

If this is true, can you please try a different approach and use TCP?

mysqldump -h 127.0.0.1 -P <port> ....

Let me know if the problem persists or not.

If the problem goes away then the issue is likely related to the use of unix socket domain, and it is possible that there is a bug in the specific scenario of using SSL via unix socket domain and large resultsets, that to be honest it is a scenario for which we don't have a lot of testing.

The handling of TCP connections and unix sockets are not identical.

We can try to reproduce it in the next days.

Thanks,

René

To view this discussion visit https://groups.google.com/d/msgid/proxysql/c0f1135b-70a0-4381-becd-174b79abe100n%40googlegroups.com.

[Jamie McClown]

Yes!! Nice detective work. When I switch from -h localhost to -h 127.0.0.1 it consistently works.

Thanks for your help getting to the bottom of it. If you make any patches, please let me know and I would be happy to test them.

jamie