List:General Discussion« Previous MessageNext Message »
From:Rik Wasmus Date:January 23 2012 12:07pm
Subject:Re: Suggestions for ibdata files structure
View as plain text  
> Dear all,
> 
> I have some doubts regarding the configuration of Innodb files structure.
> As we know all myisam tables are stored in database directory and innodb
> use tablespaces ( ibdata1 ).
> In My application , databases are the mixture of mysql & innodb storage
> engines.
> In my database ,size of innodb tables may be between 100 & 200 Gb & may
> be some tables >300Gb.I have 4 Hds.
> /dev/sdb1             274G  225G   36G  87% /hdd2-1
> /dev/sdc5             274G  225G   36G  87% /hdd3-1
> /dev/sdd5             274G  219G   42G  85% /hdd4-1
> /dev/sde1             266G  184G   69G  73% /hdd5-1

> /hdd5-1/innodb_data5/ibdata100:8G;/hdd5-1/innodb_data5/ibdata101:8G:autoex
> tend

[...snip...]

> But I don't think this is the correct way to store data for better
> performance.
> Do i need to set partitioning for bigger tables or some standard ways to
> configure table spaces in mysql.

If you really want to partition it, I'd use table-level paritioning 
(http://dev.mysql.com/doc/refman/5.5/en/partitioning-overview.html), with a 
custom data-directory for different partitions. Keeps lookups on keys (assuming 
there is 1 major key by which records are fetched, otherwise, this might even 
degrade performance). 

Pros:
- Per table config
- Easily portable
- Easy dropping & recreating partitions
- Can result in great performance gains due to partition pruning.

Cons:
- Could result in a degrade of performance when keys to partition by are 
chosen incorrectly, or the table needs a lot of lookups on different keys
- Partitioning is done per table, so new tables need to have their config 
explicitly set.
- Harder to control which percentages are on which disk (as it is data-
related)

However, the last part of that page should not be taken lightly:

> Other benefits usually associated with partitioning include those in the
> following list. These features are *not currently implemented in MySQL*
> Partitioning, but are high on our list of priorities. 

> - Queries involving aggregate functions such as SUM() and COUNT() can easily
> be parallelized. A simple example of such a query might be SELECT
> salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY
> salesperson_id;. By “parallelized,” we mean that the query can be run
> simultaneously on each partition, and the final result obtained merely by
> summing the results obtained for all partitions. 
> -  Achieving greater query throughput in virtue of spreading data seeks over
> multiple disks.

Not implemented yet....... I personally have those 2 high on my wishlist ;)
-- 
Rik Wasmus
Thread
Suggestions for ibdata files structureAdarsh Sharma23 Jan
  • Re: Suggestions for ibdata files structureJohan De Meersman23 Jan
    • Re: Suggestions for ibdata files structureAdarsh Sharma23 Jan
      • Re: Suggestions for ibdata files structureJohan De Meersman23 Jan
  • Re: Suggestions for ibdata files structureRik Wasmus23 Jan
Re: Suggestions for ibdata files structureAdarsh Sharma25 Jan