Re: Large Table - high frequency reads & writes

[Mikael Ronström]

Hi,
I did some blogging to answer some of your questions. Here are some more
details.

Jayant Kumar wrote:
> I have a MYISAM table which currently has
> 90,000,000 records. This is a very high frequency
> table where user information is being logged and
> retrieved. The table is growing by 300,000 rows
> every day.
>
> I was planning to break up the table using my own
> logic. Which is -> if i create n tables,
> information of user with id x will go into table
> no (x%n).
>
> However, i came across mysql partitioning and
> found that hash partitioning in mysql does exactly
> what i am planning to do.
>

Yep, you can use PARTITION BY HASH (id) which will do exactly what you
said above. However if you want to optimize for faster adding of new partitions
as data sizes grow you might want to consider PARTITION BY LINEAR HASH (id).
This makes sizes of partitions slightly unbalanced, some partitions
will get twice
as many rows as others if number of partitions isn't equal to 2^n. However
if you add one partition it is only necessary to reorganise 1
partition and not all of
them. Check the manual for more details.


> My question is :
>
> 1. Whenever an insert/update in happening, in a
> partitioned table, will all partitions be locked?
> If yes, what task would be accomplished during the
> locked period or what would be duration of the
> lock?
>
> 2. If i do a select which needs scanning of more
> than 1 partition, would my queries be executed in
> parallel on different partitions?
>

It seems that even though there is no parallel scan feature in 5.1 you
still will
have a lot of assistance from the partition management features and the
optimizer features for partitioning in 5.1.

On parallel scans there is quite a lot one can do from the MySQL client API's.
I managed a master thesis student that benchmarked parallel query behaviour
from the MySQL client API's for a number of test queries and the results were
very promising with very good scalability of performance.

> 3. Would maintenance/administration of partitions
> be efficient so that i can increase the no of
> partitions?
>

See the reply above, PARTITION BY LINEAR HASH sounds very promising
for your needs.

Rgrds Mikael

> Partitioning in mysql would be a very good feature
> if it enhances the speed of queries for large
> tables. I wish i could use it, since it would
> reduce the burden of administrative overhead on
> tables broken up my me.
>
> Thanks a lot
> -Jayant