From: Johan De Meersman Date: January 23 2012 12:52pm Subject: Re: Suggestions for ibdata files structure List-Archive: http://lists.mysql.com/mysql/226646 Message-Id: <2a9a82f7-603b-4684-8e30-7ff059b7a113@zimbra> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_921cd605-f87f-498e-9d87-736cde0731c7" --=_921cd605-f87f-498e-9d87-736cde0731c7 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit ----- 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 --=_921cd605-f87f-498e-9d87-736cde0731c7--