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.

In reply to DBI and FEDERATED table slowdown by misterperl

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.