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=sh...@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=arch...@jab.org
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 -MM-DD format is for
> your dml to reference dates with DATE_FORMAT('-MM-DD','%Y-%m-%d') e.g.
>
>
>
> mysql> select DEIT_EVENT_SEQUENCE_ID,DEIT_EVENT_STATUS_CODE,DEIT_EVENT_DATE
> from DEIT;
> +++-+
> | DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
> +++-+
> | 1 | 1 | 2006-09-04
> |
> | 2 | 2 | 2006-09-05
> |
> | 3 | 3 | 2006-09-06
> |
> +++-+
> 3 rows in set (0.00 sec)
>
>
>
> mysql> delete from DEIT where
> DEIT_EVENT_DATE 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_DATE
> from DEIT;
> +++-+
> | 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
> __
> Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
>
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
> sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
> oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich
> dem Austausch von Informationen und entfaltet keine rechtliche
> Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen
> wir keine Haftung fuer den Inhalt uebernehmen.
>
> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
> destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
> l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci
> est interdite. Ce message sert à l'information seulement et n'aura pas
> n'importe quel effet légalement obligatoire. Étant donné que les email
> peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
> aucune responsabilité pour le contenu fourni.
>
>
>
>
>
>> From: br...@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 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=mgai...@hotmail.com
>>
>
> _
> Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox.
> http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_1
--
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=arch...@jab.org
RE: Best way to purge old records from a huge table?
Hi Brian-
i think the best way to ensure your dates are using -MM-DD format is for
your dml to reference dates with DATE_FORMAT('-MM-DD','%Y-%m-%d') e.g.
mysql> select DEIT_EVENT_SEQUENCE_ID,DEIT_EVENT_STATUS_CODE,DEIT_EVENT_DATE
from DEIT;
+++-+
| DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
+++-+
|1 | 1 | 2006-09-04
|
|2 | 2 | 2006-09-05
|
|3 | 3 | 2006-09-06
|
+++-+
3 rows in set (0.00 sec)
mysql> delete from DEIT where
DEIT_EVENT_DATE select DEIT_EVENT_SEQUENCE_ID,DEIT_EVENT_STATUS_CODE,DEIT_EVENT_DATE
from DEIT;
+++-+
| 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
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
> From: br...@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 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=mgai...@hotmail.com
>
_
Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_1
Re: Best way to purge old records from a huge table?
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=prajapat...@gmail.com > >
Re: Best way to purge old records from a huge table?
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=anan...@gmail.com > >
