Re: Utilize maximum system resources for better performance

[Rick James]

> init-connect='SET NAMES utf8'

Any SUPER user (eg 'root') will not execute the init-connect.

> query_cache_type = 1

In servers with 'continual' writes, the query cache is usually a waste. Each write to a table will purge all entries in the QC for that table.

> need to update all rows (6mil+) once a day

Is that `content`? Are you updating just a few column(s)? Perhaps votes and dates?

I would recommend (if practical) that you 'vertically partition' that table. Move the things you need to update into a second table. The two tables would share the PRIMARY KEY(record_num) (except that it would be AUTO_INCREMENT in only one table). The new table would be something like 1/20th the size, hence the nightly effort to update it would take about 1/20th the time. Also move any indexes that are needed.

JOINing on record_num would let you "put them back together" for whatever queries need both sets of columns.

Caveat: It will take a long time to do the split. pt-online-schema-change may provide a way to do it with virtually zero downtime.

> "hash" is hex string with 40 length

Yes it is 40 _characters_, but no it is still not 40 _bytes_, but rather 120 (because of utf8). Assuming it is only ascii characters:

ALTER TABLE content MODIFY COLUMN hash CHAR(40) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL

That will shave about 1GB off `content`, which now takes about 10GB.

> free emory is showing 20 G even though i have set innodb buffer pool size 24 GB

I think that is because you have not actually brought in 24GB yet. The total in InnoDB tables is only about 14GB.