DBI and FEDERATED table slowdown

misterperl has asked for the wisdom of the Perl Monks concerning the following question:

some of my mysql 5.7 tables are federated, hosted on a remote machine, on the same LAN. Pings between the machines are good- about 80ms.
Prior to federation, DBI 1.643 transactions to the mysql database were fast. Since federation though, federated table transaction slowed to the point that users get messages like:

DBD::mysql::st execute failed: Got timeout reading communication packets

a WISE monk here once told me that DBI does nothing more than run the same mysql transactions as run ad hoc. That statement helped me, and held up through many trials for years (logged in as the same user running the same query, DBI and ad hoc produce the same result, or error, in roughly the same time).

So, I tried the exact query that timed out in mysql, and the federated table (joined with a local table) responded immediately; no apparent speed difference with federated vs local tables from the mysql ad hoc query. I'd expected given the theorm above, it would also time out, or at least be slow. No; lightning fast!

So I propose that theorm is not always true- there is some apparent and significant difference using DBI, vs ad hoc queries, on federated tables. Being a pretty rare configuration, I'm not confident that even the astute monk community would have advice on this topic, and I may have to ask the esteemed Tim Bunce if he's still in the DBI business! If not I wish him well.

Best regards to all Monks, and any thoughts are appreciated. Gemini suggested increasing the TIMEOUTS on each end which I did, but that didn't help.

Comment on DBI and FEDERATED table slowdown Download Code

Replies are listed 'Best First'.
Re: DBI and FEDERATED table slowdown by hippo (Archbishop) on Feb 27, 2024 at 23:17 UTC
... on the same LAN. Pings between the machines are good- about 80ms. 80ms between machines on the same LAN is not good at all. I would expect to see sub-millisecond pings between machines on the same LAN. 🦛	[reply]
Re: DBI and FEDERATED table slowdown by talexb (Chancellor) on Feb 28, 2024 at 04:35 UTC
As hippo said, if you're getting an 80ms ping time for a server on a local network, there's something else going on. The speed of light is .3km/sec, so in 80ms, you should be able to do a round trip of 24km. Unless this a really, really large LAN, I'm guessing that there are delays elsewhere. In addition, DBI timeouts are usually in the order of seconds. I don't know what information you could get by using DBI->trace, but I'd give that a shot -- see where the time is being spent. Alex / talexb / Toronto Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.	[reply]
Re^2: DBI and FEDERATED table slowdown by Marshall (Canon) on Feb 28, 2024 at 07:16 UTC
Your estimate for the speed of light is wildly off. The speed of light in a vacuum is 299,792,458 meters per second. The speed within the fiber cable or coax is considerably slower than that. Typical coax might be 66% of vacuum speed. The time is going to be taken up in the various gateways and routers. Perhaps also in retransmissions. 80ms is so incredibly slow for a remote DB, that this is for sure a major problem. My home PC pings google in 13 ms round trip. A well-configured local LAN is going to be much faster than that.	[reply]
Re^2: DBI and FEDERATED table slowdown by hippo (Archbishop) on Feb 28, 2024 at 10:49 UTC
The speed of light is .3km/sec I think you are confusing the speed of light with the speed of sound (in air at sea level). 0.3km/s is Mach 1. so in 80ms, you should be able to do a round trip of 24km. Even if it were 0.3km/s, in 80ms it would only go 24m, not 24km. Fortunately, light speed is much, much faster than that at 2.99x10⁸m/s. This is how I can get round-trip pings across the Atlantic Ocean in 80ms and how I know it is a rubbish ping time for a LAN. The point about using DBI->trace is well made, though. I'd also add that a check of the slow query log might be instructive. 🦛	[reply]
Re^2: DBI and FEDERATED table slowdown by talexb (Chancellor) on Feb 28, 2024 at 14:52 UTC
Jeepers. That will teach me not to post just before bed. I meant to say .3Mkm/sec, you folks are 100% right. Anyway. It's the next morning, and now I've had my coffee. Looking forward to hearing what `trace` turns up. Alex / talexb / Toronto Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.	[reply] [d/l]
Re^3: DBI and FEDERATED table slowdown by soonix (Chancellor) on Feb 28, 2024 at 19:34 UTC
PMFJI, but .3 Mkm/sec? ITYM .3 Gm/sec - it's Gigameters, just like it's Gigabytes instead of Megakilobytes ;-)	[reply]
Re^3: DBI and FEDERATED table slowdown by cavac (Prior) on Mar 07, 2024 at 08:28 UTC
Well, you were not totally wrong, you just referenced the wrong set of dimensions. 300m/s is about right for the speed of light on Terry Pratchett's Discworld, due to the high intensity of its magic field ;-) Do you currently live in Ankh-Morpork and access PerlMonks through our Clacks/L-Space gateway? PerlMonks XP is useless? Not anymore: XPD - Do more with your PerlMonks XP	[reply]
Re^3: DBI and FEDERATED table slowdown by misterperl (Friar) on Feb 28, 2024 at 16:25 UTC
I can't believe this devolved into a physics discussion but everyone gets a ++ vote for their kind assistance!	[reply]
Re^2: DBI and FEDERATED table slowdown (time travel ;) by LanX (Saint) on Feb 28, 2024 at 10:40 UTC
> The speed of light is .3km/sec That's rather the speed of sound and would allow to time travel with supersonic planes. Hmm... OTOH it's true that the Concorde was capable to arrive before it left. ;) Cheers Rolf _{(addicted to the Perl Programming Language :) see Wikisyntax for the Monastery} Updates: added links and amended title	[reply]
Re^2: DBI and FEDERATED table slowdown by misterperl (Friar) on Feb 28, 2024 at 16:20 UTC
TY Monks, appreciate the thoughts. On our network, 80ms is, I should say, RELATIVELY fast. But I experimented a bit and you're totally correct- some nodes are sub 1ms. VERY interesting. I'm opening a ticket for our networking crew; I want <1ms I like your idea. I'll try that trace also- very useful idea.	[reply]
Re^3: DBI and FEDERATED table slowdown by afoken (Chancellor) on Feb 28, 2024 at 19:58 UTC
On our network, 80ms is, I should say, RELATIVELY fast. Tear it down and rebuild it from scratch. Ping from home to one of the servers at home is less than 1 ms. Ping to the office firewall is 16 ms (13 hops on the way, 40 km street distance). I've just logged in via VPN to the office network, and I get a constant ping of 16 ms to a random server in the office. I also pinged another system connected via VPN over LTE to the office network to get 52 ms ping time (another 15 km street distance away). 80 ms in a LAN is impressively slow. It sounds like some firewall or router in between your machine and the other machine that is very overloaded and/or very misconfigured. Or you have a severe cabling problem. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]
Re^4: DBI and FEDERATED table slowdown by afoken (Chancellor) on Feb 29, 2024 at 12:21 UTC
Re^5: DBI and FEDERATED table slowdown (war stories) by LanX (Saint) on Feb 29, 2024 at 14:37 UTC
Re^3: DBI and FEDERATED table slowdown by talexb (Chancellor) on Feb 28, 2024 at 21:25 UTC
On our network, 80ms is, I should say, RELATIVELY fast. But I experimented a bit and you're totally correct- some nodes are sub 1ms. Yeah, 80ms is a little beefy. I just tried pinging my client (he's about 10km away, still inside Toronto) and got 16-27ms. Then I tried pinging my own server (with pair.com, same place that hosts this site) and got a much more consistent 17-21ms -- and that's all the way down in Pittsburgh, PA, about 50x further. Let us know how it all turns out. :) Alex / talexb / Toronto Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.	[reply]