Make delete requests without impact on a database

------=_NextPart_000_001B_01CADBE4.5484DBA0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi,



I am using MySQL version 4.1.12-log. All the databases on it are using
MyISAM database engine.



Every day, I delete almost 90000 rows on a table of 3 153 916 rows.



To delete the rows, I use a request like this : "DELETE QUICK FROM [table]
WHERE [column] < '2010-04-13 00:00:00' LIMIT 7500". I execute this request
until all the rows are delete.



This works but when I run the request, I can't access to the database (make
INSERT and SELECT requests) during I do the DELETE.



How can I do a "DELETE" without impact on INSERT and SELECT requests done on
the same time?



Regards,



David.




------=_NextPart_000_001B_01CADBE4.5484DBA0--

Re: Make delete requests without impact on a database

In the last episode (Apr 14), David Florella said:
> I am using MySQL version 4.1.12-log. All the databases on it are using
> MyISAM database engine.
>
> Every day, I delete almost 90000 rows on a table of 3 153 916 rows.
>
> To delete the rows, I use a request like this : "DELETE QUICK FROM [table]
> WHERE [column] < '2010-04-13 00:00:00' LIMIT 7500". I execute this
> request until all the rows are delete.
>
> This works but when I run the request, I can't access to the database
> (make INSERT and SELECT requests) during I do the DELETE.
>
> How can I do a "DELETE" without impact on INSERT and SELECT requests done
> on the same time?

Switch to InnoDB :) The MyISAM engine has to lock the entire table during
write queries, so all queries have to wait for slow UPDATE/INSERT/DELETE
calls to complete. An alternative would be to lower your LIMIT even more;
say to 1000. Then you'll do 90 very small deletes instead of 12 smallish
ones. Hopefully you're doing this loop in a program somewhere and not
manually running the deletes from a mysql CLI prompt... You may also want
to add the LOW_PRIORITY keyword to your DELETE statement; that will keep the
DELETE from moving to the front of the queue if there are other SELECT
statements pending.

http://dev.mysql.com/doc/refman/5.1/en/delete.html

--
Dan Nelson
dnelson@allantgroup.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: Make delete requests without impact on a database

It looks like you only want to keep the current data, perhaps the current
day's worth, and delete the old data.

I would store the data in separate MySIAM tables, each table would
represent a date, like D20100413 and D20100414. Your program will decide
which table to insert the data into by creating a current date variable and
now all data gets inserted to the table named by that variable. When the
older data is no longer needed, just drop the table. If you want to keep
the last 7 days of data, create a merge table of the last 7 tables. When
you drop the oldest table, redefine the merge table. You can accomplish all
this in just milliseconds.

Mike



At 08:08 AM 4/14/2010, you wrote:
>Hi,
>
>
>
>I am using MySQL version 4.1.12-log. All the databases on it are using
>MyISAM database engine.
>
>
>
>Every day, I delete almost 90000 rows on a table of 3 153 916 rows.
>
>
>
>To delete the rows, I use a request like this : "DELETE QUICK FROM [table]
>WHERE [column] < '2010-04-13 00:00:00' LIMIT 7500". I execute this request
>until all the rows are delete.
>
>
>
>This works but when I run the request, I can't access to the database (make
>INSERT and SELECT requests) during I do the DELETE.
>
>
>
>How can I do a "DELETE" without impact on INSERT and SELECT requests done on
>the same time?
>
>
>
>Regards,
>
>
>
>David.
>
>


--
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: Make delete requests without impact on a database

Been there, done that. It's a maintenance nightmare.

Another idea: Have a separate "deleted" table with the IDs of the rows
that you consider deleted. Re-write your queries to do a
left-join-not-in-the-other-table agains the "delete" table. Then, either
wait for a maintenance window to delete the rows both the original table
and the "delete" table. Or remove just a few rows at a time.

The "deleted" table can be created with just a read lock on the original
table, and since it's going to be fairly small, the impact of stuffing
it with data is not going to be great.

It's a bit of a hassle to set up, but once done you don't have to worry
about creating and deleting tables every day.

/ Carsten

mos skrev:
> It looks like you only want to keep the current data, perhaps the
> current day's worth, and delete the old data.
>
> I would store the data in separate MySIAM tables, each table would
> represent a date, like D20100413 and D20100414. Your program will decide
> which table to insert the data into by creating a current date variable
> and now all data gets inserted to the table named by that variable. When
> the older data is no longer needed, just drop the table. If you want to
> keep the last 7 days of data, create a merge table of the last 7 tables.
> When you drop the oldest table, redefine the merge table. You can
> accomplish all this in just milliseconds.
>
> Mike
>
>
>
> At 08:08 AM 4/14/2010, you wrote:
>> Hi,
>>
>>
>>
>> I am using MySQL version 4.1.12-log. All the databases on it are using
>> MyISAM database engine.
>>
>>
>>
>> Every day, I delete almost 90000 rows on a table of 3 153 916 rows.
>>
>>
>>
>> To delete the rows, I use a request like this : "DELETE QUICK FROM
>> [table]
>> WHERE [column] < '2010-04-13 00:00:00' LIMIT 7500". I execute this
>> request
>> until all the rows are delete.
>>
>>
>>
>> This works but when I run the request, I can't access to the database
>> (make
>> INSERT and SELECT requests) during I do the DELETE.
>>
>>
>>
>> How can I do a "DELETE" without impact on INSERT and SELECT requests
>> done on
>> the same time?
>>
>>
>>
>> Regards,
>>
>>
>>
>> David.
>>
>>
>
>

--
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: Make delete requests without impact on a database

At 01:20 PM 4/14/2010, Carsten Pedersen wrote:
>Been there, done that. It's a maintenance nightmare.

Why is it a maintenance nightmare? I've been using this technique for a
couple of years to store large amounts of data and it has been working just
fine. I have each table representing one year of data and I can go back and
access 30 years worth of data using either the individual table or a merge
table. Loading data is also faster than trying to store tens of millions of
rows of data into one table because the index may get too unbalanced.

Your method is preferred if there is no way to separate the data into
distinct tables. But with the example provided, it appeared (at least to
me), he was saving just one days worth of data. If that's the case, my
method would take only milliseconds to get rid of the old data. Your
suggestion of flagging the rows as deleted and then deleting them later
requires more work for the database and there needs to be a lull in order
to delete the data. He will also have to optimize the table to get rid of
the deleted rows and this requires the table to be locked.

Like I said, both methods will work. It depends on how the data is
organized and how much down time he can dedicate to the process in order to
determine which process is the best best .

Mike


>Another idea: Have a separate "deleted" table with the IDs of the rows
>that you consider deleted. Re-write your queries to do a
>left-join-not-in-the-other-table agains the "delete" table. Then, either
>wait for a maintenance window to delete the rows both the original table
>and the "delete" table. Or remove just a few rows at a time.
>
>The "deleted" table can be created with just a read lock on the original
>table, and since it's going to be fairly small, the impact of stuffing it
>with data is not going to be great.
>
>It's a bit of a hassle to set up, but once done you don't have to worry
>about creating and deleting tables every day.
>
>/ Carsten
>
>mos skrev:
>>It looks like you only want to keep the current data, perhaps the current
>>day's worth, and delete the old data.
>>I would store the data in separate MySIAM tables, each table would
>>represent a date, like D20100413 and D20100414. Your program will decide
>>which table to insert the data into by creating a current date variable
>>and now all data gets inserted to the table named by that variable. When
>>the older data is no longer needed, just drop the table. If you want to
>>keep the last 7 days of data, create a merge table of the last 7 tables.
>>When you drop the oldest table, redefine the merge table. You can
>>accomplish all this in just milliseconds.
>>Mike
>>
>>At 08:08 AM 4/14/2010, you wrote:
>>>Hi,
>>>
>>>
>>>
>>>I am using MySQL version 4.1.12-log. All the databases on it are using
>>>MyISAM database engine.
>>>
>>>
>>>
>>>Every day, I delete almost 90000 rows on a table of 3 153 916 rows.
>>>
>>>
>>>
>>>To delete the rows, I use a request like this : "DELETE QUICK FROM [table]
>>>WHERE [column] < '2010-04-13 00:00:00' LIMIT 7500". I execute this request
>>>until all the rows are delete.
>>>
>>>
>>>
>>>This works but when I run the request, I can't access to the database (make
>>>INSERT and SELECT requests) during I do the DELETE.
>>>
>>>
>>>
>>>How can I do a "DELETE" without impact on INSERT and SELECT requests done on
>>>the same time?
>>>
>>>
>>>
>>>Regards,
>>>
>>>
>>>
>>>David.
>>>


--
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: Make delete requests without impact on a database

mos skrev:
> At 01:20 PM 4/14/2010, Carsten Pedersen wrote:
>> Been there, done that. It's a maintenance nightmare.
>
> Why is it a maintenance nightmare? I've been using this technique for a
> couple of years to store large amounts of data and it has been working
> just fine.

In a previous reply, you mentioned splitting the tables on a daily
basis, not yearly. Enormous difference. It's one thing to fiddle with a
set of merge table once a year to create a new instance. Quite another
when it's to be done every day. If you want to change the table
structure, you'll have to do that on every single one of the underlying
tables. That might be fine for 5 year-tables, but not fun if you need to
do it for hundreds of tables.

If your merge table consists of 30 underlying tables*, a search in the
table will result in 30 separate searches, one per table. Also, MySQL
will need one file descriptor per underlying table *per client accessing
that table*. Plus one shared file descriptor per index file. So if 30
clients are accessing a merge table that consists of 30 days worth of
data, that's 930 file descriptors for the OS to keep track of. Clearly,
this doesn't scale well.

*Approx 1 month in your suggested solution, which also fits with OP
saying that ~90k of about ~3.2 mio get deleted every day.

/ Carsten


--
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: Make delete requests without impact on a database

> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@allantgroup.com]
> Sent: Wednesday, April 14, 2010 7:23 AM
> To: David Florella
> Cc: mysql@lists.mysql.com
> Subject: Re: Make delete requests without impact on a database
>
> In the last episode (Apr 14), David Florella said:
> > I am using MySQL version 4.1.12-log. All the databases on
> it are using
> > MyISAM database engine.
> >
> > Every day, I delete almost 90000 rows on a table of 3 153 916 rows.
> >
> > To delete the rows, I use a request like this : "DELETE
> QUICK FROM [table]
> > WHERE [column] < '2010-04-13 00:00:00' LIMIT 7500". I execute this
> > request until all the rows are delete.
> >
> > This works but when I run the request, I can't access to
> the database
> > (make INSERT and SELECT requests) during I do the DELETE.
> >
> > How can I do a "DELETE" without impact on INSERT and SELECT
> requests done
> > on the same time?
>
> Switch to InnoDB :) The MyISAM engine has to lock the entire
> table during
> write queries, so all queries have to wait for slow
> UPDATE/INSERT/DELETE
> calls to complete. An alternative would be to lower your
> LIMIT even more;
> say to 1000. Then you'll do 90 very small deletes instead of
> 12 smallish
> ones. Hopefully you're doing this loop in a program somewhere and not
> manually running the deletes from a mysql CLI prompt... You
> may also want
> to add the LOW_PRIORITY keyword to your DELETE statement;
> that will keep the
> DELETE from moving to the front of the queue if there are other SELECT
> statements pending.
>
> http://dev.mysql.com/doc/refman/5.1/en/delete.html

Another option to try is make a new column called "purge", then instead of
DELETE, use UPDATE to set the flag to 1 or something. Then at night or when
you have a slow time, run your DELETE WHERE `purge` = 1;


--
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: Make delete requests without impact on a database

On Wed, Apr 14, 2010 at 10:22 AM, Dan Nelson wrote:
> Switch to InnoDB :)

Seconded. No need to complicate your life with MyISAM workarounds
when InnoDB solves this problem already.

- 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: Make delete requests without impact on a database

Hi evryone,=20

Thank you for the advice. I will analyze the differences between InnoDB =
and
MyISAM before switching.=20

Moreover, there is a lot of indexes on the tables. I was thinking about
tunning the MySQL server. Do you know how can I benchmark the tunning of =
the
server before doing the tunning?

Regards,=20

David.=20

-----Message d'origine-----
De=A0: pharkins@gmail.com [mailto:pharkins@gmail.com] De la part de =
Perrin
Harkins
Envoyé : jeudi 15 avril 2010 02:36
À : Dan Nelson
Cc=A0: David Florella; mysql@lists.mysql.com
Objet=A0: Re: Make delete requests without impact on a database

On Wed, Apr 14, 2010 at 10:22 AM, Dan Nelson
wrote:
> Switch to InnoDB :)

Seconded. No need to complicate your life with MyISAM workarounds
when InnoDB solves this problem already.

- Perrin




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