To shrink ibdata1:
1. Dump everything
2. Stop mysql
3. change to innodb_file_per_table = 1
4. change allocation in my.cnf (my.ini) to something smaller, say 50M,AUTOEXTEND
5. remove (rm / delete) ibdata1
6. restart mysql
7. reload data
Step 3: innodb_file_per_table gives you more fine-grained con
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
because this is simply the way InnoDB is implemented
as multiple said the default of a single table space
is idiotic in my opinion, but however this is well
known over years
google: "mysql innodb reduce datafiles"
Am 22.05.2012 11:50, schrieb Kishore Vaishnav:
> Does this means that if we have a
single file will not release the space to OS, it will continue to hold the
space got from drop or truncate and used only for the database
On Tue, May 22, 2012 at 3:20 PM, Kishore Vaishnav
wrote:
> Hi Reindl Harald,
>
> Does this means that if we have a single tablespace with file per table and
>
Yes,
Looks like there is lot of fragmentation.
Is this production or dev/qa.
You need to take a dump,
drop database, make changes to parameter file to have file per table and
restore the dump, so that each table will have its own .idb files, and you
can easily manage the space.
regards
anandkl
O
Hi Reindl Harald,
Does this means that if we have a single tablespace with file per table and
doing the optimization will reduce the size of the datafile size ? If yes,
then why this not possible on the datafile (one single file) too ?
*
*
*thanks & regards,*
*__*
Kishore Kumar Vai
as multiple answered, yes it matters!
there is no way to reduce the size of a single tablespace
with file per table you can shrink the files with
"optimize table " which is in fact a "ALTER TABLE"
without real changes
Am 22.05.2012 11:28, schrieb Kishore Vaishnav:
> Right now one tablespace dataf
Changes, you have a lot of changes.
You change the equivalent of 1 gb data a day.
Every change is similar to an insert and every delete does not free any
disk space.
No way to reduce this rate unless you change the app logic.
More info needed to provide a hint.
Claudio
On May 22, 2012 10:50 AM, "K
Right now one tablespace datafile. But does it matters if i have one file
per table.
*thanks & regards,
__*
Kishore Kumar Vaishnav
*
*
On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar wrote:
> do u have one file per table or just one system tablespace datafile.
>
> On Tue, May 22, 2
do u have one file per table or just one system tablespace datafile.
On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav
wrote:
> Thanks for the reply, but in my case the datafile is growing 1 GB per day
> with only 1 DB (apart from mysql / information_schema / test) and the size
> of the DB is jus
Thanks for the reply, but in my case the datafile is growing 1 GB per day
with only 1 DB (apart from mysql / information_schema / test) and the size
of the DB is just 600MB, where records get updated / deleted / added and on
an average it maintains 600MB only. Now the datafile is increased to 30GB
Kishore,
No, as already explained, it is not possible, Innodb datafiles *never*
shrink.
Cheers
Claudio
On May 22, 2012 10:05 AM, "Kishore Vaishnav"
wrote:
> 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
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
the table and the datafile shrinks itself ?
*thanks & regards,
__*
Kishore Kumar Vaishnav
*
*
On Mon, May 21, 2012 at 6:43 PM,
- 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 connections to the
server, t
Manivannan,
There is no way to reduce the InnoDB main tablespace.
You can get rid (=regain disk space) of InnoDB tablespaces only if you have
innodb_file_per_table setting,
which allows you to get disk space back if you drop the table.
The best option you have to free your current shared tablespace
35 matches
Mail list logo
