I've a large file of many SQL statements (for the purpose of
populating). I've tried running this through mysql both (1) locally (but
using TCP) and (2) remotely (connecting via a circuit created via the -L
option of SSH). The latter is much slower.

Note that my SSH connection is nowhere close to using the available
bandwidth. I use it all the time for interactive logins, and I've never
noticed a latency issue.

When I use strace on the mysql process running remotely, I see slight
pauses while in a read() apparently awaiting the response from the
server. When I do the same on the mysql process running locally, I don't
see these pauses.

So...perhaps it's just latency that I don't normally notice, but which
adds up in this case given the large number of statements - and therefore
the large number of these little pauses. Is there anything I can do
about it?

One possibility would be to let the queries run asynchronously, where
query N could run before the response to query N-1 had been received. As
long as the queries ran in the proper order, that would be fine with me.

Unfortunately, I don't see a way to achieve this.

Any suggestions?

Thanks...Andrew

Andrew Gideon wrote:
No.

ssh encrypts and compresses traffic. That has to be decrypted and
decompressed, taking many CPU cycles
If you want speed, connect to the remote machines SQL daemon and do
security in another way.

Andrew Gideon <c182driver1 (AT) gideon (DOT) org> wrote:
Then this is *not* "locally connected" vs. "remotely connected"
but rather "directly connected" vs. "SSH tunnel"

As others already said: SSH encrypts, optionally compresses and
multiplexes packets between endpoints. This is slow.

Sure. It's CPU-bound, not I/O-bound.

I assume you cannot connect to the remote mysqld directly. But

1. you can upload (scp) your SQL file to the remote machine and
then load it there

2. you can use netcat to open an unencrypted (fast) tunnel:
remote: netcat -l -p 4711 | mysql -u... -p...
local: cat sqlfile | netcat remote 4711

This will not work. The MySQL wire protocol is synchronous.
The server will not accept a request packet before it has sent
the last response packet.


XL

On Mon, 04 Apr 2011 17:51:13 +0200, Axel Schwenke wrote:

I know. This all adds to latency. But since I don't normally notice it,
I thought I'd not notice it here. I suspect, as I wrote before, that I'm
noticing it here only because there are so many "transactions" (in the
communication sense of the word) that those little pauses are adding up.

[...]

That's what I did for "local" testing. In production this won't be an
option.

The server in question in behind a firewall and in RFC1918 address
space. Beyond doing some DNAT in our routers, my only access is via a VPN
(with its own encryption).

On the other hand, this makes me realize that I was SSHing over the VPN.
Thus: two steps of encryption. So I can try using netcat to eliminate
one of those steps, and see what happens.

I'm running that test now.

It'll take a while to run. But, just from cursory examination of "show
processlist", I'm not seeing the same occasional "query doing nothing"
states. So this is promising.

I'm also seeing higher bandwidth utilization on the link than normal.
That could just be a coincidence, but if it isn't then it is another good
sign.

That's unfortunate. I was hoping.

Thanks...

Andrew

Axel Schwenke wrote:

UUOC.

remote: nc -l -p 4711 | mysql -u... -p...
local: nc remote 4711 < sqlfile

--
PointedEars

On Mon, 04 Apr 2011 17:36:32 +0000, Andrew Gideon wrote:

I'm still running some tests, but two interesting cases stand out.

Where mysql connects to a remote mysqld over an encrypting VPN (w/o that
extra SSH I was imposing previously), it takes many hours. Where I
simply cat the file through nc to another nc through that same VPN, where
the destination nc is running on the same server that is running mysqld,
and where the destination nc is connecting via TCP to mysqld, it takes an
hour.

So just moving the mysql "further away" (where distance is measured in
terms of latency) slows the process. I'd guess that this is the result
of the synchronous nature of the protocol, where reply N must be received
by mysql before request N+1 is sent. Moving mysql further away means
that the reply takes more time, as does the transmission of the request.

Actual query processing takes the same time either way, of course.

I believe that an asynchronous approach would eliminate the delay as
those round-trips could occur in parallel with the actual processing of
queries. I could shift this to a more asynchronous approach by using
multiple TCP connections. Has anyone done something like this?

I don't even think a lot of parallelism is required to eliminate the cost
imposed by the latency, since I believe the processing time is higher
than the latency. I'd have to segment the queries into groups that were
unrelated, so there'd be no ordering requirement between groups. But the
query builder - which is processing some large data files I'm downloading
from FAA.GOV, in case anyone's interested - could do that pretty easily.

Another idea I'm considering is making use of an additional daemon which
would act in the role, more or less, of the receiving nc above. It would
basically provide a "bulk multiquery service". But hasn't this been done
before? Perhaps something like SQLRelay can be used this way? This
appears to me to be too straightforward an issue for it to never have
been addressed previously.

I'm not actually sure that this will work in my particular case, though,
as I don't believe that I'll be able to add this daemon on the server
running mysqld in production. But perhaps just getting to another box
that can reach that server w/o encryption or routing is sufficient. As I
wrote, I've more testing to do.

Any suggestions or thoughts?

Thanks...Andrew

Andrew Gideon <c182driver1 (AT) gideon (DOT) org> wrote:
I guess there is something about encryption and/or packet (de)-
fragmenting that causes increased latency. A mysqldump taken with
default parameters uses multi-row INSERT statements of ~1M length.
You must have really huge latency to see an effect under those
circumstances.

Netcat can do buffering (the network stack does) and is thus much
less affected by any latency issues. I guess you could do even better
with this pipe: cat $sqlfile | nc -network-> nc | buffer | mysql

Exactly.

Drizzle enhanced the MySQL protocol to allow pipelining:
http://docs.drizzle.org/protocol.html

Of course. But before you can load parallel, you have to dump
parallel: http://dom.as/2009/02/03/mydumper/

Parallel loading has benefits on current hardware. MySQL uses at
most one cpu core per connection, so on a N-core machine you will
leave N-1 cores unused when loading single-threaded.

Some years ago I did something like that. Back then I had a bunch of
separate data files (each could be seen as a handful of tables). The
idea was to sort them in descending size and start N loading processes.
Whenever one process was finished, the next job was started from the
queue. So as long as there was something in the queue, exactly N jobs
were running in parallel. Was some lines of Perl, around fork() and
exec() and wait().


XL