List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 30 2012 4:21pm
Subject:RE: engine is now innoDB instead of MyISAM.
View as plain text  
Did the upgrade actually change the Engine on _existing_ tables?
I thought the setting in my.cnf only applied to _new_ tables that do _not_ have an engine
explicitly specified.

True, it is not totally safe to blindly change Engines; there can be performance
'surprises'.  This document outlines many of them:
   http://mysql.rjweb.org/doc.php/myisam2innodb

In the long run there are advantages in using InnoDB instead of MyISAM.  I suggest you put
the change on your roadmap.

The new default came with MySQL 5.5 (IIRC); not with Enterprise specifically.



> -----Original Message-----
> From: Claudio Nanni [mailto:claudio.nanni@stripped]
> Sent: Sunday, April 29, 2012 6:31 AM
> To: Brown, Charles
> Cc: mysql@stripped
> Subject: RE: engine is now innoDB instead of MyISAM.
> 
> Charles,
> 
> I think going back to MyISAM as default is a good idea.
> All you have to do is:
> Export all databases
> .Shutdown the cluster MySQL
> .Add one parameter to your old my.cnf , default-storage-engine=myisam
> .add cluster specific parameters to your my.cnf , they should be a
> couple .Put back your my.cnf on all SQL nodes .restart the cluster
> .reimport your dump , which should drop all Innodb tables
> 
> I'm on a bus now I can't check better ;)
> 
> Cheers
> 
> Claudio
> On Apr 29, 2012 2:50 PM, "Brown, Charles" <CBrown@stripped> wrote:
> 
> > Hello Claudio,****
> >
> > ** **
> >
> > Thanks for quick response and the whitepaper. What precipitated my
> > research and questions is a complain I received from one of my
> > developer that after following our MySQL Cluster migration, tables
> are
> > now defaulting  to innodb ( how can he tell) and performance is poor.
> > I'm very much concerned worried because my cnf doesn't  reflect any
> > parameter changes or tuning opportunities for innodb -  I'm using all
> > defaults since we are not using innodb storage engine. Our MySQL
> > implementation is very simple and limited. Ours model most large IT
> > shops meaning it provides nothing but a nice, fancy glossary front
> end
> > ( meaning placed outside the cooperate fire wall). Inside this
> > cooperate firewall resides myriads of industry strength databases and
> > data structures.  The white paper warns not to expect much from MySQL
> > Cluster until it's optimized.  Very little was said about Myisam.
> Most
> > of the discussions were centered on innordb and NDB. Thus my
> objective
> > is clearly laid out. I may have to convert all tables back to MyISAM
> > or invest time in tuning. Your thoughts ?****
> >
> > ** **
> >
> > Lastly, can I bring over old performance parameters found in my.cnf.
> > These are parameters that have sustain me over the years. Now, I'm
> > doing MySql Cluster  are they any more relevant.  ****
> >
> > ** **
> >
> > Best regards,  ****
> >
> > ** **
> >
> > ** **
> >
> > ** **
> >
> > *From:* Claudio Nanni [mailto:claudio.nanni@stripped]
> > *Sent:* Sunday, April 29, 2012 4:29 AM
> > *To:* Brown, Charles
> > *Cc:* mysql@stripped
> > *Subject:* Re: engine is now innoDB instead of MyISAM.****
> >
> > ** **
> >
> > Charles,****
> >
> > ** **
> >
> > the pleasure is all mine in meeting such a kind person as you
> are.****
> >
> > ** **
> >
> > To reply to your question, I would recommend to read frst a basic
> > document about MySQL Cluster,****
> >
> > like the whitepaper (http://kae.li/iiisv) so that you have an
> overview
> > of the infrastructure.****
> >
> > ** **
> >
> > Consider that only the SQL node(that is just a regular MySQL
> instance)
> > and the Management console have a client command to access the node
> > and the command is different, the regular 'mysql' client command is
> > used to access the SQL node (which as said is just a regular MySQL
> > instance with added NDB support), and to access the management
> console
> > the command is 'ndb_mgm'.**
> > **
> >
> > ** **
> >
> > You cannot access the Data nodes with a client command, those nodes
> > communicate with other cluster nodes but not with the user.****
> >
> > ** **
> >
> > Please feel free to ask any further question.****
> >
> > ** **
> >
> > Thanks****
> >
> > ** **
> >
> > Claudio****
> >
> > ** **
> >
> > 2012/4/29 Brown, Charles <CBrown@stripped>****
> >
> > Hello Claudio, ****
> >
> >  ****
> >
> > In all honesty, the chances of coming into contact with  an
> individual
> > like you who has a grounded knowledge and an ability to articulate
> > thoughts is perhaps a once in a life time experience. However, I
> meant
> > "MySQL Cluster as a product" . How can one identify these three
> > different types of nodes. Are there any useful diagnostics command
> > that I can issue in order to identify which node is Data, Management
> or SQL node. Please assist me.
> > See below for the logon messages****
> >
> >  ****
> >
> > Welcome to the MySQL monitor.  Commands end with ; or \g.****
> >
> > Your MySQL connection id is 374141****
> >
> > Server version: 5.5.19-ndb-7.2.4-gpl MySQL Cluster Community Server
> > (GPL)*
> > ***
> >
> >  ****
> >
> > Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights
> > reserved.****
> >
> >  ****
> >
> > Oracle is a registered trademark of Oracle Corporation and/or its****
> >
> > affiliates. Other names may be trademarks of their respective****
> >
> > owners.****
> >
> >  ****
> >
> > Type 'help;' or '\h' for help. Type '\c' to clear the current input
> > statement.****
> >
> >  ****
> >
> > mysql>****
> >
> >  ****
> >
> >  ****
> >
> >  ****
> >
> >  ****
> >
> > *From:* Claudio Nanni [mailto:claudio.nanni@stripped]
> > *Sent:* Saturday, April 28, 2012 2:48 PM
> > *To:* Brown, Charles
> > *Cc:* mysql@stripped****
> >
> >
> > *Subject:* Re: engine is now innoDB instead of MyISAM.****
> >
> >  ****
> >
> > Charles,****
> >
> >  ****
> >
> > first, my opinion is that if you are paying Oracle you should get
> > answers from them about this, and then of course is good to have a
> > second opinion
> > here.****
> >
> >  ****
> >
> > Related to your specific question you should specify what you mean
> > with 'clustering' which is a generic term,****
> >
> > if with it you mean "MySQL Cluster" product then you have to know
> that
> > this implementation of database cluster is based on a specific
> storage
> > engine called Network DataBase (NDB), so you can't choose another
> > engine for it.****
> >
> > The default storage engine in the SQL nodes of a MySQL Cluster can be
> > any storage engine and whether it be MyISAM, InnoDB, NDB and it's a
> > design decision, but only NDB tables will benefit from the clustering
> capabilities.
> > ****
> >
> >  ****
> >
> > I hope I got your question right and that this shed a bit more light
> > for
> > you.****
> >
> >  ****
> >
> > Cheers****
> >
> >  ****
> >
> > Claudio****
> >
> >  ****
> >
> > 2012/4/28 Brown, Charles <CBrown@stripped>****
> >
> > The question is, is it advisable to change the default engine of a
> > cluster setting to Myisam? Does anyone have an opinion or experience
> on this issue?
> > Do I have choice when it comes to clustering on which type engine
> > supports clustering.
> > Thanks****
> >
> >
> >
> >
> > -----Original Message-----
> > From: Reindl Harald [mailto:h.reindl@stripped]
> > Sent: Saturday, April 28, 2012 3:01 AM
> > To: mysql@stripped
> > Subject: Re: engine is now innoDB instead of MyISAM.
> >
> >
> >
> > Am 28.04.2012 09:00, schrieb Brown:
> > > We recently switched from MySQL community to Mysql clustered using
> > Oracle supported MySQl. The problem is, during our testing phase, we
> > observed the default engine is now innoDB instead of MyISAM. Any
> > thoughts on that? We're not getting the performance that we expected
> -
> > any thoughts or advices out there will be greatly appreciated.
> >
> > my.cnf: default-storage-engine = myisam
> >
> > you cann not compare MyISAm and InnoDB for the same workloads that's
> > why my.cnf exists and we usually define each known setting to prevent
> > chnages in the behavior of applications by software-updates
> >
> > the same for "php.ini"****
> >
> > ********************************************
> > This message is intended only for the use of the Addressee and may
> > contain information that is PRIVILEGED and CONFIDENTIAL.
> >
> > If you are not the intended recipient, you are hereby notified that
> > any dissemination of this communication is strictly prohibited.
> >
> > If you have received this communication in error, please erase all
> > copies of the message and its attachments and notify us immediately.
> >
> > Thank you.
> > ********************************************
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql****
> >
> >
> >
> > ****
> >
> >  ****
> >
> > --
> > Claudio****
> >
> > ********************************************
> > This message is intended only for the use of the Addressee and may
> > contain information that is PRIVILEGED and CONFIDENTIAL.****
> >
> > If you are not the intended recipient, you are hereby notified that
> > any dissemination of this communication is strictly prohibited.****
> >
> > If you have received this communication in error, please erase all
> > copies of the message and its attachments and notify us
> > immediately.****
> >
> > Thank you.
> > ************************************************
> >
> >
> >
> > ****
> >
> > ** **
> >
> > --
> > Claudio****
> >
> > ********************************************
> > This message is intended only for the use of the Addressee and may
> > contain information that is PRIVILEGED and CONFIDENTIAL.
> >
> > If you are not the intended recipient, you are hereby notified that
> > any dissemination of this communication is strictly prohibited.
> >
> > If you have received this communication in error, please erase all
> > copies of the message and its attachments and notify us immediately.
> >
> > Thank you.
> > ********************************************
> >
Thread
RFE: Allow to use version-specific my.cnf filesHonza Horak25 Apr
  • Re: RFE: Allow to use version-specific my.cnf filesAndrĂ©s Tello25 Apr
    • Re: RFE: Allow to use version-specific my.cnf filesHonza Horak27 Apr
      • Re: RFE: Allow to use version-specific my.cnf filesShawn Green27 Apr
        • engine is now innoDB instead of MyISAM. Charles Brown28 Apr
          • Re: engine is now innoDB instead of MyISAM.Reindl Harald28 Apr
            • RE: engine is now innoDB instead of MyISAM.Charles Brown28 Apr
              • Re: engine is now innoDB instead of MyISAM.Claudio Nanni28 Apr
        • Re: RFE: Allow to use version-specific my.cnf filesHonza Horak2 May
    • RE: RFE: Allow to use version-specific my.cnf filesRick James2 May
      • Can the mysql replication limited to one database Charles Brown2 May
        • Re: Can the mysql replication limited to one databasea.smith2 May
          • RE: Can the mysql replication limited to one databaseRick James2 May
            • using the bin-log approach on the master side, how can I accomplishmy replication objectivesCharles Brown3 May
              • Re: using the bin-log approach on the master side, how can I accomplish my replication objectivesNitin Mehta3 May
        • RE: Can the mysql replication limited to one database Rick James2 May
          • Re: Can the mysql replication limited to one databaseClaudio Nanni2 May
            • Re: Can the mysql replication limited to one databaseSinger X.J. Wang2 May
            • RE: Can the mysql replication limited to one databaseRick James2 May
          • RE: Can the mysql replication limited to one database Charles Brown2 May
            • RE: Can the mysql replication limited to one database Rick James2 May
Re: engine is now innoDB instead of MyISAM.Claudio Nanni29 Apr
  • RE: engine is now innoDB instead of MyISAM.Charles Brown29 Apr
    • Re: engine is now innoDB instead of MyISAM.Reindl Harald29 Apr
      • Re: engine is now innoDB instead of MyISAM.Charles Brown29 Apr
        • Re: engine is now innoDB instead of MyISAM.Michael Dykman30 Apr
    • RE: engine is now innoDB instead of MyISAM.Claudio Nanni29 Apr
      • RE: engine is now innoDB instead of MyISAM.Rick James30 Apr
Re: using the bin-log approach on the master side, how can I accomplish my replication objectivesNitin Mehta3 May
  • RE: using the bin-log approach on the master side, how can Iaccomplish my replication objectivesCharles Brown3 May
    • Re: using the bin-log approach on the master side, how can I accomplish my replication objectivesNitin Mehta3 May
  • master BIN-LOG maintenaceCharles Brown3 May
    • Re: master BIN-LOG maintenaceNitin Mehta3 May
      • RE: master BIN-LOG maintenaceRick James3 May
  • RE: using the bin-log approach on the master side, how can Iaccomplish my replication objectivesRick James3 May