MySQL Forums
Forum List  »  Performance

Re: Utilize maximum system resources for better performance
Posted by: Rick James
Date: August 02, 2015 11:10AM

> now my queries takes 1.xx seconds

Turn the QC back on while we fix those queries.
Let's see one of them. Please show us these 3 things

SELECT...
EXPLAIN SELECT ...
SHOW CREATE TABLE

We may be able to speed up the query so that it will be fast even if the QC is off. This will _indirectly_ help the UPDATEs.

> 2-3 table joins

The number of tables being JOINed is not as critical as the indexes involved. Let's see and example; include the 3 things mentioned above.

> hex chars falls unders ascii so we can set ascii character set. and if the field contains non ascii characters then we need to use utf which uses more bytes per char.

Correct. (I guess I left out a few steps in the logic.)
Also, CHAR preallocates the full size; VARCHAR is flexible. In your `hash` case you had both CHAR and utf8.

> it needs to change queries in my whole app.

Sorry, if there were a magic wand, I would have given you that. Instead the fix is more invasive. Maybe others readers can fix their apps before it becomes such a big task.

Oh, since you already have the slowlog turned on, you could use pt-query-digest to summarize it, then show us the first couple of queries (together with the 3 things above). Those _may_ be quick wins in the performance arena.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Utilize maximum system resources for better performance
1105
August 02, 2015 11:10AM


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.