答复: Alter table
it will lock the table,read and write are blocked during the process. -原始邮件- 发件人: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] 发送时间: 2007年11月19日 15:24 收件人: MySql 主题: Alter table Hi Everybody, I would like to know that, while changing storage engine from one type to another locks that table or not. alter table engine=myisam; alter table engine=innodb; During the process of changing storage engine from one type to another. Whether it will work fine for an online system. Thanks, -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt. Ltd, 201,202, Ashoka Bhoopal Chambers, S P Road, Secunderabad 53. Ph. No. - 040-39188771 Url: www.ed-ventures-online.com
Alter table
Hi Everybody, I would like to know that, while changing storage engine from one type to another locks that table or not. alter table engine=myisam; alter table engine=innodb; During the process of changing storage engine from one type to another. Whether it will work fine for an online system. Thanks, -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt. Ltd, 201,202, Ashoka Bhoopal Chambers, S P Road, Secunderabad 53. Ph. No. - 040-39188771 Url: www.ed-ventures-online.com
Re: creating indexes with myisamchk
Joris Kinable wrote: Good evening, I've got to create a very large table: 180GB of data has to be stored. In order to to this I'm using the following steps: 1. Create database structure including keys. 2. Disable keys: ALTER TABLE ut_netflow_4 DISABLE KEYS 3. Load data into the database. 4. Generate all index keys: myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M -r --tmpdir=/tmpdir --quick /ut_netflow_4.MYI 5. flush privileges: FLUSH TABLE ut_netflow_4 6. restart server. Everything up to step 3 goes great. Step 4 and 5 on the other hand do not seem to work. After running the command at step 4, I've got a huge ut_netflow_4.MYI index file, but none of the indexes seem to be enabled. If I use phpmysql to view the table, it says that the cardinalities of the indexes are 0. Instead of the command at step 4, I've also tried: -myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M --sort-recover /ut_netflow_4.MYI -myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M --parallel-recover /ut_netflow_4.MYI -myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M -r /ut_netflow_4.MYI None of these seem to work either. The index file becomes larger, but phpmyadmin keeps indicating that there are no indexes. What am I doing wrong? How can I create and enable the indexes after loading the data into the table? In step 4, I'd just do ALTER TABLE ENABLE KEYS. Manipulating MyISAM files externally while the server is running is asking for trouble, in my opinion. In step 5, you aren't flushing privileges. You're flushing the table to disk. But it's already flushed to disk, since you did step 4 externally. The restart will do the same thing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
creating indexes with myisamchk
Good evening, I've got to create a very large table: 180GB of data has to be stored. In order to to this I'm using the following steps: 1. Create database structure including keys. 2. Disable keys: ALTER TABLE ut_netflow_4 DISABLE KEYS 3. Load data into the database. 4. Generate all index keys: myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M -r --tmpdir=/tmpdir --quick /ut_netflow_4.MYI 5. flush privileges: FLUSH TABLE ut_netflow_4 6. restart server. Everything up to step 3 goes great. Step 4 and 5 on the other hand do not seem to work. After running the command at step 4, I've got a huge ut_netflow_4.MYI index file, but none of the indexes seem to be enabled. If I use phpmysql to view the table, it says that the cardinalities of the indexes are 0. Instead of the command at step 4, I've also tried: -myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M --sort-recover /ut_netflow_4.MYI -myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M --parallel-recover /ut_netflow_4.MYI -myisamchk --sort_buffer_size=2700M --key_buffer_size=2700M -r /ut_netflow_4.MYI None of these seem to work either. The index file becomes larger, but phpmyadmin keeps indicating that there are no indexes. What am I doing wrong? How can I create and enable the indexes after loading the data into the table? Thnx in advance, Joris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb rollback question
At 02:05 PM 11/16/2007, you wrote: How do you import the data? Load data from file is faster thought so better to export myisam -> file and then you do load data from file make sure you set autocommit=0 to make it faster Ady, Sure but won't the entire Load Data will still be wrapped in a single transaction? How long would it take to rollback that transaction if it had over 130 million rows? Mike On Nov 17, 2007 12:29 AM, B. Keith Murphy <[EMAIL PROTECTED]> wrote: > I have something to throw out. I just got done importing 140 million > rows from a myisam table to a innodb table. While it worked I had a > thought about 3/4ths of the way through. What if the transaction had > been canceled about 130 million rows in? It would have taken weeks to > roll back. > > The only way I know of to stop a rollback like that is to bring out the > sledgehammer and kill the mysql processes and then rip out the entire > database and re-import. Faster than the rollback granted - but not very > elegant. Not something you want to do on a production server either > (the only time I had this happen it was in a test environment so there > were no consequences to my subsequent actions :) > > Any better way to do this? > > Thanks, > > Keith > > -- > 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]
