0

There are two problems that I'm facing on a live site when executing a multi query statement using mysqli.

Terminology: when I say query I mean multiple INSERT and UPDATE statements wrapped in a transaction. Roughly 5k.

Issue #1: The query is executed fairly quickly (just a few seconds) but the database reflects the changes in increments of 10 to 20. Almost like I'm reaching an insert limit and there's a delay involved.

Issue #2: Due to multi statement query I have to call ->next_result(); to free each one of 5k results. This process takes so long that the page eventually times out.

Local setup: Vagrant with PHP 5.6 + MySQL 5.6 the above issues don't exist and everything runs just under 5 seconds (there's a CSV import involved).

Prod site: PHP 5.5 + MySQL 5.5.55-cll.

I tried comparing global variables and nothing really stands out except innodb_thread_concurrency which was set to 4 in comparison to my local value of 0. Updating the value on production site did not show any performance improvement.

mysql> describe product_filters_link;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| product | int(11) | NO   | PRI | NULL    |       |
| filter  | int(11) | NO   | PRI | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Any ideas where else I could look? Thank you.

dchayka
  • 1,291
  • 12
  • 20
  • Tough to say outright without seeing the schema or seeing your own `EXPLAIN` statements. – Makoto Oct 02 '17 at 00:32
  • @Makoto I've updated my question with the table schema. I don't think the table structure has anything to do with the processing of queries since I pulled the DB from the site and imported it locally and I get lightning fast processing. – dchayka Oct 02 '17 at 00:46
  • Check [php mysqli multi query asynchronous?](https://stackoverflow.com/q/6102968/6248528) It's hard to be more concrete without seeing the code, especially checking why the two setups might act differently. And a general note: there is rarely a reason to use multiquery, and almost never a reason to include 5k queries in it. You will also get a lot more control over your query(s) if you send them seperately (e.g. error handling, no problem with `next_result()`, ...) – Solarflare Oct 02 '17 at 07:50

0 Answers0