Re: Suggestions for ibdata files structure
Adarsh Sharma wrote: Thanks for the suggestions. Last thing to close this topic :- I create new partitions & files for ibdata files as :- log-bin=/hdd5-1/mysql-bin innodb_data_file_path = /hdd2-1/innodb_data1/ibdata1:250G;/hdd3-1/innodb_data2/ibdata2:250G;/hdd4-1/innodb_data3/ibdata3:8G:autoextend ( When I delete old ibdata files & start mysql again to create new ibdata files ( 250 GB) , mysql takes too much time : [root@s6-mysd-1 mysql]# /etc/init.d/mysql start Starting MySQL ERROR! Log says 120125 16:02:56 InnoDB: Setting file /hdd2-1/innodb_data1/ibdata1 size to 256000 MB InnoDB: Database physically writes the file full: wait... What is the reason behing this, I think connect_timeout may be the issue. Now I need to place some innodb tables with partitioning enabled. Please check the table structure. CREATE TABLE `metadata` ( `meta_id` bigint(20) NOT NULL AUTO_INCREMENT, `u_id` bigint(20) DEFAULT NULL, `c_p_url` varchar(800) DEFAULT NULL, `meta_field` varchar(200) DEFAULT NULL, `meta_value` varchar(2000) DEFAULT NULL, `dt_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`meta_id`) ) ENGINE=InnoDB AUTO_INCREMENT=447567739 DEFAULT CHARSET=utf8 You can check the size of auto_increment column & how can i partition my data effectively fore,g: |PARTITION BY RANGE (year(dt_stamp)) (PARTITION p0 VALUES LESS THAN (2000), -> PARTITION p1 VALUES LESS THAN (2001) , PARTITION p2 VALUES LESS THAN (2002) , -> PARTITION p3 VALUES LESS THAN (2003) , PARTITION p4 VALUES LESS THAN (2004) , -> PARTITION p5 VALUES LESS THAN (2005) , PARTITION p6 VALUES LESS THAN (2006) , -> PARTITION p7 VALUES LESS THAN (2007) , PARTITION p8 VALUES LESS THAN (2008) , -> PARTITION p9 VALUES LESS THAN (2009) , PARTITION p10 VALUES LESS THAN (2010) || Above command partition data by year wise but i want to partition by month wise ( fore.g 3 months data in one partition & so on). How can I achieve this Where all these partitions get stored , do it makes internal partitions in ibdata1 or ibdata2 or in ibdata3 files. Thanks & Regards Adarsh Sharma | Johan De Meersman wrote: *From: *"Adarsh Sharma" Thanks for the quick response , Ya I am aware of the full dump & restore & changing my.cnf for that. Just need to confirm that this configuration is better than the previous one or may any other ( Single 1TB with ibdata1:10MB:autoextend) is best for keeping away from future issues. Personally, I'm partial to innodb-file-per-table because of the easier space management. I'm not entirely sure, but it might well be that innodb will also start if a tablefile is missing, while it almost certainly won't start when a normal datafile is missing. You should definitely look into hard- or software RAID setup for your datafiles, though. Apart from redundancy benefits, most RAID setups will also help your read performance along. RAID 10 or 51 are generally recommended for databases, although you can certainly get away with simple 1 or 5, too, if you don't need the last drop of performance. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Suggestions for ibdata files structure
- Original Message - > From: "Adarsh Sharma" > 1. I am planning to to fix one partition /hdd2-1 for my binary logs > for HA, rotate them monthly to extra backups. Keeping binary and other logs on separate disks from your datafiles is a very good plan. For backup purposes, make sure you have copies of your logs (and of course appropriate full dumps) off the system, too. > 2. Remaining 3 system partitions ( /hdd3-1,/hdd4-1,/hdd5-1) contains > ibdata1:250GB,ibdata2:250GB, ibdata3:200Gb :Autoextend. Store each > innodb table data partitioning by date ( 6 months ) as my myisam > tables remains in /var/lib/mysql/directory because their size > increases in MBs. You are aware that this will require a full dump and restore, I trust? > 3. What to do when my final partition /hdd5-1 is having no space ( > less than 1 Gb ), do i need to configure RAID 10 for that. You'll need more disks, of course. Wether you put them in raid or not does not change that fact. Using a RAID config would definitely be a good idea, though; since a single missing datafile will prevent innodb from starting up at all. > 4. I need 3 months past data at any given point of time , so I can > manually alter ( delete ) my partitions for that. Yeps. > 5. How can I increase my select query performance on a 200Gb innodb > table. Make sure your query uses indices - the more selective the better. Make sure those indexes are fully in memory - tune the innodb pool size and the like; and make sure there's plenty of memory available. For Innodb, the primary index is faster than any secondary one. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Suggestions for ibdata files structure
Ya i know that this is absolutely wrong , but I have some questions in my mind :- 1. I am planning to to fix one partition /hdd2-1 for my binary logs for HA, rotate them monthly to extra backups. 2. Remaining 3 system partitions ( /hdd3-1,/hdd4-1,/hdd5-1) contains ibdata1:250GB,ibdata2:250GB, ibdata3:200Gb :Autoextend. Store each innodb table data partitioning by date ( 6 months ) as my myisam tables remains in /var/lib/mysql/directory because their size increases in MBs. 3. What to do when my final partition /hdd5-1 is having no space ( less than 1 Gb ), do i need to configure RAID 10 for that. 4. I need 3 months past data at any given point of time , so I can manually alter ( delete ) my partitions for that. 5. How can I increase my select query performance on a 200Gb innodb table. Thanks Johan De Meersman wrote: Gah, my eyes :-) That seems... needlessly complex. As a general rule, it's a good idea to have one datafile per physical disk. Putting more datafiles on a single disk doesn't magically increase parallellism. - Original Message - From: "Adarsh Sharma" To: "mysql" Sent: Monday, 23 January, 2012 10:50:33 AM Subject: Suggestions for ibdata files structure 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 Data is inserted continuously. Presently I have a structure like below innodb_data_file_path = /hdd2-1/innodb_data1/ibdata1:8G;/hdd3-1/innodb_data1/ibdata2:8G;/hdd4-1/innodb_data1/ibdata3:8G;/hdd2-1/innodb_data1/ibdata4: 8G;/hdd3-1/innodb_data1/ibdata5:8G;/hdd4-1/innodb_data1/ibdata6:8G;/hdd2-1/innodb_data1/ibdata7:8G;/hdd3-1/innodb_data1/ibdata8:8G;/hdd4-1/innodb_data1/ibdata9:8G; /hdd2-1/innodb_data1/ibdata10:8G;/hdd3-1/innodb_data1/ibdata11:8G;/hdd4-1/innodb_data1/ibdata12:8G;/hdd2-1/innodb_data2/ibdata13:8G;/hdd3-1/innodb_data2/ibdata14:8G; /hdd4-1/innodb_data2/ibdata15:8G;/hdd2-1/innodb_data2/ibdata16:8G;/hdd3-1/innodb_data2/ibdata17:8G;/hdd4-1/innodb_data2/ibdata18:8G;/hdd2-1/innodb_data2/ibdata19:8G; /hdd3-1/innodb_data2/ibdata20:8G;/hdd4-1/innodb_data2/ibdata21:8G;/hdd2-1/innodb_data2/ibdata22:8G;/hdd3-1/innodb_data2/ibdata23:8G;/hdd4-1/innodb_data2/ibdata24:8G; /hdd2-1/innodb_data3/ibdata25:8G;/hdd3-1/innodb_data3/ibdata26:8G;/hdd4-1/innodb_data3/ibdata27:8G;/hdd2-1/innodb_data3/ibdata28:8G;/hdd3-1/innodb_data3/ibdata29:8G; /hdd4-1/innodb_data3/ibdata30:8G;/hdd2-1/innodb_data3/ibdata31:8G;/hdd3-1/innodb_data3/ibdata32:8G;/hdd4-1/innodb_data3/ibdata33:8G;/hdd2-1/innodb_data3/ibdata34:8G;/ hdd3-1/innodb_data3/ibdata35:8G;/hdd4-1/innodb_data3/ibdata36:8G;/hdd2-1/innodb_data4/ibdata37:8G;/hdd3-1/innodb_data4/ibdata38:8G;/hdd4-1/innodb_data4/ibdata39:8G; /hdd2-1/innodb_data4/ibdata40:8G;/hdd3-1/innodb_data4/ibdata41:8G;/hdd4-1/innodb_data4/ibdata42:8G;/hdd2-1/innodb_data4/ibdata43:8G;/hdd3-1/innodb_data4/ibdata44:8G; /hdd4-1/innodb_data4/ibdata45:8G;/hdd2-1/innodb_data4/ibdata46:8G;/hdd3-1/innodb_data4/ibdata47:8G;/hdd4-1/innodb_data4/ibdata48:8G;/hdd2-1/innodb_data5/ibdata49:8G; /hdd3-1/innodb_data5/ibdata50:8G;/hdd4-1/innodb_data5/ibdata51:8G;/hdd2-1/innodb_data5/ibdata52:8G;/hdd3-1/innodb_data5/ibdata53:8G;/hdd4-1/innodb_data5/ibdata54:8G; /hdd2-1/innodb_data5/ibdata55:8G;/hdd3-1/innodb_data5/ibdata56:8G;/hdd4-1/innodb_data5/ibdata57:8G;/hdd2-1/innodb_data5/ibdata58:8G;/hdd3-1/innodb_data5/ibdata59:8G; /hdd4-1/innodb_data5/ibdata60:8G;/hdd2-1/innodb_data6/ibdata61:8G;/hdd3-1/innodb_data6/ibdata62:8G;/hdd2-1/innodb_data6/ibdata63:8G;/hdd3-1/innodb_data6/ibdata64:8G; /hdd2-1/innodb_data6/ibdata65:8G;/hdd3-1/innodb_data6/ibdata66:8G;/hdd2-1/innodb_data6/ibdata67:8G;/hdd3-1/innodb_data6/ibdata68:8G;/hdd2-1/innodb_data7/ibdata69:8G; /hdd3-1/innodb_data7/ibdata70:8G;/hdd2-1/innodb_data7/ibdata71:8G;/hdd3-1/innodb_data7/ibdata72:8G;/hdd2-1/innodb_data7/ibdata73:8G;/hdd3-1/innodb_data7/ibdata74:8G; /hdd2-1/innodb_data7/ibdata75:8G;/hdd3-1/innodb_data7/ibdata76:8G;/hdd4-1/innodb_data6/ibdata77:8G;/hdd4-1/innodb_data6/ibdata78:8G;/hdd4-1/innodb_data6/ibdata79:8G; /hdd4-1/innodb_data6/ibdata80:8G;/hdd4-1/innodb_data7/ibdata81:8G;/hdd5-1/innodb_data1/ibdata82:8G;/hdd5-1/innodb_data1/ibdata83:8G;/hdd5-1/innodb_data1/ibdata84:8G; /hdd5-1/innodb_data1/ibdata85:8G;/hdd5-1/innodb_data2/ibdata86:8G;/hdd5-1/innodb_data2/ibdata87:8G;/hdd5-1/innodb_data2/ibdata88:8G;/hdd5-1/innodb_data2/ibdata89:8G;/ hdd5-1/innodb_data3/ibdata90:8G;/hdd5-1/innodb_data3/ibdata91:8G;/hdd5-1/innodb_data3/ibdata9
Re: Suggestions for ibdata files structure
> 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
Re: Suggestions for ibdata files structure
Gah, my eyes :-) That seems... needlessly complex. As a general rule, it's a good idea to have one datafile per physical disk. Putting more datafiles on a single disk doesn't magically increase parallellism. - Original Message - > From: "Adarsh Sharma" > To: "mysql" > Sent: Monday, 23 January, 2012 10:50:33 AM > Subject: Suggestions for ibdata files structure > > 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 > > Data is inserted continuously. > Presently I have a structure like below > innodb_data_file_path = > /hdd2-1/innodb_data1/ibdata1:8G;/hdd3-1/innodb_data1/ibdata2:8G;/hdd4-1/innodb_data1/ibdata3:8G;/hdd2-1/innodb_data1/ibdata4: > 8G;/hdd3-1/innodb_data1/ibdata5:8G;/hdd4-1/innodb_data1/ibdata6:8G;/hdd2-1/innodb_data1/ibdata7:8G;/hdd3-1/innodb_data1/ibdata8:8G;/hdd4-1/innodb_data1/ibdata9:8G; > /hdd2-1/innodb_data1/ibdata10:8G;/hdd3-1/innodb_data1/ibdata11:8G;/hdd4-1/innodb_data1/ibdata12:8G;/hdd2-1/innodb_data2/ibdata13:8G;/hdd3-1/innodb_data2/ibdata14:8G; > /hdd4-1/innodb_data2/ibdata15:8G;/hdd2-1/innodb_data2/ibdata16:8G;/hdd3-1/innodb_data2/ibdata17:8G;/hdd4-1/innodb_data2/ibdata18:8G;/hdd2-1/innodb_data2/ibdata19:8G; > /hdd3-1/innodb_data2/ibdata20:8G;/hdd4-1/innodb_data2/ibdata21:8G;/hdd2-1/innodb_data2/ibdata22:8G;/hdd3-1/innodb_data2/ibdata23:8G;/hdd4-1/innodb_data2/ibdata24:8G; > /hdd2-1/innodb_data3/ibdata25:8G;/hdd3-1/innodb_data3/ibdata26:8G;/hdd4-1/innodb_data3/ibdata27:8G;/hdd2-1/innodb_data3/ibdata28:8G;/hdd3-1/innodb_data3/ibdata29:8G; > /hdd4-1/innodb_data3/ibdata30:8G;/hdd2-1/innodb_data3/ibdata31:8G;/hdd3-1/innodb_data3/ibdata32:8G;/hdd4-1/innodb_data3/ibdata33:8G;/hdd2-1/innodb_data3/ibdata34:8G;/ > hdd3-1/innodb_data3/ibdata35:8G;/hdd4-1/innodb_data3/ibdata36:8G;/hdd2-1/innodb_data4/ibdata37:8G;/hdd3-1/innodb_data4/ibdata38:8G;/hdd4-1/innodb_data4/ibdata39:8G; > /hdd2-1/innodb_data4/ibdata40:8G;/hdd3-1/innodb_data4/ibdata41:8G;/hdd4-1/innodb_data4/ibdata42:8G;/hdd2-1/innodb_data4/ibdata43:8G;/hdd3-1/innodb_data4/ibdata44:8G; > /hdd4-1/innodb_data4/ibdata45:8G;/hdd2-1/innodb_data4/ibdata46:8G;/hdd3-1/innodb_data4/ibdata47:8G;/hdd4-1/innodb_data4/ibdata48:8G;/hdd2-1/innodb_data5/ibdata49:8G; > /hdd3-1/innodb_data5/ibdata50:8G;/hdd4-1/innodb_data5/ibdata51:8G;/hdd2-1/innodb_data5/ibdata52:8G;/hdd3-1/innodb_data5/ibdata53:8G;/hdd4-1/innodb_data5/ibdata54:8G; > /hdd2-1/innodb_data5/ibdata55:8G;/hdd3-1/innodb_data5/ibdata56:8G;/hdd4-1/innodb_data5/ibdata57:8G;/hdd2-1/innodb_data5/ibdata58:8G;/hdd3-1/innodb_data5/ibdata59:8G; > /hdd4-1/innodb_data5/ibdata60:8G;/hdd2-1/innodb_data6/ibdata61:8G;/hdd3-1/innodb_data6/ibdata62:8G;/hdd2-1/innodb_data6/ibdata63:8G;/hdd3-1/innodb_data6/ibdata64:8G; > /hdd2-1/innodb_data6/ibdata65:8G;/hdd3-1/innodb_data6/ibdata66:8G;/hdd2-1/innodb_data6/ibdata67:8G;/hdd3-1/innodb_data6/ibdata68:8G;/hdd2-1/innodb_data7/ibdata69:8G; > /hdd3-1/innodb_data7/ibdata70:8G;/hdd2-1/innodb_data7/ibdata71:8G;/hdd3-1/innodb_data7/ibdata72:8G;/hdd2-1/innodb_data7/ibdata73:8G;/hdd3-1/innodb_data7/ibdata74:8G; > /hdd2-1/innodb_data7/ibdata75:8G;/hdd3-1/innodb_data7/ibdata76:8G;/hdd4-1/innodb_data6/ibdata77:8G;/hdd4-1/innodb_data6/ibdata78:8G;/hdd4-1/innodb_data6/ibdata79:8G; > /hdd4-1/innodb_data6/ibdata80:8G;/hdd4-1/innodb_data7/ibdata81:8G;/hdd5-1/innodb_data1/ibdata82:8G;/hdd5-1/innodb_data1/ibdata83:8G;/hdd5-1/innodb_data1/ibdata84:8G; > /hdd5-1/innodb_data1/ibdata85:8G;/hdd5-1/innodb_data2/ibdata86:8G;/hdd5-1/innodb_data2/ibdata87:8G;/hdd5-1/innodb_data2/ibdata88:8G;/hdd5-1/innodb_data2/ibdata89:8G;/ > hdd5-1/innodb_data3/ibdata90:8G;/hdd5-1/innodb_data3/ibdata91:8G;/hdd5-1/innodb_data3/ibdata92:8G;/hdd5-1/innodb_data3/ibdata93:8G;/hdd5-1/innodb_data4/ibdata94:8G; > /hdd5-1/innodb_data4/ibdata95:8G;/hdd5-1/innodb_data4/ibdata96:8G;/hdd5-1/innodb_data4/ibdata97:8G;/hdd5-1/innodb_data5/ibdata98:8G;/hdd5-1/innodb_data5/ibdata99:8G; > /hdd5-1/innodb_data5/ibdata100:8G;/hdd5-1/innodb_data5/ibdata101:8G:autoextend > > 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. > > > Thanks > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
