Scaling Mysql

--000e0cd480a6c93a910471a0ffe1
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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

--000e0cd480a6c93a910471a0ffe1--

Re: Scaling Mysql

Krishna Chandra Prajapati schrieb:
> 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.
>

Hi Krishna,
i see you are using some kind of queue mechanism but
to get a useful answer you need to be more specific:
e.g. what are your safety requirements ? Tables in RAM are very fast.
e.g. do you need forgein keys ?

When will data be copied (send->alt) ? after 1 day ? 1 hour ?
how long to you need to store data at alt ?
how often is the access ?

If speed is a concern do you need a database at all ? (KISS)

where does the current system spend its time ? and why ?

You see your request is far from simple and demands detail knowlegde about
your requirements going beyound what can be done in such a ML
(and this is only software, there is also hardware an economics).
Here you can ask "how can i improve SQL statement XX ?"

re,
wh







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Scaling Mysql

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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=jschwartz@the-
>infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Scaling Mysql

RENAME statement is atomic, and you can specify multiple tables to rename a=
t 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@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=3Djschwartz@the-
>infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e 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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Scaling Mysql

>-----Original Message-----
>From: Gavin Towey [mailto:gtowey@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:jschwartz@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=jschwartz@the-
>>infoshop.com
>
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=gtowey@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=gcdmg-mysql-2@m.gmane.org

RE: Scaling Mysql

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@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=jschwartz@the-
> >infoshop.com
>
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=gtowey@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=gcdmg-mysql-2@m.gmane.org

Re: Scaling Mysql

--001636ed657e0b8ce10471b35790
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi wharms,

Yor are right. It's some kind of queue mechanism. Right now i am working i
telco company (We used to send sms)

Users will be inserting records into send_sms @ 30,000msg/min Then those
record will be updated and moved to alt_send_sms and deleted from send_sms.
After that 30,000msg/min will be taken out from alt_send for processing and
sending to client. All the above task are happening concurrently. We will be
dealing with million of records/hour

On Fri, Aug 21, 2009 at 6:11 PM, walter harms wrote:

>
>
> Krishna Chandra Prajapati schrieb:
> > 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.
> >
>
> Hi Krishna,
> i see you are using some kind of queue mechanism but
> to get a useful answer you need to be more specific:
> e.g. what are your safety requirements ?

After moving the data to next stage Data is deleted from current table.

> Tables in RAM are very fast.
> e.g. do you need forgein keys ?

No

>
>
> When will data be copied (send->alt) ?

30000records/min

> after 1 day ? 1 hour ?
> how long to you need to store data at alt ?

Min 1 minute (we need to process immeaditely and send to the users as sms)

>
> how often is the access ?

per/sec

>
>
> If speed is a concern do you need a database at all ? (KISS)
>
> where does the current system spend its time ? and why ?
>
> You see your request is far from simple and demands detail knowlegde about
> your requirements going beyound what can be done in such a ML
> (and this is only software, there is also hardware an economics).
> Here you can ask "how can i improve SQL statement XX ?"
>
> re,
> wh
>
Thanks
krishna

--001636ed657e0b8ce10471b35790--

RE: Scaling Mysql

Have you looked at MySQL cluster? It was created specifically for telco ne=
eds.


-----Original Message-----
From: Krishna Chandra Prajapati [mailto:prajapatikc@gmail.com]
Sent: Friday, August 21, 2009 9:23 PM
To: wharms@bfs.de
Cc: MySQL
Subject: Re: Scaling Mysql

Hi wharms,

Yor are right. It's some kind of queue mechanism. Right now i am working i
telco company (We used to send sms)

Users will be inserting records into send_sms @ 30,000msg/min Then those
record will be updated and moved to alt_send_sms and deleted from send_sms.
After that 30,000msg/min will be taken out from alt_send for processing and
sending to client. All the above task are happening concurrently. We will b=
e
dealing with million of records/hour

On Fri, Aug 21, 2009 at 6:11 PM, walter harms wrote:

>
>
> Krishna Chandra Prajapati schrieb:
> > Hi list,
> >
> > I have two tables send_sms and alt_send_sms. Users are inserting record=
s
> > 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 ou=
t
> at
> > the rate 1000records/seconds from alt_send_sms.
> >
> > Which engine is more better for the above senario.
> >
>
> Hi Krishna,
> i see you are using some kind of queue mechanism but
> to get a useful answer you need to be more specific:
> e.g. what are your safety requirements ?

After moving the data to next stage Data is deleted from current table.

> Tables in RAM are very fast.
> e.g. do you need forgein keys ?

No

>
>
> When will data be copied (send->alt) ?

30000records/min

> after 1 day ? 1 hour ?
> how long to you need to store data at alt ?

Min 1 minute (we need to process immeaditely and send to the users as sms)

>
> how often is the access ?

per/sec

>
>
> If speed is a concern do you need a database at all ? (KISS)
>
> where does the current system spend its time ? and why ?
>
> You see your request is far from simple and demands detail knowlegde abou=
t
> your requirements going beyound what can be done in such a ML
> (and this is only software, there is also hardware an economics).
> Here you can ask "how can i improve SQL statement XX ?"
>
> re,
> wh
>
Thanks
krishna

The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Scaling Mysql

--00504502ae30fad1060471f0c043
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Yes, it's in our planning

On Mon, Aug 24, 2009 at 11:10 PM, Gavin Towey wrote:

> Have you looked at MySQL cluster? It was created specifically for telco
> needs.
>
>
> -----Original Message-----
> From: Krishna Chandra Prajapati [mailto:prajapatikc@gmail.com]
> Sent: Friday, August 21, 2009 9:23 PM
> To: wharms@bfs.de
> Cc: MySQL
> Subject: Re: Scaling Mysql
>
> Hi wharms,
>
> Yor are right. It's some kind of queue mechanism. Right now i am working i
> telco company (We used to send sms)
>
> Users will be inserting records into send_sms @ 30,000msg/min Then those
> record will be updated and moved to alt_send_sms and deleted from send_sms.
> After that 30,000msg/min will be taken out from alt_send for processing and
> sending to client. All the above task are happening concurrently. We will
> be
> dealing with million of records/hour
>
> On Fri, Aug 21, 2009 at 6:11 PM, walter harms wrote:
>
> >
> >
> > Krishna Chandra Prajapati schrieb:
> > > 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.
> > >
> >
> > Hi Krishna,
> > i see you are using some kind of queue mechanism but
> > to get a useful answer you need to be more specific:
> > e.g. what are your safety requirements ?
>
> After moving the data to next stage Data is deleted from current table.
>
> > Tables in RAM are very fast.
> > e.g. do you need forgein keys ?
>
> No
>
> >
> >
> > When will data be copied (send->alt) ?
>
> 30000records/min
>
> > after 1 day ? 1 hour ?
> > how long to you need to store data at alt ?
>
> Min 1 minute (we need to process immeaditely and send to the users as sms)
>
> >
> > how often is the access ?
>
> per/sec
>
> >
> >
> > If speed is a concern do you need a database at all ? (KISS)
> >
> > where does the current system spend its time ? and why ?
> >
> > You see your request is far from simple and demands detail knowlegde
> about
> > your requirements going beyound what can be done in such a ML
> > (and this is only software, there is also hardware an economics).
> > Here you can ask "how can i improve SQL statement XX ?"
> >
> > re,
> > wh
> >
> Thanks
> krishna
>
> 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.
>

--00504502ae30fad1060471f0c043--