Best way to purge old records from a huge table?

Hey all -

I have a table with 12,000,000 records spread over about 6 years. I'm =
trying to delete all but the last 2 years, but no matter how small of a =
group I try to delete at a time, it keeps hanging up the server and I =
eventually have to restart MySQL. The table looks like this:

`creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update =
CURRENT_TIMESTAMP,
`lat` double NOT NULL default '0',
`lon` double NOT NULL default '0',
`referer` int(12) NOT NULL default '0',
PRIMARY KEY (`referer`,`lat`,`lon`),
KEY `creation` (`creation`,`referer`)

And the query I've been trying looks like this:

delete from tablename where `creation` < '2006-04-01 00:00:00'

....trying to do the oldest 1 month of records at a time. So am I just =
trying a really inefficient query? Is there a better way to do this?=

--
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: Best way to purge old records from a huge table?

--000e0cd32c7e19ca29048835da64
Content-Type: text/plain; charset=ISO-8859-1

dont use a single delete statment.
Use a stored proc, loop through and delete record by record and commit for
every 10k. In this way, your mysql will not hang and if you replication
setup, slave also will not lag behind.

regards
anandkl

On Fri, Jun 4, 2010 at 8:40 PM, Brian Dunning wrote:

> Hey all -
>
> I have a table with 12,000,000 records spread over about 6 years. I'm
> trying to delete all but the last 2 years, but no matter how small of a
> group I try to delete at a time, it keeps hanging up the server and I
> eventually have to restart MySQL. The table looks like this:
>
> `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
> `lat` double NOT NULL default '0',
> `lon` double NOT NULL default '0',
> `referer` int(12) NOT NULL default '0',
> PRIMARY KEY (`referer`,`lat`,`lon`),
> KEY `creation` (`creation`,`referer`)
>
> And the query I've been trying looks like this:
>
> delete from tablename where `creation` < '2006-04-01 00:00:00'
>
> ...trying to do the oldest 1 month of records at a time. So am I just
> trying a really inefficient query? Is there a better way to do this?
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

--000e0cd32c7e19ca29048835da64--

Re: Best way to purge old records from a huge table?

--00c09fa2176f9bc83f0488362234
Content-Type: text/plain; charset=ISO-8859-1

Hi Brian,

I would suggest you to use mk-archiver (Maatkit Tools) for this activity.

http://www.percona.com/files/presentations/Make_Life_Easier_ Maatkit_v2.pdf

Regards,
Krishna

On Fri, Jun 4, 2010 at 8:40 PM, Brian Dunning wrote:

> Hey all -
>
> I have a table with 12,000,000 records spread over about 6 years. I'm
> trying to delete all but the last 2 years, but no matter how small of a
> group I try to delete at a time, it keeps hanging up the server and I
> eventually have to restart MySQL. The table looks like this:
>
> `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
> `lat` double NOT NULL default '0',
> `lon` double NOT NULL default '0',
> `referer` int(12) NOT NULL default '0',
> PRIMARY KEY (`referer`,`lat`,`lon`),
> KEY `creation` (`creation`,`referer`)
>
> And the query I've been trying looks like this:
>
> delete from tablename where `creation` < '2006-04-01 00:00:00'
>
> ...trying to do the oldest 1 month of records at a time. So am I just
> trying a really inefficient query? Is there a better way to do this?
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=prajapatikc@gmail.com
>
>

--00c09fa2176f9bc83f0488362234--

RE: Best way to purge old records from a huge table?

--_824efe08-820c-4a41-b87a-9722b495fd67_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi Brian-

=20

i think the best way to ensure your dates are using YYYY-MM-DD format is fo=
r your dml to reference dates with DATE_FORMAT('YYYY-MM-DD'=2C'%Y-%m-%d') e=
..g.

=20

mysql> select DEIT_EVENT_SEQUENCE_ID=2CDEIT_EVENT_STATUS_CODE=2CDEIT_EVENT _=
DATE from DEIT=3B
+------------------------+------------------------+--------- --------+
| DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
+------------------------+------------------------+--------- --------+
| 1 | 1 | 2006-09-0=
4 |
| 2 | 2 | 2006-09-0=
5 |
| 3 | 3 | 2006-09-0=
6 |
+------------------------+------------------------+--------- --------+
3 rows in set (0.00 sec)

=20

mysql> delete from DEIT where DEIT_EVENT_DATE Y-%m-%d')=3B
Query OK=2C 1 row affected (0.02 sec)

=20

--the record is deleted so lets select to make sure

mysql> select DEIT_EVENT_SEQUENCE_ID=2CDEIT_EVENT_STATUS_CODE=2CDEIT_EVENT _=
DATE from DEIT=3B
+------------------------+------------------------+--------- --------+
| DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
+------------------------+------------------------+--------- --------+
| 2 | 2 | 2006-09-05 |
| 3 | 3 | 2006-09-06 |
+------------------------+------------------------+--------- --------+
2 rows in set (0.00 sec)


hth

Martin Gainty=20
______________________________________________=20
Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit=
=E9

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng=
er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter=
leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l=
ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin=
dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w=
ir keine Haftung fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAtes=
pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sat=
isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e=
ou la copie de ceci est interdite. Ce message sert =E0 l'information seule=
ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d=
onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation=
=2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni=
..



=20

> From: brian@briandunning.com
> Subject: Best way to purge old records from a huge table?
> Date: Fri=2C 4 Jun 2010 08:10:07 -0700
> To: mysql@lists.mysql.com
>=20
> Hey all -
>=20
> I have a table with 12=2C000=2C000 records spread over about 6 years. I'm=
trying to delete all but the last 2 years=2C but no matter how small of a =
group I try to delete at a time=2C it keeps hanging up the server and I eve=
ntually have to restart MySQL. The table looks like this:
>=20
> `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT=
_TIMESTAMP=2C
> `lat` double NOT NULL default '0'=2C
> `lon` double NOT NULL default '0'=2C
> `referer` int(12) NOT NULL default '0'=2C
> PRIMARY KEY (`referer`=2C`lat`=2C`lon`)=2C
> KEY `creation` (`creation`=2C`referer`)
>=20
> And the query I've been trying looks like this:
>=20
> delete from tablename where `creation` < '2006-04-01 00:00:00'
>=20
> ...trying to do the oldest 1 month of records at a time. So am I just try=
ing a really inefficient query? Is there a better way to do this?
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.com
>=20
=20
____________________________________________________________ _____
Hotmail has tools for the New Busy. Search=2C chat and e-mail from your inb=
ox.
http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID283 26::T:WLMTAGL:O=
N:WL:en-US:WM_HMP:042010_1=

--_824efe08-820c-4a41-b87a-9722b495fd67_--

Re: Best way to purge old records from a huge table?

I can't help but wonder how this is in any way relevant to the
original question.

On Fri, Jun 4, 2010 at 6:12 PM, Martin Gainty wrote:
>
> Hi Brian-
>
>
>
> i think the best way to ensure your dates are using YYYY-MM-DD format is =
for your dml to reference dates with DATE_FORMAT('YYYY-MM-DD','%Y-%m-%d') e=
..g.
>
>
>
> mysql> select DEIT_EVENT_SEQUENCE_ID,DEIT_EVENT_STATUS_CODE,DEIT_EVENT_DA=
TE from DEIT;
> +------------------------+------------------------+--------- --------+
> | DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
> +------------------------+------------------------+--------- --------+
> | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
1 | 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 2006-09-04 =A0 =A0 =A0|
> | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
2 | 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 2006-09-05 =A0 =A0 =A0|
> | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
3 | 3 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 2006-09-06 =A0 =A0 =A0|
> +------------------------+------------------------+--------- --------+
> 3 rows in set (0.00 sec)
>
>
>
> mysql> delete from DEIT where DEIT_EVENT_DATE Y-%m-%d');
> Query OK, 1 row affected (0.02 sec)
>
>
>
> --the record is deleted so lets select to make sure
>
> mysql> select DEIT_EVENT_SEQUENCE_ID,DEIT_EVENT_STATUS_CODE,DEIT_EVENT_DA=
TE from DEIT;
> +------------------------+------------------------+--------- --------+
> | DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
> +------------------------+------------------------+--------- --------+
> | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 | 2 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0| 2006-09-05 =A0 =A0 =A0|
> | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03 | 3 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0| 2006-09-06 =A0 =A0 =A0|
> +------------------------+------------------------+--------- --------+
> 2 rows in set (0.00 sec)
>
>
> hth
>
> Martin Gainty
> ______________________________________________
> Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit=
=E9
>
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfae=
nger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter=
leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l=
ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin=
dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w=
ir keine Haftung fuer den Inhalt uebernehmen.
>
> Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAt=
es pas le destinataire pr=E9vu, nous te demandons avec bont=E9 que pour sat=
isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e=
ou la copie de ceci est interdite. Ce message sert =E0 l'information seule=
ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d=
onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation, =
nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni.
>
>
>
>
>
>> From: brian@briandunning.com
>> Subject: Best way to purge old records from a huge table?
>> Date: Fri, 4 Jun 2010 08:10:07 -0700
>> To: mysql@lists.mysql.com
>>
>> Hey all -
>>
>> I have a table with 12,000,000 records spread over about 6 years. I'm tr=
ying to delete all but the last 2 years, but no matter how small of a group=
I try to delete at a time, it keeps hanging up the server and I eventually=
have to restart MySQL. The table looks like this:
>>
>> `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURREN=
T_TIMESTAMP,
>> `lat` double NOT NULL default '0',
>> `lon` double NOT NULL default '0',
>> `referer` int(12) NOT NULL default '0',
>> PRIMARY KEY (`referer`,`lat`,`lon`),
>> KEY `creation` (`creation`,`referer`)
>>
>> And the query I've been trying looks like this:
>>
>> delete from tablename where `creation` < '2006-04-01 00:00:00'
>>
>> ...trying to do the oldest 1 month of records at a time. So am I just tr=
ying a really inefficient query? Is there a better way to do this?
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.com
>>
>
> ____________________________________________________________ _____
> Hotmail has tools for the New Busy. Search, chat and e-mail from your inb=
ox.
> http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID283 26::T:WLMTAGL=
:ON:WL:en-US:WM_HMP:042010_1



--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
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: Best way to purge old records from a huge table?

Brian Dunning wrote:
> Hey all -
>
> I have a table with 12,000,000 records spread over about 6 years. I'm trying to delete all but the last 2 years, but no matter how small of a group I try to delete at a time, it keeps hanging up the server and I eventually have to restart MySQL. The table looks like this:
>
> `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
> `lat` double NOT NULL default '0',
> `lon` double NOT NULL default '0',
> `referer` int(12) NOT NULL default '0',
> PRIMARY KEY (`referer`,`lat`,`lon`),
> KEY `creation` (`creation`,`referer`)
>
> And the query I've been trying looks like this:
>
> delete from tablename where `creation` < '2006-04-01 00:00:00'
>
> ...trying to do the oldest 1 month of records at a time. So am I just trying a really inefficient query? Is there a better way to do this?
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=shawn@mysql.com
>

My idea is to create a new table with just the data you want to keep and
drop the old one. Every batch you delete must update the indexes on the
existing table. Creating a new,smaller, batch of data with a fresh set
of indexes should be much faster than incrementally deflating the
existing huge set of data.

Once the new table is created, use a RENAME TABLE to swap both table
names to put the new table into the old one's place and to give the old
table a name you can work with later.

--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, 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