From: Rik Wasmus
Date: January 23 2012 12:07pm
Subject: Re: Suggestions for ibdata files structure
List-Archive: http://lists.mysql.com/mysql/226644
Message-Id: <201201231307.03918.rik@grib.nl>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable

> Dear all,
>=20
> 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=20
(http://dev.mysql.com/doc/refman/5.5/en/partitioning-overview.html), with a=
=20
custom data-directory for different partitions. Keeps lookups on keys (assu=
ming=20
there is 1 major key by which records are fetched, otherwise, this might ev=
en=20
degrade performance).=20

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

Cons:
=2D Could result in a degrade of performance when keys to partition by are=
=20
chosen incorrectly, or the table needs a lot of lookups on different keys
=2D Partitioning is done per table, so new tables need to have their config=
=20
explicitly set.
=2D 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.=20

> - Queries involving aggregate functions such as SUM() and COUNT() can eas=
ily
> 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 =E2=80=9Cparallelized,=E2=80=9D we mean that the quer=
y can be run
> simultaneously on each partition, and the final result obtained merely by
> summing the results obtained for all partitions.=20
> -  Achieving greater query throughput in virtue of spreading data seeks o=
ver
> multiple disks.

Not implemented yet....... I personally have those 2 high on my wishlist ;)
=2D-=20
Rik Wasmus