List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:March 16 2011 5:26pm
Subject:RE: Suggestions for InnoDB files
View as plain text  
You should use a simpl data path and create a separate tablespace for each InnoDB file

innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table

This way, ibdata1 only contains the metadata and MVCC control data for all InnoDB files and transactions

Awhile back, you ran a query to get the Diskspace used from you data and sent back this:

+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| MyISAM         |             0.010 TB |             0.001 TB |             0.011 TB | 
| InnoDB         |             0.161 TB |             0.010 TB |             0.171 TB | 
| Total          |             0.171 TB |             0.011 TB |             0.182 TB | 
+----------------+----------------------+----------------------+----------------------+

This means you only have something like 200GB of data.

Your architecture has 101 files that are 8G each to house all InnoDB data. You had a 553GB table which must be spread out over at least 69 of those 8G files.

You should convert over to 6 x 300GB RAID10 set which will give you 824GB of space to start.

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwards@stripped
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: Adarsh Sharma [mailto:adarsh.sharma@stripped] 
Sent: Wednesday, March 16, 2011 5:33 AM
To: Johan De Meersman
Cc: mysql@lists.mysql.com
Subject: Re: Suggestions for InnoDB files

Johan De Meersman wrote:
>> From: "Adarsh Sharma" <adarsh.sharma@stripped>
>>
>> Johan De Meersman wrote:
>>     
>>> Interesting, but why like this instead of simply larger disks or raidsets ?
>>>       
>> It's the IT-Admin Issue , I can't question that and we have only disks of 300GB ( SAS ).
>>     
>
> Your admin is supposed to provide services that benefit the application you need to run on the server. You're stuck with the hardware, but not the setup.
>
>
>   
>>> Why would you use 8G datafiles instead of large, partition-filling ones?
>>>       
>> What is your recommendations for number of ibdata files , keeping in Mind Raid10 is not used and the size of tables .
>> Because in RAID10 :
>>
>> We can utilize 50 – 55 percent size of hard disk.(50-55 % of 4 hard disk total space if hard disks are 500 GB X 4 then we can 
>> utilize only 1 TB space from 2 TB.
>>     
>
> Correct. That's the price you pay for the performance and redundancy RAID10 gives you. Nothing is free in life :-) Incidentally, it's going to be exactly 50% - I'll be very interested to see where he pulls those extra 5% from.
>
> You could ostensibly go for RAID5, which will allow you to use 1.5 TB off those same four disks, at a minor loss of disk redundancy (only one may fail) and some loss of performance - but still better than no RAID at all. If you want to lose no space at all, use RAID0 (striping) to increase performance, but that offers no disk redundancy at all - single disk fails, you lose all data.
>
> As a small overview, RAID 10 gives you the benefits of striping (data for a single file is split over multiple disks) so reads and writes faster, AND of mirrorring (every block is available on multiple disks, which provides insurance data loss when a disk breaks and additionally increases the read speed even more. You won't actually quadruple the read speed, but I wouldn't be surprised to see it triple on a 4-disk RAID 10.
>
> RAID 5 uses one of your disks for redundancy purposes, so any single disk may fail and you'll still have all your data. Data is striped, so disk performance also increases, although not as much as mirrorring. This is however the most CPU-intensive form, as checksumming over all disks happens at every write. This also makes that write speed won't see as much benefit.
>
> RAID 0 has no redundancy whatsoever - if anything you could say it's worse than data over multiple disks, because if one disk fails the entire volume is lost. Because it offers striping, however, it gives performance a good boost.
>
>
>   
>> Software RAID is not reliable on production environment because software raid is dependent on hardware and software both thing 
>> if one thing go down then it will not work, but in hardware raid there is no role of software every thing is depend on hardware.
>> But, We are not able to afford Hardware RAID.
>>     
>
> Maybe you shouldn't have an OS then, either; because if that fails everything is down? My word, if that's his excuse, I seriously recommend you get a better admin.
>
> Software RAID offers the same or better performance than hardware RAID, save for the real high-end RAID cards. Additionally it offers more flexibility in the setup - many combinations of RAID levels are possible, whereas the majority of controllers offer 1, 5 and 10 at most.
>
> An additional benefit that is not to be laughed at, especially if you're on a budget, is that software RAID will work regardless of the hardware involved. Hardware RAID controllers tend to have their own specific set of metadata on the disks, and if your controller breaks, you had better manage to get the exact same one, or you risk not being able to read your disks. Sofware RAID, by virtue of being software, can simply be reinstalled on another system if need be. Tell MD to scan for and assemble RAID arrays and it'll just find the appropriate partitions and match the pieces together. No more accidentally putting a disk in the wrong bay and having it break the RAIDset. (I'll admit that has become rare with controllers getting smarter over the years, but I've seen multi-terabyte arrays go useless because some idiot operator switched two disks into the wrong bays)
>
>
> So, yes, my recommendation remains the same: switch the system to software RAID; preferably 10, 5 or 0 if you really need all that space.
>
>
>   

A Heartiest Thanks from my heart for explaining all these things in a 
fantastic manner. I agreed with your suggestions but one thing which 
isn't explained from your side , as you go deeper in RAID point.

Q:- What is your recommendations for number of ibdata files , would it be

Make sure the disk /hdd2-1/innodb_data1 is big enough and it doesn't affect performance.


I need your help while configuring RAID10 on a Server, may be next week.


Best Regards,
Adarsh Sharma



Thread
Suggestions for InnoDB filesAdarsh Sharma16 Mar
  • Re: Suggestions for InnoDB filesJohan De Meersman16 Mar
Re: Suggestions for InnoDB filesJohan De Meersman16 Mar
  • Re: Suggestions for InnoDB filesAdarsh Sharma16 Mar
    • Re: Suggestions for InnoDB filesJohan De Meersman16 Mar
    • RE: Suggestions for InnoDB filesRolando Edwards16 Mar