List:General Discussion« Previous MessageNext Message »
From:Jon Hancock Date:October 28 2003 3:05am
Subject:Re: Partial replicate InnoDB -> MyISAM
View as plain text  
see below...

----- Original Message ----- 
From: "Chris Nolan" <chris@stripped>
To: "Jon Hancock" <jhancock@stripped>; <mysql@stripped>
Sent: Tuesday, October 28, 2003 10:49 AM
Subject: Re: Partial replicate InnoDB -> MyISAM


> Hmm....
>
> How much lag time can you afford between the master's contents being
updated
> and the slave being updated? Taking advantage of MyISAM's compressed table
> features might help performance if that is an issue.

I need to put copy things into MyISAM from InnoDB anyway in order to use
FULLTEXT.  Right now, we are doing double inserts into InnoDB and then an
insert into MyISAM (only the searchable fields).  O would like to solve this
at the DB level.
Can I use FULLTEXT on compressed tables?  Should there be any speed
degragation other than the normal compress overhead?

>
> Every independant test out there shows that in the cases where InnoDB is
> slower for reads than MyISAM, the speed differential is quite small. In
those
> cases, the differences come from InnoDB's higher disk space and RAM usage
> seemingly.
>
> Just some of my thoughts.
>
> Regards,
>
> Chris
>
> On Tue, 28 Oct 2003 01:40 pm, Jon Hancock wrote:
> > This thread started as "Re: MySQL/InnoDB-4.0.16 is released + sneak peek
of
> > 4.1.1".
> >
> > I may want to have one MySQL server as the Read only "Search" server.
If I
> > did this and I have all InnoDB table on my Master, then could I
replicate
> > only certain columns into the MyISAM slave ?
> >
> > Any other efficient ideas on how to do this?
> >
> > thanks, Jon
> >
> > ----- Original Message -----
> > From: "Chris Nolan" <chris@stripped>
> > To: "Jon Hancock" <jhancock@stripped>
> > Cc: <mysql@stripped>
> > Sent: Monday, October 27, 2003 8:45 PM
> > Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
> >
> > > Hi,
> > >
> > > As I have said before, I'm not Heikki, but I'm such a massive geek I'm
> > > likely to have one or two useful bits of info for you. :-)
> > >
> > > 1. You'd have a rough time getting indexes and tables to be seperated
> > > out, unless you were willing to set up your various symlinks/hardlinks
> > > by hand. Even then, you may be inviting problems. Additionally, no
date
> > > has been announced for FULLTEXT indexing on InnoDB tables, and
> > > Heikki considers it a low priority by the looks of things (not having
a
> > > go at the god of multiversioned DBs, just making a possibly incorrect
> > > observation).
> > >
> > > 2. I personally use ReiserFS for all of my stuff, most of which is
based
> > > upon InnoDB. One thing you have to remember is that InnoDB
> > > treats the space inside the tablespace as a Berkeley Fast
> > > Filesystem-style space, using the underlaying filesystem minimally. To
> >
> > quote
> >
> > > the manuals, raw partition usage can speed up IO on a number of UNIXes
> > > (and Windows too seemingly). Regarding backup, you'd
> > > need to use mysqldump or InnoDB Hot Backup to backup a raw-partition
> > > setup. This isn't a bad thing though - I use mysqldump and
> > > can get a consistant snapshot of a 12 GB DB without problems while the
> > > thing is running.
> > >
> > > Hope this helps!
> > >
> > > Regards,
> > >
> > > Chris
> > >
> > > Jon Hancock wrote:
> > > >Heikki,
> > > >I have two questions in regards to the tablespace changes:
> > > >
> > > >1 - You mention being able to store indexes in a seperate tablespace.
> >
> > How
> >
> > > >far off is this for MySQL to implement?  I would like to see FULLTEXT
> > > >indexes stored in seperate tablspace (seperate RAID channel) so the
two
> > > >features (InnoDB FULLTEXT) would both need to be available.
> > > >2 - Is there any value to using Journaled file systems with the
InnoDB
> > > >tablespaces?  A new system I'm putting together will have seperate
> > > > drives for only InnoDB data.  Is a Journaled file system extra
> > > > overhead?  If so,
> >
> > is
> >
> > > >Raw significantly more efficient?  How does this choice effect backup
> > > >soultion?
> > > >
> > > >thanks, Jon
> > > >
> > > >>----- Original Message -----
> > > >>From: "Heikki Tuuri" <Heikki.Tuuri@stripped>
> > > >>To: <mysql@stripped>
> > > >>Sent: Friday, October 24, 2003 9:55 PM
> > > >>Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
> > > >>
> > > >>>Eduardo,
> > > >>>
> > > >>>to make the user interface simple, I decided to take the table
> per
> > > >>> file approach. Each .ibd file is internally a 'tablespace'.
> > > >>>
> > > >>>The simple approach I chose is similar to how MyISAM now works.
> I
> > > >
> > > >thought
> > > >
> > > >>it
> > > >>
> > > >>>would be nice for current MySQL users.
> > > >>>
> > > >>>In Oracle, one can store several tables into a single named
> > > >>> tablespace,
> > > >>
> > > >>and
> > > >>
> > > >>>can also split indexes and data of a single table to separate
> > > >
> > > >tablespaces.
> > > >
> > > >>>Nothing prevents adding those features to InnoDB, too. It just
> > > >>> requires
> > > >>
> > > >>new
> > > >>
> > > >>>syntax in CREATE TABLE to specify these options.
> > > >>>
> > > >>>Best regards,
> > > >>>
> > > >>>Heikki
> > > >>>Innobase Oy
> > > >>>http://www.innodb.com
> > > >>>InnoDB - transactions, row level locking, and foreign keys for
MySQL
> > > >>>InnoDB Hot Backup - hot backup tool for InnoDB which also backs
> up
> > > >
> > > >MyISAM
> > > >
> > > >>>tables
> > > >>>
> > > >>>..........................
> > > >>>From: "Eduardo D Piovesam" (eduardo@stripped)
> > > >>>Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of
> 4.1.1
> > > >>>
> > > >>>
> > > >>>View this article only
> > > >>>Newsgroups: mailing.database.myodbc
> > > >>>Date: 2003-10-23 14:43:28 PST
> > > >>>
> > > >>>(Sorry for the last email, it's not complete).
> > > >>>
> > > >>>Hello Heikki,
> > > >>>
> > > >>>Sorry, but I didn't understand the concept of tablespace
> applied.
It's
> > > >>>different from Oracle, right?
> > > >>>
> > > >>>AFAIK, tablespace is utilized to logically group "tables" into
> one
(or
> > > >>
> > > >>more)
> > > >>
> > > >>>files.
> > > >>>
> > > >>>And to group "indexes" into another files...
> > > >>>
> > > >>>But you said that the each table (with its indexes) will be in
> one
> > > >
> > > >file...
> > > >
> > > >>>is there an reason? Is it better than "split" tables and
> indexes?
> > > >>>
> > > >>>Thank you.
> > > >>>
> > > >>>Eduardo
> > > >>>
> > > >>>
> > > >>>--
> > > >>>MySQL General Mailing List
> > > >>>For list archives: http://lists.mysql.com/mysql
> > > >>>To unsubscribe:
> > > >>
> > > >>http://lists.mysql.com/mysql?unsub=1
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> >
> > http://lists.mysql.com/mysql?unsub=1
>
>

Thread
MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri22 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Chris Nolan22 Oct
  • 2 Query with same criteria giving different number of rowsHector Del Curto22 Oct
    • Re: 2 Query with same criteria giving different number of rowsChris Tucker22 Oct
    • Re: 2 Query with same criteria giving different number of rowsIllyes Laszlo23 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam23 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam23 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri24 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Martijn Tonies24 Oct
    • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Chris Nolan24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam24 Oct
    • MySQL/InnoDB-4.0.16 +Optimizer behaviourSergey S. Kostyliov29 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Martijn Tonies24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri27 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri26 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Martijn Tonies26 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Jon Hancock27 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Chris Nolan27 Oct
    • InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1)Gabriel Ricard27 Oct
  • Partial replicate InnoDB -> MyISAMJon Hancock28 Oct
    • Re: Partial replicate InnoDB -> MyISAMChris Nolan28 Oct
  • Re: Partial replicate InnoDB -> MyISAMJon Hancock28 Oct
Re: InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1)Heikki Tuuri27 Oct