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
[email protected]
http://www.linkedin.com/in/rolandoedwards
-----Original Message-----
From: Adarsh Sharma [mailto:[email protected]]
Sent: Wednesday, March 16, 2011 5:33 AM
To: Johan De Meersman
Cc: [email protected]
Subject: Re: Suggestions for InnoDB files
Johan De Meersman wrote:
>> From: "Adarsh Sharma" <[email protected]>
>>
>> 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
