MySQL Forums
Forum List  »  MyISAM

Re: How to enable index fast in MyISAM table
Posted by: Gejun Shen
Date: March 17, 2014 01:38AM

Rick James Wrote:
-------------------------------------------------------
>
> You have several VARCHAR(32) for some kinds of
> ids. What kind of data is in them? GUIDs?
> Other?
>
> If those ids have hex in them, then this should be
> overridden with ascii:
> > DEFAULT CHARSET=utf8
> Or you should pack them into BINARY(16).
>
> Can any of the bulky fields be reasonably
> 'normalized'? That might turn 32 bytes into 3
> (MEDIUMINT UNSIGNED) or 4 (INT UNSIGNED).

Yes. Some of the bulky fields can be normalized from a schema design perspective. However, that means change to existing running code. I have plan to do that but I have to be careful on this.

> > How many connections can mysqld handle without
> obvious performance downgrade?
>
> Number of connections (even thousands) is not an
> issue.
> Number of queries running at the same time is an
> issue.

I do have thousands of queries during busy hour. Normally it would be 1 insertion thread and thousands of select queries in different connection I showed you in the example query, will this perform worse?

I do see table lock issue in the slow log and want to fix that. Besides normalizing some fields to reduce disk usage, I am still considering sharding data to multiple database and mysql instances. Do you think it doable and any recommendation on the sharding library/project?

> > select xxxx from servicetable where
> timestamp>="xxx" and timestamp<"xxx" ...
>
> could be sped up a little by adding
> AND date >= ... AND date < ...
> in order to get pruning.
>
> > where process_id="xxx" ...
> is stuck with hitting all the partitions. Even if
> that is a typo for process_sid.

I tried adding date to it to prune partition. However, it is slower than no pruning. Hence I removed the "date=xx" criteria.

>
> > `original_files` mediumtext,
> > `filelist` mediumtext,
> > `memo` mediumtext,
>
> Do you often fetch these fields? Are they usually
> bulky? Consider moving them to a 'parallel'
> table. This would decrease I/O somewhat.

Yes. Most queries need read them out for processing.

> I see several fields, even some indexed fields,
> being NULLable. Was this a mistake?

It existed for long time and I don't know the reason. Is there any big performance difference between Nullable and not nullable fields?

> In some later version, you can effectively get
> PARTITION BY RANGE(timestamp); this might be a
> better way to go.
>
> I cannot predict whether InnoDB would be faster or
> slower. Probably slower because of being bulkier.
> But some of that is softened by the differences
> in index structures and caching. InnoDB would
> probably shine for the first query if you had
> PRIMARY KEY(timestamp, seq), INDEX(seq).
> That way it could take advantage of 'clustering'
> of the PK.
>
> You do have new enough version to set
> key_buffer_size bigger than 4G. Suggest 10G for
> your 48GB RAM.

These are all good comments and I will apply it if I migrate it from MyISAM to Innodb.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to enable index fast in MyISAM table
1781
March 17, 2014 01:38AM


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.