Selecting, Inserting and Deleting data

--001485ea8c56f73e21047daa9a2b
Content-Type: text/plain; charset=ISO-8859-1

Hi List,

I am working for a messaging company, sending sms to enterprise customers.

In a mysql table data is being continuously inserted by user. Most of the
time we have 5 to 10 millions of data in this table.

Table name : alt_send_sms engine myisam

From this table, i need to select data based on below parameter. Send some
where else and then delete the selected data.

selection and deletion part is done in bulk.

SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id,
service, account, id, sms_type, mclass, mwi, coding, compress FROM
alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20

delete from alt_send_sms where sql_id in
(....................................................)

sql_id is a unique bigint column with auto_increment.

Since the selection and deletion is done in bulk. Therefore, i cannot run
many similar concurrent queries. As duplicate messages will be send. What
can be the solution for this ?

Any response is highly appreciated.

Thanks,
Krishna

--001485ea8c56f73e21047daa9a2b--

Re: Selecting, Inserting and Deleting data

--001636e908a12594d6047daad491
Content-Type: text/plain; charset=ISO-8859-1

Hi Krishna,
As table is using MyISAM engine and it acquires a table level lock, the
queries will be executed one after one .
By converting it into Innodb as it acquires a row level lock, doing a select
and delete based on primary key will be faster and the concurrency
increases.

--
Thanks
Suresh Kuna
MySQL DBA


On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati <
prajapatikc@gmail.com> wrote:

> Hi List,
>
> I am working for a messaging company, sending sms to enterprise customers.
>
> In a mysql table data is being continuously inserted by user. Most of the
> time we have 5 to 10 millions of data in this table.
>
> Table name : alt_send_sms engine myisam
>
> From this table, i need to select data based on below parameter. Send some
> where else and then delete the selected data.
>
> selection and deletion part is done in bulk.
>
> SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id,
> service, account, id, sms_type, mclass, mwi, coding, compress FROM
> alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20
>
> delete from alt_send_sms where sql_id in
> (....................................................)
>
> sql_id is a unique bigint column with auto_increment.
>
> Since the selection and deletion is done in bulk. Therefore, i cannot run
> many similar concurrent queries. As duplicate messages will be send. What
> can be the solution for this ?
>
> Any response is highly appreciated.
>
> Thanks,
> Krishna
>

--001636e908a12594d6047daad491--

Re: Selecting, Inserting and Deleting data

--0016369fa19b0b0a67047dab2200
Content-Type: text/plain; charset=ISO-8859-1

Hi Suresh,

my question is how i can run concurrent connection with the above work load.

Thanks,
Krishna

On Thu, Jan 21, 2010 at 4:46 PM, Suresh Kuna wrote:

> Hi Krishna,
> As table is using MyISAM engine and it acquires a table level lock, the
> queries will be executed one after one .
> By converting it into Innodb as it acquires a row level lock, doing a
> select and delete based on primary key will be faster and the concurrency
> increases.
>
> --
> Thanks
> Suresh Kuna
> MySQL DBA
>
>
>
> On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati <
> prajapatikc@gmail.com> wrote:
>
>> Hi List,
>>
>> I am working for a messaging company, sending sms to enterprise customers.
>>
>> In a mysql table data is being continuously inserted by user. Most of the
>> time we have 5 to 10 millions of data in this table.
>>
>> Table name : alt_send_sms engine myisam
>>
>> From this table, i need to select data based on below parameter. Send some
>> where else and then delete the selected data.
>>
>> selection and deletion part is done in bulk.
>>
>> SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id,
>> service, account, id, sms_type, mclass, mwi, coding, compress FROM
>> alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT
>> 0,20
>>
>> delete from alt_send_sms where sql_id in
>> (....................................................)
>>
>> sql_id is a unique bigint column with auto_increment.
>>
>> Since the selection and deletion is done in bulk. Therefore, i cannot run
>> many similar concurrent queries. As duplicate messages will be send. What
>> can be the solution for this ?
>>
>> Any response is highly appreciated.
>>
>> Thanks,
>> Krishna
>>
>
>
>

--0016369fa19b0b0a67047dab2200--

Re: Selecting, Inserting and Deleting data

--001636ed69a64a4a2d047dab47b8
Content-Type: text/plain; charset=ISO-8859-1

Innodb contains multi-version property, so it can handle more concurrent
queries from user connections.


On Thu, Jan 21, 2010 at 5:07 PM, Krishna Chandra Prajapati <
prajapatikc@gmail.com> wrote:

> Hi Suresh,
>
> my question is how i can run concurrent connection with the above work
> load.
>
> Thanks,
> Krishna
>
>
> On Thu, Jan 21, 2010 at 4:46 PM, Suresh Kuna wrote:
>
>> Hi Krishna,
>> As table is using MyISAM engine and it acquires a table level lock, the
>> queries will be executed one after one .
>> By converting it into Innodb as it acquires a row level lock, doing a
>> select and delete based on primary key will be faster and the concurrency
>> increases.
>>
>> --
>> Thanks
>> Suresh Kuna
>> MySQL DBA
>>
>>
>>
>> On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati <
>> prajapatikc@gmail.com> wrote:
>>
>>> Hi List,
>>>
>>> I am working for a messaging company, sending sms to enterprise
>>> customers.
>>>
>>> In a mysql table data is being continuously inserted by user. Most of the
>>> time we have 5 to 10 millions of data in this table.
>>>
>>> Table name : alt_send_sms engine myisam
>>>
>>> From this table, i need to select data based on below parameter. Send
>>> some
>>> where else and then delete the selected data.
>>>
>>> selection and deletion part is done in bulk.
>>>
>>> SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id,
>>> service, account, id, sms_type, mclass, mwi, coding, compress FROM
>>> alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT
>>> 0,20
>>>
>>> delete from alt_send_sms where sql_id in
>>> (....................................................)
>>>
>>> sql_id is a unique bigint column with auto_increment.
>>>
>>> Since the selection and deletion is done in bulk. Therefore, i cannot run
>>> many similar concurrent queries. As duplicate messages will be send. What
>>> can be the solution for this ?
>>>
>>> Any response is highly appreciated.
>>>
>>> Thanks,
>>> Krishna
>>>
>>
>>
>>
>


--
Thanks
Suresh Kuna
MySQL DBA

--001636ed69a64a4a2d047dab47b8--

Re: Selecting, Inserting and Deleting data

--00504502b0a29ca2ce047dab6a19
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Jan 21, 2010 at 5:18 PM, Suresh Kuna wrote:

> Innodb contains multi-version property, so it can handle more concurrent
> queries from user connections.
>
>
> On Thu, Jan 21, 2010 at 5:07 PM, Krishna Chandra Prajapati <
> prajapatikc@gmail.com> wrote:
>
> > Hi Suresh,
> >
> > my question is how i can run concurrent connection with the above work
> > load.
> >
> > Thanks,
> > Krishna
> >
> >
> > On Thu, Jan 21, 2010 at 4:46 PM, Suresh Kuna > >wrote:
> >
> >> Hi Krishna,
> >> As table is using MyISAM engine and it acquires a table level lock, the
> >> queries will be executed one after one .
> >> By converting it into Innodb as it acquires a row level lock, doing a
> >> select and delete based on primary key will be faster and the
> concurrency
> >> increases.
> >>
> >> --
> >> Thanks
> >> Suresh Kuna
> >> MySQL DBA
> >>
> >>
> >>
> >> On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati <
> >> prajapatikc@gmail.com> wrote:
> >>
> >>> Hi List,
> >>>
> >>> I am working for a messaging company, sending sms to enterprise
> >>> customers.
> >>>
> >>> In a mysql table data is being continuously inserted by user. Most of
> the
> >>> time we have 5 to 10 millions of data in this table.
> >>>
> >>> Table name : alt_send_sms engine myisam
> >>>
> >>> From this table, i need to select data based on below parameter. Send
> >>> some
> >>> where else and then delete the selected data.
> >>>
> >>> selection and deletion part is done in bulk.
> >>>
> >>> SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id,
> >>> service, account, id, sms_type, mclass, mwi, coding, compress FROM
> >>> alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT
> >>> 0,20
> >>>
> >>> delete from alt_send_sms where sql_id in
> >>> (....................................................)
> >>>
> >>> sql_id is a unique bigint column with auto_increment.
> >>>
> >>> Since the selection and deletion is done in bulk. Therefore, i cannot
> run
> >>> many similar concurrent queries. As duplicate messages will be send.
> What
> >>> can be the solution for this ?
> >>>
> >>> Any response is highly appreciated.
> >>>
> >>> Thanks,
> >>> Krishna
> >>>
> >>
> >>
> >>
> >
>
>
> --
> Thanks
> Suresh Kuna
> MySQL DBA
>



Hi Krishna,

Can you please tell me what kind query you mostly run is it select or
insert?

--
Abhishek Kumar Singh

--00504502b0a29ca2ce047dab6a19--

Re: Selecting, Inserting and Deleting data

--0016e64980fe23fd0f047daba846
Content-Type: text/plain; charset=ISO-8859-1

Hi Abhishek.

insert, select and delete are in the same proportion.

1. Inserted data into a table A by user.
2. Selecting data from table A inserting data to table B after applying some
rules(update).
3. Deleting data from table A.
4. Selecting data from table B using some conditions (SELECT sql_id, momt,
sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id,
sms_type, mclass, mwi, coding, compress FROM alt_send_sms WHERE smsc_id =
'ite' ORDER BY msg_priority, sql_id LIMIT 0,20) moving to third party for
sending sms.
5. Deleting the selected data from table B.

With the above scenario, i am not able to user concurrent connections. Other
wise it will send duplicate sms.

Thanks,
Kishna

On Thu, Jan 21, 2010 at 5:28 PM, Abhishek Singh wrote:

>
>
> On Thu, Jan 21, 2010 at 5:18 PM, Suresh Kuna wrote:
>
>> Innodb contains multi-version property, so it can handle more concurrent
>> queries from user connections.
>>
>>
>> On Thu, Jan 21, 2010 at 5:07 PM, Krishna Chandra Prajapati <
>> prajapatikc@gmail.com> wrote:
>>
>> > Hi Suresh,
>> >
>> > my question is how i can run concurrent connection with the above work
>> > load.
>> >
>> > Thanks,
>> > Krishna
>> >
>> >
>> > On Thu, Jan 21, 2010 at 4:46 PM, Suresh Kuna >> >wrote:
>> >
>> >> Hi Krishna,
>> >> As table is using MyISAM engine and it acquires a table level lock, the
>> >> queries will be executed one after one .
>> >> By converting it into Innodb as it acquires a row level lock, doing a
>> >> select and delete based on primary key will be faster and the
>> concurrency
>> >> increases.
>> >>
>> >> --
>> >> Thanks
>> >> Suresh Kuna
>> >> MySQL DBA
>> >>
>> >>
>> >>
>> >> On Thu, Jan 21, 2010 at 4:30 PM, Krishna Chandra Prajapati <
>> >> prajapatikc@gmail.com> wrote:
>> >>
>> >>> Hi List,
>> >>>
>> >>> I am working for a messaging company, sending sms to enterprise
>> >>> customers.
>> >>>
>> >>> In a mysql table data is being continuously inserted by user. Most of
>> the
>> >>> time we have 5 to 10 millions of data in this table.
>> >>>
>> >>> Table name : alt_send_sms engine myisam
>> >>>
>> >>> From this table, i need to select data based on below parameter. Send
>> >>> some
>> >>> where else and then delete the selected data.
>> >>>
>> >>> selection and deletion part is done in bulk.
>> >>>
>> >>> SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time,
>> smsc_id,
>> >>> service, account, id, sms_type, mclass, mwi, coding, compress FROM
>> >>> alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT
>> >>> 0,20
>> >>>
>> >>> delete from alt_send_sms where sql_id in
>> >>> (....................................................)
>> >>>
>> >>> sql_id is a unique bigint column with auto_increment.
>> >>>
>> >>> Since the selection and deletion is done in bulk. Therefore, i cannot
>> run
>> >>> many similar concurrent queries. As duplicate messages will be send.
>> What
>> >>> can be the solution for this ?
>> >>>
>> >>> Any response is highly appreciated.
>> >>>
>> >>> Thanks,
>> >>> Krishna
>> >>>
>> >>
>> >>
>> >>
>> >
>>
>>
>> --
>> Thanks
>> Suresh Kuna
>> MySQL DBA
>>
>
>
>
> Hi Krishna,
>
> Can you please tell me what kind query you mostly run is it select or
> insert?
>
> --
> Abhishek Kumar Singh
>
>

--0016e64980fe23fd0f047daba846--

Re: Selecting, Inserting and Deleting data

On Thu, Jan 21, 2010 at 7:15 AM, Krishna Chandra Prajapati
wrote:
> 1. Inserted data into a table A by user.
> 2. Selecting data from table A inserting data to table B after applying some
> rules(update).
> 3. Deleting data from table A.
> 4. Selecting data from table B using some conditions (SELECT sql_id, momt,
> sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id,
> sms_type, mclass, mwi, coding, compress FROM alt_send_sms WHERE smsc_id =
> 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20) moving to third party for
> sending sms.
> 5. Deleting the selected data from table B.
>
> With the above scenario, i am not able to user concurrent connections. Other
> wise it will send duplicate sms.

I see, you want to lock on steps 4 and 5 to prevent concurrent access
to the same records. You can use SELECT FOR UPDATE (if you're using
InnoDB) or just lock the whole table.

- Perrin

--
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: Selecting, Inserting and Deleting data

Krishna Chandra Prajapati wrote:
> Hi List,
>
> I am working for a messaging company, sending sms to enterprise customers.
>
> In a mysql table data is being continuously inserted by user. Most of the
> time we have 5 to 10 millions of data in this table.
>
> Table name : alt_send_sms engine myisam
>
> From this table, i need to select data based on below parameter. Send some
> where else and then delete the selected data.
>
> selection and deletion part is done in bulk.
>
> SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id,
> service, account, id, sms_type, mclass, mwi, coding, compress FROM
> alt_send_sms WHERE smsc_id = 'ite' ORDER BY msg_priority, sql_id LIMIT 0,20
>
> delete from alt_send_sms where sql_id in
> (....................................................)
>
> sql_id is a unique bigint column with auto_increment.
>
> Since the selection and deletion is done in bulk. Therefore, i cannot run
> many similar concurrent queries. As duplicate messages will be send. What
> can be the solution for this ?
>
> Any response is highly appreciated.
>
> Thanks,
> Krishna
>

The main thing you want to achieve is to isolate these rows from other
queries, correct? One easy way around this is to "tag" each row with a
unique value.

UPDATE SET tag_column= WHERE conditions> AND tag_column=0

The last part, "AND tag_column=0" is the part that ensures that only
untagged columns that meet your condition are tagged with your unique
identifier. You can repeat this tagging process on several tables (using
the same value) to build a set of related values for your processing needs.

After you have set your values, you have identified an entire set of
data that you want to manipulate. When you are done processing the
SELECT, you can very easily drop just those rows by

DELETE FROM WHERE tag_column=

Or you can return those rows into the general pool of data by simply
clearing the flag

UPDATE SET tag_column = 0 WHERE tag_column = value>;

Some ideas for unique values:
* the thread number from within your application.
* the connection number for your MySQL client
* a UUID value
* a hashed value of a combination of pseudo-random values (IP address,
time, someone's name, a random number, etc.) .
* some sequential number you track in another table.

All you really need is a number statistically improbable to repeat
between any two of your clients.

--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



--
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: Selecting, Inserting and Deleting data

At 06:15 AM 1/21/2010, Krishna Chandra Prajapati wrote:
>Hi Abhishek.
>
>insert, select and delete are in the same proportion.
>
>1. Inserted data into a table A by user.
>2. Selecting data from table A inserting data to table B after applying some
>rules(update).
>3. Deleting data from table A.
>4. Selecting data from table B using some conditions (SELECT sql_id, momt,
>sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id,
>sms_type, mclass, mwi, coding, compress FROM alt_send_sms WHERE smsc_id =
>'ite' ORDER BY msg_priority, sql_id LIMIT 0,20) moving to third party for
>sending sms.
>5. Deleting the selected data from table B.
>
>With the above scenario, i am not able to user concurrent connections. Other
>wise it will send duplicate sms.
>
>Thanks,
>Kishna

Kishna,
If the table A and B are not doing Updates, then there is a solution
using MyISAM tables. :-)

1) Start by optimizing the tables to remove any deleted rows (holes) in the
table. Call this table "A". Do the same for Table "B".
2) Create a third table "DA" that contains one column, the Rcd_Id of the
rows in table A that needs deleting. You can create table "DB" with the
rcd_id of the deleted rows from table B. Of course tables DA and DB have an
index on this Rcd_Id column.
3) When you start the DA and DB tables are empty and optimized (no holes)

4) Instead of deleting rows from table A, you add its Rcd_id to table DA.
Same with table B and DB.
5) Your Select statements on table A and table B will do a Left Join to DA
and DB respectively as in:
select ... from A left join DA on A.Rcd_Id=DA.Rcd_Id where DA.Rcd_Id is NULL
or
select ... from B left join DB on B.Rcd_Id=DB.Rcd_Id where DB.Rcd_Id is NULL

Since DA.Rcd_Id and Db.Rcd_id are indexed, this will be quite fast. Make
them a memory table if you like.
Now what makes this work is MyISAM tables will NOT issue a lock when
inserting rows on an optimized table! This goes for both table A and DA.

Once a day you will delete the deleted rows from Table A using DA.
You may be able to reduce this time by taking advantage of the ability to
rename multiple tables at one time. See
http://dev.mysql.com/doc/refman/5.0/en/rename-table.html
so you ...

.... can create a duplicate empty tables using:
create table DUPA like A; insert into DUPA select * from A left join DA on
A.Rcd_Id=DA.Rcd_Id where DA.Rcd_Id is null;
create table DUPDA like DA;
create table DUPB like B;insert into DUPB select * from A left join DA on
B.Rcd_Id=DB.Rcd_Id where DB.Rcd_Id is null;
create table DUPDB like DB;

drop table if exists DupA, DupDA, DupB, DupDB, OldA, OldB;

Now in one statement execute:
rename table A to OldA, DUPA to A, DA to OldDA, DupDA to DA, B to OldB,
DUPB to B, DB to OldDB, DupDB to DB;
This is atomic so all tables get renamed at once using one lock.

Now you have to copy the few rows that were inserted into A after DupA was
created and before the tables were renamed;
set @MaxRcdId := select max(Rcd_Id) from A;
Insert into A select * from OldA where Rcd_Id>@MaxRcdId;

Now you have to copy the few rows that were inserted into B after DupB was
created and before the tables were renamed;
set @MaxRcdId := select max(Rcd_Id) from B;
Insert into B select * from OldB where Rcd_Id>@MaxRcdId;

This theoretically should work. I've done this from the top of my head so
there may be syntax errors. This should get you on the right road.

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