0

I am facing a really strange problem with my database. I have a table with less than 20 columns. When i run a select * query, it's taking almost 6 to 7 seconds to execute. I couldn't identify the issue and tried selecting each columns one by one. By the way, total number of rows are just 269.

Finally i found 2 columns which causes the issue. Both these columns store some kind of description and the datatype is MEDIUMTEXT.

SELECT LocalDescription FROM test.hotels where Id > 0;
SELECT LocalFacilitiesDescription FROM test.hotels where Id > 0;

It took between 5 to 6 seconds to finish the execution. There is no delay for the rest of the columns.

1
  • 2
    optimation question need much more information, create table with indexes , EXPLAIN of your queries
    – nbk
    Commented Dec 3, 2020 at 15:35

2 Answers 2

1

It has to do with the way VARCHAT/TEXT/BLOB field are handled.

I personally observed this in an RDS instance

I had taken a table with 25 million rows which had a TEXT field in it and ran this

SELECT DISTINCT LENGTH(colname) FROM tblname;

and I saw just 2 numbers, 3 and 10.

I converted the field from TEXT to VARCHAR(10). What this did was increase the amount of RAM occupied in the InnoDB Buffer Pool. I verified this from a CloudWatch graph, see Free RAM dip right after doing this and the graph stayed that way. The number of free buffer pool pages dropped accordingly.

I am not the only to have seen this phenomenon. Someone once posted a blog entitled "Drain your InnoDB Buffer Pool by moving large fields out of MySQL" who had observed similar Buffer Pool weirdness.

What is interesting is that Bill Karwin had commented on this in a DBA StackExchange post (MySQL stores BLOB in innodb buffer pool (innodb_buffer_pool_size)?). He mentioned that BLOBs will occupy InnoDB Buffer Pool Pages. Shrinking them can make data fit in the InnoDB Buffer Pool better could work for you or against you depending on the size of the data.

In your particular case, you only have 269 rows. This should let you know that handling TEXT fields requires some extra I/O in RAM or on disk. You are using MEDIUMTEXT, which can hold up to 16M. Given innodb_page_size = 16384 (16k), a MEDUIMTEXT value can occupy up to 1024 pages. This potential means you are dragging a lot of space from disk based on how much data is in each MEDIUMTEXT field. You may have to live with it.

SUGGESTIONS

1
  • Buffer pool size was the issue. It was only 8 MB in the configuration. Just increased the size and worked fine
    – BPX
    Commented Jan 7, 2021 at 9:27
0

Large TEXT/BLOB columns are stored "off-record". Numbers, dates, small varchars, etc are stored on-record.

Everything is blocked into 16KB blocks.

The on-record data will take only a few blocks to hold all of the on-record information for 269 rows. The off-record data will take as many blocks as necessary.

So, SELECT * or SELECT LocalDescription will need to fetch those off-record blocks, of which there seem to be many.

Get a feel for the table size by doing SHOW TABLE STATUS and look at Data_length.

6-7 seconds on HDD can fetch about 600 blocks -- about 10MB. Is that how much data in that column? That is, is the average length for the column about 40KB?

Or, for SSD, maybe 6K blocks, 100MB, 400KB.

Bottom line: Fetching a lot of data from disk takes time. Also, don't use SELECT * if there are bulky columns that you don't need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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