MySQL Forums
Forum List  »  InnoDB

Re: Data Directory is not moving subpartition
Posted by: Michael Muratet
Date: August 29, 2014 01:51PM

Thanks for the reply,

Rick James Wrote:
-------------------------------------------------------
> I don't think that feature existed in 5.5.38:
>
> "
> ----- 2012-08-07 5.6.6 Milestone 9 --
> Functionality Added or Changed -- InnoDB -----
>
>
> Check the 5.5 manual page to see if it explicitly
> mentions InnoDB. The DATA DIRECTORY feature was
> implemented eons ago for MyISAM, but only recently
> added to InnoDB and PARTITIONing.

It does not explicity mention 5.5. I did find one line that says DATA DIRECTORY is not supported by InnoDB. There is nothing on the 'limitations by engine' page. I guess I'll have to go up one version.

>
> > table across multiple disks to get the space and
> improve throughput
>
> You are unlikely to get any benefit. Why?
> Because operations will tend to be serial --
> moving from one disk to the next, getting little
> or no overlap.
>
> You get much more bang for your buck by striping
> the disks. Production machines standardly use
> RAID-5 for both striping and fault tolerance.
>
> By using striping, you have a better chance of
> making use of multiple heads, even within a given
> partition.

I might be able to pull that off with the controller I have. I'm no expert, but the research I have done points to partitioning to improve throughput (and table compression, too).

>
> > char(30)
>
> This is a bad choice unless the field is always 30
> characters. Even so, if it is utf8 and not an
> Asian language, CHAR(30) is not as good as
> VARCHAR(30).

It is not always 30. This is also a throughput enhancement I found on the web. It seemed reasonable that there would be less overhead and the cost in space is tolerable.

>
> > seq text(1000) NOT NULL DEFAULT '',
>
> 100M rows with TEXT? Is that field repeated any?
> Would normalization help?
> Or is the text usually 'long'? If so, consider
> compressing it before storing it. (And then store
> it in a BLOB.) English text generally compresses
> 3:1.

I was planning on compressing it. The data is DNA sequences, there's no way to normalize it but it's a 4 character alphabet and should compress nicely. It is variable length and I could put it in a varchar, but the server didn't like char(1000) and I was trying to trade space for performance.

>
> How fast are you inserting? You have 3 secondary
> keys; each needs to be updated eventually. This
> can be costly during the INSERTs.

Inbserts are slower than I want. I think I'm going to remove the indices during insert and build an index later.

>
> How much RAM do you have?

64 GB

> Setting
> innodb_buffer_pool to about 70% of that is the
> most important tuning to do.
>
> You mention "throughput"; what are the common
> SELECTs?

A common query is "select count(distinct sample_id) where pep='xxxxx'"

another query would be "select count(disinct seq) where pep='xxx'"

It may be that SQL is overkill and piped awk/grep/sort/count would do just fine.

Thanks for your suggestions

Cheers

Mike

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Data Directory is not moving subpartition
875
August 29, 2014 01:51PM


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.