MySQL Forums
Forum List  »  Performance

Re: Utilize maximum system resources for better performance
Posted by: Rick James
Date: July 31, 2015 10:20PM

> 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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Utilize maximum system resources for better performance
1092
July 31, 2015 10:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.