Deleting huge chunks of data from mysql innodb

Posted by ming yeow on Stack Overflow See other posts from Stack Overflow or by ming yeow
Published on 2010-05-12T04:28:31Z Indexed on 2010/05/12 4:34 UTC
Read the original article Hit count: 451

Filed under:

innodb

|

mysql

|

sql

I need to delete a huge chunk of my data in my production database, which runs about 100GB in size. If possible, i would like to minimize my downtime.

My selection criteria for deleting is likely to be

DELETE * FROM POSTING WHERE USER.ID=5 AND UPDATED_AT<100

What is the best way to delete it?

Build an index?
Write a sequential script that deletes via paginating through the rows 1000 at a time?

© Stack Overflow or respective owner

Deleting huge chunks of data from mysql innodb

Posted by mingyeow on Server Fault See other posts from Server Fault or by mingyeow
Published on 2010-05-12T04:15:48Z Indexed on 2010/05/12 4:25 UTC
Read the original article Hit count: 451

Filed under:

innodb

|

database

|

database-adminstration

I need to delete a huge chunk of my data in my production database, which runs about 100GB in size. If possible, i would like to minimize my downtime.

My selection criteria for deleting is likely to be

DELETE * FROM POSTING WHERE USER.ID=5 AND UPDATED_AT<100

What is the best way to delete it?

Build an index?
Write a sequential script that deletes via paginating through the rows 1000 at a time?

© Server Fault or respective owner

Related posts about innodb

MySQL InnoDB Corruption after power outage, possible to recover?

as seen on Server Fault - Search for 'Server Fault'
Hey Guys, I recently started trying to get Redmine up and running after a power outage that seems to have corrupted our InnoDB database in MySQL. Redmine had an extensive set of documentation that I would like to get even if redmine isn't able to run. The service fails on startup. I have tried… >>> More
Mysql server crashes Innodb

as seen on Server Fault - Search for 'Server Fault'
Today we got some DB crash. The DB is InnoDB. At firstin log: 120404 10:57:40 InnoDB: ERROR: the age of the last checkpoint is 9433732, InnoDB: which exceeds the log group capacity 9433498. InnoDB: If you are using big BLOB or TEXT rows, you must set the InnoDB: combined size of log files at least… >>> More
Issue with InnoDB engine while enabling and [ skip-innodb ] - [ERROR] Plugin 'InnoDB' init function returned error

as seen on Server Fault - Search for 'Server Fault'
How to enable InnoDB, which was previously disabled with skip-innodb option. Case 1: Disabled the innodb with skip-innodb option and show engines givens as below. Engine | Support ... | InnoDB | NO ...... Case 2: As I want to enable the innodb, I commanded the #skip-innodb option… >>> More
MySQL table does not exist

as seen on Server Fault - Search for 'Server Fault'
I am getting following error in err file. 110803 6:51:26 InnoDB: Error: table `ims`.`temp_discoveryjobdetails` already exists in InnoDB internal InnoDB: data dictionary. Have you deleted the .frm file InnoDB: and not used DROP TABLE? Have you used DROP DATABASE InnoDB: for InnoDB tables in MySQL… >>> More
MySQL died during the night on a 12.04.1 Ubuntu

as seen on Server Fault - Search for 'Server Fault'
I can't explain why, but somehow during the night, one of my MySQL running on an Ubuntu 12.04.1 box broke. The service is running but I can't login anymore (to SQL), the previous password is not working anymore. It does not looks like the server has been compromised (nothing in /var/auth.log) It… >>> More

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More