mysql - innodb save storage space -

i want ask mysql innodb suggest getting smaller database?

i not have innodb_file_per_table not need reclaim space(this have been nice see how space able save).

most of strings saved utf8mb4. columns use latin1(i know there can there latin characters). think save lot of space if convert them latin1? seems change should performance increase in searches.

i have defined columns have lot of text mediumtext . think should save space if define column text? - see type acts varchar(it occupies length of string)

what other suggestions have?

the database has more 300 millions rows , stored in 100g

thanks

changing character set not help. when use utf8 or utf8mb4, each character stored in variable number of bytes. characters can stored in single byte stored way.

changing mediumtext text not much. each string in such columns stored in variable length, length needed string stored. text can store strings 64kb, mediumtext can store strings 16mb. suppose each such string may need single length indicator, 2 bytes per text , 3 bytes per mediumtext. might save @ 300mb per column on entire database (and not much). won't make enough of difference.

you can find out how free space have in tablespace. run show table status 'sometable' "sometable" name of of tables in tablespace.

one of fields returned data_free. free space in bytes in tablespace. when have multiple tables in same global tablespace, every table reports same value. doesn't mean free space sum total of these figures, it's 1 free space repeated in each table status.

to save space, people declare innodb tales row_format=compressed requires use file-per-table. since have tables in global tablespace, if restructure tables file-per-table, won't shrink global tablespace. move tables own files, it'll leave giant global tablespace empty. make storage problem worse.

the thing can suggest following steps. no 1 can use database while you're doing this.

1. dump data innodb tables, saving output of dump volume if necessary. wherever there's space. can compress output of dump like:

   mysqldump ... | gzip -c > dump.sql.gz 

2. stop mysqld process.

3. remove whole global tablespace, i.e. rm /var/lib/mysql/ibdata1, , *.ibd files might still have. you should take backup first, of course.
4. enable innodb_file_per_table in /etc/my.cnf. enable innodb_file_format=barracuda depending on version of mysql.
5. start mysqld process. it'll automatically recreate global tablespace new, small file.
6. restore tables dumped. they'll put individual innodb files, not global tablespace.
7. if necessary, alter each table use row_format=compressed.

this takes long time dump , reload 300m rows. take many hours, , database not usable during time.

if can't make database unavailable while procedure, you'll have on replica, , when procedure done , replica in sync master, can substitute replica master. still cause brief interruption in service while make switch, quick.

next time start larger storage volume database server. project amount of storage you'll need , plan it.

---

re comment, changed mediumtext text , saved space.

the sizes in information_schema (which same reported show table status) estimates, , can out of date or otherwise way off. running analyze table once in while way update statistics.

a table can fragmented, , rebuilding once in while can reclaim of space. use optimize table.

another possibility mediumtext columns storing longer text strings fit in text column, , alter table truncated them.

here's demonstration:

mysql> create table m ( m mediumtext);  mysql> insert m set m = repeat('x', 1024*1024*2); query ok, 1 row affected (0.05 sec)  mysql> select length(m) m; +-----------+ | length(m) | +-----------+ |   2097152 | +-----------+  mysql> alter table m modify column m text; query ok, 1 row affected (0.01 sec) records: 1  duplicates: 0  warnings: 0  mysql> select length(m) m; +-----------+ | length(m) | +-----------+ |         0 | +-----------+ 

i filled mediumtext 2mb of data, used alter change column text. didn't truncate 64kb fit in text column, truncated text down 0 characters.

so hope didn't wipe out text data.