Re[2]: Innobase in MySQL
Hello Andreas, Saturday, March 17, 2001, 6:36:17 PM, you wrote: AV I noticed that in 3. and 4. the machine load was constantly at 100% during AV the inserts. AV but during index recreation the load springs wildley between 0 and 100%, so AV the machine is not maxed out in terms of CPU utilization here (in other AV words, the machine is waiting for something to do :-). The same applies to AV 2. when commit() was send. AV I tested also 1. and 3. with a myisam-table. Index recreation draws AV constantly 100% load from the machine here, therfore it's faster. AV results: AV 5. same as 1. but with myisam type = 116 seconds AV 6. same as 3. but with myisam type = 65 seconds + additional 33 seconds for AV index recreation. Could you tell me what do you mean by index creation ? Did you drop all indexes or only secondary index while leaving primary in fact ? -- Best regards, Petermailto:[EMAIL PROTECTED] - 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[2]: Innobase in MySQL
-Ursprngliche Nachricht- Von: Peter Zaitsev [EMAIL PROTECTED] An: Andreas Vierengel [EMAIL PROTECTED] Cc: Greg Cope [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Datum: 18 March, 2001 10:35 Betreff: Re[2]: Innobase in MySQL Could you tell me what do you mean by index creation ? Did you drop all indexes or only secondary index while leaving primary in fact ? I've created the table without any index, then did all the inserts and afterwards created all index with one "alter table". --Andy - 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[2]: Innobase in MySQL
Hello Greg, Sunday, March 18, 2001, 9:29:45 PM, you wrote: The only problem I see here - i don't know how innobase/bdb will handle tables without primary key... i think perfomance should not be so good as with myisam. Still there is a possibility to load all data to myisam and then run alter table which adds all indexes required and then converts the table to format required on the same pass. GC Agreed, but it would be nice if this was automatic with mysqldump ?! GC Although a gut feeling would be that create myisam table, insert, create GC indexes, convert will be slower. Well. Index creation and conversion can be done in one pass if I'm not mistaken. -- Best regards, Petermailto:[EMAIL PROTECTED] - 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[2]: Innobase in MySQL
Hello Dan, Tuesday, March 13, 2001, 6:37:16 PM, you wrote: DN In the last episode (Mar 13), Peter Zaitsev said: Well guys mysqldump have one serious problem - the speed. The backup speed is quite upsetting and loads system much, but the worst thing is recovery speed. In my case the data is added in realtime - most queries are inserts which utilize system quite hard. So to recover data I have gathered for a month it will take about 1 week to feed mysql with mysqldump output, even with extended inserts. So at least this is not complete solution. DN Make sure you remove all your indexes during the load; that can really DN slow down inserts. Well I tried to do so but still it takes an amount of time to add the indexes... I think the good Idea would be to add to mysqldump an option to create the table without index, then insert all data indeed and then add keys required. This would be a good point in --opt mode or in other option :) -- Best regards, Petermailto:[EMAIL PROTECTED] - 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[2]: Innobase in MySQL
hi! "Peter" == Peter Zaitsev [EMAIL PROTECTED] writes: Peter Hello Dan, Peter Tuesday, March 13, 2001, 6:37:16 PM, you wrote: DN In the last episode (Mar 13), Peter Zaitsev said: Well guys mysqldump have one serious problem - the speed. The backup speed is quite upsetting and loads system much, but the worst thing is recovery speed. In my case the data is added in realtime - most queries are inserts which utilize system quite hard. So to recover data I have gathered for a month it will take about 1 week to feed mysql with mysqldump output, even with extended inserts. So at least this is not complete solution. DN Make sure you remove all your indexes during the load; that can really DN slow down inserts. Peter Well I tried to do so but still it takes an amount of time to add the Peter indexes... Peter I think the good Idea would be to add to mysqldump an option to create Peter the table without index, then insert all data indeed and then add keys Peter required. This would be a good point in --opt mode or in other option Peter :) This already happens when using LOAD DATA INFILE and MyISAM tables, but the Innobase table handler can't yet do this. Regards, Monty - 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[2]: Innobase in MySQL
Hello Heikki, Tuesday, March 13, 2001, 1:31:04 AM, you wrote: HT Joshua, I hope you can also use MySQL dump, in which case, you don't have to shut down, right? HT yes, you can use mysqldump without shutting down. It did not come to my HT mind that actually mysqldump is a kind of online backup mechanism :). HT Since Innobase is multiversioned, you will get consistent snapshots of HT your tables, and since the consistent read does not set any locks, your HT users should be able to update the tables concurrently. Here I have HT to check if mysqldump sets a full table read lock on the table you dump: HT for Innobase that is not needed, but maybe MySQL currently does this because of HT other table types. Well guys mysqldump have one serious problem - the speed. The backup speed is quite upsetting and loads system much, but the worst thing is recovery speed. In my case the data is added in realtime - most queries are inserts which utilize system quite hard. So to recover data I have gathered for a month it will take about 1 week to feed mysql with mysqldump output, even with extended inserts. So at least this is not complete solution. -- Best regards, Petermailto:[EMAIL PROTECTED] - 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[2]: Innobase in MySQL
If you block user access during the recovery, would it be faster w/o the indexes and then add the indexes through alter table and then let the user's in. This is the recommended solution for recovery for DB2. If you have to do a recovery, its normally assumed that the database is locked for single DBA use until its fully recovered. This is how its done on the big systems. Just a thought. On Tue, 13 Mar 2001, Peter Zaitsev wrote: Date: Tue, 13 Mar 2001 11:43:30 +0300 From: Peter Zaitsev [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re[2]: Innobase in MySQL Hello Heikki, Tuesday, March 13, 2001, 1:31:04 AM, you wrote: HT Joshua, I hope you can also use MySQL dump, in which case, you don't have to shut down, right? HT yes, you can use mysqldump without shutting down. It did not come to my HT mind that actually mysqldump is a kind of online backup mechanism :). HT Since Innobase is multiversioned, you will get consistent snapshots of HT your tables, and since the consistent read does not set any locks, your HT users should be able to update the tables concurrently. Here I have HT to check if mysqldump sets a full table read lock on the table you dump: HT for Innobase that is not needed, but maybe MySQL currently does this because of HT other table types. Well guys mysqldump have one serious problem - the speed. The backup speed is quite upsetting and loads system much, but the worst thing is recovery speed. In my case the data is added in realtime - most queries are inserts which utilize system quite hard. So to recover data I have gathered for a month it will take about 1 week to feed mysql with mysqldump output, even with extended inserts. So at least this is not complete solution. -- Best regards, Petermailto:[EMAIL PROTECTED] - 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 Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - 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
