>-----Original Message----- >From: Gavin Towey [mailto:gto...@ffn.com] >Sent: Friday, August 21, 2009 2:45 PM >To: Jerry Schwartz; 'mos'; 'MySQL' >Subject: RE: Scaling Mysql > >RENAME statement is atomic, and you can specify multiple tables to rename at >once. > >Instead of two statements, do this: >rename table send_sms to send_sms_full, send_sms_empty to >send_sms; > [JS] Ah, I didn't think about that.
>There will be no "gap" in-between. > >-----Original Message----- >From: Jerry Schwartz [mailto:jschwa...@the-infoshop.com] >Sent: Friday, August 21, 2009 8:32 AM >To: 'mos'; 'MySQL' >Subject: RE: Scaling Mysql > >> >>Krishna, >> Rather than copying rows from one table to another, and deleting the >>previous rows, why not just do: >> >>1) create table send_sms_empty like send_sms; >> >>2) rename table send_sms to send_sms_full;rename send_sms_empty to >send_sms; >> >>3) insert into alt_send_sms select * from send_sms_full; drop table >>send_sms_full; >> >>because step #2 is two sql statements, they will get executed together and >>will take just 1 or 2 ms and now you have an empty table that continues to >>get filled. This eliminates the insert delete table locking. Plus you >>always start with an empty optimized table. >> >>Step #3 uses a drop table which is much faster than trying to delete the >rows. >> >[JS] You'd have to make sure that the application, which is after all >pounding the database pretty hard, doesn't gag. As fast as that operation >might be, the application is likely to collide with it. > >You cannot rename a locked table, so I'm not sure how you could do what you >are suggesting in an ACID way. You'd need some kind of semaphore somewhere. > >Regards, > >Jerry Schwartz >The Infoshop by Global Information Incorporated >195 Farmington Ave. >Farmington, CT 06032 > >860.674.8796 / FAX: 860.674.8341 > >www.the-infoshop.com > > > > > >>Mike >> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwa...@the- >>infoshop.com > > > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=gto...@ffn.com > > >The information contained in this transmission may contain privileged and >confidential information. It is intended only for the use of the person(s) >named above. If you are not the intended recipient, you are hereby notified >that any review, dissemination, distribution or duplication of this >communication is strictly prohibited. If you are not the intended recipient, >please contact the sender by reply email and destroy all copies of the original >message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
