I have a quite peculiar problem. I have a website that is running on a ubuntu server under Apache2.4. The site is written in PHP and uses MySQL as a database. I have realised "file storage" for small files in the MySQL database as longblobs.
When I want to download a blob/file from the MySQL database, I open a PDO connection to the database.
$sql = $pdoData->prepare("SELECT * FROM fileblob WHERE ID = ?");
$sql->execute(array($blobID));
$row = $sql->fetch();
$content = $row['data'];
via "echo $content" I then transmit the file to the browser. All of this works flawlessly. However. During the download the whole site stops responding, every new request runs into a timeout. The download is continuing fine.
When the download gets to 4MB remaining, the website starts functioning again, but very slowly. When it is finished, the site is completely back to normal.
The MySQL database uses an InnoDB backend, has a maximum of 500 concurrent connections etc.
During the time of the download there are only 6 open SQL connections. Disk usage is at max at around 20%,CPU usage under 10% Apache is also set up to handle 1k concurrent connections (10 threads with 100 children at max). The server is connected via an unmetered 1Gbps line. I can not think of any hardware bottleneck.
What am I missing? I am happy to answer any questions...
When you are using PHP's default file-based session mechanism, PHP locks the session data file while any script accessing that specific session is running.
So while your download is running, the session data file is kept open all the while, and any other scripts are prevented from accessing the same session - they will simply have to wait, until the file lock is released again.
To help prevent that, session_write_close can be used. It tells PHP that the current script is done with manipulating the session data, so that the session content can be written to the data file now, and the file lock released, so that other scripts can access it again.
(You obviously need to be done with your session data manipulation at this point; you can not change session contents again in that same script afterwards.)