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,
Kishore,
No, as already explained, it is not possible, Innodb datafiles *never*
shrink.
Cheers
Claudio
On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org
wrote:
Hi,
I understand that if I set the innodb_file_per_table then once the table
is drop the datafile will also be
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
do u have one file per table or just one system tablespace datafile.
On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav
kish...@railsfactory.orgwrote:
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
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 anan...@gmail.com wrote:
do u have one file per table or just one system tablespace datafile.
On
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,
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 tblname which is in fact a ALTER TABLE
without real changes
Am 22.05.2012 11:28, schrieb Kishore Vaishnav:
Right now one tablespace
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
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
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
kish...@railsfactory.orgwrote:
Hi Reindl Harald,
Does this means that if we have a single tablespace with
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
- Original Message -
From: Pothanaboyina Trimurthy skd.trimur...@gmail.com
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
- Original Message -
From: Reindl Harald h.rei...@thelounge.net
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
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,
Am 22.05.2012 13:19, schrieb Johan De Meersman:
- Original Message -
From: Reindl Harald h.rei...@thelounge.net
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
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
- Original Message -
From: Ananda Kumar anan...@gmail.com
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,
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 vegiv...@tuxera.bewrote:
--
*From: *Ananda Kumar anan...@gmail.com
yes, there some
- Original Message -
From: Reindl Harald h.rei...@thelounge.net
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 every beginner
Am 22.05.2012 13:40, schrieb Johan De Meersman:
- Original Message -
From: Reindl Harald h.rei...@thelounge.net
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
- Original Message -
From: Ananda Kumar anan...@gmail.com
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
Am 22.05.2012 13:52, schrieb Johan De Meersman:
- Original Message -
From: Ananda Kumar anan...@gmail.com
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.
- Original Message -
From: Reindl Harald h.rei...@thelounge.net
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
Am 22.05.2012 13:59, schrieb Johan De Meersman:
- Original Message -
From: Reindl Harald h.rei...@thelounge.net
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
- Original Message -
From: Reindl Harald h.rei...@thelounge.net
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
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 vegiv...@tuxera.bewrote:
- Original Message -
From: Reindl Harald h.rei...@thelounge.net
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 anan...@gmail.com wrote:
Is you system READ intensive or WRITE intensive.
If you have enable compression for WRITE intensive
From: Claudio Nanni claudio.na...@gmail.com
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
- Original Message -
From: Jan Steinman j...@bytesmiths.com
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
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 j...@bytesmiths.com
From: Claudio Nanni
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
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 reducing
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
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
- Original Message -
From: Manivannan S. manivanna...@spanservices.com
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
35 matches
Mail list logo
