List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:August 21 2009 6:44pm
Subject:RE: Scaling Mysql
View as plain text  
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.

-----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