How to extend innodb files?

Hello,

Currently I have the setting:

innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ib data4:10G:autoextend

Because the last file of ibdata4 is very large (more than 50G), if I
want extend the data to more files, for example, ibdata5, ibdata6...
how to do it?

Thanks!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to extend innodb files?

You have to round the size of the last data file (ibdata4) and add the
new ones.
You can find more information on the manual:
http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration. html
http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.h tml

Carlos

On 9/28/2010 12:59 AM, Vokern wrote:
> Hello,
>
> Currently I have the setting:
>
> innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ib data4:10G:autoextend
>
> Because the last file of ibdata4 is very large (more than 50G), if I
> want extend the data to more files, for example, ibdata5, ibdata6...
> how to do it?
>
> Thanks!
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to extend innodb files?

--------------------------------------------------
From: "Carlos Proal"
> You have to round the size of the last data file (ibdata4) and add the
> new ones.
> You can find more information on the manual:
> http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration. html
> http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.h tml
>
> Carlos

I do not think there is anything wrong with having one huge file is there?
We have one innodb file of 85GB on ext3.
Is there?

Jangita | +254 76 918383 | MSN & Y!: jangita@yahoo.com
Skype: jangita | GTalk: jangita.nyagudi@gmail.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to extend innodb files?

2010/9/28 Jangita :

>
> I do not think there is anything wrong with having one huge file is there?
> We have one innodb file of 85GB on ext3.
> Is there?
>

but how about the problem on the file has been increasing continuously?

thanks

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to extend innodb files?

--0016e65ae596de025004914db95a
Content-Type: text/plain; charset=ISO-8859-1

On Tue, Sep 28, 2010 at 10:23 AM, Vokern wrote:

> 2010/9/28 Jangita :
>
> >
> > I do not think there is anything wrong with having one huge file is
> there?
> > We have one innodb file of 85GB on ext3.
>

In and of itself, there is no problem with that. You may, however, prefer
multiple smaller files for managability, when doing file-level backups, to
put files on different disks, ...

It works just as well, but there can be reasons to split, too.


> but how about the problem on the file has been increasing continuously?
>

The file size increases as you add data to InnoDB tables. This is nothing to
worry about - unless you *aren't* actually adding data :-)


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0016e65ae596de025004914db95a--

Re: How to extend innodb files?

--0014850172d7d0003f0491501d0f
Content-Type: text/plain; charset=ISO-8859-1

Hi Vokern,

I suggest to have a single ibdata1 file and use *innodb_file_per_table* to
have multiple .ibd tables.

_Krishna

On Tue, Sep 28, 2010 at 11:29 AM, Vokern wrote:

> Hello,
>
> Currently I have the setting:
>
>
> innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ib data4:10G:autoextend
>
> Because the last file of ibdata4 is very large (more than 50G), if I
> want extend the data to more files, for example, ibdata5, ibdata6...
> how to do it?
>
> Thanks!
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=prajapatikc@gmail.com
>
>

--0014850172d7d0003f0491501d0f--

Re: How to extend innodb files?

--0003255762de8db30504915048f3
Content-Type: text/plain; charset=ISO-8859-1

That's a very good point, actually, as that will also immediately free the
space from tables you delete.

My instincts say that it's marginally slower, though; although honestly I
don't have any data to support that. Does anyone have benchmarks about that
?


On Tue, Sep 28, 2010 at 1:26 PM, Krishna Chandra Prajapati <
prajapatikc@gmail.com> wrote:

> Hi Vokern,
>
> I suggest to have a single ibdata1 file and use *innodb_file_per_table* to
> have multiple .ibd tables.
>
> _Krishna
>
> On Tue, Sep 28, 2010 at 11:29 AM, Vokern wrote:
>
> > Hello,
> >
> > Currently I have the setting:
> >
> >
> >
> innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ib data4:10G:autoextend
> >
> > Because the last file of ibdata4 is very large (more than 50G), if I
> > want extend the data to more files, for example, ibdata5, ibdata6...
> > how to do it?
> >
> > Thanks!
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=prajapatikc@gmail.com
> >
> >
>



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0003255762de8db30504915048f3--

Re: How to extend innodb files?

2010/9/28 Krishna Chandra Prajapati :
> Hi Vokern,
>
> I suggest to have a single ibdata1 file and use innodb_file_per_table to
> have multiple .ibd tables.
>

Can I upgrade to innodb_file_per_table smoothly?

thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to extend innodb files?

--0015175cb5809d4a15049150948a
Content-Type: text/plain; charset=ISO-8859-1

Hi Vokern,

On a running MySQL Server enabling *innodb_file_per_table* makes no changes
to the existing tables. The newly created table (innodb) will be affected
and have thier own .ibd and .frm tables.

Although, you can enable smoothly. But it's better to have it from scratch.
So, that you can reclaim the free space if required simply by dropping the
table.

_Krishna

On Tue, Sep 28, 2010 at 5:16 PM, Vokern wrote:

> 2010/9/28 Krishna Chandra Prajapati :
> > Hi Vokern,
> >
> > I suggest to have a single ibdata1 file and use innodb_file_per_table to
> > have multiple .ibd tables.
> >
>
> Can I upgrade to innodb_file_per_table smoothly?
>
> thanks.
>

--0015175cb5809d4a15049150948a--

Re: How to extend innodb files?

--00221532cb60cd420d0491509aca
Content-Type: text/plain; charset=ISO-8859-1

On Tue, Sep 28, 2010 at 1:46 PM, Vokern wrote:

> Can I upgrade to innodb_file_per_table smoothly?
>

When you activate it, the db will keep reading and using your existing
innodb datafiles. All new tables will be created using .ibd files.
Converting your existing tables is done table per table using ALTER TABLE
mytable ENGINE=INNODB which will basically do a full table copy - keep I/O
and lock time in mind when doing this.


>
> thanks.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--00221532cb60cd420d0491509aca--

Re: How to extend innodb files?

> From: "Jangita"
>=20
> I do not think there is anything wrong with having one huge file is =
there?

There is if you're doing incremental back-ups, in which case adding one =
byte to that file costs you 50GB of backup space.

----------------
You don't have to take insults personally. You can sidestep negative =
energy; you can look for the good in others and utilize that good, as =
different as it may be, to improve your point of view and to enlarge =
your perspective. -- Stephen R. Covey
:::: Jan Steinman, EcoReality Co-op ::::







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: How to extend innodb files?

--00032557563ad3ac2304915f9197
Content-Type: text/plain; charset=ISO-8859-1

On Tue, Sep 28, 2010 at 6:24 PM, Jan Steinman wrote:

> > From: "Jangita"
> >
> > I do not think there is anything wrong with having one huge file is
> there?
>
> There is if you're doing incremental back-ups, in which case adding one
> byte to that file costs you 50GB of backup space.
>

That is only true if you're backing up on the FS level, which means you have
to quiesce the database first. The better/easier way to do incremental mysql
backups is by grabbing the closed binlog files; the downside to that is of
course that replaying those takes more time than restoring the datafile.

That being said, I don't know wether InnoDB files don't get updated metadata
even if no DML happens in them.


>
> ----------------
> You don't have to take insults personally. You can sidestep negative
> energy; you can look for the good in others and utilize that good, as
> different as it may be, to improve your point of view and to enlarge your
> perspective. -- Stephen R. Covey
> :::: Jan Steinman, EcoReality Co-op ::::
>
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--00032557563ad3ac2304915f9197--