This will start series of blog posts about cost savings on Amazon. There are many typical mistakes that everyone is making, quick wins action that you can take to save some costs both for big scale and small scale deployments.
When you migrate to the cloud most probably you will be using Ec2 and RDS databases. Whole my private project code base relies on Spring boot + JPA + mysql8.. adapting this to other storage type would cause that I am more dependent on Amazon which I do not want. And also I would need seriously change not only DAO layer, but the way I model my data.. a bit to much..
My db size is around 10 gigabytes.. average utilisation is low I stop db over night using cloud custodian so I mostly pay for the storage.
I already know that if I was using google cloud I would switch to kubernetes and install mysql db myself… then I would be paying for tiny computing power and a bit of storage… on Amazon RDS service includes automated backups (which are super convenient), possibility to have multi AZ database (which I do not need)… but for now.. I will stick to Amazon.. what can I do?
Recently I learned that after switching from mysql 5 to mysql 8 default character encoding changed..
In mysql5 it was called utf8 which could not cope with any character (yes – surprise!). In mysql8 it’s called utf8mb4 which is a real utf, but it’s size. is 4 bytes for each character.
In practice the difference is neglectable. Old utf8 takes 3 bytes per character and the only characters it could have issues with are some emoticons data. It’s unlikely that it could cause you a problem unless you are developing application for many users and you do want to allow users to store emoticons in – let’s say blog comments..
Anyway.. if you migrate your db from mysql5 to 8 expect your database size, snapshots increase in worst case by 33% (from 3 bytes per character to 4 bytes).
That includes also traffic between db and application..
In my app most of my data are just numbers so that experiment will not affect me much. But other tables mostly contains names that are could be just plain ascii. I do not store any input from users… sounds like a place for a improvement…
There are default settings for character sets and collations at four levels: server, database, table, and column.
https://dev.mysql.com/doc/refman/8.0/en/charset-syntax.html
To change default character set you can use :
ALTER TABLE t CHARACTER SET ascii;
but this does not convert existing data values.. for that you need to execute:
ALTER TABLE t CONVERT TO CHARACTER SET ascii;
After that.. my database was smaller by 10% and my storage costs dropped by 10% as well.
Ok and do we have any drawbacks? If you really know that your table and application usage will not be hurt by using ascii, latin or old utf8 character then no. If you want to introduce the change only to some tables then bare in mind that if you want to join let’s say “sessionId” from one table which is ascii and with second table where it is utf8 such join is not sagrable (indexes won’t be used) check here https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable
So as always.. you need to decide, experiment and check.
Interesting comparison of impact on character set and collaction across mysql 5 and 8 is here
https://www.percona.com/blog/2019/02/27/charset-and-collation-settings-impact-on-mysql-performance/
Bear in mind that the default utf8mb4 is there for a reason.. using that character you will not have any issues when storing data from many sources.. be it users or web scrapping data… Think about optimizations only if you have a use case for that (terrabytes of data for instance!)