-2

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

Marat Minnekhanov
  • 1
  • 2
    Might be about network latency, table structure, indexes and query efficiency. So, you should give more details about your schema and query. – abeyaz Sep 14 '17 at 11:46
  • It use 32 tables including self joins (13 unique tables) executing in 3 seconds in one connection, but in two parallel it will take 6 seconds in each process and so on, like their executes in queue. Query are big enough with many joins but without any aggregating and sorting. only one table scan 4262 rows with using_where and 45 % filtered, other using index – Marat Minnekhanov Sep 14 '17 at 12:31
  • Looks like you are locking the table in each query. Are you using BEGIN TRANSACTION ? – abeyaz Sep 14 '17 at 12:34
  • If each query adds another ~3seconds, it obviously locks the table. – abeyaz Sep 14 '17 at 12:35
  • No, there's no BEGIN TRANSACTION and autocommit is on. But why simple reading lock tables? Even in READ UNCOMMITED mode. Is the reason some full table scan? But i can't see it in EXPLAIN – Marat Minnekhanov Sep 14 '17 at 12:47

1 Answers1

0

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?

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 1 CPU core, 15GB RAM. Even 2 process lead to increase time twice for each. there's no insert/update activity on whole DB. And yes, seems to have a bad query. But can't optimize it well, cause it implemented in API. So is horizontal sharding may help? – Marat Minnekhanov Sep 15 '17 at 06:58
  • Perhaps CPU is at 100% when it is running? That would explain 3s for one; 6s for 2. If you can't change the query generated, you can't implement sharding. Let's see the query. – Rick James Sep 15 '17 at 15:54