List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:January 23 2012 12:52pm
Subject:Re: Suggestions for ibdata files structure
View as plain text  
----- Original Message -----

> From: "Adarsh Sharma" <adarsh.sharma@stripped>

> 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 

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