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
is
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 is
- 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
Hello all.
I would like to ask for advice with performance tuning with MySQL.
Following are some data for my server.
CPU: Xeon(TM) 2.8GHz (2CPUs - 8core total)
Memory : 8GB
OS : RHEL 4.4 x86_64
MySQL : MySQL 5.0.50sp1-enterprise
Attached file
# my.cnf.txt : my.cnf
Also following is the free command result.
total used free sharedbuffers cached
Mem: 81623807843676 318704 0 956325970892
-/+ buffers/cache:17771526385228
Swap: 8032492 235608008932
Thanks,
Yu
Yu
Hello,
I seem your mysqld doesn't use enough memory.
Date Time CPU% RSS VSZ
2012/5/22 21:00:39 109 294752 540028
if your mysqld uses InnoDB oftenly,
edit innodb_buffer_pool_size in you my.cnf.
I don't see any attachments.
First, I would upgrade to 5.5 as 5.0 is very old. The upgrade process
is painless.
Second, make sure your Innodb buffer pool is allocating as much ram as
possible. I'd even go as far as adding another 8gb of ram to the
server. The buffer pool setting is going to give
Hello Tsubasa.
Thank you for the reply. (返信ありがとうございます。)
Our high loaded DB are both INNODB and MyISAM.
Espicially , on MyISAM.
I will consider the tuning of innodb_buffer_pool_size as well.
Do you know the tips for how to tune the disk access for MyISAM?
Thanks,
Yu
Tsubasa Tanaka さんは書きました:
Hello, Yu-san,
(へろへろな英語で申し訳ないです)
Can I think that you already tweaked Index on the tables?
if you yet,please create apt indexes.
MyISAM caches only Index without data.
i take way for decreasing disk seek,
1) create more indexes on the tables,if the tables doesn't update quite often.
On Tue, May 22, 2012 at 12:02 AM, Marc Zampetti marc.zampe...@gmail.com wrote:
Is Puppet Labs saying they are ending support of MySQL and instead will only
support PostgreSQL? That is going to be a big problems for shops that do not
support PostgresSQL, or are only allowed to run DB systems on
40 matches
Mail list logo