MySQL - Benchmarking Varchar vs Text
A while ago, I was trying to analyze how much impact it would have if I was to use a very large
VARCHAR compared to a
TEXT type, to store the log messages in my database.
These are log messages, and are not indexed. Instead, they are accompanied by a timestamp and an ID which are used for filtering/selection and archiving.
I built a tool in PHP to benchmark the performance difference between using the TEXT type and a very large VARCHAR.
How It Tests
- The tool creates a TEXT and a VARCHAR(21,000) based table on a master and slave database each.
- It then proceeds to insert a set number of logs into each of the tables and tracks how long this takes.
- It then proceeds to insert the same number of rows into the slave tables but with different content.
- It then runs the sync tool to sync the master to the slave and tracks how long that takes.
- Deebian 9.4
- MySQL 5.7 with InnoDB tables
- 1 GB Ram
- 2 vCPUs
When I ran the test set to use 10,000 logs I got the following results:
insert_logs_textfield: 9.482647895813 seconds insert_logs_varchar: 9.5715599060059 seconds sync_test_varchar: 25.566293954849 seconds sync_test_text: 25.053405046463 seconds
When I ran with 100,000 logs, I got the following:
insert_logs_textfield: 90.345357179642 seconds insert_logs_varchar: 91.518849372864 seconds sync_test_varchar: 1482.8896179199 seconds sync_test_text: 1460.9850070477 seconds
I couldn't reliably find any performance difference when using VARCHAR or TEXT types. I had expected VARCHAR to be noticeably faster as it stores the data in-line, but evidently I was wrong. This has further reinforced on me the importance of testing/benchmarking everything and assuming nothing. From now on, I will not worry about a performance impact of using the TEXT type on large tables and will purely focus on using the type that is most appropriate for the content. E.g. TEXT for a comment/message and VARCHAR for storing a user's email address.
Do You Feel I Missed Something?
If you feel that my benchmarking tool missed something and could be improved to simulate some sort of workload, please let me know in the comments or submit a pull request to the Github repository.
- Stack Overflow - MySQL: Large VARCHAR vs. TEXT?
- Percona Database Performance Blog - Blob Storage in Innodb
First published: 16th August 2018