> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
