Converted TEXT to VARCHAR(30000), huge increase in IO

MySQL & MariaDB Other MySQL® Questions
1

Hi,

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?

2

Do you update the files a lot?

And try indexing all ORDER BY/GROUP BY queries, that should reduce write i/o due to filesorts on disk.

3

It could be that by bringing the data in-row your rows become much bigger, so there is much more data to read and write for all sorts of queries, even if they don’t involve the fields you’ve changed. Also your row format is dynamic, which could decrease performance.

You could try to split out all TEXT/Varchar data to another table with the same primary key as your current table, and setting the ROW_FORMAT to FIXED on the old table. This might be a good idea depending on the usage patterns and how often you need the variable length data.

4

I was able to resolve it by reverting back to TEXT fields. I’m currently running some benchmarks (load from backup, replay query log) while using Windows tracing and MySQL statistics to try to narrow down either a fix or at least the cause.

Could it be possible I’m hitting some sort of temp-table or sort-table limit, where at that limit the sort reverts to a file-based sort instead of in-memory sort?