PHP/Nginx layer adding to response time when loading simple MySQL query from RDS

Question

I've been dealing with this for a long time now and I've narrowed down where the problem lies.

Initially, I thought the problem was either my medium EC2 instance or my medium RDS instance but after connecting to my RDS via MySQL command line from my EC2 and experiencing no delay in query load/response time, I see now that the only things left to be causing this issue are PHP or Nginx.

Here is the code I'm executing:

$connection = mysqli_connect("database-3...us-east-2.rds.amazonaws.com", "admin", "...", "prod_db", 3306);
$start_time = microtime(true);
mysqli_query($connection, "SELECT * FROM `product_categories` ORDER BY `list_order` ASC");
$end_time = microtime(true);
echo "execution time: ".(($end_time-$start_time)*1000)."ms

";

Here is a screenshot of what I'm seeing most of the time when this code is executed:

However, a good chunk of time, I'm seeing this:

You can see it takes 10x as long some of the time to execute these queries.

This wouldn't be such a big deal if this was the worst of it. But my website is suffering immensely because of this delay. My page loads are sometimes taking 2-3 seconds because of 10ms query responses taking 100ms each.

Here is my nginx config file for my site:

server {
fastcgi_read_timeout 300;
proxy_read_timeout 300;

set $no_cache 1;

if ($arg_trycache = 1) {
    set $no_cache 0;
}   

root /var/www/html;

# Add index.php to the list if you are using PHP
index index.php;

server_name server.com www.server.com;

location / {
    try_files $uri $uri/ /index.php?$args;
}

location ~ ^\.php$ {
    include fastcgi_params;
    fastcgi_param SCRIPT_FILENAME $document_root/index.php;     
    include snippets/fastcgi-php.conf;
    

    # With php-fpm (or other unix sockets):
    fastcgi_pass unix:/var/run/php/php7.4-fpm.sock;
    # With php-cgi (or other tcp sockets):
    #fastcgi_pass 127.0.0.1:9000;
    
    fastcgi_cache ZONE_1;
    fastcgi_cache_valid 200 1m;
    fastcgi_cache_bypass $no_cache;
    fastcgi_no_cache $no_cache;
    fastcgi_param PHP_VALUE "memory_limit = 1024M";
    #fastcgi_buffer_size 16k;
    fastcgi_buffers 8 16k;
    fastcgi_buffer_size 32k;
    
    #limit_req zone=two;
    #limit_req zone=one burst=40 nodelay;
}

location ~ \.php$ { # will be used for any PHP request except 'index.php'
    return 404;
}

location ~ /api/v1/app-content/search-all.php {
    #include snippets/fastcgi-php.conf;
    #fastcgi_pass unix:/var/run/php/php7.4-fpm.sock;
    fastcgi_cache ZONE_1;
    fastcgi_cache_valid 200 1m;
    fastcgi_cache_bypass $no_cache;
    fastcgi_no_cache $no_cache;
    
}

# pass PHP scripts to FastCGI server
#
location ~ \.php$ {
    include snippets/fastcgi-php.conf;
    

    # With php-fpm (or other unix sockets):
    fastcgi_pass unix:/var/run/php/php7.4-fpm.sock;
    # With php-cgi (or other tcp sockets):
    #fastcgi_pass 127.0.0.1:9000;
    
    fastcgi_cache ZONE_1;
    fastcgi_cache_valid 200 1m;
    fastcgi_cache_bypass $no_cache;
    fastcgi_no_cache $no_cache;
    fastcgi_param PHP_VALUE "memory_limit = 1024M";
    #fastcgi_buffer_size 16k;
    fastcgi_buffers 8 16k;
    fastcgi_buffer_size 32k;
    
}


    location @rewrite {
        rewrite ^ $uri.php last;
    }


listen [::]:443 ssl ipv6only=on; # managed by Certbot
listen 443 ssl; # managed by Certbot

ssl_certificate /home/ubuntu/chain.crt; # managed by Certbot
ssl_certificate_key /home/ubuntu/server.key; # managed by Certbot

}

I'm truly at a loss at this point. I thought for days that it was somehow the RDS instance I have running but after eliminating that possibility by connecting via command line, I'm back at square one.

So essentially my question is this: is it possible that PHP itself or Nginx are the culprit here? Keep in mind that my PHP code is as simple as possible. No extra layers on top, just straight mysqli_ functions.

PHP/Nginx layer adding to response time when loading simple MySQL query from RDS

0 Answers