Debian Question posted in
The oficial documentation can be found here.

Update a table with longblobs is too slow – Debian

I have a table filled with data (about 20,000 records). I am trying to update it by the data from another table, but I have a timeout (30 seconds).
At first I tried a naive solution:

UPDATE TableWhithBlobs a
JOIN AnotherTable b on a.AnotherTableId = b.Id
SET a.SomeText= b.Description;

This script is working much longer then 30 seconds, so I tried to reduce join:

UPDATE TableWhithBlobs a
SET a.SomeText = (select b.Description from AnotherTable b where a.AnotherTableId = b.Id);

but this one is still very slow. Is there any cases how it could be fast?

Edit:

A bit explanation about what I’m doing. Previously, I had two tables, which in my script are called TableWhithBlobs and AnotherTable. In table TableWhithBlobs, a link to table AnotherTable was stored, but this link was not a real foreign key, it was just a guid from table AnotherTable. And there is a Unique key constraint for this reference in TableWhithBlobs for this guid. I decided to fix this, remove the old field from table TableWhithBlobs and add a normal foreign key to it (using the primary ID from AnotherTable). The script from the question just adds the correct data to this new field. After that, I delete old guid reference and add a new foreign key constraint. Everything works fine in the small amount of data in TableWhithBlobs, but on QA database with 20000 rows its extremely slow.

Update

SHOW CREATE TABLE TableWhithBlobs;

CREATE TABLE `TableWhithBlobs` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`AnotherTableId` char(36) CHARACTER SET ascii NOT NULL,
`ChunkNumber` bigint(20) NOT NULL,
`Content` longblob NOT NULL,
`SomeText` bigint(20) NOT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `AnotherTableId` (`AnotherTableId`,`ChunkNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

SHOW CREATE TABLE AnotherTable ;

CREATE TABLE `AnotherTable` (
`Description` bigint(20) NOT NULL AUTO_INCREMENT,
`Id` char(36) CHARACTER SET ascii NOT NULL,
`Length` bigint(20) NOT NULL,
`ContentDigest` char(68) CHARACTER SET ascii NOT NULL,
`ContentAndMetadataDigest` char(68) CHARACTER SET ascii NOT NULL,
`Status` smallint(6) NOT NULL,
`ChunkStartNumber` bigint(20) NOT NULL DEFAULT '0',
`IsTestData` bit(1) NOT NULL DEFAULT b'0',
PRIMARY KEY (`Description`),
UNIQUE KEY `Id` (`Id`),
UNIQUE KEY `ContentAndMetadataDigest` (`ContentAndMetadataDigest`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

PS. Column names may look weird because i want to hide the actual production scheme names.

innodb_buffer_pool_size is 134217728, RAM is 4Gb

Result of 

explain UPDATE TableWhithBlobs a JOIN AnotherTable b on a.AnotherTableId = 
b.Id SET a.SomeText= b.Description;

Update explanation

Version: mysql Ver 14.14 Distrib 5.7.21-20, for debian-linux-gnu (x86_64) using 6.3

Tags:

2

Answers


  1. 0

    What about doing smaller updates?

    UPDATE TableWhithBlobs a
JOIN AnotherTable b on a.AnotherTableId = b.Id
SET a.SomeText= b.Description
WHERE a.SomeText <> b.Description;

    or even:

    UPDATE TableWhithBlobs a
JOIN AnotherTable b on a.AnotherTableId = b.Id
SET a.SomeText= b.Description
WHERE a.SomeText <> b.Description
LIMIT 100;

    Your timeout problem should be solved 😉, but i do not know how many times you have to run this to finally get the 0 rows affected…

    Login or Signup to reply.
  2. 0

    Some thoughts, none of which jump out as “the answer”:

    • Increase innodb_buffer_pool_size to 1500M, assuming this does not lead to swapping.
    • Step back and look at “why” the BIGINT needs to be copied over so often. And whether “all” rows need updating.
    • Put the LONGBLOB into another table in parallel with the current one. That will add a JOIN for the cases when you need to fetch the blob, but may keep it out of the way for the current query. (I would not expect the blob to be “in the way”, but apparently it is.)
    • What is in the blob? In some situations, it is better to have the blob in a file. A prime example is an image for a web site — it could be accessed via http’s <img...>.
    • Increase the timeout — but this just “sweeps the problem under the rug” and probably leads to 30+ second delays in other things that are waiting for it. I don’t recognize 30 seconds as a timeout amount. Look through SHOW VARIABLES LIKE '%out'; Try increasing any that are 30.
    • Do the update piecemeal — but would this have other implications? (Anyway, Luuk should carry this option forward.)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search