2

Hello everyone and sorry for what may be a silly question, but I'm completely dazzled.

First thing is that I'm not sure that it's related to InnoDB, but I've tried this on MariaDB and on MySQL and results are the same.

Let's cut to the chase:

I have huge performance issues in the most unpredictable places. To illustrate it best, here is an example:

I have a table called 'image' which stores images as BLOBs as well as the images metadata like image_name or DPI. It's relatively small (~14000 rows) and most images are 250-1000 KB. In the example I will use LOWER() to get rid of index that I have on image_name column.

SELECT id FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

This is super fast, at worst couple milliseconds, we got result id = 1.

SELECT * FROM image WHERE id = '1'

This is fast again, most time spent transferring BLOB to me, no surprise (in the end, it's PRIMARY key).

SELECT * FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

Now this thing is just refuses to execute. And I mean it. In 95% of the cases I will receive a timeout and occasionally it will execute after 10 minutes or so.

EXPLAIN SELECT obviously tells me that it's simple SELECT. And I cannot run SHOW PROFILES because the request is never completed, so I cannot even think of where I can start debugging it.

  • I've never known a database giving a timeout, unless there was a firewall or antivirus in between me and the database, that cut the connection when seeing a specific kind of data passing. But since the same image does pass now and then, you'd think there must be something else. If you tried it with MariaDB too (or MySQL too), how was the data transfered to the other database? I'm asking to see if you could have disk problems. What is your storage and OS? – Gerard H. Pille Mar 17 '18 at 9:46
  • It's timeout as well from localhost, so it's for sure not related to firewall or anything like that. HDD is in perfectly healthy condition, I've used ESF Migration trial just to check if problem is MariaDB specific. OS: Windows Server 2012 R2 – Evgenii Burmistrov Mar 17 '18 at 9:57
  • 1
    Was ESF Migration involved in creating the original database (the MariaDB, I suppose?). Can you add the DDL for the image table? What activity do you see on your system during the wait? – Gerard H. Pille Mar 17 '18 at 10:13
  • No (yes). Pastebin: pastebin.com/TrqYKE1E Almost none (2% CPU, ususal stuff. Remember, it's one image). – Evgenii Burmistrov Mar 17 '18 at 10:23
  • Some kind of lock? What kind of message do you get when there is a timeout? What does MariaDB report in the logs? What client is issuing the "select *"? I'm asking since I wonder how a longblob would be displayed. – Gerard H. Pille Mar 17 '18 at 10:25
2

First of all, here are a few things you can try to get more info about the query in your case:

  1. After starting the query, you can check its status by running

    SHOW FULL PROCESSLIST
    
  2. Also, you can try running the query on a smaller set of rows by adding another where predicate (covered by an index), for example:

    SELECT * FROM image WHERE id < 500 and LOWER(image_name) = 'img_0209.jpg'
    

As for why the query may be slow, I believe that what happens with the third query

SELECT * FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

is that since the index can't be used, the engine does a full table scan and reads all the data for each row (including full BLOB data) and only then tries to filter them. So it probably reads a few GBs of data with that query.

Looking at the amount of data read from disc during the query may give some hints. You can try using the innodb_data_read service status variable:

SET @read_metric = 'innodb_data_read';
SET @read_before = (
  SELECT variable_value
  FROM information_schema.session_status
  WHERE variable_name = @read_metric
);

SELECT * FROM image WHERE id < 500 and LOWER(image_name) = 'img_0209.jpg';

SET @read_after = (
  SELECT variable_value
  FROM information_schema.session_status
  WHERE variable_name = @read_metric
);

SELECT (@read_after - @read_before) / 1024 / 1024;

It should be similar to the size of data in BLOBs:

SELECT sum(length(image_data)) / 1024 / 1024 FROM image WHERE id < 500;

To speed up the query you need to avoid accesing BLOBs as much as possible. A few suggestions:

  1. in your specific case changing the collation to an ignore-case one should help, so the image_name index may be used to find the single row you need
  2. try using a self join to separate image_name and BLOBs access

    SELECT image_data.image_data
    FROM image
    JOIN image as image_data on image_data.id = image.id
    WHERE LOWER(image.image_name) = 'img_0209.jpg';
    
  3. as described in the Optimizing for BLOB Types manual, move the BLOBs to a separate table with just two colums: id and data

| improve this answer | |
  • Thank you a lot, I will try this in the spare time. In the meantime we moved our database to MSSQL which doesn't have same issue (neither does Postgre in case anyone is wondering about free solutions) – Evgenii Burmistrov Mar 21 '18 at 13:25
  • MySQL' InnoDB moves large TEXT/BLOB columns to a separate place, somewhat like having a separate table. This is automatic. – Rick James May 15 '18 at 16:36
  • @RickJames, somewhat, but not. You may want to read percona.com/blog/2010/02/09/blob-storage-in-innodb about BLOB storage in InnoDB. The detail that is important here is: MySQL internally has no optimizations to read portions of the blob – it is either read completely or not at all, so the 768 bytes on the row page is a little use – if BLOB is accessed external page will always have to be read. Also, there is a verified bug in MySQL which I think describes the same limitation: bugs.mysql.com/bug.php?id=58234 – Sergey Jul 6 '19 at 14:54
  • @Sergey - I think this is being partially addressed in MySQL 8.0, especially for JSON. – Rick James Jul 6 '19 at 20:30
1

Don't hide an indexed column inside a function (LOWER). In this case, have the collation be anything with ..._ci, meaning case insensitive. Then you can say simply say WHERE image_name = 'img_0209.jpg' and have INDEX(image_name). Now you don't have to search the entire table for that one row.

What will you do with the BLOB after you fetch it? (There may be other optimizations there.) About how many rows in the table? (This may factor into the equation.)

Specific suggestions

ALTER TABLE `image`
    MODIFY COLUMN `image_name`
        varchar(80) CHARACTER SET ascii COLLATE ascii_general_ci
        DEFAULT NULL';

and remove the use of LOWER():

WHERE image_name = 'img_0209.jpg'

Your existing index can stay unchanged (except that it will be rebuilt to handle the change in collation):

 UNIQUE KEY `image_name` (`image_name`)

Caveat: The ALTER is likely to take a long time.

| improve this answer | |
  • If you'd read the body, that's exactly what I was trying to do. Searching in non-indexed column yields exactly the same results. – Evgenii Burmistrov Mar 21 '18 at 13:23
  • LOWER() causes the query to be very slow. Remove the function call. – Rick James Mar 21 '18 at 14:29
  • @RickJames A) Would you suggest ALWAYS avoid LOWER() in SELECT specifications? B) Could you provide an example of the suggestion from Mar 18, 2018 that uses the ..._ci technique using the query from the top of this question. – Wilson Hauck May 13 '18 at 11:23
  • @WilsonHauck - I can't think of a case where LOWER() is better than INDEX(name) with name VARCHAR(...) COLLATION ..._ci. – Rick James May 15 '18 at 16:35
0

Don't select a longblob when your interface is a terminal. The bytes of eg. an image may contain characters that are control characters for your interface or connection.

| improve this answer | |
  • This is obviously not the case, otherwise SELECT * would never work. – Evgenii Burmistrov Mar 17 '18 at 12:07
  • Depends on what the image contains. Just select a number of columns, apart from the image column. – Gerard H. Pille Mar 17 '18 at 12:09
  • Sure, you will get gibberish for an image blob; SELECT ... HEX(imageblob) ... would avoid it. But this is irrelevant to the question about timing out. – Rick James May 15 '18 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.