title: How to parallelize requests with MySQL+PHP+nginx?
body: <h1>Recent Discovery</h1>
  
  <p>Among everything else I tried, I replaced my JMeter profile with a custom JavaScript that hit each of my API endpoints in turn in an infinite loop, and then ran this script in parallel in different browsers (one Firefox, one Chrome, one Safari) - to try and rule out issues related to all of my connections coming from the same source (same user agent, same cookies, same session ID, etc)</p>
  
  <p>When I did this, I noticed that all of my issues went away. The queries ran in parallel and the app was a lot more responsive than JMeter would have you believe</p>
  
  <p>It seems impossible to me that JMeter would be serializing the requests, since it's a de facto standard for load testing. So I started trying to reproduce the behavior</p>
  
  <p>In an attempt to re-create the JMeter I created the following two PHP scripts which (hopefully) simulated my Yii application:</p>
  
  <p><strong><em>slow.php</em></strong></p>
  
  <pre><code>&lt;?php
  
  session_start();
  
  $_SESSION['some'] = 'value';
  // Yii is calling session_write_close() almost immediately after
  // the session is initialized, but to try and exacerbate issues,
  // I've commented it out:
  // session_write_close();
  
  $dsn = "mysql:host=localhost;dbname=platypus;unix_socket=/tmp/mysql.sock";
  $pdo = new PDO($dsn, "user", "password");
  // Yii was using whatever the default persistence behavior was,
  // but to try and exacerbate issues I set this flag:
  $pdo-&gt;setAttribute(PDO::ATTR_PERSISTENT, true);
  // Simulate a query running for 1 second by issuing a 1-second sleep
  $pdo-&gt;query("DO SLEEP(1)");
  
  echo "Done";
  </code></pre>
  
  <p><strong><em>fast.php</em></strong></p>
  
  <pre><code>&lt;?php
  
  session_start();
  
  $_SESSION['some'] = 'value';
  
  $dsn = "mysql:host=localhost;dbname=platypus;unix_socket=/tmp/mysql.sock";
  $pdo = new PDO($dsn, "user", "password");
  $pdo-&gt;setAttribute(PDO::ATTR_PERSISTENT, true);
  // Simulate a query running for 0.1 seconds
  $pdo-&gt;query("DO SLEEP(0.1)");
  
  echo "Done";
  </code></pre>
  
  <p>Running JMeter against these two new endpoints there was no serialization of requests. Everything ran in parallel. <strong>fast.php</strong> always returned in 100-150ms and slow.php always returned in 1000-1050ms even as I scaled up to 3, 4, and 5 threads. I <strong><em>was</em></strong> able to watch things collapse at 11 threads, but that's because I exceeded the number of worker threads in PHP</p>
  
  <p>So to summarize:</p>
  
  <ul>
  <li>The issue only occurs when profiling my API with JMeter and is <strong>not</strong> inherent in the app itself</li>
  <li>The issue isn't just a JMeter bug, but is somehow tied to my application or Yii 1.1</li>
  <li>I tried but could not come up with a minimum repro case</li>
  </ul>
  
  <p>Despite the issue being non-existent when profiling with others tools, lots of people responded and gave lots of helpful information:</p>
  
  <ul>
  <li>Avoid persistent connections in PHP (<strong>could</strong> cause multiple requests to share a connection, probably not)</li>
  <li>Avoid session locking by calling <code>session_write_close()</code> as early as possible</li>
  <li>Ensure you have enough PHP worker threads to handle the number of simultaneous connections</li>
  <li>MySQL fully supports parallel requests (if the hardware can handle it)</li>
  <li>Be wary of table locking (any transaction with an <code>UPDATE</code> statement could potentially lock the tables)</li>
  <li>MyISAM does table-level locking instead of row-level locking</li>
  </ul>
  
  <h1>Original Post</h1>
  
  <p>I inherited a web application and I'm trying to make sense of its performance profile so I can start optimizing it for speed.</p>
  
  <p>One thing I noticed pretty early on is that requests to the server are being serialized. For instance, suppose I have three endpoints with response times like so:</p>
  
  <pre><code>/api/endpoint1 --&gt; 50ms
  /api/endpoint2 --&gt; 1000ms
  /api/endpoint3 --&gt; 100ms
  </code></pre>
  
  <p>If I hit a single endpoint, I measure the appropriate response times. But when I set up a script to hit all 3 at once I will sometimes see something like the following:</p>
  
  <pre><code>endpoint1: 50ms
  endpoint2: 1050ms
  endpoint3: 1150ms
  </code></pre>
  
  <p>Clearly the call to endpoint3 was queued and waiting for the call to endpoint2 to finish before it got a response.</p>
  
  <p>My first thought was that this should be trivially solved with multithreading, so I took a look at the server configuration. PHP-FPM's process manager was set to "dynamic" with "start_servers" of 1, "max_children" of 5, and "max_spare_servers" of 2. For testing purposes I swapped this to "static" so that 5 PHP processes would remain open for handling connections in parallel (more than the 3 for the number of endpoints I was hitting, so they should be able to process simultaneously)</p>
  
  <p>This had no impact on performance, so I looked at my nginx config. "worker_processes" was set to 1 with "worker_connections" set to 1024. I know that nginx uses an event loop model, so it shouldn't be blocking while it waits for a response from PHP-FPM. But <strong><em>just in case</em></strong>, I bumped up "worker_processes" to 5</p>
  
  <p>Still, no effect. So next I looked at the database. All 3 endpoints had to hit the database, and I know as a fact that the 1000ms response time is mostly spent waiting on a long-running database query. I tried setting "thread_pool-size" to 5 and also within the MySQL REPL I set "innodb_parallel_read_threads" and "mysqlx_min_worker_threads" to 5</p>
  
  <p>Still, my requests were getting serialized. When I log into the MySQL REPL and type <code>show processlist;</code> while my script is running (using a while-true loop to repeatedly hit those 3 API endpoints) I noticed that there was only ever one connection to the web application's user</p>
  
  <p>Unfortunately I'm not sure if my issue lies with the database (not allowing more than one connection), with PHP-FPM (not processing more than one request at a time), or with nginx (not forwarding more than one request at a time to PHP-FPM). I'm also not sure how to figure out which one is acting as the bottleneck</p>
  
  <h2>Update</h2>
  
  <p>Looking around some more I found <a href="https://stackoverflow.com/a/35128498/436976">this SO post</a> which seems to suggest that MySQL doesn't support parallel queries from the same user (e.g. from the web application user)</p>
  
  <p>Is this true? Surely such a ubiquitous database engine wouldn't have such a performance flaw, especially given how commonly it's used with AWS for massively scaled applications. I understand that for simple "read from disk" queries parallelizing them wouldn't improve performance since they'd just have to sit in a queue waiting on disk I/O, but modern databases have in-memory caches, and most of the really slow operations like filesort tend to happen in memory. There's no reason a disk-bound query couldn't run in parallel (make a request to disk and start waiting on I/O) while a cpu-bound query is busy sorting a table in RAM. The context switching may slightly slow down the cpu-bound queries, but if slowing those down from 1000ms to 1200ms means my 5ms query can run in 5 ms, I think that's worth it.</p>
  
  <h2>My queries</h2>
  
  <p>Here are the queries for my 3 endpoints. Note that the timings listed are the response time for the <strong><em>full HTTP pipeline</em></strong> (from browser request to response) so this includes overhead from nginx and PHP, plus any post-processing of the query done in PHP. That said, the query in endpoint 2 makes up 99% of the runtime, and locks the database so that endpoints 1 and 3 are queued up instead of returning quickly.</p>
  
  <p><strong>endpoint1 (50ms)</strong></p>
  
  <pre><code>SELECT * FROM Widget WHERE id = 1 LIMIT 1
  </code></pre>
  
  <p>(Note that 50ms is the full response time for the endpoint, not how long the query takes. This query is clearly on the order of microseconds)</p>
  
  <p><strong>endpoint2 (1000ms)</strong></p>
  
  <pre><code>USE platypus;
  SELECT `t`.`(49 fields)` AS `t0_cX`,
         `site`.`(29 fields)` AS `t2_cX`,
         `customer`.`(26 fields)` AS `t4_cX`,
         `domain`.`(20 fields)` AS `t6_c0`,
         `domain-general_settings`.`(18 fields)` AS `t8_cX`,
         `domain-access_settings`.`(17 fields)` AS `t9_cX`,
         `customer-general_settings`.`(18 fields)` AS `t10_cX`,
         `customer-access_settings`.`(17 fields)` AS `t11_cX`,
         `site-general_settings`.`(18 fields)` AS `t12_cX`,
         `site-access_settings`.`(17 fields)` AS `t13_cX`,
         `backup_broadcast`.`(49 fields)` AS `t14_cX`,
         `playlists`.`(11 fields)` AS `t16_cX`,
         `section`.`(10 fields)` AS `t17_cX`,
         `video`.`(16 fields)` AS `t18_cX`,
         `general_settings`.`(18 fields)` AS `t19_cX`,
         `access_settings`.`(17 fields)` AS `t20_cX`,
  FROM   `broadcast` `t`
         LEFT OUTER JOIN `site` `site`
                      ON ( `t`.`site_id` = `site`.`id` )
         LEFT OUTER JOIN `customer` `customer`
                      ON ( `site`.`customer_id` = `customer`.`id` )
         LEFT OUTER JOIN `domain` `domain`
                      ON ( `customer`.`domain_id` = `domain`.`id` )
         LEFT OUTER JOIN `generalsettings` `domain-general_settings`
                      ON ( `domain`.`general_settings_id` =
                           `domain-general_settings`.`id` )
         LEFT OUTER JOIN `accesssettings` `domain-access_settings`
                      ON
         ( `domain`.`access_settings_id` = `domain-access_settings`.`id` )
         LEFT OUTER JOIN `generalsettings` `customer-general_settings`
                      ON ( `customer`.`general_settings_id` =
                           `customer-general_settings`.`id` )
         LEFT OUTER JOIN `accesssettings` `customer-access_settings`
                      ON ( `customer`.`access_settings_id` =
                           `customer-access_settings`.`id` )
         LEFT OUTER JOIN `generalsettings` `site-general_settings`
                      ON ( `site`.`general_settings_id` =
                           `site-general_settings`.`id` )
         LEFT OUTER JOIN `accesssettings` `site-access_settings`
                      ON ( `site`.`access_settings_id` =
                           `site-access_settings`.`id` )
         LEFT OUTER JOIN `broadcast` `backup_broadcast`
                      ON ( `t`.`backup_broadcast_id` = `backup_broadcast`.`id` )
                         AND ( backup_broadcast.deletion IS NULL )
         LEFT OUTER JOIN `playlist_broadcast` `playlists_playlists`
                      ON ( `t`.`id` = `playlists_playlists`.`broadcast_id` )
         LEFT OUTER JOIN `playlist` `playlists`
                      ON
         ( `playlists`.`id` = `playlists_playlists`.`playlist_id` )
         LEFT OUTER JOIN `section` `section`
                      ON ( `t`.`section_id` = `section`.`id` )
         LEFT OUTER JOIN `video` `video`
                      ON ( `t`.`video_id` = `video`.`id` )
                         AND ( video.deletion IS NULL )
         LEFT OUTER JOIN `generalsettings` `general_settings`
                      ON ( `t`.`general_settings_id` = `general_settings`.`id` )
         LEFT OUTER JOIN `accesssettings` `access_settings`
                      ON ( `t`.`access_settings_id` = `access_settings`.`id` )
  WHERE
  (
      (
          t.id IN (
      SELECT `broadcast`.id FROM broadcast
         LEFT JOIN `mediashare` `shares`
                ON ( `shares`.`media_id` = `broadcast`.`id` )
                   AND `shares`.media_type = 'Broadcast'
      WHERE 
      (
          (
              broadcast.site_id IN(
                  '489', '488', '253', '1083', '407'
              )
              OR
              shares.site_id IN(
                  '489', '488', '253', '1083', '407'
              )
          )
      )
          )
      )
      AND
      (
          (
              (
                  (t.deletion IS NULL)
              )
          )
          AND
          (
              IF(
                  t.backup_mode IS NULL,
                  t.status,
                  IF(
                      t.backup_mode = 'broadcast',
                      backup_broadcast.status,
                      IF(
                          t.backup_mode = 'embed',
                          IF(
                              t.backup_embed_status,
                              t.backup_embed_status,
                              IF(
                                  '2020-01-08 16:34:52' &lt; t.date,
                                  1,
                                  IF(
                                      t.date &gt; Date_sub(
                                          '2020-01-08 16:34:52',
                                          INTERVAL IF(t.expected_duration IS NULL, 10800, t.expected_duration) second
                                      ),
                                      10,
                                      12
                                  )
                              )
                          ),
                          t.status
                      )
                  )
              ) != 0
          )
      )
  )
  LIMIT  10;
  </code></pre>
  
  <p>This query takes roughly 1000ms to run, but the PHP for the endpoint is extremely simple (run the query, return the results as JSON) and only adds a couple milliseconds of overhead</p>
  
  <p><strong>endpoint 3 (100ms)</strong></p>
  
  <pre><code>SELECT * FROM platypus.Broadcast
      WHERE deletion IS NULL
      AND site_id IN (SELECT id FROM platypus.Site
                 WHERE deletion IS NULL
                   AND customer_id = 7);
  </code></pre>
  
  <p>There's additional validation on the PHP side here which makes this endpoint take 100ms. The SQL, as you can see, is still fairly simple.</p>
  
  <h2>Create Table Statements</h2>
  
  <p>As there is a post length limit in StackOverflow, I cannot show the CREATE TABLE for every single table touched by endpoint 2, but I can show at least one table. Others use the same engine.</p>
  
  <pre><code>CREATE TABLE `Widget` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `widget_name` varchar(255) NOT NULL,
    `widget_description` varchar(255) NOT NULL,
    `status` varchar(255) NOT NULL,
    `date_created` datetime NOT NULL,
    `date_modified` datetime NOT NULL,
    `auto_play` varchar(255) NOT NULL,
    `on_load_show` varchar(255) NOT NULL,
    `widget_content_source` varchar(255) NOT NULL,
    `associated_sites` text NOT NULL,
    `author_id` int NOT NULL,
    `associated_sections` text,
    `after_date` datetime DEFAULT NULL,
    `before_date` datetime DEFAULT NULL,
    `show_playlists` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    `is_classic` tinyint(1) NOT NULL,
    `default_site` int unsigned DEFAULT NULL,
    `auth_code_url` varchar(255) DEFAULT NULL,
    `widget_layout_id` int unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fk_Widget_widget_layout_id_WidgetLayout_id` (`widget_layout_id`),
    CONSTRAINT `fk_Widget_widget_layout_id_WidgetLayout_id` FOREIGN KEY (`widget_layout_id`) REFERENCES `WidgetLayout` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB AUTO_INCREMENT=1412 DEFAULT CHARSET=utf8
  </code></pre>
  
  <h2>Note</h2>
  
  <p>Notice that endpoint 2 doesn't even touch the Widget table, but endpoint 1 (which ONLY touches the Widget table) is also queued up. This eliminates the possibility of table locking.</p>
  
  <p>When observing the process list in MySQL, only one connection is ever being made to the database from the application user. The issue may therefore lie in my PHP configuration.</p>
  
  <h2>Explain for query 2</h2>
  
  <p>Attached is the <code>EXPLAIN SELECT ...</code> query for endpoint 2</p>
  
  <p><a href="https://i.stack.imgur.com/8buSq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8buSq.png" alt="enter image description here"></a></p>
  
  <h2>Simpler experiments</h2>
  
  <p>To try and determine where the parallel pipeline was falling apart, I created two simple scripts:</p>
  
  <p><strong>sleep.php</strong></p>
  
  <pre><code>&lt;?php
  sleep(5);
  echo "Done sleeping";
  </code></pre>
  
  <p><strong>return.php</strong></p>
  
  <pre><code>&lt;?php
  echo "That's all";
  </code></pre>
  
  <p>Doing this (sleeping in PHP) and running my script to hit these two endpoints with 3 threads I saw no issues. <strong>return.php</strong> always came back in ~11 milliseconds, despite <strong>sleep.php</strong> taking 5066 on average. I then tried doing the sleeping in MySQL:</p>
  
  <p><strong>sleep.php</strong></p>
  
  <pre><code>&lt;?php
  $pdo = new PDO("...", "user", "pass");
  $pdo-&gt;query("DO SLEEP(5)");
  echo "Done sleeping";
  </code></pre>
  
  <p>This, <strong>again</strong>, had no issues. The sleeping endpoint did not block the non-sleeping one.</p>
  
  <p>This means that the issue <strong>does not</strong> exist at the nginx, PHP, or PDO level - but that there <strong>must</strong> be some kind of table or row locking going on. I'm going to re-enable the general query log and scan through <strong>every</strong> query being performed to see if I can figure out what's happening.</p>
  
  <h2>Final Update</h2>
  
  <p>If you scroll up to "Recent Discovery" at the top of this post, you'll notice that I've modified my understanding of the problem.</p>
  
  <p>I was not having an issue with parallelization, but with JMeter. I have failed to create a simple repro case, but I know now that the issue does not lie with my application but rather with how I'm profiling it.</p>