Increase connections to mysql cause rising sending data time in each on same query

Question

Forking multiple process in php (Supervisor). Each create connection to same Mysql DB and execute same SELECT query in parallel (Gearman). If i increase amount of processes (i.e. same time connections) and more same queries will run in parallel lead to increase sending data time in SHOW PROCESSLIST in each process. It's a simple select with transaction level READ UNCOMMITED. Is it some mysql config issue? Or SELECT query caused tables locks? Or maybe full scan does?

Server: Ubuntu 16.04.2 LTS. 1 CPU core. MySQL 5.7.17. innodb_buffer_pool_size 12 GB

Answer 1

It use 32 tables including self joins (13 unique tables) executing in 3 seconds in one connection

Gotta see the details. Sounds like missing or inadequate indexes.

Is this "Entity-Attribute-Value? If so, have you followed the tips here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

InnoDB does not lock tables. But it could be doing table scans which would lock all rows. Again, sounds like bad indexes and/or query formulation.

Please provide SHOW CREATE TABLE for all 13 tables, plus SELECT and EXPLAIN SELECT ....

If there is some kind of write going on in the background, that could impact the SELECT, even in READ UNCOMMITTED mode.

At least 16GB of RAM?

Forking how many processes? How many CPU cores do you have?