MySQL Forums
Forum List  »  Performance

Re: Utilize maximum system resources for better performance
Posted by: Rick James
Date: July 15, 2015 08:36PM

Memory allocation: http://mysql.rjweb.org/doc.php/memory

For MyISAM, _Most_ of the RAM should be left for caching the _data_ of MyISAM tables. So, 29GB/32GB free is about right.
key_buffer_size is about half what it should be.

> KEY `e_v` (`enabled`,`verified`),
> KEY `e` (`enabled`),

The latter index is redundant with the former, and can be DROPped.

> `hash` char(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

Is the "hash" a hex string? UUID? You are currently allocating 120 bytes (per row) for `hash`.
For an MD5, CHAR(32) CHARACTER SET ascii -- 32 bytes
For a UUID, CHAR(36) CHARACTER SET ascii -- 36 bytes

> ROW_FORMAT=FIXED

Don't use that; it's an old wives tale that there is any benefit from it.

> WHERE enabled = 1 ORDER BY upload_date DESC

would benefit from INDEX(enabled, upload_date)

> LIMIT 6514850,25

Get real! Under what condition do you need to jump to row number 6514850?? The query _must_ scan 6514850 rows before finding the 25 you want.

> log_queries_not_using_indexes = 1

That clutters the slowlog, without providing useful info.

> will switching to innodb improve overall read performance ?

Not for _this_ query. But InnoDB is better in general.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Utilize maximum system resources for better performance
1067
July 15, 2015 08:36PM


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.