There is a very good reason: it is the phenomenon of row drift. The
master and slave can appear to be in good sync, but often it is not
actually the case.
... sounds interesting; have you got any document explaining
this phenomenon? AFAIK, the things that (silently) break
my solution. I
just need a way to make the same thing work with InnoDB.
--
Eric Robinson
Disclaimer - January 25, 2011
This email and any files transmitted with it are confidential and intended
solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named
addressee you should
?
Based on everything you've said so far, I still prefer my solution. I
just need a way to make the same thing work with InnoDB.
this is simply impossible
--
Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p
240 mysql-servers?
why there is no consolidation?
I said 240+ mysql *instances*, not servers. It's actually just 3
physical servers (not counting standby cluster nodes).
just need a way to make the same thing work with InnoDB.
this is simply impossible
That is very unfortunate
On Mon, Jan 24, 2011 at 6:43 PM, Gavin Towey gto...@ffn.com wrote:
If you show the EXPLAIN SELECT .. output, and the table structure, someone
will be able to give a more definite answer.
Thanks for the reply Gavin. I actually did place this info in my very first
message on this thread, along
?
sorry but your smallest problem is really innodb per rsync
just need a way to make the same thing work with InnoDB.
this is simply impossible
That is very unfortunate.
The whole world can work with replication-slaves and you are
the only one who installing an endless count of mysql-services
240 mysql-instances on 3 physical hosts?
what crazy setup is this please?
Processors average 90% idle, peaks are low, iowait is low, the system is
not swapping, response time is good, and our users are happy all around
the country. What is crazy about that?
The whole world can work with
On 1/25/2011 10:45, Robinson, Eric wrote:
There is a very good reason: it is the phenomenon of row drift. The
master and slave can appear to be in good sync, but often it is not
actually the case.
... sounds interesting; have you got any document explaining
this phenomenon? AFAIK, the things
On 1/25/2011 09:00, Robinson, Eric wrote:
...
I'm starting to worry that you may be right. I know FLUSH TABLES WITH
READ LOCK does not work as expected with InnoDB, but is there really no
way to put InnoDB into a state where all changes have been flushed to
disk and it is safe to rsync
On 1/25/2011 10:45, Robinson, Eric wrote:
There is a very good reason: it is the phenomenon of row
drift. The
master and slave can appear to be in good sync, but often
it is not
actually the case.
... sounds interesting; have you got any document explaining this
phenomenon?
I'm starting to worry that you may be right. I know FLUSH
TABLES WITH
READ LOCK does not work as expected with InnoDB, but is
there really
no way to put InnoDB into a state where all changes have
been flushed
to disk and it is safe to rsync the directory? Is stopping
the service
You need to quiesce the InnoDb background threads. One technique is
mentioned here:
http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp
aces.html
Look for the section talking about clean backups.
Now we're talkin. I'll check it out.
I read that section
Hi everybody!
Shawn Green (MySQL) wrote:
On 1/21/2011 14:21, Kendall Gifford wrote:
Hello everyone, I've got a database on an old Fedora Core 4 server
running
MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question
has
just two (InnoDB) tables:
messages (approx 2.5 million
). The database in question
has
just two (InnoDB) tables:
messages (approx 2.5 million records)
recipients (approx 6.5 million records)
[[ ... see the original post for the schema details ... ]]
I have the following query that is just too slow:
SELECT messages.* FROM messages
Fedora Core 4 server
running
MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question
has
just two (InnoDB) tables:
messages (approx 2.5 million records)
recipients (approx 6.5 million records)
[[ ... see the original post for the schema details ... ]]
I have
server doing simple inner join of two InnoDB
tables
On Mon, Jan 24, 2011 at 2:20 PM, Kendall Gifford zettab...@gmail.comwrote:
On Mon, Jan 24, 2011 at 3:40 AM, Joerg Bruehe joerg.bru...@oracle.comwrote:
Hi everybody!
Shawn Green (MySQL) wrote:
On 1/21/2011 14:21, Kendall Gifford wrote
Is there a way to safely backup an InnoDB database using rsync?
Right now we have a very efficient and reliable way to backup 240+
separate instances of MySQL with MyISAM tables. The databases range in
size from .5GB to 16GB. During this time, users can still access the
system, so our customers
I suspect the same trick might work with InnoDB (with pretty much the same
caveats), but you'd be best off setting innodb-file-per-table - I'm sure
you've already seen that the large datafiles are a hindrance to smooth
rsyncing :-)
Make sure to test extensively, though.
On Tue, Jan 25, 2011
Hello everyone, I've got a database on an old Fedora Core 4 server running
MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has
just two (InnoDB) tables:
messages (approx 2.5 million records)
recipients (approx 6.5 million records)
These track information about email
running
MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has
just two (InnoDB) tables:
messages (approx 2.5 million records)
recipients (approx 6.5 million records)
These track information about email messages. Each message has many
recipient records. The structure
On 1/21/2011 14:21, Kendall Gifford wrote:
Hello everyone, I've got a database on an old Fedora Core 4 server running
MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question has
just two (InnoDB) tables:
messages (approx 2.5 million records)
recipients (approx 6.5 million records
(InnoDB) tables:
messages (approx 2.5 million records)
recipients (approx 6.5 million records)
These track information about email messages. Each message has many
recipient records. The structure of the two tables (omitting irrelevant
data
fields) are as follows
Folks :
two questions:
1. can Innodb create per database table space , not per table table space?
2. can we store table on specific tablespace like Oracle or DB2 when creating
table?
Many thanks.
reply inline
On Mon, Jan 17, 2011 at 9:30 PM, Angela liu yyll2...@yahoo.com wrote:
Folks :
two questions:
1. can Innodb create per database table space , not per table table space?
No. The only available options are creating a global tablespace which
can be many files or a file per table
Thanks first,
I checked MySQL 5.1 manual, looks like , 'create tablespace' is ok only with
NDB and NDBCLUSTER, NOT INNODB
And I did not find 'create tablespace' in MySQL 5.5 manual:(
looks like MySQL5.5 does not offer 'create tablespace' anymore.
--- On Mon, 1/17/11, Eric Bergen eric.ber
Pito,
can u show us the innodb parameters in the my.cnf file.
regards
anandkl
On Sat, Jan 8, 2011 at 10:31 PM, Pito Salas r...@salas.com wrote:
I am very new to trying to solve a problem like this and have searched
and searched the web for a useful troubleshooting guide but I am
honestly
mysqld started
110107 15:07:15 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
110107 15:07:15 InnoDB: Starting log
I messed up the ibdata1,ib_logfile1,0 files and encounter the problems of
openning innodb tables even after I copied the back-up files of few months ago.
I don't have many innodb tables and so this not causing too much trouble.
I don't know what to do for restoring those tables, and not even
Dont you have mysql dump file for those table? It is best way to restore
InnoDB tables.
2011/1/7 Elim PDT e...@pdtnetworks.net
I messed up the ibdata1,ib_logfile1,0 files and encounter the problems of
openning innodb tables even after I copied the back-up files of few months
ago. I don't
is mysql 4.1.22
and the target is 5.0.77.
I did a mysqldump (--create-options --default-character-set=utf8 -K -e
--opt -q) on the source server and imported that dump on the new system,
but all innodb tabels are converted to myisam.
In the last time I focused on other topics than mysql, so may
I messed up the ibdata1,ib_logfile1,0 files and encounter the problems of
openning innodb tables even after I copied the back-up files of few months ago.
I don't have many innodb tables and so this not causing too much trouble.
I don't know what to do for restoring those tables, and not even
Good day all
We have downloaded the following MySQL version which untarred to
provide a list of RPM's:
MySQL-server-advanced-gpl-5.1.50-1.rhel5.x86_64.tar
The installations went through fine, however while doing some
configurations I found that Innodb is not enabled / installed
Once you get innodb corruption like this, you generally have to try to dump all
your data, shutdown mysql, wipe out all innodb tables and files, then restart
mysql reimport:
It gives the link http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html,
to help explain how to start innodb
Hello,
I have a database with tables in innodb and from today database can't be start.
I don't know what to do, if your can help, this my mysql log error :
Oct 31 13:18:16 myserver mysqld[13681]: 101031 13:18:16 [Note]
/usr/sbin/mysqld: Normal shutdown
Oct 31 13:18:16 myserver mysqld[13681
On Mon, Oct 25, 2010 at 6:25 AM, mos mo...@fastmail.fm wrote:
At 06:12 AM 10/24/2010, you wrote:
Regardless of that, it would be nice to know what the parameters are that
cause this slowdown - some people may be stuck with the default version -
companies with a support contract come to mind.
,
Try percona server. It gives better performance than mysql.
Krishna
On Sat, Oct 23, 2010 at 3:37 AM, Willy Mularto sangpr...@gmail.com
wrote:
Dear List,
I have MySQL 5.14 installed on Dell R710 32GB RAM 600GB SAS HDD with
Ubuntu
10.04 64 Bit. I deploy InnoDB as my default engine
with 150 rows. If you drop all indexes to the table you'll probably find
adding rows to a large table will be quite fast. The only way to increase
performance is to maintain only the minimum # of indexes necessary.
If the problem is #2, you could try and optimize the Innodb table with an
Optimize
Hi Willy,
Try percona server. It gives better performance than mysql.
Krishna
On Sat, Oct 23, 2010 at 3:37 AM, Willy Mularto sangpr...@gmail.com wrote:
Dear List,
I have MySQL 5.14 installed on Dell R710 32GB RAM 600GB SAS HDD with Ubuntu
10.04 64 Bit. I deploy InnoDB as my default engine
Dear List,
I have MySQL 5.14 installed on Dell R710 32GB RAM 600GB SAS HDD with Ubuntu
10.04 64 Bit. I deploy InnoDB as my default engine. The server is a high load
server. On a fresh install and empty table it can insert around 5 millions new
records per day average. But when the table getting
Based on my reply below, do you recommend I continue to have these indexes ?
-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Wed, Oct 13, 2010 at 8:22 PM
Subject: Re: Primary key not unique on InnoDB table
To: Travis Ard travis_...@hotmail.com
Cc
not unique on InnoDB table
Based on my reply below, do you recommend I continue to have these indexes ?
-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Wed, Oct 13, 2010 at 8:22 PM
Subject: Re: Primary key not unique on InnoDB table
To: Travis Ard
Would really appreciate some help or suggestions on this please, if anyone
can assist ?
Regards
Neil
-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Tue, Oct 12, 2010 at 5:45 PM
Subject: Backing up the InnoDB tables
To: [MySQL] mysql@lists.mysql.com
...@googlemail.com
Date: Tue, Oct 12, 2010 at 5:45 PM
Subject: Backing up the InnoDB tables
To: [MySQL] mysql@lists.mysql.com
Hi
On a shared MySQL server with access just to my own database, what is the
recommend backup methods and strategies for the InnoDB tables ?
Cheers
Neil
--
Thanks
Suresh Kuna
really appreciate some help or suggestions on this please, if anyone
can assist ?
Regards
Neil
-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Tue, Oct 12, 2010 at 5:45 PM
Subject: Backing up the InnoDB tables
To: [MySQL] mysql@lists.mysql.com
Quoting Tompkins Neil neil.tompk...@googlemail.com:
The problem is I don't have any command line access, just direct MySQL
access to the database tables.
I dont know xtra backup, but if thats not an option you can just use
mysqldump. This can be run from a remote server to your DB server,
The problem is I don't have any command line access, just direct MySQL
access to the database tables.
whats wrong with mysqldump?
--
bEsT rEgArDs| Confidence is what you have before you
tomasz dereszynski | understand the problem. -- Woody Allen
NO ACTION ON UPDATE NO
ACTION,
CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci
I'm confused, I thought primary keys
NO
ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci
I'm confused, I thought primary keys were always unique ?
Cheers
Neil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
`positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
COLLATE
KEY (`default_teams_id`)
REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci
I'm confused, I thought primary keys were always unique ?
Cheers
Neil
`FK_players_master_teams_id` FOREIGN KEY
(`default_teams_id`)
REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci
I'm confused, I thought primary keys were always unique ?
Cheers
Neil
,
CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY
(`default_teams_id`)
REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci
I'm confused, I thought primary keys were always
(`default_teams_id`)
REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE
NO
ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci
I'm confused, I thought primary keys were always unique ?
Cheers
Neil
--
MySQL
`FK_players_master_positions_id` FOREIGN KEY (`positions_id`)
REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION
) ENGINE=InnoDB
KEY (`positions_id`)
REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT
On 10/13/2010 11:37 AM, Tompkins Neil wrote:
Shawn, sorry my error, I didn't realise I had two fields as the primary key
That's misinformation. You can have multiple fields as a primary key.
Show us what you think is duplicate data and I may be able to help you
fix your definition
--
Shawn it is fine. I thought my primary key was just 1 field.
On Wed, Oct 13, 2010 at 4:44 PM, Shawn Green (MySQL)
shawn.l.gr...@oracle.com wrote:
On 10/13/2010 11:37 AM, Tompkins Neil wrote:
Shawn, sorry my error, I didn't realise I had two fields as the primary
key
That's
to
your storage requirements.
-Travis
-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Wednesday, October 13, 2010 8:37 AM
To: [MySQL]
Subject: Primary key not unique on InnoDB table
I've the following table. But why isn't the primary key unique, e.g
requirements.
-Travis
-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Wednesday, October 13, 2010 8:37 AM
To: [MySQL]
Subject: Primary key not unique on InnoDB table
I've the following table. But why isn't the primary key unique, e.g.
preventing
Hi List,
Last night accidentally one of my InnoDB table crash. And cause client can not
connect to MySQL, it always said cannot connect to socket, even the daemon is
launched. I tried to set innodb_force_recovery from 0 to 6 and only number 3
bring back the connection. After that I dump
Hey Willy - Install the new binaries and start mysql with new binary as
basedir and see whether innodb has enabled or not. Check the error log why
the innodb is getting disabled, make a copy of it here too.
On Tue, Oct 12, 2010 at 2:57 PM, Willy Mularto sangpr...@gmail.com wrote:
Hi List,
Last
Hi
On a shared MySQL server with access just to my own database, what is the
recommend backup methods and strategies for the InnoDB tables ?
Cheers
Neil
mysql with new binary as
basedir and see whether innodb has enabled or not. Check the error log why
the innodb is getting disabled, make a copy of it here too.
On Tue, Oct 12, 2010 at 2:57 PM, Willy Mularto sangpr...@gmail.com wrote:
Hi List,
Last night accidentally one of my InnoDB table crash
I'm interested in InnoDB backups. Does anyone use PHPMyAdmin ? I've a
MySQL server on a shared hosting server.
Cheers
Neil
On Sat, Oct 9, 2010 at 3:21 AM, short.cut...@yahoo.com.cn wrote:
Hello,
Is there any good document for backup of InnoDB?
includes the increment backup and full backup
Hi,
On Sat, Oct 9, 2010 at 4:21 AM, short.cut...@yahoo.com.cn wrote:
Hello,
Is there any good document for backup of InnoDB?
includes the increment backup and full backup.
There is an overview of backups here
http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html
XtraBackup supports
Hello,
Is there any good document for backup of InnoDB?
includes the increment backup and full backup.
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
as they happen.
From: Hank [mailto:hes...@gmail.com]
Primarily due to many positive posts I've seen about MySQL 5.5 and
advances in InnoDB, I'm seriously considering converting all my MyISAM
databases to InnoDB. I don't need many of the InnoDB features, but if
I'm going to upgrade from 4.1.14 to 5.5, I
information
like myisamchk output. That would be an awesome feature to add to 5.5.
Expect to see anywhere from a 1.5x to a 3x increase in size when converting
from myisam to innodb, depending on your field types and indexes. It's the
penalty you pay for supporting transactions and concurrent read
You have to round the size of the last data file (ibdata4) and add the
new ones.
You can find more information on the manual:
http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html
http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html
Carlos
On 9/28/2010 12:59 AM, Vokern
--
From: Carlos Proal carlos.pr...@gmail.com
You have to round the size of the last data file (ibdata4) and add the
new ones.
You can find more information on the manual:
http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html
http
2010/9/28 Jangita jang...@jangita.com:
I do not think there is anything wrong with having one huge file is there?
We have one innodb file of 85GB on ext3.
Is there?
but how about the problem on the file has been increasing continuously?
thanks
--
MySQL General Mailing List
For list
On Tue, Sep 28, 2010 at 10:23 AM, Vokern vok...@gmail.com wrote:
2010/9/28 Jangita jang...@jangita.com:
I do not think there is anything wrong with having one huge file is
there?
We have one innodb file of 85GB on ext3.
In and of itself, there is no problem with that. You may, however
Hi Vokern,
I suggest to have a single ibdata1 file and use *innodb_file_per_table* to
have multiple .ibd tables.
_Krishna
On Tue, Sep 28, 2010 at 11:29 AM, Vokern vok...@gmail.com wrote:
Hello,
Currently I have the setting:
That's a very good point, actually, as that will also immediately free the
space from tables you delete.
My instincts say that it's marginally slower, though; although honestly I
don't have any data to support that. Does anyone have benchmarks about that
?
On Tue, Sep 28, 2010 at 1:26 PM,
2010/9/28 Krishna Chandra Prajapati prajapat...@gmail.com:
Hi Vokern,
I suggest to have a single ibdata1 file and use innodb_file_per_table to
have multiple .ibd tables.
Can I upgrade to innodb_file_per_table smoothly?
thanks.
--
MySQL General Mailing List
For list archives:
Hi Vokern,
On a running MySQL Server enabling *innodb_file_per_table* makes no changes
to the existing tables. The newly created table (innodb) will be affected
and have thier own .ibd and .frm tables.
Although, you can enable smoothly. But it's better to have it from scratch.
So, that you can
On Tue, Sep 28, 2010 at 1:46 PM, Vokern vok...@gmail.com wrote:
Can I upgrade to innodb_file_per_table smoothly?
When you activate it, the db will keep reading and using your existing
innodb datafiles. All new tables will be created using .ibd files.
Converting your existing tables is done
From: Jangita jang...@jangita.com
I do not think there is anything wrong with having one huge file is there?
There is if you're doing incremental back-ups, in which case adding one byte to
that file costs you 50GB of backup space.
You don't have to take insults personally.
Primarily due to many positive posts I've seen about MySQL 5.5 and
advances in InnoDB, I'm seriously considering converting all my MyISAM
databases to InnoDB. I don't need many of the InnoDB features, but
if I'm going to upgrade from 4.1.14 to 5.5, I might as well bit the
bullet since that seems
1. Generally reducing fragmentation in the data/index files will reduce the
footprint of tables on disk, and can be more efficient to query. With innodb
you need to be using the innodb-file-per-table option, and then you can use
OPTIMIZE TABLE table; to rebuild it. You don't get detailed
Also note, 5.5 isn't production ready. 5.1 is the current GA release.
-Original Message-
From: Hank [mailto:hes...@gmail.com]
Sent: Tuesday, September 28, 2010 3:29 PM
To: mysql@lists.mysql.com
Subject: Migrating my mindset from MyISAM to InnoDB
Primarily due to many positive posts
restoring the datafile.
That being said, I don't know wether InnoDB files don't get updated metadata
even if no DML happens in them.
You don't have to take insults personally. You can sidestep negative
energy; you can look for the good in others and utilize that good
Hello,
Currently I have the setting:
innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend
Because the last file of ibdata4 is very large (more than 50G), if I
want extend the data to more files, for example, ibdata5, ibdata6...
how to do it?
Thanks!
--
MySQL
Hi,
I got this result on InnoDB Buffer Pool Status:
Free pages 1
Dirty pages 2,040
Pages containing data 31,359
Pages to be flushed 457,083,205
Busy pages 1,408
Read requests 31,348,288,497
Write requests 7,913,407,934
Read misses 39,736,110
Write waits 0
Read
Willy Mularto wrote:
Hi,
I got this result on InnoDB Buffer Pool Status:
Free pages1
Dirty pages 2,040
Pages containing data 31,359
Pages to be flushed 457,083,205
Busy pages1,408
Read requests 31,348,288,497
Write requests7,913,407,934
Read misses
On Tue, Sep 21, 2010 at 12:49 PM, Willy Mularto sangpr...@gmail.com wrote:
Hi,
I got this result on InnoDB Buffer Pool Status:
Free pages 1
Dirty pages 2,040
Pages containing data 31,359
Pages to be flushed 457,083,205
Busy pages 1,408
Read requests 31,348,288,497
!) be converted to a disk table.
On Thu, Sep 2, 2010 at 3:58 AM, Daevid Vincent dae...@daevid.com wrote:
InnoDB is one of MANY engines in the RDBMS mySQL.
There IS in fact a few ways to store in temporary tables (both RAM and DISK
based)
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
tablespace file if I don't use
innodb_file_per_table?
On Thu, Sep 2, 2010 at 3:58 AM, Daevid Vincent dae...@daevid.com wrote:
InnoDB is one of MANY engines in the RDBMS mySQL.
There IS in fact a few ways to store in temporary tables (both RAM and
DISK
based)
http://dev.mysql.com/doc/refman/5.1
On 9/2/2010 1:39 PM, neutron wrote:
Hello Johan,
Thanks for the reply.
On Thu, Sep 2, 2010 at 3:34 AM, Johan De Meersman vegiv...@tuxera.be wrote:
I suspect he is talking about the Temp Tablespace concept from Oracle, which
is different from a temporary table or a memory table.
MySQL will
Hi all,
As far as I know, some DB has a separate table space to store temp
data (such as for external sort).
My questions are:
1. Does innodb also has a separate temp-tablespace?
2. If I don't use innodb_file_per_table, where is innodb's
temporary tablespace？ Is it in the shared tablespace
InnoDB is one of MANY engines in the RDBMS mySQL.
There IS in fact a few ways to store in temporary tables (both RAM and DISK
based)
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
Look at:
TABLESPACE
PARTITIONS
ENGINE
-Original Message-
From: neutron [mailto:neutronsh
Most alter table operations in 5.0 will rebuild the entire table. The
best thing to increase for alter table speed in innodb is the buffer
pool. For more details on how innodb handles alter table see
http://ebergen.net/wordpress/2007/05/07/how-alter-table-locks-tables-and-handles-transactions
I have been trying to speed up an ALTER TABLE command that adds a
column to a large InnoDB table of about 80M rows.
I have found and tried many different methods, but they are all slow.I
have tried both optimizing the ALTER TABLE
command, and dumping and loading the table (in both SQL and CSV
At 10:34 AM 8/18/2010, Xn Nooby wrote:
minutes to dump the 70M rows. However, it takes the LOAD FILE command
13 hours to import the CSV file. My
understanding of LOAD FILE was that it was already optimized to load
the data, then build the indices afterwords. I
don't understand why it takes so
Nooby [mailto:xno...@gmail.com]
Sent: Wednesday, August 18, 2010 9:34 AM
To: mysql@lists.mysql.com
Subject: Slow ALTER TABLE on 70M row InnoDB table
I have been trying to speed up an ALTER TABLE command that adds a
column to a large InnoDB table of about 80M rows.
I have found and tried many
Below is a generic version of the code I am trying. It does copy the
rows very quickly, but I will have to test to see how quickly the
indices are built. Is the below code what you were suggesting? I had
a little trouble dropping and later adding the primary index, but I
think I got it figured
, and rename
the_table_clone to the_table
USE the_database;
DROP TABLE IF EXISTS the_table_clone;
CREATE TABLE the_table_clone LIKE the_table;
Or you can try something like:
create table the_table_clone engine=innodb select * from the_table limit=0;
This will create the same table structure
the_table_clone engine=innodb select * from the_table limit=0;
This will create the same table structure but not the indexes so you don't
have to drop the indexes below.
That is good to know. I did not mind dropping the indices in this
case, because the table was still empty.
# drop minor
It appears the ALTER TABLE starts off quick, and then slows down. I
feel like the indices are larger than allocated RAM, and the system is
slowing down because it is busy swapping out to disk. Is there an
InnoDB specific buffer than can help this? The sort_buffer_size
apparently is only
the_database;
DROP TABLE IF EXISTS the_table_clone;
CREATE TABLE the_table_clone LIKE the_table;
Or you can try something like:
create table the_table_clone engine=innodb select * from the_table limit=0;
This will create the same table structure but not the indexes so you don't
have
301 - 400 of 5209 matches
Mail list logo
