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]
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: 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]
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 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 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]
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]
Re: MyIsam Vs InnoDB
Hello. innodb_log_file_size=10M innodb_log_buffer_size=1M These variables have too small values, increase them. Follow other recomendations from: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html Andrew stolarz [EMAIL PROTECTED] wrote: hello, here are my current setttings: # MySQL Server Instance Configuration File # -- # Generated by the MySQL Server Instance Configuration Wizard # # # Installation Instructions # -- # # On Linux you can copy this file to /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options # (@localstatedir@ for this installation) or to # ~/.my.cnf to set user-specific options. # # On Windows you should keep this file in the installation directory # of your server (e.g. C:\Program Files\MySQL\MySQL Server 4.1). To # make sure the server reads the config file use the startup option # --defaults-file. # # To run run the server from the command line, execute this in a # command line shell, e.g. # mysqld --defaults-file=C:\Program Files\MySQL\MySQL Server 4.1\my.ini # # To install the server as a Windows service manually, execute this in a # command line shell, e.g. # mysqld --install MySQL41 --defaults-file=C:\Program Files\MySQL\MySQL Server 4.1\my.ini # # And then execute this in a command line shell to start the server, e.g. # net start MySQL41 # # # Guildlines for editing this file # -- # # In this file, you can use all long options that the program supports. # If you want to know the options a program supports, start the program # with the --help option. # # More detailed information about the individual options can also be # found in the manual. # # # CLIENT SECTION # -- # # The following options will be read by MySQL client applications. # Note that only client applications shipped by MySQL are guaranteed # to read this section. If you want your own MySQL client program to # honor these values, you need to specify it as an option during the # MySQL client library initialization. # [client] port=3306 [mysql] default-character-set=latin1 # SERVER SECTION # -- # # The following options will be read by the MySQL Server. Make sure that # you have installed the server correctly (see above) so it reads this # file. # [mysqld] # The TCP/IP Port the MySQL Server will listen on port=3306 #Path to installation directory. All paths are usually resolved relative to this. basedir=C:/Program Files/MySQL/MySQL Server 5.0/ #Path to the database root datadir=C:/Program Files/MySQL/MySQL Server 5.0/Data/ # The default character set that will be used when a new schema or table is # created and no character set is defined default-character-set=latin1 # The default storage engine that will be used when create new tables when default-storage-engine=innodb # Set the SQL mode to strict sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # The maximum amount of concurrent sessions the MySQL server will # allow. One of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. max_connections=5 # Query cache is used to cache SELECT results and later return them # without actual executing the same query once again. Having the query # cache enabled may result in significant speed improvements, if your # have a lot of identical queries and rarely changing tables. See the # Qcache_lowmem_prunes status variable to check if the current value # is high enough for your load. # Note: In case your tables change very often or if your queries are # textually different every time, the query cache may result in a # slowdown instead of a performance improvement. query_cache_size=0 # The number of open tables for all threads. Increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable open-files-limit in # section [mysqld_safe] table_cache=256 # Maximum size for internal (in-memory) temporary tables. If a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. There can be many # of them. tmp_table_size=9M # How many threads we should keep in a cache for reuse. When a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. This greatly reduces # the amount of thread creations needed if
Re: MyIsam Vs InnoDB
Hello. Without seeing at least your configuration it is difficult to say what's going on. Please, provide your config file. Andrew stolarz [EMAIL PROTECTED] wrote: When I do a bulk import into a MyIsam engine database, I can reach about 2-3 thousand records imported per second. However when I use the InnoDB engine, I am only importing about 30-50 records per second? Am I missing something here? its a P4 3 Ghz machine with 1024mb ram. running MySQL 5.0 thanks Andrew -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.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
hello, here are my current setttings: # MySQL Server Instance Configuration File # -- # Generated by the MySQL Server Instance Configuration Wizard # # # Installation Instructions # -- # # On Linux you can copy this file to /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options # (@localstatedir@ for this installation) or to # ~/.my.cnf to set user-specific options. # # On Windows you should keep this file in the installation directory # of your server (e.g. C:\Program Files\MySQL\MySQL Server 4.1). To # make sure the server reads the config file use the startup option # --defaults-file. # # To run run the server from the command line, execute this in a # command line shell, e.g. # mysqld --defaults-file=C:\Program Files\MySQL\MySQL Server 4.1\my.ini # # To install the server as a Windows service manually, execute this in a # command line shell, e.g. # mysqld --install MySQL41 --defaults-file=C:\Program Files\MySQL\MySQL Server 4.1\my.ini # # And then execute this in a command line shell to start the server, e.g. # net start MySQL41 # # # Guildlines for editing this file # -- # # In this file, you can use all long options that the program supports. # If you want to know the options a program supports, start the program # with the --help option. # # More detailed information about the individual options can also be # found in the manual. # # # CLIENT SECTION # -- # # The following options will be read by MySQL client applications. # Note that only client applications shipped by MySQL are guaranteed # to read this section. If you want your own MySQL client program to # honor these values, you need to specify it as an option during the # MySQL client library initialization. # [client] port=3306 [mysql] default-character-set=latin1 # SERVER SECTION # -- # # The following options will be read by the MySQL Server. Make sure that # you have installed the server correctly (see above) so it reads this # file. # [mysqld] # The TCP/IP Port the MySQL Server will listen on port=3306 #Path to installation directory. All paths are usually resolved relative to this. basedir=C:/Program Files/MySQL/MySQL Server 5.0/ #Path to the database root datadir=C:/Program Files/MySQL/MySQL Server 5.0/Data/ # The default character set that will be used when a new schema or table is # created and no character set is defined default-character-set=latin1 # The default storage engine that will be used when create new tables when default-storage-engine=innodb # Set the SQL mode to strict sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # The maximum amount of concurrent sessions the MySQL server will # allow. One of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. max_connections=5 # Query cache is used to cache SELECT results and later return them # without actual executing the same query once again. Having the query # cache enabled may result in significant speed improvements, if your # have a lot of identical queries and rarely changing tables. See the # Qcache_lowmem_prunes status variable to check if the current value # is high enough for your load. # Note: In case your tables change very often or if your queries are # textually different every time, the query cache may result in a # slowdown instead of a performance improvement. query_cache_size=0 # The number of open tables for all threads. Increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable open-files-limit in # section [mysqld_safe] table_cache=256 # Maximum size for internal (in-memory) temporary tables. If a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. There can be many # of them. tmp_table_size=9M # How many threads we should keep in a cache for reuse. When a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. This greatly reduces # the amount of thread creations needed if you have a lot of new # connections. (Normally this doesn't give a notable performance # improvement if you have a good thread implementation.) thread_cache_size=8 #*** MyISAM Specific options # The maximum size of the temporary file MySQL is allowed to use while # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE. # If the file-size would be bigger than this, the index
Re: MyIsam Vs InnoDB
Is your database connection auto-commit? MyISAM commits everything at once, where InnoDB you can commit whenever you want. You might want to commit at the end of your batch. Also, look at your indexes - indexes make selects fast, but slow down inserts and deletes, and can slow down updates in some situations. David Andrew stolarz wrote: hello, here are my current setttings: # MySQL Server Instance Configuration File # -- # Generated by the MySQL Server Instance Configuration Wizard # # # Installation Instructions # -- # # On Linux you can copy this file to /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options # (@localstatedir@ for this installation) or to # ~/.my.cnf to set user-specific options. # # On Windows you should keep this file in the installation directory # of your server (e.g. C:\Program Files\MySQL\MySQL Server 4.1). To # make sure the server reads the config file use the startup option # --defaults-file. # # To run run the server from the command line, execute this in a # command line shell, e.g. # mysqld --defaults-file=C:\Program Files\MySQL\MySQL Server 4.1\my.ini # # To install the server as a Windows service manually, execute this in a # command line shell, e.g. # mysqld --install MySQL41 --defaults-file=C:\Program Files\MySQL\MySQL Server 4.1\my.ini # # And then execute this in a command line shell to start the server, e.g. # net start MySQL41 # # # Guildlines for editing this file # -- # # In this file, you can use all long options that the program supports. # If you want to know the options a program supports, start the program # with the --help option. # # More detailed information about the individual options can also be # found in the manual. # # # CLIENT SECTION # -- # # The following options will be read by MySQL client applications. # Note that only client applications shipped by MySQL are guaranteed # to read this section. If you want your own MySQL client program to # honor these values, you need to specify it as an option during the # MySQL client library initialization. # [client] port=3306 [mysql] default-character-set=latin1 # SERVER SECTION # -- # # The following options will be read by the MySQL Server. Make sure that # you have installed the server correctly (see above) so it reads this # file. # [mysqld] # The TCP/IP Port the MySQL Server will listen on port=3306 #Path to installation directory. All paths are usually resolved relative to this. basedir=C:/Program Files/MySQL/MySQL Server 5.0/ #Path to the database root datadir=C:/Program Files/MySQL/MySQL Server 5.0/Data/ # The default character set that will be used when a new schema or table is # created and no character set is defined default-character-set=latin1 # The default storage engine that will be used when create new tables when default-storage-engine=innodb # Set the SQL mode to strict sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # The maximum amount of concurrent sessions the MySQL server will # allow. One of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. max_connections=5 # Query cache is used to cache SELECT results and later return them # without actual executing the same query once again. Having the query # cache enabled may result in significant speed improvements, if your # have a lot of identical queries and rarely changing tables. See the # Qcache_lowmem_prunes status variable to check if the current value # is high enough for your load. # Note: In case your tables change very often or if your queries are # textually different every time, the query cache may result in a # slowdown instead of a performance improvement. query_cache_size=0 # The number of open tables for all threads. Increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable open-files-limit in # section [mysqld_safe] table_cache=256 # Maximum size for internal (in-memory) temporary tables. If a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. There can be many # of them. tmp_table_size=9M # How many threads we should keep in a cache for reuse. When a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. This greatly reduces # the amount of thread creations needed if you have a lot of new # connections.
Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
Eamon Daly [EMAIL PROTECTED] wrote on 08/24/2005 12:40:55 PM: We have a table containing just one column that we use for unique IDs: CREATE TABLE `id_sequence` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) TYPE=MyISAM Watching 'SHOW FULL PROCESSLIST' and reading the slow query log shows the occasional backlog of locks. Has anyone found significant speed increases or better concurrency by switching over to InnoDB for such a table? Eamon Daly So long as you do not have any deletion gaps in your data, there shouldn't be any read locks on this table even while you are appending new records. Are you locking against reads or writes. BTW - is there a great reason why you are generating auto_incremented ID values separately from the actual data they identify? I ask this because I don't think many people actually USE that kind of table so you may not get any responses from your last question. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
I'm not doing any explicit locking-- these are just straight INSERTs, and there are no gaps in the table (This is MySQL 4.0.20-standard-log, by the way). As for why, there are several tables which share this PK, each of which is heavily UPDATEd, and most make use of INSERT DELAYED. This can't be entirely uncommon: I'm pretty sure I've seen a similar example in the Cookbook. Eamon Daly - Original Message - From: [EMAIL PROTECTED] To: Eamon Daly [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, August 24, 2005 12:05 PM Subject: Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table Eamon Daly [EMAIL PROTECTED] wrote on 08/24/2005 12:40:55 PM: We have a table containing just one column that we use for unique IDs: CREATE TABLE `id_sequence` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) TYPE=MyISAM Watching 'SHOW FULL PROCESSLIST' and reading the slow query log shows the occasional backlog of locks. Has anyone found significant speed increases or better concurrency by switching over to InnoDB for such a table? Eamon Daly So long as you do not have any deletion gaps in your data, there shouldn't be any read locks on this table even while you are appending new records. Are you locking against reads or writes. BTW - is there a great reason why you are generating auto_incremented ID values separately from the actual data they identify? I ask this because I don't think many people actually USE that kind of table so you may not get any responses from your last question. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
If you have any deletion gaps in your data, the SQL engine puts a full TABLE LOCK on your table while performing inserts to a MyISAM table. You don't have to do it, the engine does it for you. As to your application design, all I am going to say is that if it works for you, that's fine by me. I probably wouldn't pick that design for myself but you seem to have it working so I will butt out. As to relieving your locking contentions on this table, switching to InnoDB may help but you should try the OPTIMIZE TABLE command first to collapse out any deletion gaps. The only way you will know for sure is to benchmark both methods. Stick with what works best for your data, on your hardware, and under your loading. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Eamon Daly [EMAIL PROTECTED] wrote on 08/24/2005 01:49:07 PM: I'm not doing any explicit locking-- these are just straight INSERTs, and there are no gaps in the table (This is MySQL 4.0.20-standard-log, by the way). As for why, there are several tables which share this PK, each of which is heavily UPDATEd, and most make use of INSERT DELAYED. This can't be entirely uncommon: I'm pretty sure I've seen a similar example in the Cookbook. Eamon Daly - Original Message - From: [EMAIL PROTECTED] To: Eamon Daly [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, August 24, 2005 12:05 PM Subject: Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table Eamon Daly [EMAIL PROTECTED] wrote on 08/24/2005 12:40:55 PM: We have a table containing just one column that we use for unique IDs: CREATE TABLE `id_sequence` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) TYPE=MyISAM Watching 'SHOW FULL PROCESSLIST' and reading the slow query log shows the occasional backlog of locks. Has anyone found significant speed increases or better concurrency by switching over to InnoDB for such a table? Eamon Daly So long as you do not have any deletion gaps in your data, there shouldn't be any read locks on this table even while you are appending new records. Are you locking against reads or writes. BTW - is there a great reason why you are generating auto_incremented ID values separately from the actual data they identify? I ask this because I don't think many people actually USE that kind of table so you may not get any responses from your last question. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
Hello. If you have lots of concurrent updates and selects on the same table, InnoDB usually has better performance. Use the benchmarks to determine what configuration is preferred. Super-smack for example allows you to write very flexible tests. Be aware of different behavior of AUTO_INCREMENT columns in InnoDB. See: http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html Eamon Daly [EMAIL PROTECTED] wrote: We have a table containing just one column that we use for unique IDs: CREATE TABLE `id_sequence` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) TYPE=MyISAM Watching 'SHOW FULL PROCESSLIST' and reading the slow query log shows the occasional backlog of locks. Has anyone found significant speed increases or better concurrency by switching over to InnoDB for such a table? Eamon Daly -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.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 for an AUTO_INCREMENT counter table
Eamon Daly wrote: We have a table containing just one column that we use for unique IDs: CREATE TABLE `id_sequence` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) TYPE=MyISAM Watching 'SHOW FULL PROCESSLIST' and reading the slow query log shows the occasional backlog of locks. Has anyone found significant speed increases or better concurrency by switching over to InnoDB for such a table? I've found that MS Access will produce this behaviour ( among others ) when you get a number of clients connected to MyISAM tables. Converting to InnoDB solves it every time. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB ----- Incorrect key file for table error
Hello. mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686) You have an old MySQL version which contains lots of bugs (it's an alpha!). I strongly recommend you to upgrade to the latest release (4.1.12 now) and use official binaries. Hi, In a table of 20,000 records I am frequented with this error: Error 1034: Incorrect key file for table: ''; try to repair it Frequency of this error: Three or four times a week. I am logging the data it was trying to insert or update. After I, repair table tablename if, I try to insert the same data.. its accepts without getting corrupted. Has this got something to do with concurrent users transacting with the database? We are using the default type i.e MyISAM. InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine with commit, rollback, and crash recovery capabilities. Does a change to InnoDB will get rid of this problem? How can we make MyISAM ACID complaint? Do we have to write it into our application? MySQL version: mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686) On Red Hat Linux release 8.0 (Psyche) Linux version 2.4.18-14smp ([EMAIL PROTECTED]) (gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)) Praveen KS [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.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 ----- Incorrect key file for table error
Praveen KS [EMAIL PROTECTED] writes: In a table of 20,000 records I am frequented with this error: Error 1034: Incorrect key file for table: ''; try to repair it Frequency of this error: Three or four times a week. I am logging the data it was trying to insert or update. After I, repair table tablename if, I try to insert the same data.. its accepts without getting corrupted. Your tables are getting corrupted. You might have a software of hardware error. Since you are running an ALPHA release my guess is that you have a software problem. Upgrade mysql to a production release and see if that takes care of your problem. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. InnoDB for heavily-indexed, read-mostly data
At 06:37 PM 12/20/2004, you wrote: I'm new to MySQL and I was wondering which storage engine is the best choice for heavily-indexed, read-mostly data. From skimming over the documentation, it seems that MyISAM is a better choice since it doesn't have the transactional overhead. Yet I'm worried that it's becoming depricated and won't be supported in future versions. I need the highest read performance possible, with many indexes and joins. It has to be able to cache query results in memory to service a large number of concurrent requests per second. Which way to go? What's the pros and cons of each engine for my particular situation? I appreciate your help. -- Homam Homam, MyISAM will be faster than InnoDb for reading. In my tests, MyISAM was about 10x faster than an untuned InnoDb installation for simple Select statements. InnoDb is capable of faster speeds but requires a lot of tuning to get the peak performance from it. InnoDb is great for updates but for reading I prefer MyISAM hands down. MyISAM is the most popular table type for MySQL and will be around for years to come. It also supports FullText searching which InnoDb does not (perhaps in the future it will). Of course the bottleneck for MyISAM is its table locking. If you get more than 20 concurrent updates per second for a table, there may be delays in getting a lock on the table (you will need to do your own testing). In this case you either have to switch to InnoDb, get a faster server, or batch the updates or inserts. Keep in mind every time the table is updated, even if only 1 record is changed, the query cache is discarded (true for MyISAM and InnoDb). So you really don't want to be continuously updating the table that a lot of people are reading from. It is better to update the table every 5-10 minutes. One more thing. If you are just inserting rows into a MyISAM table, locks are not required if the table has been optimized (holes created from deleted rows have been removed). So if you optimize the table and then do not delete any rows from the table, and people only insert rows to the table, locking should not be a problem. You will of course need to do your own testing to confirm this. Hope this helps. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance of Joining Tables From Different Storage Engines -- Re: MyISAM vs. InnoDB for heavily-indexed, read-mostly data
Thanks Mike for the information. Yes, Emmett mentioned the same thing in a private message, and it seems that MyISAM is exactly what I'm looking for: a heavily-indexed large table that will be also indexed for full-text search and built off-line -- no updates whatsoever. However, I will be joining this table with other tables that need to be updated frequently, and that would require InnoDB for concurrency. And my concern is: How efficiently MySQL handles joins between MyISAM and InnoDB tables? In other words, does the overhead of joining between a MyISAM table and an InnoDB table outweigh any read performance advantage I get by using MyISAM for the read-mostly table? Meaning, would it be more efficient if I just made all the tables InnoDB to improve join performance, or there's no particular overhead in heterogeneous engine queries? I appreciate your feedback, Homam --- mos [EMAIL PROTECTED] wrote: At 06:37 PM 12/20/2004, you wrote: I'm new to MySQL and I was wondering which storage engine is the best choice for heavily-indexed, read-mostly data. From skimming over the documentation, it seems that MyISAM is a better choice since it doesn't have the transactional overhead. Yet I'm worried that it's becoming depricated and won't be supported in future versions. I need the highest read performance possible, with many indexes and joins. It has to be able to cache query results in memory to service a large number of concurrent requests per second. Which way to go? What's the pros and cons of each engine for my particular situation? I appreciate your help. -- Homam Homam, MyISAM will be faster than InnoDb for reading. In my tests, MyISAM was about 10x faster than an untuned InnoDb installation for simple Select statements. InnoDb is capable of faster speeds but requires a lot of tuning to get the peak performance from it. InnoDb is great for updates but for reading I prefer MyISAM hands down. MyISAM is the most popular table type for MySQL and will be around for years to come. It also supports FullText searching which InnoDb does not (perhaps in the future it will). Of course the bottleneck for MyISAM is its table locking. If you get more than 20 concurrent updates per second for a table, there may be delays in getting a lock on the table (you will need to do your own testing). In this case you either have to switch to InnoDb, get a faster server, or batch the updates or inserts. Keep in mind every time the table is updated, even if only 1 record is changed, the query cache is discarded (true for MyISAM and InnoDb). So you really don't want to be continuously updating the table that a lot of people are reading from. It is better to update the table every 5-10 minutes. One more thing. If you are just inserting rows into a MyISAM table, locks are not required if the table has been optimized (holes created from deleted rows have been removed). So if you optimize the table and then do not delete any rows from the table, and people only insert rows to the table, locking should not be a problem. You will of course need to do your own testing to confirm this. Hope this helps. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Send a seasonal email greeting and help others. Do good. http://celebrity.mail.yahoo.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 for heavily-indexed, read-mostly data
Homam S.A. wrote: I'm new to MySQL and I was wondering which storage engine is the best choice for heavily-indexed, read-mostly data. From skimming over the documentation, it seems that MyISAM is a better choice since it doesn't have the transactional overhead. Yet I'm worried that it's becoming depricated and won't be supported in future versions. I need the highest read performance possible, with many indexes and joins. It has to be able to cache query results in memory to service a large number of concurrent requests per second. Which way to go? What's the pros and cons of each engine for my particular situation? Homan: I believe in your situation MyISAM is better. I do not expect to see it becoming obsolete for at least another 5 years. If it does at some point in time, you will be able to run ALTER TABLE to covert to a better table type. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance of Joining Tables From Different Storage Engines -- Re: MyISAM vs. InnoDB for heavily-indexed, read-mostly data
At 04:00 PM 12/21/2004, Homam S.A. wrote: Thanks Mike for the information. Yes, Emmett mentioned the same thing in a private message, and it seems that MyISAM is exactly what I'm looking for: a heavily-indexed large table that will be also indexed for full-text search and built off-line -- no updates whatsoever. However, I will be joining this table with other tables that need to be updated frequently, and that would require InnoDB for concurrency. And my concern is: How efficiently MySQL handles joins between MyISAM and InnoDB tables? In other words, does the overhead of joining between a MyISAM table and an InnoDB table outweigh any read performance advantage I get by using MyISAM for the read-mostly table? Meaning, would it be more efficient if I just made all the tables InnoDB to improve join performance, or there's no particular overhead in heterogeneous engine queries? I appreciate your feedback, Homam Homan, I can't really say since I've never joined heterogeneous tables together. The best thing you can do is create a test database with the same table structure. Fill it with 10x the number of rows you expect to have, and then execute your expected SQL query statements. Measure the times to see how it can be optimized. Then when you have it optimized for one user, simulate multiple clients from several machines doing the same queries to see how it reacts. To fill the text data you can use words from the dictionary. There are a few free plain text dictionaries online. See http://www.translatum.gr/dictionaries/download-english.htm. Load the words into a (memory) table and then randomly add words to your test table's text/memo fields. I've used this technique to fill tables with 10 million rows of random words. Each memo field can have dozens of words (random number), and the text fields can have multiple words as well. Now you can do full text search of multiple word occurrences, such as dog and tree to see how fast it is. Your client computers can pull in 1 or 2 words at random from the dictionary, and then do a fulltext search on it. You can then time the results. This should get you going. :) Mike --- mos [EMAIL PROTECTED] wrote: At 06:37 PM 12/20/2004, you wrote: I'm new to MySQL and I was wondering which storage engine is the best choice for heavily-indexed, read-mostly data. From skimming over the documentation, it seems that MyISAM is a better choice since it doesn't have the transactional overhead. Yet I'm worried that it's becoming depricated and won't be supported in future versions. I need the highest read performance possible, with many indexes and joins. It has to be able to cache query results in memory to service a large number of concurrent requests per second. Which way to go? What's the pros and cons of each engine for my particular situation? I appreciate your help. -- Homam Homam, MyISAM will be faster than InnoDb for reading. In my tests, MyISAM was about 10x faster than an untuned InnoDb installation for simple Select statements. InnoDb is capable of faster speeds but requires a lot of tuning to get the peak performance from it. InnoDb is great for updates but for reading I prefer MyISAM hands down. MyISAM is the most popular table type for MySQL and will be around for years to come. It also supports FullText searching which InnoDb does not (perhaps in the future it will). Of course the bottleneck for MyISAM is its table locking. If you get more than 20 concurrent updates per second for a table, there may be delays in getting a lock on the table (you will need to do your own testing). In this case you either have to switch to InnoDb, get a faster server, or batch the updates or inserts. Keep in mind every time the table is updated, even if only 1 record is changed, the query cache is discarded (true for MyISAM and InnoDb). So you really don't want to be continuously updating the table that a lot of people are reading from. It is better to update the table every 5-10 minutes. One more thing. If you are just inserting rows into a MyISAM table, locks are not required if the table has been optimized (holes created from deleted rows have been removed). So if you optimize the table and then do not delete any rows from the table, and people only insert rows to the table, locking should not be a problem. You will of course need to do your own testing to confirm this. Hope this helps. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Send a seasonal email greeting and help others. Do good. http://celebrity.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance of Joining Tables From Different Storage Engines -- Re: MyISAM vs. InnoDB for heavily-indexed, read-mostly data
Thanks Mike. I think testing ultimately determines how efficient heterogeneous engine joins are. I just wanted to know if someone had issues with them in a heavy-load environment. --- mos [EMAIL PROTECTED] wrote: At 04:00 PM 12/21/2004, Homam S.A. wrote: Thanks Mike for the information. Yes, Emmett mentioned the same thing in a private message, and it seems that MyISAM is exactly what I'm looking for: a heavily-indexed large table that will be also indexed for full-text search and built off-line -- no updates whatsoever. However, I will be joining this table with other tables that need to be updated frequently, and that would require InnoDB for concurrency. And my concern is: How efficiently MySQL handles joins between MyISAM and InnoDB tables? In other words, does the overhead of joining between a MyISAM table and an InnoDB table outweigh any read performance advantage I get by using MyISAM for the read-mostly table? Meaning, would it be more efficient if I just made all the tables InnoDB to improve join performance, or there's no particular overhead in heterogeneous engine queries? I appreciate your feedback, Homam Homan, I can't really say since I've never joined heterogeneous tables together. The best thing you can do is create a test database with the same table structure. Fill it with 10x the number of rows you expect to have, and then execute your expected SQL query statements. Measure the times to see how it can be optimized. Then when you have it optimized for one user, simulate multiple clients from several machines doing the same queries to see how it reacts. To fill the text data you can use words from the dictionary. There are a few free plain text dictionaries online. See http://www.translatum.gr/dictionaries/download-english.htm. Load the words into a (memory) table and then randomly add words to your test table's text/memo fields. I've used this technique to fill tables with 10 million rows of random words. Each memo field can have dozens of words (random number), and the text fields can have multiple words as well. Now you can do full text search of multiple word occurrences, such as dog and tree to see how fast it is. Your client computers can pull in 1 or 2 words at random from the dictionary, and then do a fulltext search on it. You can then time the results. This should get you going. :) Mike --- mos [EMAIL PROTECTED] wrote: At 06:37 PM 12/20/2004, you wrote: I'm new to MySQL and I was wondering which storage engine is the best choice for heavily-indexed, read-mostly data. From skimming over the documentation, it seems that MyISAM is a better choice since it doesn't have the transactional overhead. Yet I'm worried that it's becoming depricated and won't be supported in future versions. I need the highest read performance possible, with many indexes and joins. It has to be able to cache query results in memory to service a large number of concurrent requests per second. Which way to go? What's the pros and cons of each engine for my particular situation? I appreciate your help. -- Homam Homam, MyISAM will be faster than InnoDb for reading. In my tests, MyISAM was about 10x faster than an untuned InnoDb installation for simple Select statements. InnoDb is capable of faster speeds but requires a lot of tuning to get the peak performance from it. InnoDb is great for updates but for reading I prefer MyISAM hands down. MyISAM is the most popular table type for MySQL and will be around for years to come. It also supports FullText searching which InnoDb does not (perhaps in the future it will). Of course the bottleneck for MyISAM is its table locking. If you get more than 20 concurrent updates per second for a table, there may be delays in getting a lock on the table (you will need to do your own testing). In this case you either have to switch to InnoDb, get a faster server, or batch the updates or inserts. Keep in mind every time the table is updated, even if only 1 record is changed, the query cache is discarded (true for MyISAM and InnoDb). So you really don't want to be continuously updating the table that a lot of people are reading from. It is better to update the table every 5-10 minutes. One more thing. If you are just inserting rows into a MyISAM table, locks are not required if the table has been optimized (holes created from deleted rows have been removed). So if you optimize the table and then do not delete any rows from the table, and people only insert rows to the table, locking should not be a problem. You will of course need to do your own testing to confirm this.
Re: MyISAM vs InnoDB + Foreign Keys
I thought that only InnoDB tables could be joined - and only if they had foreign keys. But it sounds like any kind of table can be joined, and it doesn't need a foreign key. The ability to join a bunch of tables in a query is different from foreign keys. A foreign key is a relationhip between two tables. It says that if you wish to put a value into a column with a foreign key, then that value has to exist in the other table. Can someone explain InnoDB, MyISAM and foreign keys in plain English? If I understand correctly, foreign keys simply help ensure integrity. To put it another way, they help weed out errors. InnoDB is a storage engine, and so is MyISAM. When you create a table, you specify one of the types. When you add data and indexes to that table, the type of table determins the storage engine used. InnoDB has row level locking. This means that when you are updating a row, only that one row gets locked (which means that another connection to the database cannot modify that row). MyISAM locks the entire table. Only one connection/session at a time can update/insert/delete. InnoDB uses the concept of a tablespace; MyISAM doesn't. A tablespace is where you store your data, and is made up of datafiles. You don't know where your data is stored in those data files. When you create a table in MyISAM, it creates a file of the same name as your table. InnoDB has foreign keys. What you wrote above is correct - you're defining a relationship between tables that the database will enforce. To backup InnoDB, you either have to shut down the database, or buy a hot-backup tool ($500 US, 390 Euros, I think). There is lots more, but those are the basics. MyISAM is easier, InnoDB has more enterprise features. For example, when I import data, I often get errors - something about violations of foreign key restraints. When I examine my original tables, I often find discrepancies - like eu* rather than eu in a row for Turkey, which lies partly in Europe AND Asia. Yes, the keys have to be an exact match. You could also allow a country to be in two continents with a one-to-many relationship. I've considered the possibility of creating foreign keys for quality control, then deleting them after I'm finished so I can tweak my table - like adding * to certain elements. However, it sounds like it's very difficult to delete foreign keys. I tried it in MySQL-Front or SQLyog and was presented with an 11-step process, or something like that. It's not that tough. ALTER TABLE your_table_here ADD CONSTRAINT some_foreign_key_name FOREIGN KEY (column from table) REFERENCES other_table (other column)l ALTER TABLE your_table_here DROP FOREIGN KEY some_foreign_key_name; Note that it depends on the version of MySQL you are using. Some versions of MySQL would not allow you to create foreign keys outside of CREATE-TABLE statements. Others wouldn't allow you to give your foreign key a name. Checkout http://www.innodb.com/ibman.php - it's the InnoDB manual. My understanding is that MyISAM tables are faster than InnoDB tables, but the latter offer row locking, though I don't really understand what that is. Explained it above. Putting it all together, what combination would you recommend for my project - a series of tables with data on the world's nations, states, counties and natural regions? My biggest table at present (Nations) has about 250 rows and half a dozen columns, but I'll be creating perhaps a dozen accessory tables, with data on area, population, economics, government, etc. I'm also planning a series of tables focusing on animals - common and scientific names, diet, habitat, etc. For both of these projects, I think foreign keys would be a good choice for quality control, which would, in turn, require the use of InnoDB tables. Am I right? Yes, foreign keys would help catch bugs. If there is alot of updating/inserting/deleting, InnoDB can be very helpful as well. Davis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB + Foreign Keys
On Thu, 13 May 2004 10:34:37 -0700 (PDT) David Blomstrom [EMAIL PROTECTED] wrote: I thought that only InnoDB tables could be joined - and only if they had foreign keys. But it sounds like any kind of table can be joined, and it doesn't need a foreign key. Exactly, you can do a join with any two or more tables (even the same table joined to itself actually) and on any columns. It's just a matter of whether or not the join makes any sense, for example: SELECT * FROM t1, t2 WHERE t1.city = t2.golfer_id Doesn't make much sense to do this, but the SQL will parse and attempt to execute anyway. Can someone explain InnoDB, MyISAM and foreign keys in plain English? If I understand correctly, foreign keys simply help ensure integrity. To put it another way, they help weed out errors. InnoDB and MyISAM are table types, or table engines is now the preferred terminology. Different storage implementations basically. MyISAM is so much faster because it doesn't have to deal with the overhead of transactions and foreign keys. An important aspect of foreign keys is the referential action (ON DELETE CASCADE, ON UPDATE SET NULL, etc) which allow you to have the database take care of cascading actions when a parent row is deleted without having to worry about having an application programmer do it and make a coding error or forget to and leave orphaned rows. So if you have a student parent table and an enrollment child table, you can set it to delete rows in the enrollment table for student 123 if student 123 is deleted form the student table (no orphaned rows in enrollment when a student is deleted). You can also set up referential actions to prevent deleting rows from a parent if there are rows in a child table (ON DELETE RESTRICT), it all depends on your situation. Foreign Keys also requires that any rows inserted into the the child table MUST have a value that matches in the parent table. So for the student/enrollment table, if you attempt to insert a row into enrollment for studentId 342, the only way that query will work is if there is indeed a student with studentId 342 in the student table. This is the referential integrity part of Foreign Keys. Foreign keys provide a real link between tables to implement an actual relationship between two tables, or even a table to itself. (remember this is a relational database afterall). Without that foreign key, the relationship is only implied and might not be known to anyone unless there is an entity-relationship diagram for the project. This comes in handy when trying to reverse engineer a data model from an existing db application. Data models should always come first though, but we're only human! For example, when I import data, I often get errors - something about violations of foreign key restraints. When I examine my original tables, I often find discrepancies - like eu* rather than eu in a row for Turkey, which lies partly in Europe AND Asia. I've considered the possibility of creating foreign keys for quality control, then deleting them after I'm finished so I can tweak my table - like adding * to certain elements. However, it sounds like it's very difficult to delete foreign keys. I tried it in MySQL-Front or SQLyog and was presented with an 11-step process, or something like that. ALTER TABLE mytable DROP FOREIGN KEY fk_symbol; (SHOW CREATE TABLE will reveal the fk_symbol) But why even put the foreign key in there in the first place if you're just going to violate it later? If you do that, you should have a eu* in the parent table. My understanding is that MyISAM tables are faster than InnoDB tables, but the latter offer row locking, though I don't really understand what that is. Yes. In addition to foreign keys, InnoDB offers transaction support, which is absolutely critical when dealing with larger OLTP applications. Speed does suffer though because all this Foreign Key / Transaction stuff takes lots of overhead. Putting it all together, what combination would you recommend for my project - a series of tables with data on the world's nations, states, counties and natural regions? My biggest table at present (Nations) has about 250 rows and half a dozen columns, but I'll be creating perhaps a dozen accessory tables, with data on area, population, economics, government, etc. With rows in the hundreds, InnoDB vs. MyISAM speed should be negligable. Make sure you create Indexes on your foreign key columns and you should be ok. (Indexes will speed up your queries, foreign key columns are almost always the columns used in a join condition, hence the need for an index) I'm also planning a series of tables focusing on animals - common and scientific names, diet, habitat, etc. For both of these projects, I think foreign keys would be a good choice for quality control, which would, in turn, require the use of InnoDB tables. Am I right? Yes, once you understand how they
Re: MyISAM vs. INNODB for a single blob table
Alan Williamson wrote: A quick question for the hardcore MySQL experts out there. I have a simple table; --- ID varchar (PK) DATA longblob --- This table is a simple persistence cache for one of our servers. It regularly INSERTs and SELECTs into this table data of approximately 2KB - 200KB, although the majority of inserts are around the 2KB mark. No fancy queries are ever performed, merely a single SELECT on a given key and no range queries are ever done. So with that in mind, I just noticed the table was created as a MyISAM. In your experience how does this compare to a table using INNODB? Should it have been created as a INNODB for better performance? Any thoughts, insights, would be listened to intensely! :) thanks How often are DELETE and UPDATE statements executed on this table? MyISAM is damned quick when it comes to workloads that always result in INSERTs ending up at the end of the tablespace. As MyISAM can allow SELECTs to execute while INSERTs are in progress at the end of the table (i.e When no DELETEs have been issued) thanks to it's versioning you'll find that thousands of queries a second is quite doable on modest hardware. That said, InnoDB's speed defies belief. Given that it's multiversioned, transactional and able to lock at the row level the fact that it's even in the same leauge as MyISAM performance-wise for these sorts of loads is impressive. When you have UPDATEs flying around, InnoDB may edge MyISAM out for heavy workloads. Many places have moved to InnoDB due to concurrency issues of that type. In summary, test test test! Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. INNODB for a single blob table
Thanks for that Chris, interesting thoughts. For clarification, there is *NO* UPDATEs running on this table. Not a single one! :) Many more SELECTs than INSERTs Chris Nolan wrote: Alan Williamson wrote: A quick question for the hardcore MySQL experts out there. I have a simple table; --- ID varchar (PK) DATA longblob --- This table is a simple persistence cache for one of our servers. It regularly INSERTs and SELECTs into this table data of approximately 2KB - 200KB, although the majority of inserts are around the 2KB mark. No fancy queries are ever performed, merely a single SELECT on a given key and no range queries are ever done. So with that in mind, I just noticed the table was created as a MyISAM. In your experience how does this compare to a table using INNODB? Should it have been created as a INNODB for better performance? Any thoughts, insights, would be listened to intensely! :) thanks How often are DELETE and UPDATE statements executed on this table? MyISAM is damned quick when it comes to workloads that always result in INSERTs ending up at the end of the tablespace. As MyISAM can allow SELECTs to execute while INSERTs are in progress at the end of the table (i.e When no DELETEs have been issued) thanks to it's versioning you'll find that thousands of queries a second is quite doable on modest hardware. That said, InnoDB's speed defies belief. Given that it's multiversioned, transactional and able to lock at the row level the fact that it's even in the same leauge as MyISAM performance-wise for these sorts of loads is impressive. When you have UPDATEs flying around, InnoDB may edge MyISAM out for heavy workloads. Many places have moved to InnoDB due to concurrency issues of that type. In summary, test test test! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. INNODB for a single blob table
Hi Alan, Thanks for that Chris, interesting thoughts. For clarification, there is *NO* UPDATEs running on this table. Not a single one! :) Many more SELECTs than INSERTs If you value your data, and these INSERTs are part of a multi-insert batch of related data, go with the table-type that supports transactions: InnoDB. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
