disclaimer: if you're storing many text fields in a relational database table, you might want to look again at whether that's the right place and method for storing that data - if it looks like a document and quacks like a document, then hey, maybe a document store would be more appropriate? But that's a whole other topic...
The detail is in the MySQL docs 14.4.5. How InnoDB Stores Variable-Length Columns , but I'll give a quick summary here. It hinges on the file format of your InnoDB engine.
As the docs say:
Early versions of InnoDB used an unnamed file format (now called Antelope) for database files. With that format, tables were defined with ROW_FORMAT=COMPACT (or ROW_FORMAT=REDUNDANT) and InnoDB stored up to the first 768 bytes of variable-length columns (such as BLOB and VARCHAR) in the index record within the B-tree node, with the remainder stored on the overflow pages.(emphasis mine)
So with the Antelope file format, it's perfectly possible to store a single TEXT field up to 2GB without encountering the "Row size too large" error, but it's not possible to store 10 x 1k TEXT fields - because InnoDB will store the first 768 bytes of each TEXT field on the record itself, and exceed the row size limit of 8192 bytes.
The solution is straightforward (well, mostly :) - change your innodb_file_format variable to Barracuda, and alter the table to use the DYNAMIC or COMPRESSED row_format. This will store the entire contents of the TEXT fields "off-page" -
SET GLOBAL innodb_file_format=Barracuda; SET GLOBAL innodb_file_per_table=ON; ALTER TABLE (your table) ROW_FORMAT=COMPRESSED;
- and you're good to go.
The one complication is if you're running on Amazon RDS, in which case you'll get an error saying you don't have SUPER privileges. If that's the case, you just need to set the innodb_file_format parameter in your RDS instance parameter group, and allow a few seconds for it to propagate to all your instances.
55 comments:
A fantastic article! Thank you for writing it! Two notes worth mentioning:
1. DYNAMIC vs COMPRESSED: the key difference between the two is COMPRESSED also uses zlib compression on the data. In cases where storage space is not a constraint, using "ROW_FORMAT=COMPRESSED" will avoid the unnecessary CPU load of compressing data. References:
http://dev.mysql.com/doc/innodb/1.1/en/innodb-compression-syntax-warnings.html
http://venublog.com/2008/04/25/innodb-plugin-row-format-performance/
2. IO concerns: for those who aren't aware of what the "innodb_file_per_table" flag does, it's at least important to know that this could significantly increase IO depending on the behavior of your data, i.e. foreign key constraints or many JOIN queries. References:
http://umangg.blogspot.com/2010/02/innodbfilepertable.html
http://dba.stackexchange.com/questions/16208/why-using-innodb-file-per-table
Thanks Joseph, useful reference articles
Every few weeks/months I end up here...cheers Al!
thank you
thank you
Thanks a lot for nice tutorials
I do trust all of the concepts you’ve presented on your post. They’re really convincing and will definitely work. Still, the posts are too brief for newbies. May you please extend them a little from subsequent time?Also, I’ve shared your website in my social networks.
Informatica Training in Chennai
Java Training in Chennai
Oracle Apps Training in Chennai
this is very nice blog and you are updating wonderful information to us thanks for your attention to and keep going on to updating so many useful information.
Base SAS Training in chennai
Think this blog is very useful and informative. Thank you so much.
MSBI Training in Chennai
Informatica Training in Chennai
Data Warehousing Training in Chennai
This is very nce one.. really spend time with good thing.... i will share this to my frends...thank you so much for this post....
Best Training Institute in chennai
Thank you for taking the time and sharing this information with us.
VMware Training in Chennai
Thanks for the informative article.This is one of the best resources I have found in quite some time.Nicely written and great info.I really cannot thank you enough for sharing.
Herbalife in Chennai
Subamwellness in Chennai
Weight Loss in Chennai
Weight Gain in Chennai
I’ve desired to post about something similar to this on one of my blogs and this has given me an idea. Cool Mat.
Java training in Chennai | Java training in Omr
Oracle training in Chennai
Java training in Chennai | Java training in Annanagar
Java training in Chennai | Java training institute in Chennai | Java course in Chennai
This is a nice article here with some useful tips for those who are not used-to comment that frequently. Thanks for this helpful information I agree with all points you have given to us. I will follow all of them.
Best Devops Training in pune
excel advanced excel training in bangalore
This blog is the general information for the feature. You got a good work for these blog.We have a developing our creative content of this mind.Thank you for this blog. This for very interesting and useful.
angularjs Training in chennai
angularjs Training in chennai
angularjs-Training in tambaram
angularjs-Training in sholinganallur
angularjs-Training in velachery
angularjs-Training in pune
I was recommended this web site by means of my cousin.
I am now not certain whether this post is written through him as nobody else recognise such precise about my difficulty. You're amazing! Thank you!
selenium training in Chennai
selenium training in Tambaram
selenium training in Velachery
selenium training in Omr
selenium training in Annanagar
I am obliged to you for sharing this piece of information here and updating us with your resourceful guidance. Hope this might benefit many learners. Keep sharing this gainful articles and continue updating for us.
moto service centre chennai
moto service center
motorola service center
Thanks for sharing this Post, Keep Updating such topics.
Best Ice Fishing Gloves Best Ice Fishing Gloves Best Ice Fishing Gloves
Thanks for sharing blog artice. Here i like to share your thought about deveops training
devops training in omr
best devops training in chennai
best devops training institute in omr
best devops training institute in sholinganallur
Very useful and information content has been shared out here, Thanks for sharing it.microsoft azure training in bangalore
Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing...
oracle training
Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
oracle cloud tutorial
Wow!! Really a nice Article about Data Science. Thank you so much for your efforts. Definitely, it will be helpful for others. I would like to follow your blog. Share more like this. Thanks Again.
Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery
Right here is the right site for anybody who would like to understand this topic. You understand so much its almost tough to argue with you (not that I actually would want to…HaHa). You definitely put a brand new spin on a subject that's been discussed for ages. KBC Winner Wonderful stuff, just great!
Excellent blog. the blog is really very impressive every concept of this blog is neat and very clear in the manner.
Data Science Training Course In Chennai | Data Science Training Course In Anna Nagar | Data Science Training Course In OMR | Data Science Training Course In Porur | Data Science Training Course In Tambaram | Data Science Training Course In Velachery
this page fully contain mysql coding...it may help beginner for sql keep update lots of details..relaed to my sql
AngularJS training in chennai | AngularJS training in anna nagar | AngularJS training in omr | AngularJS training in porur | AngularJS training in tambaram | AngularJS training in velachery
Thank you for excellent article.You made an article that is interesting.
SAP HR Online Training
SAP HR Classes Online
SAP HR Training Online
Online SAP HR Course
SAP HR Course Online
Very interesting blog Thank you for sharing such a nice and interesting blog and really very helpful article.
sap pm training in bangalore
sap pm class in bangalore
learn sap pm in bangalore
places to learn sap pm in bangalore
sap pm schools in bangalore
sap pm school reviews in bangalore
sap pm training reviews in bangalore
sap pm training in bangalore
sap pm institutes in bangalore
sap pm trainers in bangalore
learning sap pm in bangalore
where to learn sap pm in bangalore
best places to learn sap pm in bangalore
top places to learn sap pm in bangalore
sap pm training in bangalore india
Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot.
AWS training in Chennai
AWS Online Training in Chennai
AWS training in Bangalore
AWS training in Hyderabad
AWS training in Coimbatore
AWS training
Thank you for sharing such a great information.Its really nice and informative.hope more posts from you. I also want to share some information recently i have gone through and i had find the one of the best
Artificial Intelligence Training in Chennai | Certification | ai training in chennai | Artificial Intelligence Course in Bangalore | Certification | ai training in bangalore | Artificial Intelligence Training in Hyderabad | Certification | ai training in hyderabad | Artificial Intelligence Online Training Course | Certification | ai Online Training | Blue Prism Training in Chennai | Certification | Blue Prism Online Training Course
Thanks for sharing this blog. This very important and informative blog Learned a lot of new things from your post! Good creation.
hadoop training in chennai
hadoop training in tambaram
salesforce training in chennai
salesforce training in tambaram
c and c plus plus course in chennai
c and c plus plus course in tambaram
machine learning training in chennai
machine learning training in tambaram
The blog is very interesting and will be much useful for use. Thanks for sharing with your blog. Please keep on updating.
web designing training in chennai
web designing training in omr
digital marketing training in chennai
digital marketing training in omr
rpa training in chennai
rpa training in omr
tally training in chennai
tally training in omr
Wow very informative blog,
Thanks and keep more updates,
oracle training in chennai
oracle training in porur
oracle dba training in chennai
oracle dba training in porur
ccna training in chennai
Nice Post. Thanks for sharing with us....
hadoop training in chennai
hadoop training in annanagar
salesforce training in chennai
salesforce training in annanagar
c and c plus plus course in chennai
c and c plus plus course in annanagar
machine learning training in chennai
machine learning training in annanagar
I simply wanted to thank you so much again. I am not sure the things that I might have gone through without the type of hints revealed by you regarding that situation.
hadoop training in chennai
hadoop training in velachery
salesforce training in chennai
salesforce training in velachery
c and c plus plus course in chennai
c and c plus plus course in velachery
machine learning training in chennai
machine learning training in velachery
The blog is very different and useful. The way of writing makes it more interesting.
acte chennai
acte complaints
acte reviews
acte trainer complaints
acte trainer reviews
acte velachery reviews complaints
acte tambaram reviews complaints
acte anna nagar reviews complaints
acte porur reviews complaints
acte omr reviews complaints
Thanks for sharing this blog. really nice and useful for me. I really appreciate your post and you explain each and every point very well. Rajasthan Budget Tours
Amazing Blog, thank you so much for sharing with us. Visit Ogen Infosystem for best Website Designing Company and SEO Services in affordable price.
Web Designing Company in Delhi
Great content & Thanks for sharing with oflox, Website Designing Company In Dehradun
Thanks for sharing this great article. It's really nice and useful for us.
Data Science Online Training
Python Online Training
I am glad to post a worthy article about the German Language Course and IELTS Coaching from KCR consultants, this may change your career growth and language skill.
KCR-German Language
KCR-IELTS
I want to say thanks to you. I have bookmark your site for future updates. ExcelR Data Science Course In Pune
Thanks for the Valuable information.Really useful information. Thank you so much for sharing. It will help everyone.
SASVBA is one of the leading training providers in the country. We provide AI training Course in Delhi accordance with current industry standards, allowing students to find their dream job in the world's leading companies. SASVBA specializes in teaching services, artificial lighting training that imparts practical knowledge through live project-based learning.
FOR MORE INFO:
Informative,Thanks for sharing.
Data Science Training in Pune
Software Development Engineer Training SDET is an emerging role in the present scenario, this role needs an IT professional that can handle development as well as testing effectively. The entire software development process is handled from developing to the testing phase. So this role has high priority in the industries.
Free Download Wifi Hacker Full Version For Windows 7 i was surfing net and thankfully came throughout this website and found intensely exciting stuff here. Its in endeavor of truth amusing to settlement. I enjoyed loads. thank you for sharing this super aspire.
This very informative and interesting blog. I have read many blog in days but your writing style is very unique and understanding. if you are interested in home appliances then click below.
chiq air conditioner
buy dc inverter ac
Good blogger ever
Java course in Hyderabad
You can modify your pictures, you can use the tools provided by Photo IDimager Supreme.
https://thepcsoft.com/idimager-photo-supreme-crack/
This application is one of the world’s most frequently utilized in virtually all sectors. One of the most essential modules in ERP, SAP PP or production planning, is the SAP PP module. Materials planning, capacity planning, execution of production orders, billing materials and products transportation are just a few of the planning operations that are handled by SAP PP. Because it controls and keeps records of manufacturing process flows, SAP PP training in Hyderabad is quite significant. Several additional SAP modules such as the SD, MM and QM modules as well as FICO and PM are fully connected with the PP module of SAP.
best-sap-pp-course-in-hyderabad/
informative article. Keep spreading such a helpful article.
best institute for python in kphb
Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
Python Training institute in Hyderabad
Understanding the Limit:
The default maximum row size in MySQL is 65,535 bytes (64KB). However, the actual usable size is slightly less due to storage overhead.
Solutions:
Big Data Projects For Final Year Students
Reduce Text Field Length (if applicable):
If your text fields don't necessarily need to store massive amounts of data, consider reducing their maximum length using ALTER TABLE statements. Analyze your existing data to determine a reasonable limit.
Change Text Field Type (if data allows):
If your text data has a specific structure or limited size, consider using alternative data types:
VARCHAR(n): Stores variable-length strings up to a specified maximum (n). Ideal for text with predictable lengths.
TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT: Different variations of text data types with varying maximum storage capacities. Choose the one that best suits your data size needs.
Utilize BLOBs (if data is binary):
If your text data is binary (e.g., images, documents), consider using BLOB (Binary Large Object) data types like BLOB, MEDIUMBLOB, or LONGBLOB. These are specifically designed for storing large binary data outside the main data row, improving performance.
Enable ROW_FORMAT=DYNAMIC (InnoDB only):
Thank you for taking the time and sharing this information with us.
python-full-stack-developer
Nice blog and informative content,
Thanks for sharing with us,
Visit Insight IT - SAP BODS Training in Hyderabad
Post a Comment