Why does my InnoDB database connection experience intermittent delays of an exact number of seconds?
A little context
In my MySQL database, I have a small 'users' table with 150 records and a 'connections' table that continues to grow (up to 22,500 records). The total size of the tables and indexes is less than 5 MB.
During user activity, some changes are made to the 'connections' table, with approximately 5-50 records being altered or added. Overall, the application performs well with load times averaging below ~100 ms.
However, there are instances where performance is not consistent.
Diving deeper
Even under light loads, page load times can suddenly spike from 60 ms to anywhere between 1,000 ms and 10,000 ms.
By utilizing Symfony's profiler, I identified that more than 95% of the delay was attributed to the 'getRepository' statement, despite individual queries taking only around 1 ms each. This discovery led me to suspect that the slow component could be the process of connecting to the database. To investigate further, I created a test script that regularly establishes connections to the database.
<?php // execute this script via command line using watch
$a = microtime(true);
$pdo = new PDO('mysql:host=127.0.0.1;dbname=mydb','myuser','mypass');
file_put_contents( 'performance.txt', (microtime(true)-$a).PHP_EOL, FILE_APPEND );
The perplexity
Upon testing, I observed that establishing a connection consistently took either 1-3 ms, 1,001-1,003 ms, 2,001-2,003 ms, 3,001-3,003 ms, and so forth. Each time measurement amounted to an integer number of seconds in addition to the regular time elapsed. Surprisingly, there were no intermediary values like 400 ms or 800 ms. Interestingly, when there were no write operations occurring, the connection process happened almost instantaneously. However, as soon as writes were initiated through the application, the extended time intervals surfaced.
What could explain this peculiar behavior? Could it be related to the functionality of the InnoDB page_cleaner, operating at 1,000 ms intervals?
More importantly, how can this issue be resolved? While considering transitioning to MEMORY tables, I believe there must be more sophisticated solutions available.
UPDATE
As requested, here are the variables and global status.
Additional details: My direct connection is to 127.0.0.1 (referencing the code snippet above), and experimentation with the skip-name-resolve flag did not yield any improvements. Notably, the server in use is Debian-based.
UPDATE 2
Further investigation revealed that the delays occurred precisely after 1, 3, 7, or 15-second intervals. Remarkably, these increments follow a consistent pattern: 1 second, +2s, +4s, +8s. Is it possible that this points to a timeout-related issue?