In one of my MySQL 5.0.77 servers, I have a few MyISAM tables (72 tables, data 151 MB, index 140MB) for one of my websites. After reading a bit about TEXT columns and how (as far as I understand) they’re stored out-of-row, along with the VARCHAR() length improvements in MySQL >= 5.0.3, I decided to try to convert my TEXT columns to large (>255) VARCHAR colums.
For example, one of the columns is user-entered text, and the maximum user input is around 8000 bytes. Looking at this, and thinking “hey, I can give them a little headroom”, I changed that TEXT column to VARCHAR(30000). I did this on approx 10 columns over 8 tables, using VARCHAR(10000) through VARCHAR(30000).
Since precisely then, the IO on my server has increased dramatically - mostly in writes. For example, I was seeing approx 40 write IOPS a few days ago, and now the average is ~100 write IOPS. IO write time has increased 10x.
Because of this, I am seeing a sparrodic increase in page load time (2x page load time).
Does this make sense? I was hoping, by changing to a big VARCHAR(), I’d bring the text in-row, which would reduce the # of reads for joins, as well as the # of temp tables on disk.
What am I doing wrong?