SEO

Hello list,

I would like to grow my innodb table space, the only problem that I have
is that I did not declare any size in the config file since we were not
using it to start with. If I modify the config file, will this override
the current innodb file or will it grow it ? Or should dump all the
data, modify the config file and re-import everything ?

Thanks.

NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses.

AVIS: Le présent courriel contient des renseignements de nature privilégiée et confidentielle et n’est destiné qu'à la personne à qui il est adressé. Si vous n’êtes pas le destinataire prévu, vous êtes par les présentes avisés que toute diffusion, distribution ou reproduction de cette communication est strictement interdite.* Si vous avez reçu ce courriel par erreur, veuillez en aviser immédiatement l’expéditeur et le supprimer de votre système. Notez que la transmission de courriel ne peut en aucun cas être considéré comme inviolable ou exempt d’erreur puisque les informations qu’il contient pourraient être interceptés, corrompues, perdues, détruites, arrivées en retard ou incomplètes ou contenir un virus. *

Hi,

Jean-Sebastien Pilon wrote:
> I would like to grow my innodb table space, the only problem that I have
> is that I did not declare any size in the config file since we were not
> using it to start with. If I modify the config file, will this override
> the current innodb file or will it grow it ? Or should dump all the
> data, modify the config file and re-import everything ?

Chances are that innodb isn't even functional if you did not specify a
tablespace any way. You can check with:
mysql> SHOW VARIABLES LIKE LIKE 'innodb_data%';

If there is a data path given you'll see how it has been set up. Do you
want a fixed size or should it grow automatically?

Anyways, you might want to check this documentation:
http://dev.mysql.com/doc/refman/5.0/...-removing.html

NOTICE: You should not send privileged and confidential information to a
maillinglist

regards
Nils

+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | /var/lib/mysql/ |
+-----------------------+------------------------+

There is one defined, since I have innodb tables in there, very small
ones. But the one I need know will grow easily to 5GB of data and will
archive rows that are 30+ days old once every week.

Since I am not running out of space on the machine, I though I could
give 10GB to the datafile that will contain this new table... Should I
do something like ?

innodb_data_file_path = ibdata1:10M:autoextend;ibdata2:10240M

Will this keep the data in ibdata1 intact ?
Is it good to create a separate tablespace like this ?

> -----Original Message-----
> From: Nils Meyer [mailto:meyer@hauptsache.net]
> Sent: Wednesday, February 21, 2007 11:33 AM
> To: mysql@lists.mysql.com
> Subject: Re: Growing innodb size
>
> Hi,
>
> Jean-Sebastien Pilon wrote:
> > I would like to grow my innodb table space, the only
> problem that I have
> > is that I did not declare any size in the config file since
> we were not
> > using it to start with. If I modify the config file, will
> this override
> > the current innodb file or will it grow it ? Or should dump all the
> > data, modify the config file and re-import everything ?
>
> Chances are that innodb isn't even functional if you did not
> specify a
> tablespace any way. You can check with:
> mysql> SHOW VARIABLES LIKE LIKE 'innodb_data%';
>
> If there is a data path given you'll see how it has been set
> up. Do you
> want a fixed size or should it grow automatically?
>
> Anyways, you might want to check this documentation:
> http://dev.mysql.com/doc/refman/5.0/...-removing.html
>
NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses.

AVIS: Le présent courriel contient des renseignements de nature privilégiée et confidentielle et n’est destiné qu'à la personne à qui il est adressé. Si vous n’êtes pas le destinataire prévu, vous êtes par les présentes avisés que toute diffusion, distribution ou reproduction de cette communication est strictement interdite.* Si vous avez reçu ce courriel par erreur, veuillez en aviser immédiatement l’expéditeur et le supprimer de votre système. Notez que la transmission de courriel ne peut en aucun cas être considéré comme inviolable ou exempt d’erreur puisque les informations qu’il contient pourraient être interceptés, corrompues, perdues, détruites, arrivées en retard ou incomplètes ou contenir un virus. *

The following does not work, it gives a syntax error.

innodb_data_file_path = ibdata1:10M:autoextend;ibdata2:10240M

I tried this instead and it didn't want to start and mysql crashes, is
it because it expects the data to be located in the 1st datafile ?

innodb_data_file_path = ibdata2:10240M;ibdata1:10M:autoextend

Then I tried with the following option in my.cnf as suggested on mysql
website

innodb_force_recovery = 4

It still crashes

Works good when I put only the following

innodb_data_file_path = ibdata1:10M:autoextend


Any idea someone ?

> -----Original Message-----
> From: Jean-Sebastien Pilon [mailto:jspilon@PENSON.CA]
> Sent: Wednesday, February 21, 2007 12:53 PM
> To: Nils Meyer; mysql@lists.mysql.com
> Subject: RE: Growing innodb size
>
> +-----------------------+------------------------+
> | Variable_name | Value |
> +-----------------------+------------------------+
> | innodb_data_file_path | ibdata1:10M:autoextend |
> | innodb_data_home_dir | /var/lib/mysql/ |
> +-----------------------+------------------------+
>
> There is one defined, since I have innodb tables in there, very small
> ones. But the one I need know will grow easily to 5GB of data and will
> archive rows that are 30+ days old once every week.
>
> Since I am not running out of space on the machine, I though I could
> give 10GB to the datafile that will contain this new table... Should I
> do something like ?
>
> innodb_data_file_path = ibdata1:10M:autoextend;ibdata2:10240M
>
> Will this keep the data in ibdata1 intact ?
> Is it good to create a separate tablespace like this ?
>
> > -----Original Message-----
> > From: Nils Meyer [mailto:meyer@hauptsache.net]
> > Sent: Wednesday, February 21, 2007 11:33 AM
> > To: mysql@lists.mysql.com
> > Subject: Re: Growing innodb size
> >
> > Hi,
> >
> > Jean-Sebastien Pilon wrote:
> > > I would like to grow my innodb table space, the only
> > problem that I have
> > > is that I did not declare any size in the config file since
> > we were not
> > > using it to start with. If I modify the config file, will
> > this override
> > > the current innodb file or will it grow it ? Or should
> dump all the
> > > data, modify the config file and re-import everything ?
> >
> > Chances are that innodb isn't even functional if you did not
> > specify a
> > tablespace any way. You can check with:
> > mysql> SHOW VARIABLES LIKE LIKE 'innodb_data%';
> >
> > If there is a data path given you'll see how it has been set
> > up. Do you
> > want a fixed size or should it grow automatically?
> >
> > Anyways, you might want to check this documentation:
> > http://dev.mysql.com/doc/refman/5.0/...-removing.html
> >
NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses.

AVIS: Le présent courriel contient des renseignements de nature privilégiée et confidentielle et n’est destiné qu'à la personne à qui il est adressé. Si vous n’êtes pas le destinataire prévu, vous êtes par les présentes avisés que toute diffusion, distribution ou reproduction de cette communication est strictement interdite.* Si vous avez reçu ce courriel par erreur, veuillez en aviser immédiatement l’expéditeur et le supprimer de votre système. Notez que la transmission de courriel ne peut en aucun cas être considéré comme inviolable ou exempt d’erreur puisque les informations qu’il contient pourraient être interceptés, corrompues, perdues, détruites, arrivées en retard ou incomplètes ou contenir un virus. *

You need to "close" the size of ibdata1 and add the second file ibdata2
with the size you need (and i you like you can even add the autoextend
option, but only to the last ibdata file added)

for example, if you are aproaching the 1gb size on ibdata1, you specify
the maximun size that you want for that file and then add the second file:

innodb_data_file_path = ibdata1:988M;ibdata2:10G:autoextend


For further information check:
http://dev.mysql.com/doc/refman/5.0/...-removing.html

Carlos


Jean-Sebastien Pilon wrote:
> The following does not work, it gives a syntax error.
>
> innodb_data_file_path = ibdata1:10M:autoextend;ibdata2:10240M
>
> I tried this instead and it didn't want to start and mysql crashes, is
> it because it expects the data to be located in the 1st datafile ?
>
> innodb_data_file_path = ibdata2:10240M;ibdata1:10M:autoextend
>
> Then I tried with the following option in my.cnf as suggested on mysql
> website
>
> innodb_force_recovery = 4
>
> It still crashes
>
> Works good when I put only the following
>
> innodb_data_file_path = ibdata1:10M:autoextend
>
>
> Any idea someone ?
>
>
>> -----Original Message-----
>> From: Jean-Sebastien Pilon [mailto:jspilon@PENSON.CA]
>> Sent: Wednesday, February 21, 2007 12:53 PM
>> To: Nils Meyer; mysql@lists.mysql.com
>> Subject: RE: Growing innodb size
>>
>> +-----------------------+------------------------+
>> | Variable_name | Value |
>> +-----------------------+------------------------+
>> | innodb_data_file_path | ibdata1:10M:autoextend |
>> | innodb_data_home_dir | /var/lib/mysql/ |
>> +-----------------------+------------------------+
>>
>> There is one defined, since I have innodb tables in there, very small
>> ones. But the one I need know will grow easily to 5GB of data and will
>> archive rows that are 30+ days old once every week.
>>
>> Since I am not running out of space on the machine, I though I could
>> give 10GB to the datafile that will contain this new table... Should I
>> do something like ?
>>
>> innodb_data_file_path = ibdata1:10M:autoextend;ibdata2:10240M
>>
>> Will this keep the data in ibdata1 intact ?
>> Is it good to create a separate tablespace like this ?
>>
>>
>>> -----Original Message-----
>>> From: Nils Meyer [mailto:meyer@hauptsache.net]
>>> Sent: Wednesday, February 21, 2007 11:33 AM
>>> To: mysql@lists.mysql.com
>>> Subject: Re: Growing innodb size
>>>
>>> Hi,
>>>
>>> Jean-Sebastien Pilon wrote:
>>>
>>>> I would like to grow my innodb table space, the only
>>>>
>>> problem that I have
>>>
>>>> is that I did not declare any size in the config file since
>>>>
>>> we were not
>>>
>>>> using it to start with. If I modify the config file, will
>>>>
>>> this override
>>>
>>>> the current innodb file or will it grow it ? Or should
>>>>
>> dump all the
>>
>>>> data, modify the config file and re-import everything ?
>>>>
>>> Chances are that innodb isn't even functional if you did not
>>> specify a
>>> tablespace any way. You can check with:
>>> mysql> SHOW VARIABLES LIKE LIKE 'innodb_data%';
>>>
>>> If there is a data path given you'll see how it has been set
>>> up. Do you
>>> want a fixed size or should it grow automatically?
>>>
>>> Anyways, you might want to check this documentation:
>>> http://dev.mysql.com/doc/refman/5.0/...-removing.html
>>>
>>>
> NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses.
>
> AVIS: Le présent courriel contient des renseignements de nature privilégiée et confidentielle et n’est destiné qu'à la personne à qui il est adressé. Si vous n’êtes pas le destinataire prévu, vous êtes par les présentes avisés que toute diffusion, distribution ou reproduction de cette communication est strictement interdite. Si vous avez reçu ce courriel par erreur, veuillez en aviser immédiatement l’expéditeur et le supprimer de votre système. Notez que la transmission de courriel ne peut en aucun cas être considéré comme inviolable ou exempt d’erreur puisque les informations qu’il contient pourraient être interceptés, corrompues, perdues, détruites, arrivées en retard ou incomplètes ou contenir un virus.
>
>