Save MySQL Daemon Process's Memory Usage By Making VARCHAR Columns Smaller!

Amazon If you use MySQL memory table have you ever wondered how much memory you would save by reducing the size of your varchar columns In fact you can accurately calculate the memory savings required by the MySQL daemon process.

Read on to find out exactly how to compute how much memory you'd save by minimizing the size of varchar columns.

Find how much you've reduced varchar in size

Suppose you have a varchar(64) column. Let's say you'd like to reduce it to varchar(53) with the following SQL command.

ALTER TABLE `entry_memory` CHANGE `term` `term` VARCHAR( 53 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

Note that this column's character set is UTF8, meaning each character takes four bytes.

64 - 53 = 11. It means you've reduced the column size by 11 characters.

In my situation I had several columns I reduced the sizes of as follows.

column 1: 64 is reduced to 53
column 2: 1400 is reduced to 1271
column 3: 64 is reduced to 35
column 4: 400 is reduced to 285
column 5: 500 is reduced to 386
column 6: 256 is reduced to 149
column 7: 64 is reduced to 31

Find how much size reduction in total

Following the example above, a record in my table would save 64-53+1400-1271+64-35+400-285+500-386+256-149+64-31, or 538 four-byte characters.

Assuming I have 3599 records in my table. This means my savings in MB is 538 * 4 * 3599 / 1024 / 1024, or 7.38 MB

My machine has a total memory of 615 MB, meaning that the total savings in % is 7.38 / 615, or 1.2%

As it turned out, after I executed the above SQL commands to reduce the sizes of the columns of this database table, I've managed to reduce memory consumption of mysqld process from 14.9% to 13.7%, which is exactly 1.2%!

I use top command and press shift > to sort by memory % to find the memory usage of mysqld process.

If you have any questions let me know and I will do my best to help you!