backup of databases which have a mix of MyISAM- and InnoDB-tables
Hi, i've been already reading the documentation the whole day, but still confused and unsure what to do. We have two databases which are important for our work. So both are stored hourly. Now I recognized that each database has a mixture of MyISAM- and InnoDB-tables. A backup of this mix does not seem to be easy. Until now it was dumped using mysqldump --opt -u root --databases mausdb What I understand until now is that --opt is not necessary because it is default. It includes, among others, --lock-tables which is senseful for saving MyISAM-tables. For InnoDB-tables --single-transaction is useful. But both are mutually exclusive (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). The dump of both take about 10 seconds. If the db is locked for that period I can live with. When I use --single-transaction only the InnoDB-tables are consistent. Using --lock-tables the MyISAM-tables are stored consistently. What is about --lock-tables in conjunction with InnoDB-tables ? Are they stored consistently ? Are they locked during the dumping ? As I said, I could live with a small lock period ( 30 sec). Would --lock-all-tables be better ? Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables (from the manpage). I can live with a global read lock for the duration of the whole dump. --lock-tables causes any pending transactions to be committed implicitly (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). Is that a problem for the InnoDB tables ? Our system is: mysql-5.0.26-12.29.1 on a SLES 10 SP4 64 bit host. Bernd -- Bernd Lentes Systemadministration Institut für Entwicklungsgenetik Gebäude 35.34 - Raum 208 HelmholtzZentrum münchen bernd.len...@helmholtz-muenchen.de phone: +49 89 3187 1241 fax: +49 89 3187 2294 http://www.helmholtz-muenchen.de/idg Die Freiheit wird nicht durch weniger Freiheit verteidigt Helmholtz Zentrum München Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH) Ingolstädter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen Registergericht: Amtsgericht München HRB 6466 USt-IdNr: DE 129521671 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: backup of databases which have a mix of MyISAM- and InnoDB-tables
Am 22.08.2014 um 19:40 schrieb Lentes, Bernd: i've been already reading the documentation the whole day, but still confused and unsure what to do. We have two databases which are important for our work. So both are stored hourly. Now I recognized that each database has a mixture of MyISAM- and InnoDB-tables. A backup of this mix does not seem to be easy. Until now it was dumped using mysqldump --opt -u root --databases mausdb What I understand until now is that --opt is not necessary because it is default. It includes, among others, --lock-tables which is senseful for saving MyISAM-tables. For InnoDB-tables --single-transaction is useful. But both are mutually exclusive (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). The dump of both take about 10 seconds. If the db is locked for that period I can live with. When I use --single-transaction only the InnoDB-tables are consistent. Using --lock-tables the MyISAM-tables are stored consistently. What is about --lock-tables in conjunction with InnoDB-tables ? Are they stored consistently ? Are they locked during the dumping ? As I said, I could live with a small lock period ( 30 sec). Would --lock-all-tables be better ? Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables (from the manpage). I can live with a global read lock for the duration of the whole dump. --lock-tables causes any pending transactions to be committed implicitly (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_single-transaction ). Is that a problem for the InnoDB tables ? Our system is: mysql-5.0.26-12.29.1 on a SLES 10 SP4 64 bit host why that complex? just setup replication because you have a lot of benefits: * in case your master crashs and the FS got damaged you have a real-time backup * for backups you can stop the slave, tar the whole datadir and start the slave * after it is restarted it pulls any change happened on the master due backup * the backup is likely smaller than verbose sql dumps * you do not need to care about table types and what not else signature.asc Description: OpenPGP digital signature
Re: backup of databases which have a mix of MyISAM- and InnoDB-tables
XTrabackup can handle both InnoDB and MyISAM in a consistent way while minimizing lock time on MyISAM tables ... http://www.percona.com/doc/percona-xtrabackup/2.1/ -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) SkySQL - The MariaDB Company | http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Risks involved in MyISAM to Innodb
Am 21.09.2012 18:18, schrieb Rick James: (Apologies to the rare bottom-poster.) bullshit on mostly egvery mailing-list there are guidlines that you NOT should top-post, try it out on the postfix-list as example and wait what Wietse wille xplain you about both * your top-posting * and your stupid reply all resulting in get answers twice the case where you should be clever enough to do so is when there is a answer below and you post on top in stupidity to surround the question on both sides with answers to make it unable for anybody to read a thread anymore it is suitable if EVERY answer is on top but plain stupid after a answer at the bottom ___ AND YES my business and private communication is ALWAYS top-posting but i was smart enough to accept the it is a guidline at virtual all mailing-lists not do it there signature.asc Description: OpenPGP digital signature
Risks involved in MyISAM to Innodb
Hi Guys, I have requirement to change my production database tables which are using myISAM and now bcoz of some changes we have to move to Innodb. Can anyone suggest how the plan should be and risks involve? Thanks, Girish Talluru
Re: Risks involved in MyISAM to Innodb
Am 21.09.2012 15:26, schrieb Girish Talluru: I have requirement to change my production database tables which are using myISAM and now bcoz of some changes we have to move to Innodb. Can anyone suggest how the plan should be and risks involve? no because this depends hardly on your data and what the application does - many things may be faster, some like select count(*) from are unacceptable slow if they are called often however, it is the wrong way to ask foreign people such questions * try the migration on a staging server * test your application under load on the staging server if no staging server exists you have done something terrible wrong signature.asc Description: OpenPGP digital signature
Re: Risks involved in MyISAM to Innodb
On 21/09/2012 9:26 AM, Girish Talluru wrote: Hi Guys, I have requirement to change my production database tables which are using myISAM and now bcoz of some changes we have to move to Innodb. Can anyone suggest how the plan should be and risks involve? Thanks, Girish Talluru Whats the size of those tables? What's the possible downtime you can afford? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Risks involved in MyISAM to Innodb
Hi Reindl, I'm sorry if I ask wrong question here? I'm new to this and people before me did the terrible mistake of using myISAM even though they decided to move to Innodb many times. Data grows like hell and now requirements came in where we have to use transactions. At this stage we have to migrate to innodb but as a new guy they assigned me to get the risks document ready for migration. Thanks, Girish Talluru On Fri, Sep 21, 2012 at 6:44 AM, Reindl Harald h.rei...@thelounge.netwrote: Am 21.09.2012 15:26, schrieb Girish Talluru: I have requirement to change my production database tables which are using myISAM and now bcoz of some changes we have to move to Innodb. Can anyone suggest how the plan should be and risks involve? no because this depends hardly on your data and what the application does - many things may be faster, some like select count(*) from are unacceptable slow if they are called often however, it is the wrong way to ask foreign people such questions * try the migration on a staging server * test your application under load on the staging server if no staging server exists you have done something terrible wrong
Re: Risks involved in MyISAM to Innodb
do NOT top-post which makes threads unreadable Am 21.09.2012 15:55, schrieb Girish Talluru: On Fri, Sep 21, 2012 at 6:44 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Am 21.09.2012 15:26, schrieb Girish Talluru: I have requirement to change my production database tables which are using myISAM and now bcoz of some changes we have to move to Innodb. Can anyone suggest how the plan should be and risks involve? no because this depends hardly on your data and what the application does - many things may be faster, some like select count(*) from are unacceptable slow if they are called often however, it is the wrong way to ask foreign people such questions * try the migration on a staging server * test your application under load on the staging server if no staging server exists you have done something terrible wrong I'm sorry if I ask wrong question here? you did not ask any question because without knowing what type of queries on what type of data the application makes no answer is possible At this stage we have to migrate to innodb but as a new guy they assigned me to get the risks document ready for migration and that is why i said try the migration on a staging server setup a virtual machine for testing and look with snapshots how it behaves - any paper before is useless since MyISAM has no foreign keys it should be easy to change the table types, the other direction would be more painful signature.asc Description: OpenPGP digital signature
RE: Risks involved in MyISAM to Innodb
(Apologies to the rare bottom-poster.) This contains lots of tips on converting from MyISAM to InnoDB: http://mysql.rjweb.org/doc.php/myisam2innodb Generally, the conversion should go smoothly. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, September 21, 2012 7:11 AM To: mysql@lists.mysql.com Subject: Re: Risks involved in MyISAM to Innodb do NOT top-post which makes threads unreadable Am 21.09.2012 15:55, schrieb Girish Talluru: On Fri, Sep 21, 2012 at 6:44 AM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Am 21.09.2012 15:26, schrieb Girish Talluru: I have requirement to change my production database tables which are using myISAM and now bcoz of some changes we have to move to Innodb. Can anyone suggest how the plan should be and risks involve? no because this depends hardly on your data and what the application does - many things may be faster, some like select count(*) from are unacceptable slow if they are called often however, it is the wrong way to ask foreign people such questions * try the migration on a staging server * test your application under load on the staging server if no staging server exists you have done something terrible wrong I'm sorry if I ask wrong question here? you did not ask any question because without knowing what type of queries on what type of data the application makes no answer is possible At this stage we have to migrate to innodb but as a new guy they assigned me to get the risks document ready for migration and that is why i said try the migration on a staging server setup a virtual machine for testing and look with snapshots how it behaves - any paper before is useless since MyISAM has no foreign keys it should be easy to change the table types, the other direction would be more painful -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Cópia rápida do DB com tabelas MyISAM e InnoDB para outra servidor
Olá pessoal. Tenho um banco com tabelas originalmente myisam e algumas outras eu converti para innodb. Estou tentando converter outras tabelas maiores porém gostaria de fazer um teste primeiro, estava pensando em fazer uma cópia fiel da base, ctrl+c/v da base em outra máquina para fazer o teste. É possível fazer uma copia física do DB e jogar os arquivos em outra máquina? (ou outra forma rápida de cópia?) Eu já tinha conseguido isso quando as tabelas eram todas myisam, mas agora tem algumas como innodb e os arquivos não estão em /var/lib/mysql/nomedabase como de costume, as tabelas em innodb estão com apenas um arquivo *.frm. Obrigado. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Cópia rápida do DB com tabelas MyISAM e InnoDB para outra servidor
Hi, if you want to copy from one server to another can't you just use mysqldump? This is then restored via the mysql command using a pipe or STDIN redirection. If you can shutdown the database for the duration of the copy then you can do cold backup of all data files. Both options will be as fast as your database is small ;) Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Cópia rápida do DB com tabelas MyISAM e InnoDB para outra servidor
lol sorry for send the email in portuguese, I did not realize that the list were in english (a little mistake, I have many mailing lists). I can use mysqldump but its take many hours to complete a restore. I can stop the server, so i'm think in a physical copy of the database. But I have not found the innodb files. Where are that files? 2011/5/27 a.sm...@ukgrid.net: Hi, if you want to copy from one server to another can't you just use mysqldump? This is then restored via the mysql command using a pipe or STDIN redirection. If you can shutdown the database for the duration of the copy then you can do cold backup of all data files. Both options will be as fast as your database is small ;) Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=geanyos...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Cópia rápida do DB com tabelas MyISAM e InnoDB para outra servidor
Quoting Yoshio geanyos...@gmail.com: I can use mysqldump but its take many hours to complete a restore. I can stop the server, so i'm think in a physical copy of the database. But I have not found the innodb files. Where are that files? InnoDB is a bit tricky, without going into details (I couldn't reliably describe them anyway) doing an online backup by locking tables isn't sufficient for InnoDB. The database must be cleanly shutdown before you take your copy. Another thing I read is that you must restore all InnoDB databases and tables from a cold backup, you cannot copy only those you want. The data files should be under your data dir, normally ibdata1 and some ib_logfile files, plus any related subdirectories. You should be able to safely exclude any MyIsam only databases as per usual, cheers Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Cópia rápida do DB com tabelas MyISAM e InnoDB para outra servidor
I found the ibdata and logfiles, but outside from my database dir. so basically I need copy everything in /var/lib/mysql/* ? 2011/5/27 a.sm...@ukgrid.net: Quoting Yoshio geanyos...@gmail.com: I can use mysqldump but its take many hours to complete a restore. I can stop the server, so i'm think in a physical copy of the database. But I have not found the innodb files. Where are that files? InnoDB is a bit tricky, without going into details (I couldn't reliably describe them anyway) doing an online backup by locking tables isn't sufficient for InnoDB. The database must be cleanly shutdown before you take your copy. Another thing I read is that you must restore all InnoDB databases and tables from a cold backup, you cannot copy only those you want. The data files should be under your data dir, normally ibdata1 and some ib_logfile files, plus any related subdirectories. You should be able to safely exclude any MyIsam only databases as per usual, cheers Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Cópia rápida do DB com tabelas MyISAM e InnoDB para outra servidor
Quoting Yoshio geanyos...@gmail.com: I found the ibdata and logfiles, but outside from my database dir. so basically I need copy everything in /var/lib/mysql/* ? Yeah that's normal, they will be in the top level of your MySQL datadir. Yep, copy everything. As I said, if you have any MyISAM only databases that you don't want to copy, you should be able to exclude these directories, everything else you need to copy. cheers Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Migrating my mindset from MyISAM to InnoDB
In the last episode (Sep 28), Gavin Towey said: Also note, 5.5 isn't production ready. 5.1 is the current GA release. 5.5 is really really close, though (5.5.6 is marked as Release Candidate), Better to switch now while you're already doing a migration, and then install 5.5.x updates 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 might as well bit the bullet since that seems to be the direction of MySQL/Oracle. I very much like how verbose myisamchk is in detailing which index it is currently rebuilding, and the progress in terms of records re-indexed. SO, my questions are this: 1. With InnoDB, do the indexes ever need to be rebuilt to reduce index size and improve performance like I get with MyISAM? All databases can benefit from occasional index rebuilds/optimizations to recover slack space. Once a system gets big enough, though, the downtime required for the OPTIMIZE TABLE may outweigh the benefits. Just add more disk and RAM :) 2. If so, are there any tools like myisamchk to monitor the InnoDB index rebuild process, other than issuing a repair table... and staring indefinitely at a blank screen until it finishes hours later? Unfortunately, no. MySQL threads should really make periodic updates to their status so you can see the progress of long-running queries in the show processlist output. http://bugs.mysql.com/bug.php?id=26182 included a patch that adds progress updates to select statements, so it should be possible to do the same for ALTER TABLEs as well. 3. I've been testing the rebuild process during upgrading using alter table table_name engine=innodb to convert my tables from 4.1.14 to 5.5.6, and I'm seeing a 130% increase (more than double) in the raw disk space required for the new InnoDB tables compared to their old MyISAM counterparts. (I am using single-file-per-table). Is this normal? If not, how can I adjust the space requirements for these tables so they don't take up so much additional space? 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/write access, and for switching to an index-organized table. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Migrating my mindset from MyISAM to InnoDB
2. Don't stare at the screen. Start it, script the process have it email your phone when it's done. Do something else in the mean time. I don't literally stare at the screen -- of course I script it and do other things.. but when I have a resource limited environment, it sure would be nice to have *some idea* of the progress of the rebuild. By staring at the blank screen, I really meant to say that there is absolutely no feedback at all during the process, to get even any idea of how far it has completed and how far it has to go. From my initial tests at rebuilding a 5.6 million record table (4.75 hours), trying to rebuild a 200 million record table would take more than 7 days. And I have two of those tables to rebuild. I can accomplish the same myISAM rebuild in two hours. Unfortunately, no. MySQL threads should really make periodic updates to their status so you can see the progress of long-running queries in the show processlist output. http://bugs.mysql.com/bug.php?id=26182 included a patch that adds progress updates to select statements, so it should be possible to do the same for ALTER TABLEs as well. Wow, that sure would be nice... even with some extended 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/write access, and for switching to an index-organized table. Now that you put it that way, I'm thinking of just sticking with myisam. I can't spend two weeks upgrading the two 200 million row tables. Thanks for all your comments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Migrating my mindset from MyISAM to InnoDB
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 to be the direction of MySQL/Oracle. I've been using MySQL 4.1.14 for years in my production environment, including one master and several slaves for report and long running queries. Every 6 to 12 months the master MYI index files grow fairly large, so I take the production database offline, and run myisamchk -r on the index files to rebuild them and shrink them back down again. I usually get a 20% to 30% space saving and improved performance after the rebuilds. This has worked very well for me for, well, almost 10 years now! And when I say large my two main tables have about 200 million rows, and the myisamchk can take between 60-160 minutes to complete. I very much like how verbose myisamchk is in detailing which index it is currently rebuilding, and the progress in terms of records re-indexed. SO, my questions are this: 1. With InnoDB, do the indexes ever need to be rebuilt to reduce index size and improve performance like I get with MyISAM? 2. If so, are there any tools like myisamchk to monitor the InnoDB index rebuild process, other than issuing a repair table... and staring indefinitely at a blank screen until it finishes hours later? 3. I've been testing the rebuild process during upgrading using alter table table_name engine=innodb to convert my tables from 4.1.14 to 5.5.6, and I'm seeing a 130% increase (more than double) in the raw disk space required for the new InnoDB tables compared to their old MyISAM counterparts. (I am using single-file-per-table). Is this normal? If not, how can I adjust the space requirements for these tables so they don't take up so much additional space? I'm sure I'll have more questions later, but many thanks for your comments and thoughts. -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Migrating my mindset from MyISAM to InnoDB
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 progress like with myisamchk, but that's not important anyway. You can estimate how long it will take by keeping track of how long any given ALTER / OPTIMIZE takes in GB/hr. 2. Don't stare at the screen. Start it, script the process have it email your phone when it's done. Do something else in the mean time. 3. Yes, innodb table will take more space on disk. If you have a really long primary key, and lots of secondary indexes, then it can take a *lot* more. Disk is cheap, don't worry about it. Regards, Gavin Towey -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 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 to be the direction of MySQL/Oracle. I've been using MySQL 4.1.14 for years in my production environment, including one master and several slaves for report and long running queries. Every 6 to 12 months the master MYI index files grow fairly large, so I take the production database offline, and run myisamchk -r on the index files to rebuild them and shrink them back down again. I usually get a 20% to 30% space saving and improved performance after the rebuilds. This has worked very well for me for, well, almost 10 years now! And when I say large my two main tables have about 200 million rows, and the myisamchk can take between 60-160 minutes to complete. I very much like how verbose myisamchk is in detailing which index it is currently rebuilding, and the progress in terms of records re-indexed. SO, my questions are this: 1. With InnoDB, do the indexes ever need to be rebuilt to reduce index size and improve performance like I get with MyISAM? 2. If so, are there any tools like myisamchk to monitor the InnoDB index rebuild process, other than issuing a repair table... and staring indefinitely at a blank screen until it finishes hours later? 3. I've been testing the rebuild process during upgrading using alter table table_name engine=innodb to convert my tables from 4.1.14 to 5.5.6, and I'm seeing a 130% increase (more than double) in the raw disk space required for the new InnoDB tables compared to their old MyISAM counterparts. (I am using single-file-per-table). Is this normal? If not, how can I adjust the space requirements for these tables so they don't take up so much additional space? I'm sure I'll have more questions later, but many thanks for your comments and thoughts. -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Migrating my mindset from MyISAM to InnoDB
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 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 to be the direction of MySQL/Oracle. I've been using MySQL 4.1.14 for years in my production environment, including one master and several slaves for report and long running queries. Every 6 to 12 months the master MYI index files grow fairly large, so I take the production database offline, and run myisamchk -r on the index files to rebuild them and shrink them back down again. I usually get a 20% to 30% space saving and improved performance after the rebuilds. This has worked very well for me for, well, almost 10 years now! And when I say large my two main tables have about 200 million rows, and the myisamchk can take between 60-160 minutes to complete. I very much like how verbose myisamchk is in detailing which index it is currently rebuilding, and the progress in terms of records re-indexed. SO, my questions are this: 1. With InnoDB, do the indexes ever need to be rebuilt to reduce index size and improve performance like I get with MyISAM? 2. If so, are there any tools like myisamchk to monitor the InnoDB index rebuild process, other than issuing a repair table... and staring indefinitely at a blank screen until it finishes hours later? 3. I've been testing the rebuild process during upgrading using alter table table_name engine=innodb to convert my tables from 4.1.14 to 5.5.6, and I'm seeing a 130% increase (more than double) in the raw disk space required for the new InnoDB tables compared to their old MyISAM counterparts. (I am using single-file-per-table). Is this normal? If not, how can I adjust the space requirements for these tables so they don't take up so much additional space? I'm sure I'll have more questions later, but many thanks for your comments and thoughts. -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MyISAM to InnoDB
Hi, What is the basic functionality of the MyISAM, InnoDB etc ? Vikram A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: MyISAM to InnoDB
What is the basic functionality of the MyISAM, InnoDB etc ? http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Converting MyISAM to InnoDB
Hello again! I am trying to convert my tables to InnoDB, and i am getting an error... Error: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key Now, I converted a table in my sandbox earlier this morning to do some testing, and it worked fine... mind you, i did truncate the table first, but i am not sure if that is relavent or not. The table structure has a TONNE of fields, but to give you an idea... here is what is starts out like: CREATE TABLE `radacct_201002` ( `Year_Month` INT(11) UNSIGNED NOT NULL DEFAULT '0', `Radacct_Id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, `Acct_Authentic` VARCHAR(32) DEFAULT '', Etc etc PRIMARY KEY (`Year_Month`,`Radacct_Id`), UNIQUE KEY `radacct_id` (`Radacct_Id`), KEY (there are keys here not of any relevance that i can see) ) ENGINE=INNODB DEFAULT CHARSET=latin1 That is the table in the sandbox, and as i said, all i did was truncate it, and change to innodb (there was prolly about 5-10 rows when i did it) and there wasn't any issues. When i do it to the live database (i copied a table of live data, so i can convert it and see what kind of times/loads i get) i get the error... I am in the midst of removing the combined unique primary key, to see if that is the culperate or not, but if anyone has any ideas, i am eager to listen :) Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Converting MyISAM to InnoDB
If you have a column defined as auto_increment, there must be a key on it. This is true both in myisam and innodb. If you need further help, please show us the full structure of the real table you're operating on (not the one from your sandbox), the statement you run, and the error message. Regards, Gavin Towey -Original Message- From: Steve Staples [mailto:sstap...@mnsi.net] Sent: Monday, February 08, 2010 9:39 AM To: mysql@lists.mysql.com Subject: Converting MyISAM to InnoDB Hello again! I am trying to convert my tables to InnoDB, and i am getting an error... Error: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key Now, I converted a table in my sandbox earlier this morning to do some testing, and it worked fine... mind you, i did truncate the table first, but i am not sure if that is relavent or not. The table structure has a TONNE of fields, but to give you an idea... here is what is starts out like: CREATE TABLE `radacct_201002` ( `Year_Month` INT(11) UNSIGNED NOT NULL DEFAULT '0', `Radacct_Id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, `Acct_Authentic` VARCHAR(32) DEFAULT '', Etc etc PRIMARY KEY (`Year_Month`,`Radacct_Id`), UNIQUE KEY `radacct_id` (`Radacct_Id`), KEY (there are keys here not of any relevance that i can see) ) ENGINE=INNODB DEFAULT CHARSET=latin1 That is the table in the sandbox, and as i said, all i did was truncate it, and change to innodb (there was prolly about 5-10 rows when i did it) and there wasn't any issues. When i do it to the live database (i copied a table of live data, so i can convert it and see what kind of times/loads i get) i get the error... I am in the midst of removing the combined unique primary key, to see if that is the culperate or not, but if anyone has any ideas, i am eager to listen :) Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MyISAM vs InnoDB - Index choice and Huge performance difference
joe schrieb: U might want to try seting you index to calldate, disposition or calldate, day, disposition ... and depending on your MySQL version: (to circumvent possible limitations in InnoDB with your MySQL version) you could try WHERE calldate = '2007-07-01 00:00:00' AND calldate = '2007-07-30 23:59:59' or SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM ( SELECT DATE_FORMAT(calldate, '%d') AS day, num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' ) GROUP BY day, disposition; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, November 25, 2007 10:03 PM To: Edoardo Serra Cc: mysql@lists.mysql.com Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes -- 2Gbytes is useless On 11/25/07, Edoardo Serra [EMAIL PROTECTED] wrote: Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB - Index choice and Huge performance difference
Tnx for your interest # uname -a Linux corona 2.6.18-5-amd64 #1 SMP Thu May 31 23:51:05 UTC 2007 x86_64 GNU/Linux 64 bit shouldn't have problems in using 4gb of ram .. right ? [EMAIL PROTECTED] ha scritto: just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes -- 2Gbytes is useless On 11/25/07, Edoardo Serra [EMAIL PROTECTED] wrote: Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB - Index choice and Huge performance difference
Yes, you're right, with that index query is flying... then I used DAYOFMONTH(calldate) instead of DATE_FORMAT(calldate, '%d'), it gives an extra performance gain of 5x tnx for help joe ha scritto: U might want to try seting you index to calldate, disposition -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, November 25, 2007 10:03 PM To: Edoardo Serra Cc: mysql@lists.mysql.com Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes -- 2Gbytes is useless On 11/25/07, Edoardo Serra [EMAIL PROTECTED] wrote: Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB - Index choice and Huge performance difference
Tnx for your precious advice. Do you know if there is some documentation somewhere on the net with the most known Innodb performance limitations ? so I can avoid to teast again each query and doing different optimization. I tried the original query with 5.0 and 5.1... same results... Tnx again Edoardo Sebastian Mendel ha scritto: joe schrieb: U might want to try seting you index to calldate, disposition or calldate, day, disposition ... and depending on your MySQL version: (to circumvent possible limitations in InnoDB with your MySQL version) you could try WHERE calldate = '2007-07-01 00:00:00' AND calldate = '2007-07-30 23:59:59' or SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM ( SELECT DATE_FORMAT(calldate, '%d') AS day, num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' ) GROUP BY day, disposition; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, November 25, 2007 10:03 PM To: Edoardo Serra Cc: mysql@lists.mysql.com Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes -- 2Gbytes is useless On 11/25/07, Edoardo Serra [EMAIL PROTECTED] wrote: Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM vs InnoDB - Index choice and Huge performance difference
Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB - Index choice and Huge performance difference
just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes -- 2Gbytes is useless On 11/25/07, Edoardo Serra [EMAIL PROTECTED] wrote: Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM vs InnoDB - Index choice and Huge performance difference
U might want to try seting you index to calldate, disposition -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, November 25, 2007 10:03 PM To: Edoardo Serra Cc: mysql@lists.mysql.com Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes -- 2Gbytes is useless On 11/25/07, Edoardo Serra [EMAIL PROTECTED] wrote: Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00 AM No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage - MyISAM vs InnoDB
Hi! Comments inline. Edoardo Serra wrote: SELECT sum(usercost) FROM cdr WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59' If I run it on the MyISAM table, MySQL choose the right index (the one on the calldate column) and the query is fast enough If I run it on the InnoDB table, MySQL uses no index even if an EXPLAIN query tells me that 'calldate' is between the available indexes Here are my EXPLAIN results mysql EXPLAIN SELECT sum(usercost) FROM cdr_innodb WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59'; ++-+---+--+-+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+-+--+-+--+-+-+ | 1 | SIMPLE | cdr | ALL | calldate,date-context-cause | NULL | NULL| NULL | 5016758 | Using where | ++-+---+--+-+--+-+--+-+-+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT sum(usercost) FROM cdr_myisam WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59'; ++-+---+---+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+-+--+-+--++-+ | 1 | SIMPLE | cdr | range | calldate,date-context-cause | calldate | 8 | NULL | 772050 | Using where | ++-+---+---+-+--+-+--++-+ 1 row in set (0.11 sec) Another strange thing is that the EXPLAIN on InnoDB says the table has 5016758 rows but a SELECT count(*) returns 4999347 rows (which is the correct number) The rows returned in EXPLAIN SELECT (and SHOW TABLE STATUS) for InnoDB tables is an estimate. For MyISAM, it is the actual number of rows in the table. This is because InnoDB has to track a version for each row in the table (for transactional isolation), and MyISAM does not, which makes it much easier to just have a simple row count for the table. This estimate of rows returned is what is used by the optimizer to determine what execution plan is optimal for this particular query. In this case, there are approximately 772K out of 5M rows which meet the WHERE condition -- or about 15% of the total number of rows in the table. There is a certain threshold, where above it the optimizer will choose to do a sequential table scan of the data, versus do many random seeks into memory or disk. It seems that you are hovering around the threshold for where the optimizer chooses to do a sequential table scan (InnoDB) vs a range operation on a btree with lookups into the data file for each matched row in the index (MyISAM). The difference in returning an estimate vs. the actual row count *might* be the cause of the difference in execution plans. Or, it could have something to do with the weights that the optimizer chooses to place on bookmark lookups in MyISAM vs a quick table scan in InnoDB. I'd be interested to see what the difference in *performance* is? Also, in *either* engine, if you are executing this particular query a *lot*, the best thing for you to do would be to put the index on (calldate, usercost) so that you have a covering index available to complete the query. Cheers! Jay Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index usage - MyISAM vs InnoDB
Hi guys, I'm moving a database to InnoDB because I need some transaction related features but I'm having big problems with perrformances. I have a big table with 5mln rows on which I need to run some SELECTs. It's the Call Detail Record of a telco, so each record has a 'calldate' field with an index on it (it's a non unique index) I have the same table in InnoDB and MyISAM storage engines. I have this simple query: SELECT sum(usercost) FROM cdr WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59' If I run it on the MyISAM table, MySQL choose the right index (the one on the calldate column) and the query is fast enough If I run it on the InnoDB table, MySQL uses no index even if an EXPLAIN query tells me that 'calldate' is between the available indexes Here are my EXPLAIN results mysql EXPLAIN SELECT sum(usercost) FROM cdr_innodb WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59'; ++-+---+--+-+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+-+--+-+--+-+-+ | 1 | SIMPLE | cdr | ALL | calldate,date-context-cause | NULL | NULL| NULL | 5016758 | Using where | ++-+---+--+-+--+-+--+-+-+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT sum(usercost) FROM cdr_myisam WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59'; ++-+---+---+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+-+--+-+--++-+ | 1 | SIMPLE | cdr | range | calldate,date-context-cause | calldate | 8 | NULL | 772050 | Using where | ++-+---+---+-+--+-+--++-+ 1 row in set (0.11 sec) Another strange thing is that the EXPLAIN on InnoDB says the table has 5016758 rows but a SELECT count(*) returns 4999347 rows (which is the correct number) Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
consitent backup of MyISAM and Innodb tables
Hello, we have a MySQL DBMS with a lot of databases. Most of them are using MyISAM tables but three databases use InnoDB and MyISAM tables. What is the best method to get a consitent ONLINE backup of both table types? Thanks, Spiker -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: consitent backup of MyISAM and Innodb tables
Check out this thread: http://www.sitepoint.com/forums/showpost.php?p=3357628postcount=2 2007/7/17, [EMAIL PROTECTED] [EMAIL PROTECTED]: Hello, we have a MySQL DBMS with a lot of databases. Most of them are using MyISAM tables but three databases use InnoDB and MyISAM tables. What is the best method to get a consitent ONLINE backup of both table types? Thanks, Spiker -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: corruption in db. myisam bad? innodb good?
You would need to find out the reason for the crash to prevent or minimize it. The reason may be external to mysql. Innodb can get really, really slow when tables get physically large if you don't have a similar amount of RAM. MyISAM doesn't support transactions, so no, that wouldn't help. If you want to test Innodb as the table type, setup a replication/slave server with the tables as Innodb instead. See how your queries run against it. The table structure needs to match on a replication/salev server, not the table type. You can also make the slave the master in the event of a crash. - Original Message - From: tim h [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, May 22, 2007 2:56 PM Subject: corruption in db. myisam bad? innodb good? hi. database is myisam, 5.8Gb, 7mil records. recently had some corruption i think due to mysqld service failure. 10 tabes were crashed. question -- how can i prevent or minimize this? Will switching to innodb help? Will converting all my queries to transactions help? thanks. -- Tim H Berkeley, CA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
corruption in db. myisam bad? innodb good?
hi. database is myisam, 5.8Gb, 7mil records. recently had some corruption i think due to mysqld service failure. 10 tabes were crashed. question -- how can i prevent or minimize this? Will switching to innodb help? Will converting all my queries to transactions help? thanks. -- Tim H Berkeley, CA
Re: [OT} How to pronounce GIF (was: Re: How to pronounce MyISAM and InnoDB)
On Jan 7, 2007, at 4:23 PM, TK wrote: In short, the original inventors of the GIF format (CompuServe, 1987) have always defined the pronunciation to be like JIF. So, that has always been the correct pronunciation. Sure, so I'll start pronouncing graphics as jraphics. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [OT} How to pronounce GIF (was: Re: How to pronounce MyISAM and InnoDB)
... Especially for tall ones. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Brian Dunning [mailto:[EMAIL PROTECTED] Sent: Friday, January 12, 2007 1:49 PM To: mysql Subject: Re: [OT} How to pronounce GIF (was: Re: How to pronounce MyISAM and InnoDB) On Jan 7, 2007, at 4:23 PM, TK wrote: In short, the original inventors of the GIF format (CompuServe, 1987) have always defined the pronunciation to be like JIF. So, that has always been the correct pronunciation. Sure, so I'll start pronouncing graphics as jraphics. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to pronounce MyISAM and InnoDB
Jan, In English I pronounce them as... My-eye-sam In-oh-dee-bee ...respectively. Regards, Phil 2007/1/7, js [EMAIL PROTECTED]: Hi list, Sorry for this silly question but I've been always had trouble pronouncing MyISAM and InnoDB. How do you pronunce them? Thanks in advance. When I'm speaking Dutch (which is most of the time) I say My-ee-sahm Inno-day-bay JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to pronounce MyISAM and InnoDB
I'll bite.. Sorry for this silly question but I've been always had trouble pronouncing MyISAM and InnoDB. How do you pronunce them? I pronounce MyISAM as give-me-foreign-keys and InnoDB as curse-you-cryptic-foreign-key-errors (currently running far and fast) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to pronounce MyISAM and InnoDB
Hi list, Sorry for this silly question but I've been always had trouble pronouncing MyISAM and InnoDB. How do you pronunce them? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to pronounce MyISAM and InnoDB
I just say My, I, Sam and inno, d, b Michael -Original Message- From: js [EMAIL PROTECTED] Date: Mon, 8 Jan 2007 00:09:15 To:mysql@lists.mysql.com Subject: How to pronounce MyISAM and InnoDB Hi list, Sorry for this silly question but I've been always had trouble pronouncing MyISAM and InnoDB. How do you pronunce them? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to pronounce MyISAM and InnoDB
My friend says my sam and in-NOD-b. I want to kill him every time. He also says jif instead of gif. On Jan 7, 2007, at 7:16 AM, Michael Stearne wrote: I just say My, I, Sam and inno, d, b Michael -Original Message- From: js [EMAIL PROTECTED] Date: Mon, 8 Jan 2007 00:09:15 To:mysql@lists.mysql.com Subject: How to pronounce MyISAM and InnoDB Hi list, Sorry for this silly question but I've been always had trouble pronouncing MyISAM and InnoDB. How do you pronunce them? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to pronounce MyISAM and InnoDB
2007/1/7, js [EMAIL PROTECTED]: Hi list, Sorry for this silly question but I've been always had trouble pronouncing MyISAM and InnoDB. How do you pronunce them? Thanks in advance. When I'm speaking Dutch (which is most of the time) I say My-ee-sahm Inno-day-bay JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[OT} How to pronounce GIF (was: Re: How to pronounce MyISAM and InnoDB)
At 10:15 AM 1/7/2007 -0800, Brian Dunning wrote: My friend says my sam and in-NOD-b. I want to kill him every time. He also says jif instead of gif. The GIF Pronunciation Page: http://www.olsenhome.com/gif/ In short, the original inventors of the GIF format (CompuServe, 1987) have always defined the pronunciation to be like JIF. So, that has always been the correct pronunciation. - TK On Jan 7, 2007, at 7:16 AM, Michael Stearne wrote: I just say My, I, Sam and inno, d, b From: js [EMAIL PROTECTED] Sorry for this silly question but I've been always had trouble pronouncing MyISAM and InnoDB. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How Do I Know If mySQL is using MyISAM or InnoDB?
Is there a command at the command line that can tell me if I am using MyISAM or InnoDB? Thanks :-). -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info
Re: How Do I Know If mySQL is using MyISAM or InnoDB?
This is will tell you your default storage engine type should you create a table without specifying an engine: show variables like 'storage engine'; If you want to create a table with a specific engine, specify it at the end od the CREATE TABLE like this: CREATE TABLE ( ... ) ENGINE=MyISAM; CREATE TABLE ( ... ) ENGINE=InnoDB; To show what engines are available on your MySQL server, do this: show engines; - Original Message - From: John Kopanas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 17, 2006 12:13:33 PM GMT-0500 US/Eastern Subject: How Do I Know If mySQL is using MyISAM or InnoDB? Is there a command at the command line that can tell me if I am using MyISAM or InnoDB? Thanks :-). -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How Do I Know If mySQL is using MyISAM or InnoDB?
show variables like 'storage_engine'; I forgot the underscore in the lastmessage - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: John Kopanas [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, November 17, 2006 12:30:51 PM GMT-0500 US/Eastern Subject: Re: How Do I Know If mySQL is using MyISAM or InnoDB? This is will tell you your default storage engine type should you create a table without specifying an engine: show variables like 'storage engine'; If you want to create a table with a specific engine, specify it at the end od the CREATE TABLE like this: CREATE TABLE ( ... ) ENGINE=MyISAM; CREATE TABLE ( ... ) ENGINE=InnoDB; To show what engines are available on your MySQL server, do this: show engines; - Original Message - From: John Kopanas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 17, 2006 12:13:33 PM GMT-0500 US/Eastern Subject: How Do I Know If mySQL is using MyISAM or InnoDB? Is there a command at the command line that can tell me if I am using MyISAM or InnoDB? Thanks :-). -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How Do I Know If mySQL is using MyISAM or InnoDB?
For any specific table if you do: show create table tablename; It will tell you what the able was created using. To create tables using a specific engine add ENGINE=name to the end of your create statement. To see the default that is used (I think this is what the table_type variable does): mysql show variables like 'table_type'; +---++ | Variable_name | Value | +---++ | table_type| MYISAM | +---++ 1 row in set (0.00 sec) John Kopanas wrote: Is there a command at the command line that can tell me if I am using MyISAM or InnoDB? Thanks :-). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How Do I Know If mySQL is using MyISAM or InnoDB?
This is in 4.0, it has changed in more recent versions. Mike Kruckenberg wrote: For any specific table if you do: show create table tablename; It will tell you what the able was created using. To create tables using a specific engine add ENGINE=name to the end of your create statement. To see the default that is used (I think this is what the table_type variable does): mysql show variables like 'table_type'; +---++ | Variable_name | Value | +---++ | table_type| MYISAM | +---++ 1 row in set (0.00 sec) John Kopanas wrote: Is there a command at the command line that can tell me if I am using MyISAM or InnoDB? Thanks :-). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How Do I Know If mySQL is using MyISAM or InnoDB?
show variables like 'table_type'; (MySQL 4) show variables like 'storage_engine'; (MySQL 5) Both of these work. However, in future releases of MySQL table_type will goes away because it was kept from backward compatiblity with MySQL 4 - Original Message - From: Mike Kruckenberg [EMAIL PROTECTED] To: John Kopanas [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, November 17, 2006 12:32:19 PM GMT-0500 US/Eastern Subject: Re: How Do I Know If mySQL is using MyISAM or InnoDB? For any specific table if you do: show create table tablename; It will tell you what the able was created using. To create tables using a specific engine add ENGINE=name to the end of your create statement. To see the default that is used (I think this is what the table_type variable does): mysql show variables like 'table_type'; +---++ | Variable_name | Value | +---++ | table_type| MYISAM | +---++ 1 row in set (0.00 sec) John Kopanas wrote: Is there a command at the command line that can tell me if I am using MyISAM or InnoDB? Thanks :-). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How Do I Know If mySQL is using MyISAM or InnoDB?
Hi, If you are particular about a table, i.e what engine my table uses? Try, mysql show table status like 'tablename' \G Thanks ViSolve DB Team. - Original Message - From: John Kopanas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 17, 2006 10:43 PM Subject: How Do I Know If mySQL is using MyISAM or InnoDB? Is there a command at the command line that can tell me if I am using MyISAM or InnoDB? Thanks :-). -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On 11/6/06, Leandro Guimarães Faria Corcete DUTRA wrote: Em Thu, 02 Nov 2006 10:22:18 -0800, Jochem van Dieten escreveu: PostgreSQL supports 2 phase commit. IIRC except for transaction interleaving, join and suspend/resume it supports XA. I think that puts it about on par with Ingres and Firebird. I would have to analyze better, but I think you are mistaken, sadly. Please share your analysis with us. Jochem
Re: MyISAM vs InnoDB
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 7 Nov 2006, at 12:35, Jochem van Dieten wrote: On 11/6/06, Leandro Guimarães Faria Corcete DUTRA wrote: Em Thu, 02 Nov 2006 10:22:18 -0800, Jochem van Dieten escreveu: PostgreSQL supports 2 phase commit. IIRC except for transaction interleaving, join and suspend/resume it supports XA. I think that puts it about on par with Ingres and Firebird. I would have to analyze better, but I think you are mistaken, sadly. Please share your analysis with us. Jochem From the Handbook Postgresql 8.1 there are PREPARE TRANSACTION transaction_id COMMIT PREPARED transaction_id ROLLBACK PREPARED transaction_id and it states PREPARE TRANSACTION -- prepare the current transaction for two-phase commit - -- Viele Grüße, Lars Heidieker [EMAIL PROTECTED] http://paradoxon.info - Mystische Erklärungen. Die mystischen Erklärungen gelten für tief; die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind. -- Friedrich Nietzsche -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (Darwin) iD8DBQFFUIXPDAkIK9aNPuIRAjBnAJ92IupcD1/yAcvD88IW2szNieCg0gCgggis CJQvtMAlz6p3EWs2cc/ZstE= =IDBz -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Fri, 03 Nov 2006 09:18:21 +0100, Martijn Tonies escreveu: On two-phase commits? I guess it's the IB 6 docs where you have to read that, or get a copy of Helen Borries Firebird book. Get a copy of the IBPhoenix CD that includes docs. The Firebird project itself has no full documentation yet - it's being worked on. Hm, do you mean 2PC are only documented in old IB6 stuff? -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Thu, 02 Nov 2006 10:22:18 -0800, Jochem van Dieten escreveu: PostgreSQL supports 2 phase commit. IIRC except for transaction interleaving, join and suspend/resume it supports XA. I think that puts it about on par with Ingres and Firebird. I would have to analyze better, but I think you are mistaken, sadly. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On two-phase commits? I guess it's the IB 6 docs where you have to read that, or get a copy of Helen Borries Firebird book. Get a copy of the IBPhoenix CD that includes docs. The Firebird project itself has no full documentation yet - it's being worked on. Hm, do you mean 2PC are only documented in old IB6 stuff? Eh, no - it's documented in the available Firebird documentation. I'm saying that the available Firebird documentation is either not free (but complete) or not yet complete (but being worked on by the Firebird Documentation sub-project). Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
InterBase had two-phase commits ages ago, Firebird inherited it. If there's anything specific you want to know, ask I *am* asking — where is the specific piece of documentation? On two-phase commits? I guess it's the IB 6 docs where you have to read that, or get a copy of Helen Borries Firebird book. Get a copy of the IBPhoenix CD that includes docs. The Firebird project itself has no full documentation yet - it's being worked on. Because if you don’t read MySQL’s documentation attentively, it gives you the impression everything’s A-OK with XA. And it’s not. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Wed, 01 Nov 2006 09:34:05 -0600, mos escreveu: At 05:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not completely free (needs a proprietary backup tool); BDB, which is deprecated until further notices; and SolidDB, which is still β. Ok, so your solution is to use something else? Well, this is a MySQL list… you can use MySQL with InnoDB, if you are willing to either have a proprietary backup solution or to use a β backend. Is there a better open source database out there for that amount of data? Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into Borland stuff, Ingres if you need XA distributed transactions. I usually recommend PostgreSQL, or Ingres if two-phase commits are needed. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not completely free (needs a proprietary backup tool); BDB, which is deprecated until further notices; and SolidDB, which is still β. Ok, so your solution is to use something else? Well, this is a MySQL list… you can use MySQL with InnoDB, if you are willing to either have a proprietary backup solution or to use a β backend. Is there a better open source database out there for that amount of data? Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into Borland stuff, Ingres if you need XA distributed transactions. Firebird isn't Borland :-) I usually recommend PostgreSQL, or Ingres if two-phase commits are needed. Firebird has two-phase commits. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
At 08:32 AM 11/2/2006, you wrote: Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not completely free (needs a proprietary backup tool); BDB, which is deprecated until further notices; and SolidDB, which is still β. Ok, so your solution is to use something else? Well, this is a MySQL list you can use MySQL with InnoDB, if you are willing to either have a proprietary backup solution or to use a β backend. Is there a better open source database out there for that amount of data? Several. MySQLâs own MaxDB, PostgreSQL, Firebird if you are into Borland stuff, Ingres if you need XA distributed transactions. Firebird isn't Borland :-) I usually recommend PostgreSQL, or Ingres if two-phase commits are needed. Firebird has two-phase commits. Martijin, Can Firebird store 1TB in a single table? All of FB tables are stored in a single .GDB file, so is it possible to even split the table across several drives? There is also the Falcon table engine that is coming out for MySQL, but MySQL AB claims it is *not* a replacement for InnoDb. So why introduce it? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Is there a better open source database out there for that amount of data? Several. MySQLâ?Ts own MaxDB, PostgreSQL, Firebird if you are into Borland stuff, Ingres if you need XA distributed transactions. Firebird isn't Borland :-) I usually recommend PostgreSQL, or Ingres if two-phase commits are needed. Firebird has two-phase commits. Martijin, Can Firebird store 1TB in a single table? All of FB tables are stored in a single .GDB file, so is it possible to even split the table across several drives? You can split a database across multiple drives, but you cannot direct a specific table to be in this or that part of the database. As far as I know, this make it possible that internally, tables are split across drives, but you cannot tell Firebird to do it directly. As for 1TB - I must admit I don't know, there's probably a maximum number of rows, not data though. There is also the Falcon table engine that is coming out for MySQL, but MySQL AB claims it is *not* a replacement for InnoDb. So why introduce it? Falcon will be part of MySQL, unline InnoDB, which is licenses [from Oracle]. I would not agree with the remark that Falcon is not a replacement, as far as I understood, Falcon has a transactional storage engine, including Foreign Keys (Jim wouldn't do a database without em), MGA and more... Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Thu, 02 Nov 2006 15:32:06 +0100, Martijn Tonies escreveu: Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into Borland stuff, Ingres if you need XA distributed transactions. Firebird isn't Borland Granted. But it is (even more) attractive if you are already a Borland shop. I usually recommend PostgreSQL, or Ingres if two-phase commits are needed. Firebird has two-phase commits. Great to know — do you have any pointers? -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into Borland stuff, Ingres if you need XA distributed transactions. Firebird isn't Borland Granted. But it is (even more) attractive if you are already a Borland shop. I usually recommend PostgreSQL, or Ingres if two-phase commits are needed. Firebird has two-phase commits. Great to know — do you have any pointers? InterBase had two-phase commits ages ago, Firebird inherited it. If there's anything specific you want to know, ask :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On 11/2/06, Leandro Guimarães Faria Corcete DUTRA wrote: Em Wed, 01 Nov 2006 09:34:05 -0600, mos escreveu: Is there a better open source database out there for that amount of data? Several. MySQL's own MaxDB, PostgreSQL, Firebird if you are into Borland stuff, Ingres if you need XA distributed transactions. I usually recommend PostgreSQL, or Ingres if two-phase commits are needed. PostgreSQL supports 2 phase commit. IIRC except for transaction interleaving, join and suspend/resume it supports XA. I think that puts it about on par with Ingres and Firebird. Jochem
Re: MyISAM vs InnoDB
Em Thu, 02 Nov 2006 17:30:14 +0100, Martijn Tonies escreveu: Falcon has a transactional storage engine, including Foreign Keys (Jim wouldn't do a database without em) Obviouſly. MGA Ma ze? -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Thu, 02 Nov 2006 17:40:44 +0100, Martijn Tonies escreveu: InterBase had two-phase commits ages ago, Firebird inherited it. If there's anything specific you want to know, ask I *am* asking — where is the specific piece of documentation? Because if you don’t read MySQL’s documentation attentively, it gives you the impression everything’s A-OK with XA. And it’s not. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Tue, 31 Oct 2006 15:24:44 -0500, Francis escreveu: MyISAM vs InnoDB ? What is the best to use Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not completely free (needs a proprietary backup tool); BDB, which is deprecated until further notices; and SolidDB, which is still β. Choose your evil. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
At 07:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: snip .. further notices; and SolidDB, which is still β. Choose your evil. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] Leandro, Help this poor English-speaker - what's the symbol you use to describe SolidDB? Cheers - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.13.21/509 - Release Date: 10/31/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Miles Thompson [EMAIL PROTECTED] wrote: At 07:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: snip .. further notices; and SolidDB, which is still β. Help this poor English-speaker - what's the symbol you use to describe SolidDB? I assume it is a beta character, since solidDB for MySQL is indeed in beta. See http://dev.soliddb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On Nov 1, 2006, at 12:56 PM, Leandro Guimarães Faria Corcete DUTRA wrote: Em Tue, 31 Oct 2006 15:24:44 -0500, Francis escreveu: MyISAM vs InnoDB ? What is the best to use Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not completely free (needs a proprietary backup tool); BDB, which is deprecated until further notices; and SolidDB, which is still β. plug Excuse me, but I have to do some advertising in my own interest :) There is also the PrimeBase XT (PBXT), which is also Beta, but is already available as a pluggable storage engine for 5.1 (besides merged code version for MySQL 4.1.21). More information at: http://www.primebase.com/xt For the latest 5.1 version please check out: http://sourceforge.net/ projects/pbxt Best regards, Paul /plug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM to InnoDB conversion help
Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables structure: DAILY_EDGAR_INNODB |CREATE TABLE `DAILY_EDGAR_INNODB` ( `ftp_file_name_key` char(80) NOT NULL default '', `history_record` int(1) NOT NULL default '0', `description` char(100) NOT NULL default '', `company_fkey` char(10) NOT NULL default '', `company_name` char(100) NOT NULL default '', `subject_company_fkey` char(10) NOT NULL default '', `filer_description` char(10) NOT NULL default '', `form_fkey` char(20) NOT NULL default '', `file_accepted` char(20) NOT NULL default '', `been_evaluated` char(20) NOT NULL default '', `uport_evaluated` int(1) NOT NULL default '0', `file_date` char(10) NOT NULL default '', `file_size` char(10) NOT NULL default '50 KB', `accession_number` char(24) NOT NULL default '', `http_file_name_html` char(100) NOT NULL default '', `http_file_name_text` char(100) NOT NULL default '', `create_date` date NOT NULL default '-00-00', `change_date` date NOT NULL default '-00-00', PRIMARY KEY (`ftp_file_name_key`), KEY `company_idx` (`company_fkey`), KEY `filaccdx` (`file_accepted`), KEY `beendx` (`been_evaluated`), KEY `fidadx` (`file_date`), KEY `upevdx` (`uport_evaluated`), KEY `crdadx` (`create_date`), KEY `hiredx` (`history_record`), KEY `accession_number` (`accession_number`), KEY `fofkdx` (`form_fkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | Procedure used to execute conversion: root 27686 0.0 0.2 5840 3224 ?S 14:08:23 0:00 mysql -pxx xxx -e insert into DAILY_EDGAR_INNODB select * from DAILY_EDGAR my.cnf InnoDB section: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /export/home/mysqldata/ibdata innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /export/home/mysqldata/ibdata innodb_log_arch_dir = /export/home/mysqldata/ibdata # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 50M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 100M #innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Best, Mikhail Berman
Re: MyISAM vs InnoDB
At 05:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: Em Tue, 31 Oct 2006 15:24:44 -0500, Francis escreveu: MyISAM vs InnoDB ? What is the best to use Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not completely free (needs a proprietary backup tool); BDB, which is deprecated until further notices; and SolidDB, which is still β. Choose your evil. Ok, so your solution is to use something else? Is there a better open source database out there for that amount of data? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
MyISAM vs InnoDB ? What is the best to use Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not completely free (needs a proprietary backup tool); BDB, which is deprecated until further notices; and SolidDB, which is still β. Choose your evil. Ok, so your solution is to use something else? Is there a better open source database out there for that amount of data? Firebird? PostgreSQL? Both are open source and ALWAYS free for whatever usuage, no dual licensing whatsoever. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB conversion help
If you are do this in MySQL 5, try this: ALTER TABLE table-name ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables structure: DAILY_EDGAR_INNODB |CREATE TABLE `DAILY_EDGAR_INNODB` ( `ftp_file_name_key` char(80) NOT NULL default '', `history_record` int(1) NOT NULL default '0', `description` char(100) NOT NULL default '', `company_fkey` char(10) NOT NULL default '', `company_name` char(100) NOT NULL default '', `subject_company_fkey` char(10) NOT NULL default '', `filer_description` char(10) NOT NULL default '', `form_fkey` char(20) NOT NULL default '', `file_accepted` char(20) NOT NULL default '', `been_evaluated` char(20) NOT NULL default '', `uport_evaluated` int(1) NOT NULL default '0', `file_date` char(10) NOT NULL default '', `file_size` char(10) NOT NULL default '50 KB', `accession_number` char(24) NOT NULL default '', `http_file_name_html` char(100) NOT NULL default '', `http_file_name_text` char(100) NOT NULL default '', `create_date` date NOT NULL default '-00-00', `change_date` date NOT NULL default '-00-00', PRIMARY KEY (`ftp_file_name_key`), KEY `company_idx` (`company_fkey`), KEY `filaccdx` (`file_accepted`), KEY `beendx` (`been_evaluated`), KEY `fidadx` (`file_date`), KEY `upevdx` (`uport_evaluated`), KEY `crdadx` (`create_date`), KEY `hiredx` (`history_record`), KEY `accession_number` (`accession_number`), KEY `fofkdx` (`form_fkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | Procedure used to execute conversion: root 27686 0.0 0.2 5840 3224 ?S 14:08:23 0:00 mysql -pxx xxx -e insert into DAILY_EDGAR_INNODB select * from DAILY_EDGAR my.cnf InnoDB section: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /export/home/mysqldata/ibdata innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /export/home/mysqldata/ibdata innodb_log_arch_dir = /export/home/mysqldata/ibdata # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 50M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 100M #innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Best, Mikhail Berman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB conversion help
Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-+- -+ | Variable_name | Value | +-+- -+ | innodb_additional_mem_pool_size | 52428800 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 1073741824 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir| /export/home/mysqldata/ibdata | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/home/mysqldata/ibdata | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/home/mysqldata/ibdata | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-+- -+ Best, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:05 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB conversion help If you are do this in MySQL 5, try this: ALTER TABLE table-name ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables structure: DAILY_EDGAR_INNODB |CREATE TABLE `DAILY_EDGAR_INNODB` ( `ftp_file_name_key` char(80) NOT NULL default '', `history_record` int(1) NOT NULL default '0', `description` char(100) NOT NULL default '', `company_fkey` char(10) NOT NULL default '', `company_name` char(100) NOT NULL default '', `subject_company_fkey` char(10) NOT NULL default '', `filer_description` char(10) NOT NULL default '', `form_fkey` char(20) NOT NULL default '', `file_accepted` char(20) NOT NULL default '', `been_evaluated` char(20) NOT NULL default '', `uport_evaluated` int(1) NOT NULL default '0', `file_date` char(10) NOT NULL default '', `file_size` char(10) NOT NULL default '50 KB', `accession_number` char(24) NOT NULL default '', `http_file_name_html` char(100) NOT NULL default '', `http_file_name_text` char(100) NOT NULL default '', `create_date` date NOT NULL default '-00-00', `change_date` date NOT NULL default '-00-00', PRIMARY KEY (`ftp_file_name_key`), KEY `company_idx` (`company_fkey`), KEY `filaccdx` (`file_accepted`), KEY `beendx` (`been_evaluated`), KEY `fidadx` (`file_date`), KEY `upevdx` (`uport_evaluated`), KEY `crdadx` (`create_date`), KEY `hiredx` (`history_record`), KEY `accession_number` (`accession_number`), KEY `fofkdx` (`form_fkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | Procedure used to execute conversion: root 27686 0.0 0.2 5840 3224 ?S 14
Re: MyISAM vs InnoDB
Francis wrote: Question about MyISAM vs InnoDB ? What is the best to use, I have a large table contain around 10 millons of records. What is the best for me ? Use MyISAM or InnoDB ? Depends VERY much on your application. If any concurrency and/or durability is required then I would forget about MyISAM, as this is not ACID and integrity of the data is at risk. In fact, if the application is suitable for MyISAM and database could be embedded (runs on same machine as application) then I would probably consider SQLite as that is even faster. If concurrency and scaleability is required then I would go PostgreSQL rather tham MySQL, expecially if a large number of heavy users are on at the same time. For a web-based solution on a machine with a single processor/core then InnoDB is a strong contender. Eddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Francis wrote: Question about MyISAM vs InnoDB ? What is the best to use, I have a large table contain around 10 millons of records. What is the best for me ? Use MyISAM or InnoDB ? Depends VERY much on your application. If any concurrency and/or durability is required then I would forget about MyISAM, as this is not ACID and integrity of the data is at risk. In fact, if the application is suitable for MyISAM and database could be embedded (runs on same machine as application) then I would probably consider SQLite as that is even faster. If concurrency and scaleability is required then I would go PostgreSQL rather tham MySQL, expecially if a large number of heavy users are on at the same time. For a web-based solution on a machine with a single processor/core then InnoDB is a strong contender. Eddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB conversion help
Check these variable bulk_insert_buffer_size (Default usually 8M) innodb_buffer_pool_size (Default usually 8M) - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: Rolando Edwards [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:13:44 AM GMT-0500 US/Eastern Subject: RE: MyISAM to InnoDB conversion help Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-+- -+ | Variable_name | Value | +-+- -+ | innodb_additional_mem_pool_size | 52428800 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 1073741824 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir| /export/home/mysqldata/ibdata | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/home/mysqldata/ibdata | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/home/mysqldata/ibdata | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-+- -+ Best, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:05 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB conversion help If you are do this in MySQL 5, try this: ALTER TABLE table-name ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables structure: DAILY_EDGAR_INNODB |CREATE TABLE `DAILY_EDGAR_INNODB` ( `ftp_file_name_key` char(80) NOT NULL default '', `history_record` int(1) NOT NULL default '0', `description` char(100) NOT NULL default '', `company_fkey` char(10) NOT NULL default '', `company_name` char(100) NOT NULL default '', `subject_company_fkey` char(10) NOT NULL default '', `filer_description` char(10) NOT NULL default '', `form_fkey` char(20) NOT NULL default '', `file_accepted` char(20) NOT NULL default '', `been_evaluated` char(20) NOT NULL default '', `uport_evaluated` int(1) NOT NULL default '0', `file_date` char(10) NOT NULL default '', `file_size` char(10) NOT NULL default '50 KB', `accession_number` char(24) NOT NULL default '', `http_file_name_html` char(100) NOT NULL default '', `http_file_name_text` char(100) NOT NULL default '', `create_date` date NOT NULL default '-00-00', `change_date` date NOT NULL default '-00-00', PRIMARY KEY (`ftp_file_name_key`), KEY `company_idx` (`company_fkey`), KEY `filaccdx` (`file_accepted`), KEY `beendx
Re: MyISAM to InnoDB conversion help
I just noticed your innodb_data_file_path You have a shared InnoDB tablespace That can be murder on a MySQL Server You may want to separate each InnoDB into a separate file Here are the steps needed to separate InnoDB tables. 1) Do a mysqldump on your database to mydata.sql. 2) Shutdown MySQL 3) Goto my.cnf and add 'innodb_file_per_table' in the [mysqld] section 4) Change in my.cnf : innodb_data_file_path to ibdata1:10M:autoextend Note: You may want add this too : bulk_insert_buffer_size = 256M 5) Delete ibdata1, ibdata2, and the ib_logfile* files 6) Restart MySQL (the innodb data files and log will regenerate) 7) Run MySQL using the script mydata.sql All InnoDB data will be sitting in separate .ibd files in the database folder. Only the data dictionary info for all InnoDB tables will be sitting in the ibdata1 file. Give it a try. - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: Mikhail Berman [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:24:00 AM GMT-0500 US/Eastern Subject: Re: MyISAM to InnoDB conversion help Check these variable bulk_insert_buffer_size (Default usually 8M) innodb_buffer_pool_size (Default usually 8M) - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: Rolando Edwards [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:13:44 AM GMT-0500 US/Eastern Subject: RE: MyISAM to InnoDB conversion help Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-+- -+ | Variable_name | Value | +-+- -+ | innodb_additional_mem_pool_size | 52428800 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 1073741824 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir| /export/home/mysqldata/ibdata | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/home/mysqldata/ibdata | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/home/mysqldata/ibdata | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-+- -+ Best, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:05 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB conversion help If you are do this in MySQL 5, try this: ALTER TABLE table-name ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables
RE: MyISAM to InnoDB conversion help
Great, Thank you for your help Rolando, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:41 AM Cc: mysql@lists.mysql.com; Mikhail Berman Subject: Re: MyISAM to InnoDB conversion help I just noticed your innodb_data_file_path You have a shared InnoDB tablespace That can be murder on a MySQL Server You may want to separate each InnoDB into a separate file Here are the steps needed to separate InnoDB tables. 1) Do a mysqldump on your database to mydata.sql. 2) Shutdown MySQL 3) Goto my.cnf and add 'innodb_file_per_table' in the [mysqld] section 4) Change in my.cnf : innodb_data_file_path to ibdata1:10M:autoextend Note: You may want add this too : bulk_insert_buffer_size = 256M 5) Delete ibdata1, ibdata2, and the ib_logfile* files 6) Restart MySQL (the innodb data files and log will regenerate) 7) Run MySQL using the script mydata.sql All InnoDB data will be sitting in separate .ibd files in the database folder. Only the data dictionary info for all InnoDB tables will be sitting in the ibdata1 file. Give it a try. - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: Mikhail Berman [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:24:00 AM GMT-0500 US/Eastern Subject: Re: MyISAM to InnoDB conversion help Check these variable bulk_insert_buffer_size (Default usually 8M) innodb_buffer_pool_size (Default usually 8M) - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: Rolando Edwards [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:13:44 AM GMT-0500 US/Eastern Subject: RE: MyISAM to InnoDB conversion help Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-+- -+ | Variable_name | Value | +-+- -+ | innodb_additional_mem_pool_size | 52428800 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 1073741824 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir| /export/home/mysqldata/ibdata | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/home/mysqldata/ibdata | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/home/mysqldata/ibdata | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-+- -+ Best, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:05 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB conversion help If you are do this in MySQL 5, try this: ALTER TABLE table-name ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected
Re: MyISAM vs InnoDB
At 09:35 AM 11/1/2006, Martijn Tonies wrote: MyISAM vs InnoDB ? What is the best to use Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not completely free (needs a proprietary backup tool); BDB, which is deprecated until further notices; and SolidDB, which is still β. Choose your evil. Ok, so your solution is to use something else? Is there a better open source database out there for that amount of data? Firebird? PostgreSQL? Both are open source and ALWAYS free for whatever usuage, no dual licensing whatsoever. Martijn, Sure, I've thought of those too. But has anyone gotten Firebird to store 700-800gb tables? Can you split Firebird's .gdb file across drives? The main problem with tables of that size is maintaining the index. My upper limit for MySQL is 100 million rows. After that any new rows that are added will take much longer to add because the index tree has to be maintained. I definitely recommend cramming as much memory in the box as humanly possible because indexes of that size will need it. Probably the simplist solution for MySQL is to use Merge tables. I know some people with MySQL, Oracle and MS SQL have terabyte tables, but I haven't heard of other databases storing tables that large. So if you or anyone else has used FireBird or PostgreSQL to store terabyte tables, I'd certainly would be interested in hearing about it. :) Mike Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On 11/1/06, mos wrote: Sure, I've thought of those too. But has anyone gotten Firebird to store 700-800gb tables? Can you split Firebird's .gdb file across drives? The main problem with tables of that size is maintaining the index. My upper limit for MySQL is 100 million rows. After that any new rows that are added will take much longer to add because the index tree has to be maintained. I definitely recommend cramming as much memory in the box as humanly possible because indexes of that size will need it. Probably the simplist solution for MySQL is to use Merge tables. I know some people with MySQL, Oracle and MS SQL have terabyte tables, but I haven't heard of other databases storing tables that large. So if you or anyone else has used FireBird or PostgreSQL to store terabyte tables, I'd certainly would be interested in hearing about it. :) What is the big deal of a TB? Now, if you get past 20 TB you might want to team up with one of the commercial PostgreSQL supporters (Fujitsu, EnterpriseDB, Greenplum etc.), but Sun even sells appliances for 100 TB PostgreSQL databases. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
At 02:27 PM 11/1/2006, Jochem van Dieten wrote: On 11/1/06, mos wrote: Sure, I've thought of those too. But has anyone gotten Firebird to store 700-800gb tables? Can you split Firebird's .gdb file across drives? The main problem with tables of that size is maintaining the index. My upper limit for MySQL is 100 million rows. After that any new rows that are added will take much longer to add because the index tree has to be maintained. I definitely recommend cramming as much memory in the box as humanly possible because indexes of that size will need it. Probably the simplist solution for MySQL is to use Merge tables. I know some people with MySQL, Oracle and MS SQL have terabyte tables, but I haven't heard of other databases storing tables that large. So if you or anyone else has used FireBird or PostgreSQL to store terabyte tables, I'd certainly would be interested in hearing about it. :) What is the big deal of a TB? Now, if you get past 20 TB you might want to team up with one of the commercial PostgreSQL supporters (Fujitsu, EnterpriseDB, Greenplum etc.), but Sun even sells appliances for 100 TB PostgreSQL databases. Jochem Jochem, There is a big difference between a 20 TB database and a 20 TB table! Unless you're storing huge blobs, a table of over 1TB will have hundreds of millions of rows (billions?), and that means huge index trees that need to be maintained. If PostgreSQL can put 20 TB into a table and still have reasonably fast inserts and queries, then I'll take my hat off to them. But first I need to see proof that they can accomplish this. So if you have any sites or white papers you'd like to share, go ahead. Keep in mind we're talking about TB tables here, not databases. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On 11/1/06, mos wrote: At 02:27 PM 11/1/2006, Jochem van Dieten wrote: What is the big deal of a TB? Now, if you get past 20 TB you might want to team up with one of the commercial PostgreSQL supporters (Fujitsu, EnterpriseDB, Greenplum etc.), but Sun even sells appliances for 100 TB PostgreSQL databases. There is a big difference between a 20 TB database and a 20 TB table! Unless you're storing huge blobs, a table of over 1TB will have hundreds of millions of rows (billions?), and that means huge index trees that need to be maintained. Indexes scale with ln(O). The difference between 100 million rows and 1 billion rows is maybe 10%. And if you are worried about your indexes getting asymmetric use a hash index instead of a B-tree. Though realistically you would partition the data and then your indexes get partitioned too. If PostgreSQL can put 20 TB into a table and still have reasonably fast inserts and queries, then I'll take my hat off to them. It can if you design your queries to make use of the indexes and the partitioning. But first I need to see proof that they can accomplish this. So if you have any sites or white papers you'd like to share, go ahead. Keep in mind we're talking about TB tables here, not databases. Google the PostgreSQL and PostGIS mailinglists. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM vs InnoDB
Hi list, Question about MyISAM vs InnoDB ? What is the best to use, I have a large table contain around 10 millons of records. What is the best for me ? Use MyISAM or InnoDB ? Ty for reply ☺ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM vs InnoDB
Hello, Although the number of records is a consideration to weigh in your decision, there are many other (perhaps more important) factors to consider. For example, do you need foreign keys? transactions? row-level locks?...then InnoDB is your choice. Perhaps with more details concerning the characteristics of the data and your applications requirements, folks may be able to better help you with a design choice. Storage limits, efficiency in how space and memory is used, bulk insert speed, etc. might be other factors to consider. Take a look at: http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html Thanks, Jimmy Guerrero MySQL, Inc -Original Message- From: Francis [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 31, 2006 2:25 PM To: mysql@lists.mysql.com Subject: MyISAM vs InnoDB Hi list, Question about MyISAM vs InnoDB ? What is the best to use, I have a large table contain around 10 millons of records. What is the best for me ? Use MyISAM or InnoDB ? Ty for reply ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing engines, MyISAM to InnoDB Heelp
most of my Storage enines is MyISAM i wanna change them all to|InnoDB how do i do this ?thanks | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing engines, MyISAM to InnoDB Heelp
most of my Storage enines is MyISAM i wanna change them all to|InnoDB how do i do this ? ALTER TABLE tblname ENGINE=innodb; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing engines, MyISAM to InnoDB Heelp
Brian, you can also set a default storage engine in your mysql config file (my.cnf) to ensure future tables get created with the desired storage engine (unless otherwise specified in your create statement). From http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html If you omit the ENGINE or TYPE option, the default storage engine is used. Normally, this is MyISAM, but you can change it by using the --default-storage-engine or --default-table-type server startup option, or by setting the default-storage-engine or default-table-type option in the my.cnf configuration file. You can set the default storage engine to be used during the current session by setting the storage_engine or table_type variable: SET storage_engine=MYISAM; SET table_type=BDB; When MySQL is installed on Windows using the MySQL Configuration Wizard, the InnoDB storage engine can be selected as the default instead of MyISAM. See Section 2.3.4.6, The Database Usage Dialog. To convert a table from one storage engine to another, use an ALTER TABLE statement that indicates the new engine: ALTER TABLE t ENGINE = MYISAM; ALTER TABLE t TYPE = BDB; Best, Dan On 8/18/06, Dominik Klein [EMAIL PROTECTED] wrote: most of my Storage enines is MyISAM i wanna change them all to|InnoDB how do i do this ? ALTER TABLE tblname ENGINE=innodb; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row count discrepancy when converting from MyISAM to InnoDB
Hi, Instead of using select count(*) from tablename You can try with show table status like 'tablename' This doesn't takes much longer time. Thanks Regards Dilipkumar - Original Message - From: David Hillman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 11:51 PM Subject: Re: Row count discrepancy when converting from MyISAM to InnoDB On Jul 25, 2006, at 11:55 AM, Frank wrote: Why is the record count so low after conversion to InnoDB? Who should I believe: InnoDB or MyISAM? Any ideas as to what can be done to avoid loss of this many rows? InnoDB doesn't keep a count on number of rows, like MyISAM does. InnoDB only maintains an estimate of the number of rows in each table. This is why select count(*) from table takes a long time on big InnoDB tables. Usually the InnoDB count will be off by 50% or so. http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html -- David Hillman LiveText, Inc 1.866.LiveText x235 ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row count discrepancy when converting from MyISAM to InnoDB
Yes your right dilip , but it wont help for INNODB . INNODB , Rows ( show table status\G ) value is an approximation, and may vary from the actual value .Since innodb doesnt keep track on record count For innodb use |SELECT COUNT(*)| to obtain an accurate count.Correct me if iam wrong - Praj Dilipkumar wrote: Hi, Instead of using select count(*) from tablename You can try with show table status like 'tablename' This doesn't takes much longer time. Thanks Regards Dilipkumar - Original Message - From: David Hillman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 25, 2006 11:51 PM Subject: Re: Row count discrepancy when converting from MyISAM to InnoDB On Jul 25, 2006, at 11:55 AM, Frank wrote: Why is the record count so low after conversion to InnoDB? Who should I believe: InnoDB or MyISAM? Any ideas as to what can be done to avoid loss of this many rows? InnoDB doesn't keep a count on number of rows, like MyISAM does. InnoDB only maintains an estimate of the number of rows in each table. This is why select count(*) from table takes a long time on big InnoDB tables. Usually the InnoDB count will be off by 50% or so. http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html -- David Hillman LiveText, Inc 1.866.LiveText x235 ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com
Row count discrepancy when converting from MyISAM to InnoDB
I have a table of type MyISAM that is reporting 47 million rows when I do a SELECT COUNT(*). When I convert this table to InnoDB, running a SELECT COUNT(*) returns only 19 million rows. The conversion confirms 19 million rows were inserted and reports no warnings or duplicates. I have done the conversion to InnoDB using the following ways 1. by dumping all the data in a text file and loading it. 2. by using ALTER TABLE Why is the record count so low after conversion to InnoDB? Who should I believe: InnoDB or MyISAM? Any ideas as to what can be done to avoid loss of this many rows? I will be posting output from my latest conversion attempt in some time. --Frank
Re: Row count discrepancy when converting from MyISAM to InnoDB
On Jul 25, 2006, at 11:55 AM, Frank wrote: Why is the record count so low after conversion to InnoDB? Who should I believe: InnoDB or MyISAM? Any ideas as to what can be done to avoid loss of this many rows? InnoDB doesn't keep a count on number of rows, like MyISAM does. InnoDB only maintains an estimate of the number of rows in each table. This is why select count(*) from table takes a long time on big InnoDB tables. Usually the InnoDB count will be off by 50% or so. http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html -- David Hillman LiveText, Inc 1.866.LiveText x235
Re: Row count discrepancy when converting from MyISAM to InnoDB
Thank you to everyone who replied. It turned out I had index corruption and after running an OPTIMIZE TABLE I was able to convert all the records to InnoDB. Thanks, Frank
Re: ERROR 1114 (HY000): The table is full converting a big table from MyISAM to InnoDB on 5.0.18
Patrick, people have created files at least up to 500 GB using InnoDB's auto-extend feature. What does: ulimit -a say about the 'file size' of the user running mysqld? Have you put some disk space quotas on the directories of the MySQL datadir? Please correct me if I am wrong, but I think one can restrict how much disk space a directory can use in Linux. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Patrick Herber [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, January 15, 2006 4:16 PM Subject: RE: ERROR 1114 (HY000): The table is full converting a big table from MyISAM to InnoDB on 5.0.18 Thanks a lot for your answer! However, when I used the option innodb_file_per_table I saw that the = temp file (#sql...) was created in my DB directory and on this partition I = still have plenty of space (more than 200GB). Do you think I CAN'T use this option for such a big table and I have to = use innodb_data_file_path? Thanks a lot and regards, Patrick -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED] Sent: Sunday, 15 January 2006 15:09 To: Patrick Herber Cc: mysql@lists.mysql.com Subject: Re: ERROR 1114 (HY000): The table is full converting=20 a big table from MyISAM to InnoDB on 5.0.18 =20 Hi, =20 I think you should change the tmpdir variable value to a=20 directory which have enough room to create your temp big table (by default,=20 it points to /tmp dir). =20 Regards, Jocelyn =20 Patrick Herber a =E9crit : Hello! I have a database with a big table (Data File 45 GB, Index=20 File 30 GB).=20 Since I have some performance troubles with table-locking in a=20 multi-user environment (when one of them performs a complex=20 query all=20 the other have to wait up to 1 minute, which is not very=20 nice...), I=20 would like to convert this (and other tables) into InnoDB engine. =20 I first tried using the innodb_file_per_table option but=20 when running=20 the statement =20 ALTER TABLE invoice ENGINE=3DINNODB; =20 ERROR 1114 (HY000): The table '#sql...' is full =20 (this about one our after the start of the command, when=20 the size of=20 the file was bigger than ca. 70GB (I don't know exactly the size)) =20 I tried then without the innodb_file_per_table option, setting my=20 innodb_data_file_path as follows: =20 =20 = innodb_data_file_path=3Dibdata1:500M;ibdata2:500M;ibdata3;500M;ibdata4:5 00M;ib=20 =20 data5:500M;ibdata6:500M;ibdata7:500M;ibdata8:500M;ibdata9:500M;ibdata1 0:500M :autoextend =20 Also in this case I got the same error message. =20 What should I do in order to convert this table? =20 Should I set in the innodb_data_file_path for example 50=20 Files, each=20 big 4GB ? =20 Thanks a lot for your help. =20 Best regards, Patrick =20 PS: I'm running MySQL 5.0.18 on a Linux 2.6.13-15.7-smp server. =20 =20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: =20 http://lists.mysql.com/[EMAIL PROTECTED] =20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1114 (HY000): The table is full converting a big table from MyISAM to InnoDB on 5.0.18
Hello! I have a database with a big table (Data File 45 GB, Index File 30 GB). Since I have some performance troubles with table-locking in a multi-user environment (when one of them performs a complex query all the other have to wait up to 1 minute, which is not very nice...), I would like to convert this (and other tables) into InnoDB engine. I first tried using the innodb_file_per_table option but when running the statement ALTER TABLE invoice ENGINE=INNODB; ERROR 1114 (HY000): The table '#sql...' is full (this about one our after the start of the command, when the size of the file was bigger than ca. 70GB (I don't know exactly the size)) I tried then without the innodb_file_per_table option, setting my innodb_data_file_path as follows: innodb_data_file_path=ibdata1:500M;ibdata2:500M;ibdata3;500M;ibdata4:500M;ib data5:500M;ibdata6:500M;ibdata7:500M;ibdata8:500M;ibdata9:500M;ibdata10:500M :autoextend Also in this case I got the same error message. What should I do in order to convert this table? Should I set in the innodb_data_file_path for example 50 Files, each big 4GB ? Thanks a lot for your help. Best regards, Patrick PS: I'm running MySQL 5.0.18 on a Linux 2.6.13-15.7-smp server.
Re: ERROR 1114 (HY000): The table is full converting a big table from MyISAM to InnoDB on 5.0.18
Hi, I think you should change the tmpdir variable value to a directory which have enough room to create your temp big table (by default, it points to /tmp dir). Regards, Jocelyn Patrick Herber a écrit : Hello! I have a database with a big table (Data File 45 GB, Index File 30 GB). Since I have some performance troubles with table-locking in a multi-user environment (when one of them performs a complex query all the other have to wait up to 1 minute, which is not very nice...), I would like to convert this (and other tables) into InnoDB engine. I first tried using the innodb_file_per_table option but when running the statement ALTER TABLE invoice ENGINE=INNODB; ERROR 1114 (HY000): The table '#sql...' is full (this about one our after the start of the command, when the size of the file was bigger than ca. 70GB (I don't know exactly the size)) I tried then without the innodb_file_per_table option, setting my innodb_data_file_path as follows: innodb_data_file_path=ibdata1:500M;ibdata2:500M;ibdata3;500M;ibdata4:500M;ib data5:500M;ibdata6:500M;ibdata7:500M;ibdata8:500M;ibdata9:500M;ibdata10:500M :autoextend Also in this case I got the same error message. What should I do in order to convert this table? Should I set in the innodb_data_file_path for example 50 Files, each big 4GB ? Thanks a lot for your help. Best regards, Patrick PS: I'm running MySQL 5.0.18 on a Linux 2.6.13-15.7-smp server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
