M
M
Mikhail Ermakov2017-03-03 15:37:16
MySQL
Mikhail Ermakov, 2017-03-03 15:37:16

Why is the procedure in which the query runs longer than the "naked" query?

Faced a problem: the same request is fulfilled for different time if to push it in procedure and if it to execute out of procedure.
Take for example a query like SELECT 1;
Let's execute it in the client, for example in dbForge, as a text - 0ms
Let's execute it as a Procedure - 200ms
Don't trust the client? Ok, let's try the standard utility. You see the result)
fba16101da1a4f77bd0cf554100bf274.jpg
I tried to execute it directly on the server - it's excellent there: both the request and the procedure are processed in 0 ms.
I read on the topic - there were guesses about the encoding. I changed the base encoding - the result does not change.
The most interesting thing is that there are procedures that make much more complex queries and run in 3-4ms on the client! So it's hardly a connection!
FreeBSD OS
Mysql version 5.6.25
UPD: So, it turned out empirically that by deploying a database backup with identical configs on Debian, this problem magically disappears. Maybe there are FreeBSD experts who know some subtleties of MySQL operation on this OS?
UPD2: What else turned out: if the client connects to localhost from the console, everything works out quickly, but if you connect to the same server, but via an external ip (192.168.0.27) - even calling stored procedures from the console on the server side takes 100ms!

Answer the question

In order to leave comments, you need to log in

1 answer(s)
M
Mikhail Ermakov, 2017-03-13
@zErmak

Problem solved.
By poking and sorting, it turned out that if the server is running on FreeBSD, and the bind-address parameter in my.cnf is not specified, then the situation described above occurs. If you specify bind-address - everything works quickly, and the procedure also completes in 1ms. At the same time, this bug is not observed on Ubuntu server or debian in the absence of this parameter.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question