List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:August 21 2009 7:29pm
Subject:RE: Scaling Mysql
View as plain text  

>-----Original Message-----
>From: Gavin Towey [mailto:gtowey@stripped]
>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: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