Test the size and number of mysql blob fields.

Source: Internet
Author: User

Test conclusion
Mysql 5.1
Table type: innodb, row_format = compact (this is the default row format)


Insert more than 10 blobs. The blob data volume is small (<768 bytes) and is successfully inserted.
Insert more than 10 blobs, The blob data volume is large (> 768 bytes), insertion failed: Got error 139 from storage engine is reported.


Note: If the mysql server version is 5.1 and the innodb_file_format option does not exist, you cannot talk about the Barracuda format. Setting row_format = dynamic is meaningless.


Mysql 5.5
Table type: innodb, row_format = compact (this is the default row format)
Insert more than 10 blobs, blob data size is large (> 768 bytes), insertion failed: Row size too large (> 8126 ). changing some columns to TEXT or BLOB or using ROW_FORMAT = DYNAMIC or ROW_FORMAT = COMPRESSED may help. in current row format, BLOB prefix of 768 bytes is stored inline.


Table type: innodb, row_format = dynamic (this is the row format supported by the new file storage format Barracuda of innodb)
Insert more than 10 blobs. The blob data volume is large (> 768 bytes) and is successfully inserted.


Note:
1) In actual testing, the length of each field I use is 100 K +
2) For mysql5.5, although Barracuda is supported. However, the old format is Antelope by default.
Unless I. cnf is modified in the mysql Configuration:
Innodb_file_per_table = 1
Innodb_file_format = Barracuda
Or modify the set global command dynamically:
Set global innodb_file_format = barracuda;
Set global innodb_file_per_table = 1;


Note:
1) The modified innodb_file_format only affects the tables created later. That is, the table created later. You can set row_format to dynamic.
2) set global is only valid during mysql server running. After restart, innodb_file_format is restored to the original format.
3) A simple method to determine whether a table supports more than 10 blob fields:
Show table status like 't1' \ G
View Row_format. Compact is not supported. dynamic is supported.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.