Re: Optimize: 14 hours and still running!
Hello. As was mentioned by other members without seeing your configuration settings it is difficult to say about InnoDB performance. You can indirectly monitor the OPTIMIZE speed by ROW OPERATIONS section of SHOW INNODB STATUS. For InnoDB it maps to ALTER TABLE, which rebuilds the table. Nathan Gross wrote: On a 1.6ghz, 1gb ram, Linux machine running Mysql 4.1x. I have an Innodb table with over 20 million records and index size about 3.7 gig, data size 2.2gig (yes, many indexes, more space then the data itself). Last night I tried an Optimize from the Admin gui console (logged in as root at the host server), and the thing is still running! Problem is I need to leave early this evening and have to take some action. The Linux 'top' utility has it on the top since then at about 11%-18% cpu Disk activity is continuously heavy. 1. How long should it take? 2. If I hit cancel will it: a) Roll back what it did, another 14 hours! b) Just stop as if nothing happened. c) The table will be partially optimized and will run normally. d) hang the process and/or machine. 3. Is the data in jeopardy? Thank you all. -nat -- 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: Optimize: 14 hours and still running!
Is the box swapping? Gleb Paharenko wrote: Hello. As was mentioned by other members without seeing your configuration settings it is difficult to say about InnoDB performance. You can indirectly monitor the OPTIMIZE speed by ROW OPERATIONS section of SHOW INNODB STATUS. For InnoDB it maps to ALTER TABLE, which rebuilds the table. Nathan Gross wrote: On a 1.6ghz, 1gb ram, Linux machine running Mysql 4.1x. I have an Innodb table with over 20 million records and index size about 3.7 gig, data size 2.2gig (yes, many indexes, more space then the data itself). Last night I tried an Optimize from the Admin gui console (logged in as root at the host server), and the thing is still running! Problem is I need to leave early this evening and have to take some action. The Linux 'top' utility has it on the top since then at about 11%-18% cpu Disk activity is continuously heavy. 1. How long should it take? 2. If I hit cancel will it: a) Roll back what it did, another 14 hours! b) Just stop as if nothing happened. c) The table will be partially optimized and will run normally. d) hang the process and/or machine. 3. Is the data in jeopardy? Thank you all. -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize: 14 hours and still running!
I do not know the answer to this. I do think your machine is slow, and has too little memory. For such a large database you should have a faster processor and more memory. But, I cannot speak to how the Optimize action works. Bob Cochran Greenbelt, Maryland, USA Nathan Gross wrote: On a 1.6ghz, 1gb ram, Linux machine running Mysql 4.1x. I have an Innodb table with over 20 million records and index size about 3.7 gig, data size 2.2gig (yes, many indexes, more space then the data itself). Last night I tried an Optimize from the Admin gui console (logged in as root at the host server), and the thing is still running! Problem is I need to leave early this evening and have to take some action. The Linux 'top' utility has it on the top since then at about 11%-18% cpu Disk activity is continuously heavy. 1. How long should it take? 2. If I hit cancel will it: a) Roll back what it did, another 14 hours! b) Just stop as if nothing happened. c) The table will be partially optimized and will run normally. d) hang the process and/or machine. 3. Is the data in jeopardy? Thank you all. -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimize: 14 hours and still running!
-Original Message- From: Nathan Gross [mailto:[EMAIL PROTECTED] Sent: Thursday, December 08, 2005 13:58 To: mysql@lists.mysql.com Subject: Optimize: 14 hours and still running! On a 1.6ghz, 1gb ram, Linux machine running Mysql 4.1x. I have an Innodb table with over 20 million records and index size about 3.7 gig, data size 2.2gig (yes, many indexes, more space then the data itself). Last night I tried an Optimize from the Admin gui console (logged in as root at the host server), and the thing is still running! Problem is I need to leave early this evening and have to take some action. The Linux 'top' utility has it on the top since then at about 11%-18% cpu Disk activity is continuously heavy. 1. How long should it take? 2. If I hit cancel will it: a) Roll back what it did, another 14 hours! b) Just stop as if nothing happened. c) The table will be partially optimized and will run normally. d) hang the process and/or machine. 3. Is the data in jeopardy? Thank you all. -nat -- From my understanging of the memory needs of an InnoDB engine with tables of that size, you're system is very underpowered. Depending on your system innodb variables you could be using up all the available ram and Bogging down the OS or not giving the db enough. I think you just need to let it go and wait. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize: 14 hours and still running!
Nathan, you can use SHOW INNODB STATUS\G to monitor how many rows per second it is inserting to the new, reorganized table. If the workload is disk-bound, it may be as low as 100 rows per second. Then inserting 20 million rows will take 2 days. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Nathan Gross [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, December 08, 2005 8:58 PM Subject: Optimize: 14 hours and still running! On a 1.6ghz, 1gb ram, Linux machine running Mysql 4.1x. I have an Innodb table with over 20 million records and index size about 3.7 gig, data size 2.2gig (yes, many indexes, more space then the data itself). Last night I tried an Optimize from the Admin gui console (logged in as root at the host server), and the thing is still running! Problem is I need to leave early this evening and have to take some action. The Linux 'top' utility has it on the top since then at about 11%-18% cpu Disk activity is continuously heavy. 1. How long should it take? 2. If I hit cancel will it: a) Roll back what it did, another 14 hours! b) Just stop as if nothing happened. c) The table will be partially optimized and will run normally. d) hang the process and/or machine. 3. Is the data in jeopardy? Thank you all. -nat -- 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]
