ivanna...@spanservices.com]
> Sent: Monday, May 21, 2012 6:04 AM
> To: mysql@lists.mysql.com
> Subject: Reducing ibdata1 file size
>
> Hi ,
>
> I am trying to reduce the ibdata1 data file in MySQL.
> In MySQL data directory the ibdata1 data file is always increasing
> when
Okay, my mistake. I should write precisely when communicating with precise
people. :-)
What I meant was, dumping and importing is the "common knowledge" way of
"virtually" shrinking innodb files.
So, now that I've conceded the meta-argument, what do you think of the linked
procedure for reduci
Despite the conventional wisdom, converting to innodb_file_per_table will not
necessarily help you. It depends on your situation. If most of your growth is
in a single table, you will only have transferred the problem from the ibdata1
file to a new file. The ibdata1 file may also continue to
Jan,
that's not common wisdom, Innodb datafiles ***never*** shrink,
that in the blog from 22th of May is a workaround, one of the many.
If you ask my my favourite is to use a stand by instance and work on that.
Claudio
2012/5/22 Jan Steinman
> > From: Claudio Nanni
> >
> > No, as already expl
- Original Message -
> From: "Jan Steinman"
>
> That's been the common wisdom for a long time.
>
> However, this just popped up on my RSS reader. I haven't even looked
> at it, let alone tried it.
In brief: convert all your tables to myisam, delete ibdatafile during a
restart, convert
> From: Claudio Nanni
>
> No, as already explained, it is not possible, Innodb datafiles *never* shrink.
That's been the common wisdom for a long time.
However, this just popped up on my RSS reader. I haven't even looked at it, let
alone tried it.
I'm interested in what the experts think...
or it could be that your buffer size is too small, as mysql is spending lot
of CPU time for compress and uncompressing
On Tue, May 22, 2012 at 5:45 PM, Ananda Kumar wrote:
> Is you system READ intensive or WRITE intensive.
> If you have enable compression for WRITE intensive data, then CPU cost
Is you system READ intensive or WRITE intensive.
If you have enable compression for WRITE intensive data, then CPU cost will
be more.
On Tue, May 22, 2012 at 5:41 PM, Johan De Meersman wrote:
>
>
> - Original Message -
> > From: "Reindl Harald"
> >
> > interesting because i have here a d
- Original Message -
> From: "Reindl Harald"
>
> interesting because i have here a dbmail-server with no CPU load and
> innodb with compression enabled since 2009 (innodb plugin in the past)
Ah, this is a mixed-use server that also receives data from several Cacti
installs.
> [--] Da
Am 22.05.2012 13:59, schrieb Johan De Meersman:
> - Original Message -
>> From: "Reindl Harald"
>>
>> 95% of mysqld-installations have no problem with
>> innodb_file_per_table so DEFAULTS should not be for 5%
>
> There is "no problem", and there is "better practice"
> and if your syste
- Original Message -
> From: "Reindl Harald"
>
> 95% of mysqld-installations have no problem with
> innodb_file_per_table so DEFAULTS should not be for 5%
There is "no problem", and there is "better practice" - and if your system is
I/O bound it makes sense to minimize on-disk fragmenta
Am 22.05.2012 13:52, schrieb Johan De Meersman:
> - Original Message -
>
>> From: "Ananda Kumar"
>
>> yes, Barracuda is limited to FILE_PER_TABLE.
> Ah, I didn't realise that. Thanks :-)
>
>> Yes, true there is CPU cost, but very less.
>> To gain some you have to loss some.
>
> I've
- Original Message -
> From: "Ananda Kumar"
> yes, Barracuda is limited to FILE_PER_TABLE.
Ah, I didn't realise that. Thanks :-)
> Yes, true there is CPU cost, but very less.
> To gain some you have to loss some.
I've only got it enabled on a single environment, but enabling it added
Am 22.05.2012 13:40, schrieb Johan De Meersman:
> - Original Message -
>> From: "Reindl Harald"
>> Subject: Re: Reducing ibdata1 file size
>>
>> well but for what price?
>> the problem is the DEFAULT
>>
>> users with enough knowl
- Original Message -
> From: "Reindl Harald"
> Subject: Re: Reducing ibdata1 file size
>
> well but for what price?
> the problem is the DEFAULT
>
> users with enough knowledge could easy change the default
> currently what is happening is that mostly
yes, Barracuda is limited to FILE_PER_TABLE.
Yes, true there is CPU cost, but very less.
To gain some you have to loss some.
On Tue, May 22, 2012 at 5:07 PM, Johan De Meersman wrote:
> --
>
> *From: *"Ananda Kumar"
>
>
> yes, there some new features you can use to im
- Original Message -
> From: "Ananda Kumar"
> yes, there some new features you can use to improve performance.
> If you are using mysql 5.5 and above, with files per table, you can
> enable BARACUDA file format, which in turn provides data compression
> and dynamic row format, which will
In regards to why the file grows large, you may wish to read some of
the posts on the MySQL Performance Blog, which has quite a bit of
information on this, such as
http://www.mysqlperformanceblog.com/2010/06/10/reasons-for-run-away-main-innodb-tablespace/
--
MySQL General Mailing List
For list ar
Am 22.05.2012 13:19, schrieb Johan De Meersman:
> - Original Message -
>> From: "Reindl Harald"
>>
>> as multiple said the default of a single table space
>> is idiotic in my opinion, but however this is well
>> known over years
>
> I suppose there's a certain logic to favouring one-sho
yes, there some new features you can use to improve performance.
If you are using mysql 5.5 and above, with files per table, you can enable
BARACUDA file format, which in turn provides data compression
and dynamic row format, which will reduce IO.
For more benefits read the doc
On Tue, May 22, 20
- Original Message -
> From: "Reindl Harald"
>
> as multiple said the default of a single table space
> is idiotic in my opinion, but however this is well
> known over years
I suppose there's a certain logic to favouring one-shot allocation and never
giving up free space, in that it red
- Original Message -
> From: "Pothanaboyina Trimurthy"
>
> hi sir,
Please keep the list in CC, others may benefit from your questions, too.
> can we see any performance related improvements if we use
> "innodb_file_per_table" other than using a single ibdatafile for all
> inn
> >>>> wrote:
> >>>>
> >>>>> Hi,
> >>>>>
> >>>>> I understand that if I set the innodb_file_per_table then once the
> >>> table
> >>>>> is drop the datafile w
is not possible, Innodb datafiles
> *never*
> > >>>> shrink.
> > >>>>
> > >>>> Cheers
> > >>>>
> > >>>> Claudio
> > >>>> On May 22, 2012 10:05 AM, "Kishore Vaishnav" <
> > kish...@railsfactory.org&g
gt; >> I understand that if I set the innodb_file_per_table then once the
>>> table
>>> >> is drop the datafile will also be lost. But is there a way where I
>>> >> truncate
>>> >> the table and the datafile shrinks itself ?
>>> >>
&g
t;Kishore Vaishnav" <
> kish...@railsfactory.org>
> >>>> wrote:
> >>>>
> >>>>> Hi,
> >>>>>
> >>>>> I understand that if I set the innodb_file_per_table then once the
> >>> table
> >
>>>>
>>>>> Hi,
>>>>>
>>>>> I understand that if I set the innodb_file_per_table then once the
>>> table
>>>>> is drop the datafile will also be lost. But is there a way where I
>>>>> truncate
>>
he table and the datafile shrinks itself ?
>>>
>>> *thanks & regards,
>>> ______*
>>> Kishore Kumar Vaishnav
>>> *
>>>
>>> *
>>> On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman >> >wrote:
>>>
>&
t;> >>
>> >> I understand that if I set the innodb_file_per_table then once the
>> table
>> >> is drop the datafile will also be lost. But is there a way where I
>> >> truncate
>> >> the table and the datafile shrinks itself ?
>> >&g
t; >> is drop the datafile will also be lost. But is there a way where I
> >> truncate
> >> the table and the datafile shrinks itself ?
> >>
> >> *thanks & regards,
> >> __*
> >> Kishore Kumar Vaishnav
> >>
; *
>> On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman > >wrote:
>>
>> > - Original Message -
>> > > From: "Manivannan S."
>> > >
>> > > How to reduce the ibdata1 file size in both LINUX and WINDOWS
>> > > mach
e -----
> > > From: "Manivannan S."
> > >
> > > How to reduce the ibdata1 file size in both LINUX and WINDOWS
> > > machine.
> >
> > This is by design - you cannot reduce it, nor can you remove added
> > datafiles.
> >
> >
3 PM, Johan De Meersman wrote:
> - Original Message -
> > From: "Manivannan S."
> >
> > How to reduce the ibdata1 file size in both LINUX and WINDOWS
> > machine.
>
> This is by design - you cannot reduce it, nor can you remove added
> datafile
- Original Message -
> From: "Manivannan S."
>
> How to reduce the ibdata1 file size in both LINUX and WINDOWS
> machine.
This is by design - you cannot reduce it, nor can you remove added datafiles.
If you want to shrink the ibdata files, you must stop all conn
ta still exist in the ibdata1 data file.
>
> How to reduce the ibdata1 file size in both LINUX and WINDOWS machine.
>
> Do you have any idea how to solve this problem. Thanks for any feedback.
>
>
>
> Thanks
> Manivannan S
>
> DISCLAIMER: This email message and all atta
server but data
still exist in the ibdata1 data file.
How to reduce the ibdata1 file size in both LINUX and WINDOWS machine.
Do you have any idea how to solve this problem. Thanks for any feedback.
Thanks
Manivannan S
DISCLAIMER: This email message and all attachments are confidential and
Hello.
>there, in fact, anything important in that ibdata1 file, or can I just
>shut down mySQL, move that file somewhere else, and then re-start mySQL?
Yes, this file contains important information.
>Is this possible, or am I stuck having my database server off-line for
>
Hello!
When I first set up my mySQL 4.1 server, I did not have the
"innodb_file_per_table" option set. I have since set this option, and
re-created all my tables so that they are now in individual innoDB
files. However, the original, 44GB ibdata1 file still exists and I
can't fi
Howdy Folks,
Got a bit of a hassle. After some broken tries (got impatient) to
change the type of large table's column I found that, when I finally
got the patience, my disk went full and cancelled the operation again.
Since MySQL copies the table before such operation, my ibdata1 file
(
Hi!
- Original Message -
From: "walt" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, May 23, 2002 10:24 PM
Subject: Re: Ibdata1 file thats just too big...
> "Williamson, David" wrote:
>
> > Hi there,
> >
> > My
"Williamson, David" wrote:
> Hi there,
>
> My Ibdatafile is set too big (the disk is full to the point that nothing is
> executing properly) I am wondering is it safe to delete this file and then
> in the my.cnf file reset the size to something smaller... - how does the
> Ibdata file work in conj
I am keen to here a response to this as well!
TIA
Mike
- Original Message -
From: "Williamson, David" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, May 22, 2002 4:38 PM
Subject: Ibdata1 file thats just too big...
> Hi there,
>
> My Ibdataf
Hi there,
My Ibdatafile is set too big (the disk is full to the point that nothing is
executing properly) I am wondering is it safe to delete this file and then
in the my.cnf file reset the size to something smaller... - how does the
Ibdata file work in conjunction with mysql...
Will I lose data
43 matches
Mail list logo