Performance difference connecting locally vs. remotely
Andrew Gideon
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
The Natural Philosopher
> 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?
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.
Axel Schwenke
> 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).
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.
> Note that my SSH connection is nowhere close to using the available
> bandwidth.
Sure. It's CPU-bound, not I/O-bound.
> Is there anything I can do about it?
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
> 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.
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
Andrew Gideon
> As others already said: SSH encrypts, optionally compresses and
> multiplexes packets between endpoints. This is slow.
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.
[...]
> 1. you can upload (scp) your SQL file to the remote machine and
> then load it there
That's what I did for "local" testing. In production this won't be an
option.
> 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
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.
>> 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.
>
> 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.
That's unfortunate. I was hoping.
Thanks...
Andrew
Thomas 'PointedEars' Lahn
> 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
UUOC.
remote: nc -l -p 4711 | mysql -u... -p...
local: nc remote 4711 < sqlfile
--
PointedEars
Andrew Gideon
> I'm running that test now.
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
Axel Schwenke
>
> 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.
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.
> 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.
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
> 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.
Exactly.
> I believe that an asynchronous approach would eliminate the delay as
> those round-trips could occur in parallel with the actual processing of
> queries.
Drizzle enhanced the MySQL protocol to allow pipelining:
http://docs.drizzle.org/protocol.html
> I could shift this to a more asynchronous approach by using
> multiple TCP connections. Has anyone done something like this?
Of course. But before you can load parallel, you have to dump
parallel: http://dom.as/2009/02/03/mydumper/
> 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.
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.
> 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?
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