List:General Discussion« Previous MessageNext Message »
From:mos Date:August 21 2009 9:24pm
Subject:RE: Scaling Mysql
View as plain text  
At 01:44 PM 8/21/2009, you wrote:
>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;
>
>There will be no "gap" in-between.

Ah yes, that's what I forgot. It's all done in one sql statement.

Mike


>-----Original Message-----
>From: Jerry Schwartz [mailto:jschwartz@stripped]
>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=1
> >infoshop.com
>
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
>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.

Thread
Scaling MysqlKrishna Chandra Prajapati21 Aug
  • Re: Scaling Mysqlwalter harms21 Aug
    • Re: Scaling MysqlKrishna Chandra Prajapati22 Aug
      • RE: Scaling MysqlGavin Towey24 Aug
        • Re: Scaling MysqlKrishna Chandra Prajapati25 Aug
  • Re: Scaling Mysqlmos21 Aug
    • RE: Scaling MysqlJerry Schwartz21 Aug
      • RE: Scaling MysqlGavin Towey21 Aug
        • RE: Scaling MysqlJerry Schwartz21 Aug
        • RE: Scaling Mysqlmos21 Aug