Re: table types
Hello. SHOW TABLE STATUS SHOW CREATE TABLE sol beach [EMAIL PROTECTED] wrote: How do I find out what table type is associated with each of the tables in MYSQL? -- 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: table types
SHOW TABLE STATUS (it includes type/storage engine as one of the returned columns) --bemansell Brian E. Mansell MySQL Professional On Wed, 15 Dec 2004 19:21:24 -0800, sol beach [EMAIL PROTECTED] wrote: How do I find out what table type is associated with each of the tables in MYSQL? -- 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: Table types
Hi Ronan, Yes, it's fine to mix table types in databases and queries. Matt - Original Message - From: Ronan Lucio Sent: Thursday, June 03, 2004 2:44 PM Subject: Table types Hi, Is it wise to have a database with hybrid table types? In other words: if I have a table that wouldn´t have many INSERT/DELETE/UPDATE queries, may I create it as MyISAM type and even interact (make JOINs) with other InnoBD and MyISAM tables? Or is it better (faster) to create all columns with the same type (InnoDB)? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table types in replication
On Thu, Jul 18, 2002 at 04:40:39PM +0100, Pete French wrote: Maybe BDB tables are not for me after all... Yeah, just go with InnoDB. :-) -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 50 days, processed 1,076,939,411 queries (247/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Table types in replication
Yeah, just go with InnoDB. :-) any good ? I took a look at the documentation, but it all seemed somewhat heavyweight for my liking... I have an application that runs very nicely at the moment - we have one minor problem which is that we have one insert into two tables which may not hapen properly if a process is killed (e.g. websever sutdown) at the wrong moment. it happens once in a blue boom, but I wanted to fix it by rolling it up in a BEGIN / COMMIT just for those two sql transactions. But I;ve been working on it for 4 days now and it's not happening unfortunately! If INNODB is actually table (unlike the problems I have had with BDB) then I might try that, but currently I am thinking of just sticking with myISAM. cheers, -pcf. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table types in replication
Hello! Pete French wrote: If I have amast/salve pair where the slave is replicating from the master then do the table types have to be the same ? Specifically can I have a myisam table on the master and replicate to a bdb table on the slave ? The reasoning behind this is to try and find a *fast* was to convert a MYISAM table to a BDB table having the database down for the minimum amount of time. Idea so far is this: How about this: CREATE TABLE new_table (all like your current one, besides indexes) TYPE=BDB; INSERT INTO new_table SELECT * FROM old_table; CREATE INDEX ... ON new_table;... ALTER TABLE new_table ADD PRIMARY KEY (...);... RENAME old_table TO old_table_bak; RENAME new_table TO old_table; That should cause a downtime of less than a second... last but not least you need insert all rows that have been created between the INSERT and the last RENAME. on current database machine do a ''mysqldump' and then enable logging. Load onto new database machine with table types set of BDB. Make new db machine replicate from old until it has caught up with the new data which was inserted into the master whilst the load was happening. When both are in sync then take them down and point allupdating clients to the new database machine. Any comments ? Converting the tables in situ is painfully slow - a couple of days I suspect. Greetings Ralf -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table types in replication
CREATE TABLE new_table (all like your current one, besides indexes) TYPE=BDB; INSERT INTO new_table SELECT * FROM old_table; CREATE INDEX ... ON new_table;... ALTER TABLE new_table ADD PRIMARY KEY (...);... RENAME old_table TO old_table_bak; RENAME new_table TO old_table; That should cause a downtime of less than a second... Interesting suggestion... I have about a million rows in that table, but I'll try it... last but not least you need insert all rows that have been created between the INSERT and the last RENAME. Well, if its less than a second I can afford to have the database down for that long - its the several days that is problematical. *quick test* About 3 minutes to do thecopy -not bad at all! I've been doing some experimentswith BDB tables though and am having real problems- of the locking up the mysql server type! I have a small benchmark I use to check the speed of my main table, which locks the tbale, does two updates n a single rowof that table and unlocks it again. I run many of these in parallel (up to 100) to get a ffeel for the load. Running these tests on the BDB version of the table I have found that after aout 30 connections I get Can't lock file (errno: 12) at which point the server locks up. It will not shutdown properly, and just hangs - eventually needing to be killed by hand. Maybe BDB tables are not for me after all... -pcf. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table types
On Wed, Jan 30, 2002 at 10:35:21AM -0500, Keith C. Ivey wrote: We're currently using MyISAM tables for everything. Are there circumstances in which the InnoDB table type would be better even if we're not going to use commit/rollback, or are transactions the only advantage of InnoDB? Concurrency is the the other main benefit. Would InnoDB's row-level locking improve speed over MyISAM for tables that have lots of updates and inserts, or does the transaction overhead cancel that out? It does not cancel out the overhead (in my experience). Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 28 days, processed 624,393,907 queries (257/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: table types
Pete Kuczynski wrote: Hi, I understand mysql supported InnoDB and BDB table types for the purposes of transaction logging, which I need to use. Which is recommended by you folks for NT4 boxes. Test both if you need to know :) I believe that InnoDB is better now but this depends of context of usage. Test it! -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Hong Kong, China ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php