List:General Discussion« Previous MessageNext Message »
From:mos Date:August 21 2009 2:42pm
Subject:Re: Scaling Mysql
View as plain text  
At 01:30 AM 8/21/2009, Krishna Chandra Prajapati wrote:
>Hi list,
>
>I have two tables send_sms and alt_send_sms. Users are inserting records
>into send_sms @ 500/sec ie 30000/min. After applying some updates to
>send_sms data are transferred to alt_send_sms and deleted from send sms. The
>same thing is happening with alt_send_sms table.
>
>Is it possible to insert 1000records/sec in send_sms table and taken out at
>the rate 1000records/seconds from alt_send_sms.
>
>Which engine is more better for the above senario.
>
>Regards,
>Krishna

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.

Mike 

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