Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
246 views
in Technique[技术] by (71.8m points)

php - Speed/best practice flushing mysqli_multi_query()

I cringed when Sebastien stated he was disconnecting & reconnecting between each use of mysqli_multi_query() @ Can mysqli_multi_query do UPDATE statements? because it just didn't seem like best practice.

However, Craig @ mysqli multi_query followed by query stated in his case that it was faster to disconnect & reconnect between each use of mysqli_multi_query() than to employ mysqli_next_result().

I would like to ask if anyone has further first-hand knowledge or benchmark evidence to suggest an approximate "cutoff" (based on query volume or something) when a programmer should choose the "new connection" versus "next result" method.

I am also happy to hear any/all concerns not pertaining to speed. Does Craig's use of a connecting function have any bearing on speed?

Is there a speed difference between Craig's while statement:

while ($mysqli->next_result()) {;}

- versus -

a while statement that I'm suggesting:

while(mysqli_more_results($mysqli) && mysqli_next_result($mysqli));

- versus -

creating a new connection for each expected multi_query, before running first multi_query. I just tested this, and the two mysqli_multi_query()s were error free = no close() needed:

$mysqli1=mysqli_connect("$host","$user","$pass","$db");
$mysqli2=mysqli_connect("$host","$user","$pass","$db");

- versus -

Opening and closing between each mysqli_multi_query() like Sebastien and Craig:

$mysqli = newSQL();
$mysqli->multi_query($multiUpdates);
$mysqli->close();

- versus -

Anyone have another option to test against?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

It is not next_result() to blame but queries themselves. The time your code takes to run relies on the time actual queries take to perform.

Although mysqli_multi_query() returns control quite fast, it doesn't mean that all queries got executed by that time. Quite contrary, by the time mysqli_multi_query() finished, only first query got executed. While all other queries are queued on the mysql side for the asynchronous execution.

From this you may conclude that next_result() call doesn't add any timeout by itself - it's just waiting for the next query to finish. And if query itself takes time, then next_result() have to wait as well.

Knowing that you already may tell which way to choose: if you don't care for the results, you may just close the connection. But in fact, it'll be just sweeping dirt under the rug, leaving all the slow queries in place. So, it's better to keep next_result() loop in place (especially because you have to check for errors/affected rows/etc. anyway) but speed up the queries themselves.

So, it turns out that to solve the problem with next_result() you have to actually solve the regular problem of the query speed. So, here are some recommendations:

  1. For the select queries it's usual indexing/explain analyze, already explained in other answers.
  2. For the DML queries, especially run in batches, there are other ways:

Speaking of Craig's case, it's quite much resembling the known problem of speed of innodb writes. By default, innodb engine is set up into very cautious mode, where no following write is performed until engine ensured that previous one were finished successfully. So, it makes writes awfully slow (something like only 10 queries/sec). The common workaround for this is to make all the writes at once. For insert queries there are plenty of methods:

  • you can use multiple values insert syntax
  • you can use LOAD DATA INFILE query
  • you can wrap all the queries in a transaction.

While for updating and deleting only transaction remains reliable way. So, as a universal solution such a workaround can be offered

 $multiSQL = "BEGIN;{$multiSQL}COMMIT;";
 $mysqli->multi_query($multiSQL);
 while ($mysqli->next_result()) {/* check results here */}

If it doesn't work/inapplicable in your case, then I'd suggest to change mysqli_multi_query() for the single queries run in a loop, investigate and optimize the speed and then return to multi_query.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...