I store profile photos in my database and I've written a small C# console app that is supposed to just export these photos to disk somewhere. I select 10 photos at a time in a loop:
const int pageSize = 10;
using (var connection = new MySqlConnection(_options.Connectionstring))
{
connection.Open();
var pageCount = pageSize;
for (var page = 0; pageCount == pageSize; page++)
{
using (var command = new MySqlCommand($"SELECT
p.FirstName, p.LastName, p.Department, ph.Data
FROM Persons p
INNER JOIN Photos ph ON ph.PersonId = p.Id
LIMIT {pageSize} OFFSET {page * pageSize}", connection)
using (var reader = command.ExecuteReader())
{
for (pageCount = 0; reader.Read(); pageCount++)
{
var path = GetFilePath(reader, _options.Pattern);
EnsureDirectoryExists(path);
File.WriteAllBytes(path, (byte[])reader["Data"]);
}
}
}
}
The code is slightly modified to make it more concise, but I've mainly removed some validation and logging.
Monitoring the server in MySql Workbench while running the application shows the "InnoDB Buffer Usage" slowly creeping up until it reaches 100% after about 2 minutes or 1000 images selected. From what I've read this is perfectly normal, but the app also exports images slower and slower until the InnoDB buffer gets close to 100%, at which point the app starts timing out:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Database server info:
Photos table definition:
CREATE TABLE `Photos` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`PersonId` int(11) NOT NULL,
`Data` longblob NOT NULL,
PRIMARY KEY (`Id`),
KEY `FK_PersonId_IDX` (`photo_person_id`),
CONSTRAINT `FK_PersonId` FOREIGN KEY (`PersonId`) REFERENCES `Persons` (`Id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The memory usage of the app itself is very low. Increasing innodb_buffer_pool_size does increase the amount of time (or number of photos exported) until the problem occurs, proportional to the increase in size, but I don't want to have to add more memory to the server just to be able to export these images.
It seems to me that filling the InnoDB buffer pool with large-ish blobs is causing this problem, but from what I understand there's really no way to prevent this from happening if I do want to select all of these blobs, so what can I do? I realize I could probably increase the timeout but that's just a bad solution for obvious reasons, and I have considered changing the Photos table to MyISAM which I suppose would fix the problem, but if there's some other easy solution I'd rather not do that. I am also open to completely alternative solutions on exporting the photos if there's actually no other way around this problem.
I don't really know what other info is relevant so please ask in the comments for any other details.
Don't use OFFSET, it must step through all those rows to get to the 10 you really want. Also, if rows are being added/deleted, you could miss or duplicate a row.
Instead remember where you left off
If, for some reason, that cannot be implemented, then use "lazy eval" wherein you fetch just the 10 ids, not the rest of the columns. This is in a subquery. Then JOIN back to the table to get the rest of the desired columns.