Re: Practical samples for table types
Hi Everybody, our server was down for 8 hours. I applogize in case anybody did try to access the table chart. Its up and running and of course I would appreciate any feedback. http://www.knowd.co.jp/staff/nils/mysql-ttf.html Best regards Nils Valentin Tokyo/Japan 2003 5 30 17:34Nils Valentin : I created a little table chart showing the features of the different table types. Its not complete and might even contain wrong info. The information is taken from the the mysql manual and several books. http://knowd.co.jp/staff/nils/mysql-ttf.html As I dont have (yet) had the time to try all features of each table type I would appreciate if anybody with experience could take a sharp look and point out to me anything that is obvious wrong in the chart. I want this to be a practical chart, meaning as live is there will be a difference between the documentattion and the real life. Please also let me know why it is wrong and on which system you made the experience. Perhaps you are using features which are not even mentioned on the chart ? Also in this case let me know please, I will update the chart. Of course I will make the final chart available so the everybody can use it. Note: The Gemini table was available only from Nusphere and is not sold or used anymore. I know this, but I want to create a chart which shows the feature of all possible table types on a technical base, to fully understand the advantages/disadvantages each type has or had. ( You will also find enclosed the ISAM table type which is probably not even used anymore ;-) Best regards and thank you for any feedback Nils Valentin Tokyo/Japan 2003 5 29 16:35Nils Valentin : Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Anyway thanks for the reply. Best regards Nils Valentin 2003 5 29 16:27Jeremy Zawodny : On Tue, May 27, 2003 at 07:50:21AM -0400, Becoming Digital wrote: MyISAM is the default MySQL table type. This is the table type of choice for tables whose primary activity comes from SELECT statements. There is no need for transaction-safe tables unless INSERT, UPDATE, and/or DELETE actions will be performed frequently. Remember that with transaction-safe tables comes an increase in the amount of system resources needed to use those table types. BDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini types. Gemini? I haven't heard that name for a while. I thought it was dead. Is NuSphere still selling their Gemini-enhanced MySQL? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,209,651 queries (360/sec. avg) -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
Hi Edward, I expected this question to come up once more, so I think that this would have happened anyway, so let's just forget it shall we ? ;-) Probably it's only you and me reading it ;-) Best regards Nils Valentin Tokyo/Japan On Fri, 30 May 2003 09:07:17 -0400, Becoming Digital [EMAIL PROTECTED] wrote: It was my desire to post off-list but Jeremy's reply-to address duped me. It wasn't until after I sent that I realized this. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Becoming Digital [EMAIL PROTECTED] Sent: Friday, 30 May, 2003 08:13 Subject: Re: Practical samples for table types Hi Edward, Thank you for the reply. I am only interested in any technical details, not if it was good or bad what a certain company did. Please be so kind and leave the legal matters out of this. Thank you for understanding. Best regards Nils Valentin Tokyo/Japan 2003300:50ecoming Digital was on questionable legal ground. In fact, *I'm* convinced it was illegal. Care to elaborate on this? Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Nils Valentin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, 29 May, 2003 10:57 Subject: Re: Practical samples for table types On Thu, May 29, 2003 at 04:35:22PM +0900, Nils Valentin wrote: Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Oh, the technology was quite good. I tested it for a while. But it was on questionable legal ground. In fact, *I'm* convinced it was illegal. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 115 days, processed 3,589,652,971 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 82-0024 6-1 Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
On Thu, May 29, 2003 at 04:35:22PM +0900, Nils Valentin wrote: Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Oh, the technology was quite good. I tested it for a while. But it was on questionable legal ground. In fact, *I'm* convinced it was illegal. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 115 days, processed 3,589,652,971 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
I created a little table chart showing the features of the different table types. Its not complete and might even contain wrong info. The information is taken from the the mysql manual and several books. http://knowd.co.jp/staff/nils/mysql-ttf.html As I dont have (yet) had the time to try all features of each table type I would appreciate if anybody with experience could take a sharp look and point out to me anything that is obvious wrong in the chart. I want this to be a practical chart, meaning as live is there will be a difference between the documentattion and the real life. Please also let me know why it is wrong and on which system you made the experience. Perhaps you are using features which are not even mentioned on the chart ? Also in this case let me know please, I will update the chart. Of course I will make the final chart available so the everybody can use it. Note: The Gemini table was available only from Nusphere and is not sold or used anymore. I know this, but I want to create a chart which shows the feature of all possible table types on a technical base, to fully understand the advantages/disadvantages each type has or had. ( You will also find enclosed the ISAM table type which is probably not even used anymore ;-) Best regards and thank you for any feedback Nils Valentin Tokyo/Japan 2003 5 29 16:35Nils Valentin : Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Anyway thanks for the reply. Best regards Nils Valentin 2003 5 29 16:27Jeremy Zawodny : On Tue, May 27, 2003 at 07:50:21AM -0400, Becoming Digital wrote: MyISAM is the default MySQL table type. This is the table type of choice for tables whose primary activity comes from SELECT statements. There is no need for transaction-safe tables unless INSERT, UPDATE, and/or DELETE actions will be performed frequently. Remember that with transaction-safe tables comes an increase in the amount of system resources needed to use those table types. BDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini types. Gemini? I haven't heard that name for a while. I thought it was dead. Is NuSphere still selling their Gemini-enhanced MySQL? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,209,651 queries (360/sec. avg) -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
was on questionable legal ground. In fact, *I'm* convinced it was illegal. Care to elaborate on this? Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Nils Valentin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, 29 May, 2003 10:57 Subject: Re: Practical samples for table types On Thu, May 29, 2003 at 04:35:22PM +0900, Nils Valentin wrote: Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Oh, the technology was quite good. I tested it for a while. But it was on questionable legal ground. In fact, *I'm* convinced it was illegal. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 115 days, processed 3,589,652,971 queries (360/sec. avg) -- 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: Practical samples for table types
Hi Edward, Thank you for the reply. I am only interested in any technical details, not if it was good or bad what a certain company did. Please be so kind and leave the legal matters out of this. Thank you for understanding. Best regards Nils Valentin Tokyo/Japan 2003 5 30 20:50Becoming Digital : was on questionable legal ground. In fact, *I'm* convinced it was illegal. Care to elaborate on this? Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Nils Valentin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, 29 May, 2003 10:57 Subject: Re: Practical samples for table types On Thu, May 29, 2003 at 04:35:22PM +0900, Nils Valentin wrote: Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Oh, the technology was quite good. I tested it for a while. But it was on questionable legal ground. In fact, *I'm* convinced it was illegal. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 115 days, processed 3,589,652,971 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
On Tue, May 27, 2003 at 07:50:21AM -0400, Becoming Digital wrote: MyISAM is the default MySQL table type. This is the table type of choice for tables whose primary activity comes from SELECT statements. There is no need for transaction-safe tables unless INSERT, UPDATE, and/or DELETE actions will be performed frequently. Remember that with transaction-safe tables comes an increase in the amount of system resources needed to use those table types. BDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini types. Gemini? I haven't heard that name for a while. I thought it was dead. Is NuSphere still selling their Gemini-enhanced MySQL? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,209,651 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Anyway thanks for the reply. Best regards Nils Valentin 2003 5 29 16:27Jeremy Zawodny : On Tue, May 27, 2003 at 07:50:21AM -0400, Becoming Digital wrote: MyISAM is the default MySQL table type. This is the table type of choice for tables whose primary activity comes from SELECT statements. There is no need for transaction-safe tables unless INSERT, UPDATE, and/or DELETE actions will be performed frequently. Remember that with transaction-safe tables comes an increase in the amount of system resources needed to use those table types. BDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini types. Gemini? I haven't heard that name for a while. I thought it was dead. Is NuSphere still selling their Gemini-enhanced MySQL? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,209,651 queries (360/sec. avg) -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
Dear Edward, Thank you for the reply. I appreciate your informaton. Do you have by chance also any practical samples ? f.e Until which table size might it be better to use f.e MyISAM and when would you use another format (Heap or a TST table ? f.e When you have have mostly read only access to data and the amount fits into the memory easily than you could think about using a HEAP type etc. How big should the query cache be compared to the table size ? I believe that what I am looking for is practical samples - or some rough guidelines whats known to work good. Best regards Nils Valentin Tokyo/Japan - MyISAM is the default MySQL table type. This is the table type of choice for tables whose primary activity comes from SELECT statements. There is no need for transaction-safe tables unless INSERT, UPDATE, and/or DELETE actions will be performed frequently. Remember that with transaction-safe tables comes an increase in the amount of system resources needed to use those table types. BDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini types. InnoDB is the more popular and stable transaction-safe table type in open-source MySQL and was designed specifically for high performance with large volumes of data, as well as overall CPU efficiency. Like BDB, support for it did not appear until v3.23.34. Gemini tables are available only in NuSphere's Enhanced MySQL and not in the open source version of MySQL. All of the above is paraphrased from SAMS Teach Yourself MySQL in 24 Hrs. Who knew that book would actually come in handy. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 27 May, 2003 03:58 Subject: Practical samples for table types Hello mysql fans ;-), This time I have a question which sounds simple, but I believe the answer might not be so easy. I would appreciate to hear from anybody some really good samples of which table type would be good for which occasion. I tried to look for some information perhaps a case study or something like this on the internet but I did not find anything like this. Either I looked for the wrong information or it doesnt exist yet. I understand that ISAM tables are OS dependent and obsolete since MyISAM replaced them. That still leaves 7 formats open. (MyISAM, Merge Tables, HEAP, BDB, InnoDB, GEMINI) I would appreciate a omparison chart or soemthing similar showing the features for each table type etc. I want to figure out f.e. if I can modify HEAP tables or perhaps even use them as slaves in a replication system. What will happen when transformin Innodb tables into BDB or Gemini format. Would that be a good idea or not (and why) ? While this is a complex topic I am approaching I dont expect a simple answer or anybody to know everything I am trying to get a more complete understanding of the tables disadvantages and advantages. While the documentation lists up all options for each table I believe it doesn't necessary describe for which purpose they are useful. Perhaps anybody around who was just thinking the same ? -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 82-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
In the meanwhile I found a bit information which goes into the direction what I look for. http://www.mysql.de/newsletter/2002-12/a91.html www.nusphere.com/products/library/gemini.pdf (page 11) I still would appreciate any feedback or additonal information from experienced MySQL users. Best regards -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]