Re: Global read lock on delete
Artem - > |THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA > TABLE_NAME > 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITGlobal read lock > 270022 MDL_INTENTION_EXCLUSIVE MDL_STATEMENT Global read lock > 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock > sprusearchsobjects > 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock > sprusearches > 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITSchema metadata lockspru > 270022 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION Schema metadata lockspur The global read lock is in MDL_INTENTION_EXCLUSIVE mode, as an intention lock it's not an actual global read lock in effect, but rather something to prevent another thread to take GRL while GRL-incompatible statements (DELETE, LOCK TABLES) are still running. -- Laurynas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Global read lock on delete
On 12/9/2015 11:59 AM, Artem Kuchin wrote: 09.12.2015 19:35, shawn l.green пишет: On 12/9/2015 9:59 AM, Artem Kuchin wrote: Hello! |THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMATABLE_NAME 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITGlobal read lock 270022 MDL_INTENTION_EXCLUSIVE MDL_STATEMENT Global read lock 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearchsobjects 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearches 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITSchema metadata lock spru 270022 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION Schema metadata lock spru You gave the answer in your last statement: "All tables are myisam" . The MyISAM storage engine is not transactional and it does not do row-level locking. All UPDATE and DELETE operations require a full table lock to perform and those must wait for all earlier readers or writers to exit the table before they can start. INSERT operations are special as you can enable a mode to allow INSERTs to happen only at the end of the file and not be blocked while one of the other two operations are in progress. TABLE LOCK is okay, i understand that. But i see GLOBAL READ LOCK - not table lock, but GLOBAL. As i understand that it means ALL TABLES IN ALL DATABASES. Why? Artem That is something the official MySQL does not do. You would need to research the MariaDB fork's documentation to see why they report it as a global lock. I'm thinking that it might not be a full lock on all tables, just on the one, to prevent someone from changing the table's design before the queued UPDATE or DELETE could complete. We do that, too. We lock the definition while any writer is using the table. That is performed using a metadata lock (MDL). I, personally, have not had any time at all to dig that deeply into how the forks differ from the original in terms of lock management. Maybe another person on this list will know? Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Global read lock on delete
On 12/9/2015 12:06 PM, Artem Kuchin wrote: 09.12.2015 19:35, shawn l.green пишет: INSERT operations are special as you can enable a mode to allow INSERTs to happen only at the end of the file and not be blocked while one of the other two operations are in progress. Cannot find anything about that. Can you be a little more specific? It is unrelated the my question, but would be great to have too. Artem It's been a long while since I had to think about the variable --concurrent-insert so I blurred the lines just a little. I apologize. Changing the mode cannot allow concurrent UPDATE or DELETE with an INSERT. The UPDATE or DELETE will always ask for a full table lock. It only allows for concurrent SELECT and INSERT commands to happen at the same time to the same MyISAM table. http://dev.mysql.com/doc/refman/5.6/en/concurrent-inserts.html -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Global read lock on delete
09.12.2015 19:35, shawn l.green пишет: INSERT operations are special as you can enable a mode to allow INSERTs to happen only at the end of the file and not be blocked while one of the other two operations are in progress. Cannot find anything about that. Can you be a little more specific? It is unrelated the my question, but would be great to have too. Artem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Global read lock on delete
09.12.2015 19:35, shawn l.green пишет: On 12/9/2015 9:59 AM, Artem Kuchin wrote: Hello! |THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMATABLE_NAME 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITGlobal read lock 270022 MDL_INTENTION_EXCLUSIVE MDL_STATEMENT Global read lock 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearchsobjects 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearches 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITSchema metadata lock spru 270022 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION Schema metadata lock spru You gave the answer in your last statement: "All tables are myisam" . The MyISAM storage engine is not transactional and it does not do row-level locking. All UPDATE and DELETE operations require a full table lock to perform and those must wait for all earlier readers or writers to exit the table before they can start. INSERT operations are special as you can enable a mode to allow INSERTs to happen only at the end of the file and not be blocked while one of the other two operations are in progress. TABLE LOCK is okay, i understand that. But i see GLOBAL READ LOCK - not table lock, but GLOBAL. As i understand that it means ALL TABLES IN ALL DATABASES. Why? Artem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Global read lock on delete
On 12/9/2015 9:59 AM, Artem Kuchin wrote: Hello! I am actually using MariaDB, but they do not seem to have any public discussion system and i suppose that engine is the same basically, so, problems are probably the same. Today i setup the server to show locks and notice this: |THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMATABLE_NAME 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITGlobal read lock 270022 MDL_INTENTION_EXCLUSIVE MDL_STATEMENT Global read lock 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearchsobjects 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearches 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITSchema metadata lock spru 270022 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION Schema metadata lock spru The threads are ID: 270022 TIME:185 COMMAND:Query STATE:Waiting for table metadata lock USER:spru DB:spru LOCK TABLES searchsobjects WRITE, searches WRITE ID: 268871 TIME:3 COMMAND:Query STATE:updating USER:spru DB:spru DELETE FROM searchsobjects WHERE search_id IN ( 3680622,3677720,3679348,3679347,3680621,3678106,3678105,3680597,3680596,3680595,3676915,3676914,3676913,36777 19,3677718,3677717,3677716,3676984,3677795,3677794,3677793,3677792,3677796,3677802,3677801,3677800,3677799,3677798,3677797,3680580,3676988,3677791,3680589,36 77790,3677789,3677788,3677787,3677786,3677785,3677784,3677783,3677782,3680575,3677781,3677780,369,368,367,366,365,364,363,362 ,361,360,3677769,3677768,3677767,3677766,3677765,3677764,3680619,3680620,3682405,3677763,3677762,3677761,3677760,3677759,3677758,3680601,3677757,3680 627,3680628,3680576,3680577,3680625,3680626,3680624,3680623,3677754,3679280,3679279,3679278,3679277,3679276,3679867,3679890,3680588,3677753,3677064,3677752,3 677751,3677750,3677749,3679608,3679607,3679606,3679605,3680613 ) So, by thread id it seems like DELETE started first and the LOCK TABLES was issued. However, i do not understand how GLOBAL READ LOCK became involved in this all? And both lock tables and delete requested global read lock. All tables are myisam. MariaDB is 10.0.22 (mysql 5.6 based as i understand) Artem You gave the answer in your last statement: "All tables are myisam" . The MyISAM storage engine is not transactional and it does not do row-level locking. All UPDATE and DELETE operations require a full table lock to perform and those must wait for all earlier readers or writers to exit the table before they can start. INSERT operations are special as you can enable a mode to allow INSERTs to happen only at the end of the file and not be blocked while one of the other two operations are in progress. -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Global read lock on delete
Hello! I am actually using MariaDB, but they do not seem to have any public discussion system and i suppose that engine is the same basically, so, problems are probably the same. Today i setup the server to show locks and notice this: |THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMATABLE_NAME 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITGlobal read lock 270022 MDL_INTENTION_EXCLUSIVE MDL_STATEMENT Global read lock 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearchsobjects 268871 MDL_SHARED_NO_READ_WRITEMDL_EXPLICITTable metadata lock sprusearches 268871 MDL_INTENTION_EXCLUSIVE MDL_EXPLICITSchema metadata lockspru 270022 MDL_INTENTION_EXCLUSIVE MDL_TRANSACTION Schema metadata lockspru The threads are ID: 270022 TIME:185 COMMAND:Query STATE:Waiting for table metadata lock USER:spru DB:spru LOCK TABLES searchsobjects WRITE, searches WRITE ID: 268871 TIME:3 COMMAND:Query STATE:updating USER:spru DB:spru DELETE FROM searchsobjects WHERE search_id IN ( 3680622,3677720,3679348,3679347,3680621,3678106,3678105,3680597,3680596,3680595,3676915,3676914,3676913,36777 19,3677718,3677717,3677716,3676984,3677795,3677794,3677793,3677792,3677796,3677802,3677801,3677800,3677799,3677798,3677797,3680580,3676988,3677791,3680589,36 77790,3677789,3677788,3677787,3677786,3677785,3677784,3677783,3677782,3680575,3677781,3677780,369,368,367,366,365,364,363,362 ,361,360,3677769,3677768,3677767,3677766,3677765,3677764,3680619,3680620,3682405,3677763,3677762,3677761,3677760,3677759,3677758,3680601,3677757,3680 627,3680628,3680576,3680577,3680625,3680626,3680624,3680623,3677754,3679280,3679279,3679278,3679277,3679276,3679867,3679890,3680588,3677753,3677064,3677752,3 677751,3677750,3677749,3679608,3679607,3679606,3679605,3680613 ) So, by thread id it seems like DELETE started first and the LOCK TABLES was issued. However, i do not understand how GLOBAL READ LOCK became involved in this all? And both lock tables and delete requested global read lock. All tables are myisam. MariaDB is 10.0.22 (mysql 5.6 based as i understand) Artem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Possible bug with event and delete...limit ?
Hey list, I noticed a table that was trying to fill the disk before the weekend, so I quickly set up an event to gradually clean it out. Yesterday, however, I returned to find 400+ jobs in state "updating". I disabled the event, but the jobs hadn't cleared up today, so I had to kill them. I noticed, however, that the LIMIT statement I specified in the event wasn't present in the actual queries... Could that be a parser bug, or does the limit simply not show up in the process lists? Has anyone seen this before ? This is 5.5.30-1.1-log on Debian 64-bit. Thanks, Johan mysql> show create event jdmsyslogcleaner\G *** 1. row *** Event: jdmsyslogcleaner sql_mode: time_zone: SYSTEM Create Event: CREATE DEFINER=`root`@`localhost` EVENT `jdmsyslogcleaner` ON SCHEDULE EVERY 30 SECOND STARTS '2014-09-19 19:14:21' ON COMPLETION PRESERVE DISABLE COMMENT 'Cleanup to not kill the disk' DO delete from syslog where logtime < "2014-07-20" limit 1 character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) mysql> select * from information_schema.processlist WHERE `INFO` LIKE 'DELETE FROM `cacti%' order by time; +---+---++---+-+---+--++ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +---+---++---+-+---+--++ | 149192515 | cacti_net | host:49225 | cacti_net | Query | 21 | init | DELETE FROM `cacti_net`.`syslog` WHERE logtime < '2014-06-24 08:48:28' | [...] | 148845878 | cacti_net | host:50186 | cacti_net | Query | 47345 | updating | DELETE FROM `cacti_net`.`syslog` WHERE logtime < '2014-06-23 17:13:51' | +---+---++---+-+---+--++ 411 rows in set (13.66 sec) -- What's tiny and yellow and very, very dangerous? A canary with the root password.
RE: Performance of delete using in
Please provide SHOW CREATE TABLE cdsem_event_message_idx \G EXPLAIN SELECT * FROM cdsem_event_message_idx where event_id in () \G SHOW VARIABLES LIKE 'autocommit'; These can impact DELETE speed: * secondary indexes * whether event_id is indexed. * disk type and speed -- ordinary SATA vs RAID vs SSD vs ... * ENGINE -- SHOW CREATE will provide that info * MySQL version -- perhaps IN optimization has improved over time Rule of Thumb: 100 iops. Hence 1500 deletes is likely to take 15 seconds if they are randomly place, no secondary keys, and on non-RAIDed SATA drive. DELETEing one row at a time incurs network and parsing overhead, so it is not surprising that it is slower. That seems like a lot of overhead, so I would guess you are using InnoDB and have most of autocommit=1 and sync_binlog=1 and innodb_flush_log_at_trx_commit=1 > -Original Message- > From: Denis Jedig [mailto:d...@syneticon.net] > Sent: Wednesday, April 24, 2013 10:50 PM > To: mysql@lists.mysql.com > Subject: Re: Performance of delete using in > > Larry, > > Am 25.04.2013 02:19, schrieb Larry Martell: > > > delete from cdsem_event_message_idx where event_id in () > > > > The in clause has around 1,500 items in it. > > Consider creating a temporary table, filling it with your "IN" > values and joining it to cdsem_event_message_idx ON event_id for > deletion. > > Kind regards, > > Denis Jedig > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Performance of delete using in
Larry, Am 25.04.2013 02:19, schrieb Larry Martell: delete from cdsem_event_message_idx where event_id in () The in clause has around 1,500 items in it. Consider creating a temporary table, filling it with your "IN" values and joining it to cdsem_event_message_idx ON event_id for deletion. Kind regards, Denis Jedig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Performance of delete using in
I changed it to delete one row at a time and it's taking 3 minutes. On Wed, Apr 24, 2013 at 6:52 PM, Larry Martell wrote: > That is the entire sql statement - I didn't think I needed to list the > 1500 ints that are in the in clause. > > Also want to mention that I ran explain on it, and it is using the > index on event_id. > > On Wed, Apr 24, 2013 at 6:49 PM, Michael Dykman wrote: >> You would have to show us the whole sql statement but often 'in' clauses can >> be refactored into equivalent joins which tend to improve performance >> tremendously. >> >> - michael dykman >> >> >> On Wed, Apr 24, 2013 at 8:19 PM, Larry Martell >> wrote: >>> >>> I have a table that has 2.5 million rows and 9 columns that are all >>> int except for 2 varchar(255) - i.e. not that big of a table. I am >>> executing a delete from that table like this: >>> >>> delete from cdsem_event_message_idx where event_id in () >>> >>> The in clause has around 1,500 items in it. event_id is an int, and >>> there is an index on event_id. This statement is taking 1 hour and 5 >>> minutes to run. There is nothing else hitting the database at that >>> time, and the machine it's running on is 97% idle and has plenty of >>> free memory. This seems extremely excessive to me. I would guess it's >>> because of the in clause. Is there some better way to do a delete like >>> this? >>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe:http://lists.mysql.com/mysql >>> >> >> >> >> -- >> - michael dykman >> - mdyk...@gmail.com >> >> May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Performance of delete using in
That is the entire sql statement - I didn't think I needed to list the 1500 ints that are in the in clause. Also want to mention that I ran explain on it, and it is using the index on event_id. On Wed, Apr 24, 2013 at 6:49 PM, Michael Dykman wrote: > You would have to show us the whole sql statement but often 'in' clauses can > be refactored into equivalent joins which tend to improve performance > tremendously. > > - michael dykman > > > On Wed, Apr 24, 2013 at 8:19 PM, Larry Martell > wrote: >> >> I have a table that has 2.5 million rows and 9 columns that are all >> int except for 2 varchar(255) - i.e. not that big of a table. I am >> executing a delete from that table like this: >> >> delete from cdsem_event_message_idx where event_id in () >> >> The in clause has around 1,500 items in it. event_id is an int, and >> there is an index on event_id. This statement is taking 1 hour and 5 >> minutes to run. There is nothing else hitting the database at that >> time, and the machine it's running on is 97% idle and has plenty of >> free memory. This seems extremely excessive to me. I would guess it's >> because of the in clause. Is there some better way to do a delete like >> this? >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql >> > > > > -- > - michael dykman > - mdyk...@gmail.com > > May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Inaccurate return value from DELETE query
I think it's because of the constraint that you've defined on the table. So that the record with friday_id=1 won't be deleted because of the constraint. Sent from my BlackBerry® smartphone from Sinyal Bagus XL, Nyambung Teruuusss...! -Original Message- From: Fayaz Yusuf Khan Date: Sat, 11 Feb 2012 22:03:45 To: Subject: Inaccurate return value from DELETE query CREATE TABLE `People` ( `friday_id` bigint(20) NOT NULL, `parent_id` bigint(20) DEFAULT NULL, PRIMARY KEY (`friday_id`), KEY `parent_id` (`parent_id`), CONSTRAINT `People_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `People` (`friday_id`) ON DELETE CASCADE ) ENGINE=InnoDB; INSERT INTO People (friday_id, parent_id) values (1,1), (2,1); DELETE FROM People; Output: Query OK, 1 row affected Shouldn't this be 2 rows affected? MySQL version 5.1 -- Fayaz Yusuf Khan Cloud developer and architect Dexetra SS, Bangalore, India fayaz.yusuf.khan_AT_gmail_DOT_com fayaz_AT_dexetra_DOT_com +91-9746-830-823
Inaccurate return value from DELETE query
CREATE TABLE `People` ( `friday_id` bigint(20) NOT NULL, `parent_id` bigint(20) DEFAULT NULL, PRIMARY KEY (`friday_id`), KEY `parent_id` (`parent_id`), CONSTRAINT `People_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `People` (`friday_id`) ON DELETE CASCADE ) ENGINE=InnoDB; INSERT INTO People (friday_id, parent_id) values (1,1), (2,1); DELETE FROM People; Output: Query OK, 1 row affected Shouldn't this be 2 rows affected? MySQL version 5.1 -- Fayaz Yusuf Khan Cloud developer and architect Dexetra SS, Bangalore, India fayaz.yusuf.khan_AT_gmail_DOT_com fayaz_AT_dexetra_DOT_com +91-9746-830-823 signature.asc Description: This is a digitally signed message part.
Re: Delete from another table on update.
>>>> 2012/02/06 11:33 -0400, Paul Halliday >>>> Is it possible to wrap a DELETE statement in an ON DUPLICATE KEY UPDATE? Something like: ON DUPLICATE KEY UPDATE host="b1" (DELETE FROM another_table WHERE host="b1") ? <<<<<<<< No; see http://dev.mysql.com/doc/refman/5.5/en/insert.html Such things are done in triggers, also if you only on duplicate key want the deletion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Delete from another table on update.
Is it possible to wrap a DELETE statement in an ON DUPLICATE KEY UPDATE? Something like: ON DUPLICATE KEY UPDATE host="b1" (DELETE FROM another_table WHERE host="b1") ? Thanks. -- Paul Halliday http://www.squertproject.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete all hosts using a wildcard
> > I think what Paul (who wrote a book on MySQL, by the way) was getting at was > that you risk what database folk call "referential integrity issues" if you > mess with *any* data without knowing where else it is used. > [snip]... that was really an important post, excellently written! -Govinda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete all hosts using a wildcard
> From: Tim Dunphy > > ... this is just a test environment so getting rid of those users won't have > any meaningful impact... I think what Paul (who wrote a book on MySQL, by the way) was getting at was that you risk what database folk call "referential integrity issues" if you mess with *any* data without knowing where else it is used. But this has a bigger impact than if you mess up referential integrity on your own tables. It could be that MySQL is making certain assumptions -- such as a `user` record WILL be available if referenced in some other privilege grant -- that will break things badly, making such tables (or functions, or procs, etc.) unreachable. This could turn into a very confusing "learning opportunity" where changing one thing has far-reaching unintended impact. Or it may not, if you don't have other privileges defined, in which case you may have "learned" the false assurance that you can get away with such a thing. I've had the former "learning experience" -- that messing with privilege tables directly resulted in strange behaviour that ended with me trashing the entire thing and re-installing from scratch. My rule-of-thumb: if MySQL gives you a facility for manipulating system-level tables, just use it! :-) Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete all hosts using a wildcard
I wouldn't recommend 'playing' with the grant tables instead use the designated commands. Anyway keep in mind that if you modify the grant tables manually you have to force the reload of the privileges in memory by using the 'flush privileges'. Not needed if you use GRANT/REVOKE etc. Cheers Claudio On Jan 15, 2012 1:28 AM, "Tim Dunphy" wrote: > Hello again list, > > Thanks for pointing out where I was making my mistake. I just needed to > select the right field. And this is just a test environment so getting rid > of those users won't have any meaningful impact. Also previewing what you > will be deleting by using a select is great advice I intend to use. > > Best > tim > > - Original Message - > From: "Paul DuBois" > To: "Tim Dunphy" > Cc: mysql@lists.mysql.com > Sent: Saturday, January 14, 2012 6:46:38 PM > Subject: Re: delete all hosts using a wildcard > > > On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote: > > > hello list, > > > > I have a number of hosts that I would like to delete using a wildcard > (%) symbol. > > > > Here is the query I am using: > > > > mysql> delete from mysql.user where user='%.summitnjhome.com'; > > Couple of things: > > * You want to compare your pattern to the host column, not user. > * To match the pattern, use LIKE, not =. > > So: WHERE host LIKE '%.summitnjhome.com' > > But to see what rows your DELETE will affect, try this first: > > SELECT * FROM mysql.user WHERE host LIKE '%.summitnjhome.com'; > > Something else to consider: What if these accounts have privileges > defined in the other grant tables, such as database-level privileges > in the db table? > > > http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like > http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html > > > Query OK, 0 rows affected (0.00 sec) > > > > And I am attempting to delete all the hosts at the domain ' > summitnjhome.com'... > > > > But as you can see I am unsuccessful: > > > > mysql> select user,host from mysql.user; > > +--+-+ > > | user | host| > > +--+-+ > > | root | 127.0.0.1 | > > | repl | virtcent10.summitnjhome.com | > > | admin| virtcent11.summitnjhome.com | > > | repl | virtcent19.summitnjhome.com | > > | repl | virtcent23.summitnjhome.com | > > | repl | virtcent30.summitnjhome.com | > > +--+-+ > > > > > > I know I can delete them individually and this is what I am going to do. > But I would like to use this as a learning opportunity to help me > understand how the wildcard works. > > > > Thanks in advance.. > > > > Best regards, > > Tim > > -- > Paul DuBois > Oracle Corporation / MySQL Documentation Team > Madison, Wisconsin, USA > www.mysql.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > >
Re: delete all hosts using a wildcard
Hello again list, Thanks for pointing out where I was making my mistake. I just needed to select the right field. And this is just a test environment so getting rid of those users won't have any meaningful impact. Also previewing what you will be deleting by using a select is great advice I intend to use. Best tim - Original Message - From: "Paul DuBois" To: "Tim Dunphy" Cc: mysql@lists.mysql.com Sent: Saturday, January 14, 2012 6:46:38 PM Subject: Re: delete all hosts using a wildcard On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote: > hello list, > > I have a number of hosts that I would like to delete using a wildcard (%) > symbol. > > Here is the query I am using: > > mysql> delete from mysql.user where user='%.summitnjhome.com'; Couple of things: * You want to compare your pattern to the host column, not user. * To match the pattern, use LIKE, not =. So: WHERE host LIKE '%.summitnjhome.com' But to see what rows your DELETE will affect, try this first: SELECT * FROM mysql.user WHERE host LIKE '%.summitnjhome.com'; Something else to consider: What if these accounts have privileges defined in the other grant tables, such as database-level privileges in the db table? http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html > Query OK, 0 rows affected (0.00 sec) > > And I am attempting to delete all the hosts at the domain > 'summitnjhome.com'... > > But as you can see I am unsuccessful: > > mysql> select user,host from mysql.user; > +--+-+ > | user | host| > +--+-+ > | root | 127.0.0.1 | > | repl | virtcent10.summitnjhome.com | > | admin| virtcent11.summitnjhome.com | > | repl | virtcent19.summitnjhome.com | > | repl | virtcent23.summitnjhome.com | > | repl | virtcent30.summitnjhome.com | > +--+-+ > > > I know I can delete them individually and this is what I am going to do. But > I would like to use this as a learning opportunity to help me understand how > the wildcard works. > > Thanks in advance.. > > Best regards, > Tim -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete all hosts using a wildcard
On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote: > hello list, > > I have a number of hosts that I would like to delete using a wildcard (%) > symbol. > > Here is the query I am using: > > mysql> delete from mysql.user where user='%.summitnjhome.com'; Couple of things: * You want to compare your pattern to the host column, not user. * To match the pattern, use LIKE, not =. So: WHERE host LIKE '%.summitnjhome.com' But to see what rows your DELETE will affect, try this first: SELECT * FROM mysql.user WHERE host LIKE '%.summitnjhome.com'; Something else to consider: What if these accounts have privileges defined in the other grant tables, such as database-level privileges in the db table? http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html > Query OK, 0 rows affected (0.00 sec) > > And I am attempting to delete all the hosts at the domain > 'summitnjhome.com'... > > But as you can see I am unsuccessful: > > mysql> select user,host from mysql.user; > +--+-+ > | user | host| > +--+-+ > | root | 127.0.0.1 | > | repl | virtcent10.summitnjhome.com | > | admin| virtcent11.summitnjhome.com | > | repl | virtcent19.summitnjhome.com | > | repl | virtcent23.summitnjhome.com | > | repl | virtcent30.summitnjhome.com | > +--+-+ > > > I know I can delete them individually and this is what I am going to do. But > I would like to use this as a learning opportunity to help me understand how > the wildcard works. > > Thanks in advance.. > > Best regards, > Tim -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete syntax
>> >>> well, i am using delete/insert-statements since 10 years to maintain >>> users since you only have to know the tables in the database "mysql" >>> and use "flush privileges" after changes >>> >>> The privileges should be maintained only using the designated commands. >> You cannot rely on the knowledge you have of the underlying implementation >> which can change anytime , while the privileges command are standard. > > do what you think is good for you if YOU can't be sure what you do > [snip] > so please do not tell other peopole on what knowledge they can rely > *all* the back and forth in these threads is good.. is susses out all the knowledge for everyone to see. Sincerely thanks to everyone who chimes in from all perspectives, -Govinda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete syntax
Am 02.12.2011 21:59, schrieb Claudio Nanni: > 2011/12/2 Reindl Harald > >> well, i am using delete/insert-statements since 10 years to maintain >> users since you only have to know the tables in the database "mysql" >> and use "flush privileges" after changes >> >> The privileges should be maintained only using the designated commands. > You cannot rely on the knowledge you have of the underlying implementation > which can change anytime , while the privileges command are standard. do what you think is good for you if YOU can't be sure what you do there where i work i test updates and look at the user-tables and that is why i fixed problems where root did not have the right permissions after upgrade to 5.1 what was not corrected with "mysql_upgrade" an rolled out without any problems the same way i currently roll out fedora 15 on 24 production servers because i know what i do and have the infrastructure to prepare such major-upgrades to do them finally live after all tests are successfull and the local cach- and internal-repos are filled so please do not tell other peopole on what knowledge they can rely signature.asc Description: OpenPGP digital signature
Re: delete syntax
2011/12/2 Reindl Harald > well, i am using delete/insert-statements since 10 years to maintain > users since you only have to know the tables in the database "mysql" > and use "flush privileges" after changes > > The privileges should be maintained only using the designated commands. You cannot rely on the knowledge you have of the underlying implementation which can change anytime , while the privileges command are standard. *Cheers* Claudio Nanni > DROP USER is the only SINGLE COMMAND > > as long as you do not use table/column-privileges there are exactly > two relevant tables: "user" and "db" > > Am 02.12.2011 05:15, schrieb Stdranwl: > > DROP USER command is the only command to remove any user and its > > association from all other tables. > > > > On Fri, Dec 2, 2011 at 8:22 AM, Reindl Harald >wrote: > > > >> ALWAYS > >> start with "select * from mysql.user where user='mail_admin' and host > like > >> '\%';" > >> and look what records are affected to make sure the were-statement > works as > >> expected and then use "CURSOR UP" and edit the last command to "delete > >> from" > >> > >> not only doing this while unsure with escapes protects you against > logical > >> mistakes like forget a "and column=1" and get "1000 rows affected" with > no > >> way back > > -- Claudio
Re: delete syntax
well, i am using delete/insert-statements since 10 years to maintain users since you only have to know the tables in the database "mysql" and use "flush privileges" after changes DROP USER is the only SINGLE COMMAND as long as you do not use table/column-privileges there are exactly two relevant tables: "user" and "db" Am 02.12.2011 05:15, schrieb Stdranwl: > DROP USER command is the only command to remove any user and its > association from all other tables. > > On Fri, Dec 2, 2011 at 8:22 AM, Reindl Harald wrote: > >> ALWAYS >> start with "select * from mysql.user where user='mail_admin' and host like >> '\%';" >> and look what records are affected to make sure the were-statement works as >> expected and then use "CURSOR UP" and edit the last command to "delete >> from" >> >> not only doing this while unsure with escapes protects you against logical >> mistakes like forget a "and column=1" and get "1000 rows affected" with no >> way back signature.asc Description: OpenPGP digital signature
Re: delete syntax
DROP USER command is the only command to remove any user and its association from all other tables. Cheers On Fri, Dec 2, 2011 at 8:22 AM, Reindl Harald wrote: > ALWAYS > start with "select * from mysql.user where user='mail_admin' and host like > '\%';" > and look what records are affected to make sure the were-statement works as > expected and then use "CURSOR UP" and edit the last command to "delete > from" > > not only doing this while unsure with escapes protects you against logical > mistakes like forget a "and column=1" and get "1000 rows affected" with no > way back > > Am 02.12.2011 03:43, schrieb Shiva: > > delete from mysql.user where user='mail_admin' and host like '\%' ; > > Note: I haven't tested it and since % is a wildcard you need to escape > it. > > > > On Thu, Dec 1, 2011 at 6:09 PM, Tim Dunphy > wrote: > > > >> > >> Thanks but I probably should have noted that I only want to delete the > >> wildcard user. There are other users I would prefer to not delete. > >> > >> mysql> select user,host from mysql.user where user='mail_admin'; > >> ++---+ > >> | user | host | > >> ++---+ > >> | mail_admin | % | > >> | mail_admin | 127.0.0.1 | > >> | mail_admin | localhost | > >> | mail_admin | localhost.localdomain | > >> ++---+ > >> 4 rows in set (0.00 sec) > >
Re: delete syntax
ALWAYS start with "select * from mysql.user where user='mail_admin' and host like '\%';" and look what records are affected to make sure the were-statement works as expected and then use "CURSOR UP" and edit the last command to "delete from" not only doing this while unsure with escapes protects you against logical mistakes like forget a "and column=1" and get "1000 rows affected" with no way back Am 02.12.2011 03:43, schrieb Shiva: > delete from mysql.user where user='mail_admin' and host like '\%' ; > Note: I haven't tested it and since % is a wildcard you need to escape it. > > On Thu, Dec 1, 2011 at 6:09 PM, Tim Dunphy wrote: > >> >> Thanks but I probably should have noted that I only want to delete the >> wildcard user. There are other users I would prefer to not delete. >> >> mysql> select user,host from mysql.user where user='mail_admin'; >> ++---+ >> | user | host | >> ++---+ >> | mail_admin | % | >> | mail_admin | 127.0.0.1 | >> | mail_admin | localhost | >> | mail_admin | localhost.localdomain | >> ++---+ >> 4 rows in set (0.00 sec) signature.asc Description: OpenPGP digital signature
Re: delete syntax
You can try delete from mysql.user where user='mail_admin' and host like '\%' ; Note: I haven't tested it and since % is a wildcard you need to escape it. Best, Shiv On Thu, Dec 1, 2011 at 6:09 PM, Tim Dunphy wrote: > Hello Krishna, > > > Thanks but I probably should have noted that I only want to delete the > wildcard user. There are other users I would prefer to not delete. > > mysql> select user,host from mysql.user where user='mail_admin'; > ++---+ > | user | host | > ++---+ > | mail_admin | % | > | mail_admin | 127.0.0.1 | > | mail_admin | localhost | > | mail_admin | localhost.localdomain | > ++---+ > 4 rows in set (0.00 sec) > > > sorry for not including enough information last time. > > best > tim > > - Original Message - > From: "Krishna Chandra Prajapati" > To: "Tim Dunphy" > Cc: mysql@lists.mysql.com > Sent: Thursday, December 1, 2011 9:03:46 PM > Subject: Re: delete syntax > > delete from mysql.user where user='mail_admin'; > > Krishna > > > On Fri, Dec 2, 2011 at 7:23 AM, Tim Dunphy < bluethu...@jokefire.com > > wrote: > > > hello list, > > I am attempting to delete a user from the mysql.user table without success. > > mysql> delete from mysql.user where user='mail_admin@%'; > Query OK, 0 rows affected (0.00 sec) > > > mysql> select user,host from mysql.user where user='mail_admin'; > ++---+ > | user | host | > ++---+ > | mail_admin | % | > > > > I would appreciate any advice you may have. > > Regards, > Tim > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > >
Re: delete syntax
On 2011-12-02, Tim Dunphy wrote: > > Thanks but I probably should have noted that I only want to delete the > wildcard user. There are other users I would prefer to not delete. > > mysql> select user,host from mysql.user where user='mail_admin'; > ++---+ >| user | host | > ++---+ >| mail_admin | % | >| mail_admin | 127.0.0.1 | >| mail_admin | localhost | >| mail_admin | localhost.localdomain | > ++---+ > 4 rows in set (0.00 sec) This is just a regular table with a user and host column. If you wanted to delete the localhost row, you'd do delete from mysql.user where user='mail_admin' and host='localhost'; flush privileges; You'd need to flush privileges because you're munging the user table. But it's probably much better to use the DROP USER command. --keith -- kkeller-use...@wombat.san-francisco.ca.us (try just my userid to email me) AOLSFAQ=http://www.therockgarden.ca/aolsfaq.txt see X- headers for PGP signature information -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete syntax
Hello Krishna, Thanks but I probably should have noted that I only want to delete the wildcard user. There are other users I would prefer to not delete. mysql> select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | | mail_admin | 127.0.0.1 | | mail_admin | localhost | | mail_admin | localhost.localdomain | ++---+ 4 rows in set (0.00 sec) sorry for not including enough information last time. best tim - Original Message - From: "Krishna Chandra Prajapati" To: "Tim Dunphy" Cc: mysql@lists.mysql.com Sent: Thursday, December 1, 2011 9:03:46 PM Subject: Re: delete syntax delete from mysql.user where user='mail_admin'; Krishna On Fri, Dec 2, 2011 at 7:23 AM, Tim Dunphy < bluethu...@jokefire.com > wrote: hello list, I am attempting to delete a user from the mysql.user table without success. mysql> delete from mysql.user where user='mail_admin@%'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | I would appreciate any advice you may have. Regards, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete syntax
delete from mysql.user where user='mail_admin'; Krishna On Fri, Dec 2, 2011 at 7:23 AM, Tim Dunphy wrote: > hello list, > > I am attempting to delete a user from the mysql.user table without > success. > > mysql> delete from mysql.user where user='mail_admin@%'; > Query OK, 0 rows affected (0.00 sec) > > > mysql> select user,host from mysql.user where user='mail_admin'; > ++---+ > | user | host | > ++---+ > | mail_admin | % | > > > > I would appreciate any advice you may have. > > Regards, > Tim > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > >
delete syntax
hello list, I am attempting to delete a user from the mysql.user table without success. mysql> delete from mysql.user where user='mail_admin@%'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | I would appreciate any advice you may have. Regards, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
cascade delete question
I have the following 3 tables.. If I have a contact with just notes (no tasks), then I can simply do delete from contacts where id = ; but if the contact has a task, then I get the following error, ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`task`, CONSTRAINT `fk_task_2` FOREIGN KEY (`noteid`) REFERENCES `note` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE) to get this to work, I have to do delete from task where contactid = ; delete from contacts where id = ; I understand the problem, but I would have thought mysql would be smart enough to figure this out, and allow the delete to proceed (since its going to cascade the tasks eventually) is this normal? is there any way to specify the order which it cascades, if it were to delete the tasks before the notes, this would not be a problem... CREATE TABLE `contacts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, ... PRIMARY KEY (`id`) USING BTREE ) CREATE TABLE `note` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `contactid` bigint(20) unsigned NOT NULL, ... PRIMARY KEY (`id`) USING BTREE, KEY `contactid` (`contactid`), CONSTRAINT `fk_note_1` FOREIGN KEY (`contactid`) REFERENCES `contacts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) CREATE TABLE `task` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `contactid` bigint(20) unsigned NOT NULL, `noteid` bigint(20) unsigned NOT NULL, ... PRIMARY KEY (`id`) USING BTREE, KEY `contactid` (`contactid`) USING BTREE, KEY `noteid` (`noteid`) USING BTREE, CONSTRAINT `fk_task_1` FOREIGN KEY (`contactid`) REFERENCES `contacts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_task_2` FOREIGN KEY (`noteid`) REFERENCES `note` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Kill DELETE Query
Yeah. The "Delete from tablename " will remove the record one by one. So you can cancel it whenever you don't need to continue. David Yeung, In China, Beijing. My First Blog:http://yueliangdao0608.cublog.cn My Second Blog:http://yueliangdao0608.blog.51cto.com 2010/12/17 Willy Mularto > Thanks for the confirmation. > > > > sangprabv > sangpr...@gmail.com > http://www.petitiononline.com/froyo/ > > > On Dec 17, 2010, at 1:31 PM, Ananda Kumar wrote: > > > No...it will not. > > > > On Fri, Dec 17, 2010 at 11:26 AM, Willy Mularto > wrote: > > Thanks for the reply. I used non stored procedure approach. Another > question is if I kill the process will it crash the table? Thanks. > > > > > > > > sangprabv > > sangpr...@gmail.com > > http://www.petitiononline.com/froyo/ > > > > > > On Dec 17, 2010, at 12:06 PM, Ananda Kumar wrote: > > > >> If u have used a stored proc to delete the rows, and commting freqently, > then the kill will happen faster. > >> If you have just used "delete from table_name where , then it > would take toot much time to rollback all the deleted but not commited rows. > >> > >> Regards > >> anandkl > >> > >> On Fri, Dec 17, 2010 at 8:37 AM, Willy Mularto > wrote: > >> Hi List, > >> I run a delete query to delete around 1 million rows in innodb table, > It's been hours and still unfinish. Is it safe to kill that delete query > process while the table is also inserting and updating other rows? Thanks. > >> > >> > >> > >> > >> sangprabv > >> sangpr...@gmail.com > >> http://www.petitiononline.com/froyo/ > >> > >> > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com > >> > >> > > > > > >
Re: Kill DELETE Query
Thanks for the confirmation. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ On Dec 17, 2010, at 1:31 PM, Ananda Kumar wrote: > No...it will not. > > On Fri, Dec 17, 2010 at 11:26 AM, Willy Mularto wrote: > Thanks for the reply. I used non stored procedure approach. Another question > is if I kill the process will it crash the table? Thanks. > > > > sangprabv > sangpr...@gmail.com > http://www.petitiononline.com/froyo/ > > > On Dec 17, 2010, at 12:06 PM, Ananda Kumar wrote: > >> If u have used a stored proc to delete the rows, and commting freqently, >> then the kill will happen faster. >> If you have just used "delete from table_name where , then it >> would take toot much time to rollback all the deleted but not commited rows. >> >> Regards >> anandkl >> >> On Fri, Dec 17, 2010 at 8:37 AM, Willy Mularto wrote: >> Hi List, >> I run a delete query to delete around 1 million rows in innodb table, It's >> been hours and still unfinish. Is it safe to kill that delete query process >> while the table is also inserting and updating other rows? Thanks. >> >> >> >> >> sangprabv >> sangpr...@gmail.com >> http://www.petitiononline.com/froyo/ >> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com >> >> > >
Re: Kill DELETE Query
No...it will not. On Fri, Dec 17, 2010 at 11:26 AM, Willy Mularto wrote: > Thanks for the reply. I used non stored procedure approach. Another > question is if I kill the process will it crash the table? Thanks. > > > > sangprabv > sangpr...@gmail.com > http://www.petitiononline.com/froyo/ > > > On Dec 17, 2010, at 12:06 PM, Ananda Kumar wrote: > > If u have used a stored proc to delete the rows, and commting freqently, > then the kill will happen faster. > If you have just used "delete from table_name where , then it > would take toot much time to rollback all the deleted but not commited rows. > > Regards > anandkl > > On Fri, Dec 17, 2010 at 8:37 AM, Willy Mularto wrote: > >> Hi List, >> I run a delete query to delete around 1 million rows in innodb table, It's >> been hours and still unfinish. Is it safe to kill that delete query process >> while the table is also inserting and updating other rows? Thanks. >> >> >> >> >> sangprabv >> sangpr...@gmail.com >> http://www.petitiononline.com/froyo/ >> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com >> >> > >
Re: Kill DELETE Query
Thanks for the reply. I used non stored procedure approach. Another question is if I kill the process will it crash the table? Thanks. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ On Dec 17, 2010, at 12:06 PM, Ananda Kumar wrote: > If u have used a stored proc to delete the rows, and commting freqently, then > the kill will happen faster. > If you have just used "delete from table_name where , then it > would take toot much time to rollback all the deleted but not commited rows. > > Regards > anandkl > > On Fri, Dec 17, 2010 at 8:37 AM, Willy Mularto wrote: > Hi List, > I run a delete query to delete around 1 million rows in innodb table, It's > been hours and still unfinish. Is it safe to kill that delete query process > while the table is also inserting and updating other rows? Thanks. > > > > > sangprabv > sangpr...@gmail.com > http://www.petitiononline.com/froyo/ > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com > >
Re: Kill DELETE Query
If u have used a stored proc to delete the rows, and commting freqently, then the kill will happen faster. If you have just used "delete from table_name where , then it would take toot much time to rollback all the deleted but not commited rows. Regards anandkl On Fri, Dec 17, 2010 at 8:37 AM, Willy Mularto wrote: > Hi List, > I run a delete query to delete around 1 million rows in innodb table, It's > been hours and still unfinish. Is it safe to kill that delete query process > while the table is also inserting and updating other rows? Thanks. > > > > > sangprabv > sangpr...@gmail.com > http://www.petitiononline.com/froyo/ > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com > >
Kill DELETE Query
Hi List, I run a delete query to delete around 1 million rows in innodb table, It's been hours and still unfinish. Is it safe to kill that delete query process while the table is also inserting and updating other rows? Thanks. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does putting a LIMIT on a DELETE clause make any difference?
Correct. To verify this, simply create a select with the same structure as your delete - the execution plan will be similar. I do not believe limit will help you, however, as it is only applied after execution, when the full dataset is known. On Thu, Sep 9, 2010 at 8:06 AM, Ananda Kumar wrote: > Vincent, > > Since the column is indexed, it would use the index during the delete. > > regards > anandkl > > On Thu, Sep 9, 2010 at 5:47 AM, Daevid Vincent wrote: > > > I am curious about something. > > > > I have a "glue" or "hanging" table like so: > > > > CREATE TABLE `fault_impact_has_fault_system_impact` ( > > `id_fault_impact` int(10) unsigned NOT NULL, > > `id_fault_system_impact` smallint(5) unsigned NOT NULL, > > KEY `fault_impact_key` (`id_fault_impact`,`id_fault_system_impact`), > > KEY `id_fault_system_impact` (`id_fault_system_impact`), > > CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_1` FOREIGN KEY > > (`id_fault_impact`) REFERENCES `fault_impact` (`id_fault_impact`) ON > DELETE > > CASCADE ON UPDATE CASCADE, > > CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_2` FOREIGN KEY > > (`id_fault_system_impact`) REFERENCES `fault_system_impact` > > (`id_fault_system_impact`) ON DELETE CASCADE ON UPDATE CASCADE > > ) > > > > And a lookup table like this: > > > > CREATE TABLE `fault_system_impact` ( > > `id_fault_system_impact` smallint(5) unsigned NOT NULL auto_increment, > > `fault_sytem_impact_name` varchar(50) NOT NULL, > > PRIMARY KEY (`id_fault_system_impact`) > > ) > > > > I have a bunch of checkboxes in a and so in order to "update" > > properly, I wipe out all the PK IDs and then start inserting. It looks > like > > this: > > > > UPDATE `fault_impact` SET `bite_code` = '12', `bite_code_type` = '32', > > `bite_subcode` = '21', `description_text` = 'Some random fault > description > > here.', `fault_id` = '11-1', `fault_impact_other_explain` = '', > > `id_fault_area_impact` = '3', `symptom_lru_id` = '232', > `symptom_lru_subid` > > = '34', `sys_perf_affected` = '', `update_date` = '2010-09-09 00:04:29' > > WHERE id_fault_impact = '2495' LIMIT 1; > > > > DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact = > > 2495; > > > > INSERT INTO > > > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > > act`) VALUES(2495, 1); > > INSERT INTO > > > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > > act`) VALUES(2495, 3); > > INSERT INTO > > > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > > act`) VALUES(2495, 2); > > INSERT INTO > > > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > > act`) VALUES(2495, 7); > > INSERT INTO > > > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > > act`) VALUES(2495, 10); > > INSERT INTO > > > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > > act`) VALUES(2495, 14); > > INSERT INTO > > > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > > act`) VALUES(2495, 9); > > INSERT INTO > > > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > > act`) VALUES(2495, 4); > > > > Given that I know there can only be a maximum of id_fault_system_impact > IDs > > -- currently there are 17 rows in the fault_system_impact table -- and > > they're unique to any given id_fault_impact, would it benefit me to > change > > my DELETE statement to something like this: > > > > DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact = > > 2495 LIMIT 17; > > > > Since the fault_impact_has_fault_system_impact table could have thousands > > of rows and it seems that mySQL would do a table scan? Unfortunately, you > > can't "EXPLAIN" on a DELETE to see what it might be doing. :( OR is mySQL > > smart enough to know that the id_fault_impact is an index and therefore > it > > will just be right quick and stop after deleting those 8 rows above? > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com > > > > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Does putting a LIMIT on a DELETE clause make any difference?
Vincent, Since the column is indexed, it would use the index during the delete. regards anandkl On Thu, Sep 9, 2010 at 5:47 AM, Daevid Vincent wrote: > I am curious about something. > > I have a "glue" or "hanging" table like so: > > CREATE TABLE `fault_impact_has_fault_system_impact` ( > `id_fault_impact` int(10) unsigned NOT NULL, > `id_fault_system_impact` smallint(5) unsigned NOT NULL, > KEY `fault_impact_key` (`id_fault_impact`,`id_fault_system_impact`), > KEY `id_fault_system_impact` (`id_fault_system_impact`), > CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_1` FOREIGN KEY > (`id_fault_impact`) REFERENCES `fault_impact` (`id_fault_impact`) ON DELETE > CASCADE ON UPDATE CASCADE, > CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_2` FOREIGN KEY > (`id_fault_system_impact`) REFERENCES `fault_system_impact` > (`id_fault_system_impact`) ON DELETE CASCADE ON UPDATE CASCADE > ) > > And a lookup table like this: > > CREATE TABLE `fault_system_impact` ( > `id_fault_system_impact` smallint(5) unsigned NOT NULL auto_increment, > `fault_sytem_impact_name` varchar(50) NOT NULL, > PRIMARY KEY (`id_fault_system_impact`) > ) > > I have a bunch of checkboxes in a and so in order to "update" > properly, I wipe out all the PK IDs and then start inserting. It looks like > this: > > UPDATE `fault_impact` SET `bite_code` = '12', `bite_code_type` = '32', > `bite_subcode` = '21', `description_text` = 'Some random fault description > here.', `fault_id` = '11-1', `fault_impact_other_explain` = '', > `id_fault_area_impact` = '3', `symptom_lru_id` = '232', `symptom_lru_subid` > = '34', `sys_perf_affected` = '', `update_date` = '2010-09-09 00:04:29' > WHERE id_fault_impact = '2495' LIMIT 1; > > DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact = > 2495; > > INSERT INTO > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > act`) VALUES(2495, 1); > INSERT INTO > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > act`) VALUES(2495, 3); > INSERT INTO > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > act`) VALUES(2495, 2); > INSERT INTO > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > act`) VALUES(2495, 7); > INSERT INTO > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > act`) VALUES(2495, 10); > INSERT INTO > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > act`) VALUES(2495, 14); > INSERT INTO > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > act`) VALUES(2495, 9); > INSERT INTO > fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp > act`) VALUES(2495, 4); > > Given that I know there can only be a maximum of id_fault_system_impact IDs > -- currently there are 17 rows in the fault_system_impact table -- and > they're unique to any given id_fault_impact, would it benefit me to change > my DELETE statement to something like this: > > DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact = > 2495 LIMIT 17; > > Since the fault_impact_has_fault_system_impact table could have thousands > of rows and it seems that mySQL would do a table scan? Unfortunately, you > can't "EXPLAIN" on a DELETE to see what it might be doing. :( OR is mySQL > smart enough to know that the id_fault_impact is an index and therefore it > will just be right quick and stop after deleting those 8 rows above? > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com > >
Does putting a LIMIT on a DELETE clause make any difference?
I am curious about something. I have a "glue" or "hanging" table like so: CREATE TABLE `fault_impact_has_fault_system_impact` ( `id_fault_impact` int(10) unsigned NOT NULL, `id_fault_system_impact` smallint(5) unsigned NOT NULL, KEY `fault_impact_key` (`id_fault_impact`,`id_fault_system_impact`), KEY `id_fault_system_impact` (`id_fault_system_impact`), CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_1` FOREIGN KEY (`id_fault_impact`) REFERENCES `fault_impact` (`id_fault_impact`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_2` FOREIGN KEY (`id_fault_system_impact`) REFERENCES `fault_system_impact` (`id_fault_system_impact`) ON DELETE CASCADE ON UPDATE CASCADE ) And a lookup table like this: CREATE TABLE `fault_system_impact` ( `id_fault_system_impact` smallint(5) unsigned NOT NULL auto_increment, `fault_sytem_impact_name` varchar(50) NOT NULL, PRIMARY KEY (`id_fault_system_impact`) ) I have a bunch of checkboxes in a and so in order to "update" properly, I wipe out all the PK IDs and then start inserting. It looks like this: UPDATE `fault_impact` SET `bite_code` = '12', `bite_code_type` = '32', `bite_subcode` = '21', `description_text` = 'Some random fault description here.', `fault_id` = '11-1', `fault_impact_other_explain` = '', `id_fault_area_impact` = '3', `symptom_lru_id` = '232', `symptom_lru_subid` = '34', `sys_perf_affected` = '', `update_date` = '2010-09-09 00:04:29' WHERE id_fault_impact = '2495' LIMIT 1; DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact = 2495; INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 1); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 3); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 2); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 7); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 10); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 14); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 9); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 4); Given that I know there can only be a maximum of id_fault_system_impact IDs -- currently there are 17 rows in the fault_system_impact table -- and they're unique to any given id_fault_impact, would it benefit me to change my DELETE statement to something like this: DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact = 2495 LIMIT 17; Since the fault_impact_has_fault_system_impact table could have thousands of rows and it seems that mySQL would do a table scan? Unfortunately, you can't "EXPLAIN" on a DELETE to see what it might be doing. :( OR is mySQL smart enough to know that the id_fault_impact is an index and therefore it will just be right quick and stop after deleting those 8 rows above? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: AFTER Delete Trigger question
As far as I know, you can´t change data on the same table in triggers. "Kevin Labecot" escreveu na mensagem news:4d2ce38b-d169-478b-aebf-c19f20dce...@labecot.fr... Hi, Is there a way to update the same table on a delete trigger ? I need to call an UPDATE statement when a delete occurs. Best regards -- Kevin Labecot, Innovantic www.innovantic.fr Tél. : 05.56.45.60.54
AFTER Delete Trigger question
Hi,Is there a way to update the same table on a delete trigger ?I need to call an UPDATE statement when a delete occurs.Best regards -- Kevin Labecot, Innovanticwww.innovantic.frTél. : 05.56.45.60.54
Re: Replication : request DELETE is not executed on slave
it's worth a try -- the manual for 4.1 has the same text about ORDER BY in section 14.7. On 4/28/10 12:30 PM, "David Florella" wrote: > Hi, > > Thanks to you and everyone. > > I will test the same request with the ORDER BY clause. > > Regards, > > David. > > -Message d'origine- > De : Mattia Merzi [mailto:mattia.me...@gmail.com] > Envoyé : mercredi 28 avril 2010 17:54 > À : mysql@lists.mysql.com > Objet : Re: Replication : request DELETE is not executed on slave > > AFAIR you can use LIMIT with replication only if you use row-based > replication (or mixed), that means that you must use mysql 5.1. > > Greetings, > > Mattia. > > > 2010/4/28 Tom Worster : >> 16.3.1.9. Replication and LIMIT >> Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT >> statements is not guaranteed, since the order of the rows affected is not >> defined. Such statements can be replicated correctly only if they also >> contain an ORDER BY clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication : request DELETE is not executed on slave
Hi, Thanks to you and everyone. I will test the same request with the ORDER BY clause. Regards, David. -Message d'origine- De : Mattia Merzi [mailto:mattia.me...@gmail.com] Envoyé : mercredi 28 avril 2010 17:54 À : mysql@lists.mysql.com Objet : Re: Replication : request DELETE is not executed on slave AFAIR you can use LIMIT with replication only if you use row-based replication (or mixed), that means that you must use mysql 5.1. Greetings, Mattia. 2010/4/28 Tom Worster : > 16.3.1.9. Replication and LIMIT > Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT > statements is not guaranteed, since the order of the rows affected is not > defined. Such statements can be replicated correctly only if they also > contain an ORDER BY clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dflore...@legos.fr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication : request DELETE is not executed on slave
AFAIR you can use LIMIT with replication only if you use row-based replication (or mixed), that means that you must use mysql 5.1. Greetings, Mattia. 2010/4/28 Tom Worster : > 16.3.1.9. Replication and LIMIT > Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT > statements is not guaranteed, since the order of the rows affected is not > defined. Such statements can be replicated correctly only if they also > contain an ORDER BY clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication : request DELETE is not executed on slave
16.3.1.9. Replication and LIMIT Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements is not guaranteed, since the order of the rows affected is not defined. Such statements can be replicated correctly only if they also contain an ORDER BY clause. http://dev.mysql.com/doc/refman/5.0/en/replication-features-limit.html On 4/28/10 11:24 AM, "Jerry Schwartz" wrote: >> -Original Message- >> From: David Florella [mailto:dflore...@legos.fr] >> Sent: Wednesday, April 28, 2010 10:51 AM >> To: mysql@lists.mysql.com >> Cc: 'Krishna Chandra Prajapati' >> Subject: RE: Replication : request DELETE is not executed on slave >> >> Hi, >> >> In the MySQL documentation, it is written that the two versions are >> compatible to make a replication. >> >> It seems that if I make a DELETE without the 'LIMIT 7500', the query is >> replicated to the slave. >> > [JS] This might be my ignorance speaking, but since record order is not > defined for a database how would the slave know WHICH records to delete. > > The DELETE... LIMIT 7500 would have to be translated into 7500 separate > DELETEs, and there would have to be some unique way of identifying the > individual records. > > Regards, > > Jerry Schwartz > Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > www.the-infoshop.com > > > >> Regards, >> >> David. >> >> >> -Message d'origine- >> De : Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] >> Envoy� : mercredi 28 avril 2010 11:15 >> � : dflore...@legos.fr >> Cc : mysql@lists.mysql.com >> Objet : Re: Replication : request DELETE is not executed on slave >> >> Hi dflorella, >> >> The important thing about mysql replication is same mysql version for both >> master as well as slave should be used. It should be taken as good practice. >> >> You need to check that master and slave are in sync. Is there any error >> (replication) on the slave server. Check the mode, strict or some thing >> else. >> >> Does the delete command exits in binlog. >> >> Regards, >> Krishna >> >> >> On Wed, Apr 28, 2010 at 2:37 PM, David Florella wrote: >> >>> Hi, >>> >>> >>> >>> I am using MySQL replication : >>> >>> >>> >>> - The version of the master is 4.1.12-log >>> >>> - The version of the slave is 5.0.41 >>> >>> >>> >>> When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] < 'xx' >>> LIMIT 7500", the query is executed on the master but not on the slave. >>> >>> >>> >>> Do you know why the request is not executed on the slave? >>> >>> >>> >>> Regards, >>> >>> >>> >>> David. >>> >>> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication : request DELETE is not executed on slave
>-Original Message- >From: David Florella [mailto:dflore...@legos.fr] >Sent: Wednesday, April 28, 2010 10:51 AM >To: mysql@lists.mysql.com >Cc: 'Krishna Chandra Prajapati' >Subject: RE: Replication : request DELETE is not executed on slave > >Hi, > >In the MySQL documentation, it is written that the two versions are >compatible to make a replication. > >It seems that if I make a DELETE without the 'LIMIT 7500', the query is >replicated to the slave. > [JS] This might be my ignorance speaking, but since record order is not defined for a database how would the slave know WHICH records to delete. The DELETE... LIMIT 7500 would have to be translated into 7500 separate DELETEs, and there would have to be some unique way of identifying the individual records. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com >Regards, > >David. > > >-Message d'origine- >De : Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] >Envoyé : mercredi 28 avril 2010 11:15 >À : dflore...@legos.fr >Cc : mysql@lists.mysql.com >Objet : Re: Replication : request DELETE is not executed on slave > >Hi dflorella, > >The important thing about mysql replication is same mysql version for both >master as well as slave should be used. It should be taken as good practice. > >You need to check that master and slave are in sync. Is there any error >(replication) on the slave server. Check the mode, strict or some thing >else. > >Does the delete command exits in binlog. > >Regards, >Krishna > > >On Wed, Apr 28, 2010 at 2:37 PM, David Florella wrote: > >> Hi, >> >> >> >> I am using MySQL replication : >> >> >> >> - The version of the master is 4.1.12-log >> >> - The version of the slave is 5.0.41 >> >> >> >> When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] < 'xx' >> LIMIT 7500", the query is executed on the master but not on the slave. >> >> >> >> Do you know why the request is not executed on the slave? >> >> >> >> Regards, >> >> >> >> David. >> >> > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication : request DELETE is not executed on slave
Hi, In the MySQL documentation, it is written that the two versions are compatible to make a replication. It seems that if I make a DELETE without the 'LIMIT 7500', the query is replicated to the slave. Regards, David. -Message d'origine- De : Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Envoyé : mercredi 28 avril 2010 11:15 À : dflore...@legos.fr Cc : mysql@lists.mysql.com Objet : Re: Replication : request DELETE is not executed on slave Hi dflorella, The important thing about mysql replication is same mysql version for both master as well as slave should be used. It should be taken as good practice. You need to check that master and slave are in sync. Is there any error (replication) on the slave server. Check the mode, strict or some thing else. Does the delete command exits in binlog. Regards, Krishna On Wed, Apr 28, 2010 at 2:37 PM, David Florella wrote: > Hi, > > > > I am using MySQL replication : > > > > - The version of the master is 4.1.12-log > > - The version of the slave is 5.0.41 > > > > When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] < 'xx' > LIMIT 7500", the query is executed on the master but not on the slave. > > > > Do you know why the request is not executed on the slave? > > > > Regards, > > > > David. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication : request DELETE is not executed on slave
Hi dflorella, The important thing about mysql replication is same mysql version for both master as well as slave should be used. It should be taken as good practice. You need to check that master and slave are in sync. Is there any error (replication) on the slave server. Check the mode, strict or some thing else. Does the delete command exits in binlog. Regards, Krishna On Wed, Apr 28, 2010 at 2:37 PM, David Florella wrote: > Hi, > > > > I am using MySQL replication : > > > > - The version of the master is 4.1.12-log > > - The version of the slave is 5.0.41 > > > > When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] < 'xx' > LIMIT 7500", the query is executed on the master but not on the slave. > > > > Do you know why the request is not executed on the slave? > > > > Regards, > > > > David. > >
Replication : request DELETE is not executed on slave
Hi, I am using MySQL replication : - The version of the master is 4.1.12-log - The version of the slave is 5.0.41 When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] < 'xx' LIMIT 7500", the query is executed on the master but not on the slave. Do you know why the request is not executed on the slave? Regards, David.
RE: Make delete requests without impact on a database
Hi evryone, Thank you for the advice. I will analyze the differences between InnoDB and MyISAM before switching. 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, David. -Message d'origine- De : phark...@gmail.com [mailto:phark...@gmail.com] De la part de Perrin Harkins Envoyé : jeudi 15 avril 2010 02:36 À : Dan Nelson Cc : David Florella; mysql@lists.mysql.com Objet : 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=arch...@jab.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=arch...@jab.org
RE: Make delete requests without impact on a database
> -Original Message- > From: Dan Nelson [mailto:dnel...@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=arch...@jab.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=arch...@jab.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 9 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=arch...@jab.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 9 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=arch...@jab.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 9 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=arch...@jab.org
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 9 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 dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Make delete requests without impact on a database
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 9 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.
Re: DELETE CASCADE
On Mon, Apr 5, 2010 at 4:18 PM, Aveek Misra wrote: > I have the following two tables > > CREATE TABLE `cfg_tags` ( > `cluster` varbinary(128) NOT NULL, > `tag` varbinary(128) NOT NULL, > `user` varchar(40) NOT NULL, > PRIMARY KEY (`cluster`,`tag`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 > > > CREATE TABLE `cfg_cluster_info` ( > `cluster` varbinary(128) NOT NULL, > `admin` varbinary(128) NOT NULL, > PRIMARY KEY (`cluster`), > CONSTRAINT `cfg_cluster_info_ibfk_1` FOREIGN KEY (`cluster`) REFERENCES > `cfg_tags` (`cluster`) ON DELETE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 > > > mysql> select * from cfg_tags; > +---+--++ > | cluster | tag | user | > +---+--++ > | mycluster | tag1 | aveekm | > | mycluster | tag2 | aveekm | > +---+--++ > > Now when I delete one row from this table for the cluster 'mycluster', all > the matching rows in the table cfg_cluster_info are deleted. However this is > not what I intended. I want that the delete cascade should take effect when > "all" the rows in cfg_tags with 'mycluster' are deleted. Should I then > remove the "delete cascade" condition and take care of this myself? > > > Thanks > Aveek > how data 'mycluster' can duplicate, while he is the primary key in cfg_tags? -- Muhammad Subair
DELETE CASCADE
I have the following two tables CREATE TABLE `cfg_tags` ( `cluster` varbinary(128) NOT NULL, `tag` varbinary(128) NOT NULL, `user` varchar(40) NOT NULL, PRIMARY KEY (`cluster`,`tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `cfg_cluster_info` ( `cluster` varbinary(128) NOT NULL, `admin` varbinary(128) NOT NULL, PRIMARY KEY (`cluster`), CONSTRAINT `cfg_cluster_info_ibfk_1` FOREIGN KEY (`cluster`) REFERENCES `cfg_tags` (`cluster`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> select * from cfg_tags; +---+--++ | cluster | tag | user | +---+--++ | mycluster | tag1 | aveekm | | mycluster | tag2 | aveekm | +---+--++ Now when I delete one row from this table for the cluster 'mycluster', all the matching rows in the table cfg_cluster_info are deleted. However this is not what I intended. I want that the delete cascade should take effect when "all" the rows in cfg_tags with 'mycluster' are deleted. Should I then remove the "delete cascade" condition and take care of this myself? Thanks Aveek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Question about DELETE
Would wrapping the DELETE in a TRANSACTION improve the performance any? Also, when you say to "after each mass delete, rebuilt the indexes..." would running OPTIMIZE TABLE tablename; be the way to do this, or how? Thanks, -Randall Price From: Ananda Kumar [mailto:anan...@gmail.com] Sent: Thursday, March 18, 2010 11:15 AM To: Price, Randall Cc: Ian Simpson; Johan De Meersman; [MySQL] Subject: Re: Question about DELETE delete will also cause the undo(before image) to be generated, in case u want to rollback. This will also add up to the delete completion time. After each mass delete, rebuild indexes to remove gaps in indexes(remove fragmentatio in the index). This will improve next delete or select. regards anandkl On Thu, Mar 18, 2010 at 8:22 PM, Price, Randall mailto:randall.pr...@vt.edu>> wrote: I have the MySQL Administrator running and on the Server Connections menu on the Threads tab I can see the thread running (i.e., DELETE FROM table WHERE ...). I refresh this tab periodically to see what stage the process is in. It does not display any information about rebuilding indexes, just that is running the DELETE query. If I turn the DELETE FROM into a SELECT to see if it takes a long time to select the records to delete, it returns almost instantly so MySQL seems to be able to find the records to delete pretty fast. I also assume that turning the DELETE FROM into a SELECT is a reasonable way to determine this. When I do a mass delete on the parent table (i.e., DELETE FROM table WHERE id BETWEEN 1 AND 500) all I can see in the process list is the DELETE running. The parent records are deleted and the CASCADING DELETES then deletes the child records in the other two child tables. The process list does not show any information about deleting the child records through the CASCADING DELETES, just that it is deleting the parent records. The parent and both child tables have multiple indexes on them, plus the FOREIGN KEY CONSTRAINTS. So all the indexes on the parent table must be rebuilt, all the indexes on the two child tables must be rebuilt, and (I suspect) the foreign key constraints must be rebuilt (not sure about this). I have tried dropping the foreign keys and indexes, performing the deletes, then rebuilding the indexes and foreign keys. However, this process is equally as long (and maybe even longer) because dropping the foreign keys and indexes takes a long time, the delete seems to go pretty fast, and then rebuilding the indexes and foreign keys then takes a long time. This technique may be alright for deleting a large number of records, but for a small number it still takes a long time to drop and rebuild. I have tried deleting from the bottom up (i.e., deleting the child records first, then the parent records) to see if that would maybe bypass the FOREIGN KEY rebuild (if there is actually a rebuild for this, not sure) and speed up the process but it does not. It still takes a long time on a large number of deletes. So I am at a quandary as to how to make this delete process perform better. Thanks, -Randall Price -Original Message- From: Ian Simpson [mailto:i...@it.myjobgroup.co.uk<mailto:i...@it.myjobgroup.co.uk>] Sent: Thursday, March 18, 2010 10:11 AM To: Price, Randall Cc: Johan De Meersman; Ananda Kumar; [MySQL] Subject: RE: Question about DELETE Hi Randall, If you're talking about processes that are taking that long, then running SHOW PROCESSLIST several times during the operation should give you a rough idea what it is doing at each stage. Also, do you have an index on the id column? It could just be taking a long time to identify all the rows it needs to delete. On Thu, 2010-03-18 at 10:03 -0400, Price, Randall wrote: > Thanks for your responses on this. > > However, I suspect that the indexes are being rebuilt over and over during > the mass delete operation. > > If I delete a small number of records (i.e., DELETE FROM table WHERE id > BETWEEN 1 AND 5) it may only take a minute or so. > > If I delete a large number of records (i.e., DELETE FROM table WHERE id > BETWEEN 1 AND 500) it may take upwards of an hour or more. > > So what would cause this increased slowness the more records you delete, > unless the indexing is happening multiple times? > > Thanks, > > -Randall Price > > > From: vegiv...@gmail.com<mailto:vegiv...@gmail.com> > [mailto:vegiv...@gmail.com<mailto:vegiv...@gmail.com>] On Behalf Of Johan De > Meersman > Sent: Thursday, March 18, 2010 6:48 AM > To: Ananda Kumar > Cc: Price, Randall; [MySQL] > Subject: Re: Question about DELETE > > Given that OP is talking about a single delete statement, I'm gonna be very > surprised if he manages to squeeze an intermediate commit in there :-) > > For a single-statement delete on a single table, the indexes will be rebuilt >
Re: Question about DELETE
delete will also cause the undo(before image) to be generated, in case u want to rollback. This will also add up to the delete completion time. After each mass delete, rebuild indexes to remove gaps in indexes(remove fragmentatio in the index). This will improve next delete or select. regards anandkl On Thu, Mar 18, 2010 at 8:22 PM, Price, Randall wrote: > I have the MySQL Administrator running and on the Server Connections menu > on the Threads tab I can see the thread running (i.e., DELETE FROM table > WHERE ...). I refresh this tab periodically to see what stage the process > is in. It does not display any information about rebuilding indexes, just > that is running the DELETE query. > > If I turn the DELETE FROM into a SELECT to see if it takes a long time to > select the records to delete, it returns almost instantly so MySQL seems to > be able to find the records to delete pretty fast. I also assume that > turning the DELETE FROM into a SELECT is a reasonable way to determine this. > > When I do a mass delete on the parent table (i.e., DELETE FROM table WHERE > id BETWEEN 1 AND 500) all I can see in the process list is the DELETE > running. The parent records are deleted and the CASCADING DELETES then > deletes the child records in the other two child tables. The process list > does not show any information about deleting the child records through the > CASCADING DELETES, just that it is deleting the parent records. > > The parent and both child tables have multiple indexes on them, plus the > FOREIGN KEY CONSTRAINTS. So all the indexes on the parent table must be > rebuilt, all the indexes on the two child tables must be rebuilt, and (I > suspect) the foreign key constraints must be rebuilt (not sure about this). > > I have tried dropping the foreign keys and indexes, performing the deletes, > then rebuilding the indexes and foreign keys. However, this process is > equally as long (and maybe even longer) because dropping the foreign keys > and indexes takes a long time, the delete seems to go pretty fast, and then > rebuilding the indexes and foreign keys then takes a long time. This > technique may be alright for deleting a large number of records, but for a > small number it still takes a long time to drop and rebuild. > > I have tried deleting from the bottom up (i.e., deleting the child records > first, then the parent records) to see if that would maybe bypass the > FOREIGN KEY rebuild (if there is actually a rebuild for this, not sure) and > speed up the process but it does not. It still takes a long time on a large > number of deletes. > > So I am at a quandary as to how to make this delete process perform better. > > Thanks, > > -Randall Price > > > -Original Message- > From: Ian Simpson [mailto:i...@it.myjobgroup.co.uk] > Sent: Thursday, March 18, 2010 10:11 AM > To: Price, Randall > Cc: Johan De Meersman; Ananda Kumar; [MySQL] > Subject: RE: Question about DELETE > > Hi Randall, > > If you're talking about processes that are taking that long, then > running SHOW PROCESSLIST several times during the operation should give > you a rough idea what it is doing at each stage. > > Also, do you have an index on the id column? It could just be taking a > long time to identify all the rows it needs to delete. > > On Thu, 2010-03-18 at 10:03 -0400, Price, Randall wrote: > > Thanks for your responses on this. > > > > However, I suspect that the indexes are being rebuilt over and over > during the mass delete operation. > > > > If I delete a small number of records (i.e., DELETE FROM table WHERE id > BETWEEN 1 AND 5) it may only take a minute or so. > > > > If I delete a large number of records (i.e., DELETE FROM table WHERE id > BETWEEN 1 AND 500) it may take upwards of an hour or more. > > > > So what would cause this increased slowness the more records you delete, > unless the indexing is happening multiple times? > > > > Thanks, > > > > -Randall Price > > > > > > From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan > De Meersman > > Sent: Thursday, March 18, 2010 6:48 AM > > To: Ananda Kumar > > Cc: Price, Randall; [MySQL] > > Subject: Re: Question about DELETE > > > > Given that OP is talking about a single delete statement, I'm gonna be > very surprised if he manages to squeeze an intermediate commit in there :-) > > > > For a single-statement delete on a single table, the indexes will be > rebuilt only once. I'm not entirely sure what happens to cascaded deletes, > though. > > > > On Thu, Mar 18, 2010 at 6:05 AM, Ananda Kumar anan...@gmail.com>> wrote: > > Hi, > > It
RE: Question about DELETE
I have the MySQL Administrator running and on the Server Connections menu on the Threads tab I can see the thread running (i.e., DELETE FROM table WHERE ...). I refresh this tab periodically to see what stage the process is in. It does not display any information about rebuilding indexes, just that is running the DELETE query. If I turn the DELETE FROM into a SELECT to see if it takes a long time to select the records to delete, it returns almost instantly so MySQL seems to be able to find the records to delete pretty fast. I also assume that turning the DELETE FROM into a SELECT is a reasonable way to determine this. When I do a mass delete on the parent table (i.e., DELETE FROM table WHERE id BETWEEN 1 AND 500) all I can see in the process list is the DELETE running. The parent records are deleted and the CASCADING DELETES then deletes the child records in the other two child tables. The process list does not show any information about deleting the child records through the CASCADING DELETES, just that it is deleting the parent records. The parent and both child tables have multiple indexes on them, plus the FOREIGN KEY CONSTRAINTS. So all the indexes on the parent table must be rebuilt, all the indexes on the two child tables must be rebuilt, and (I suspect) the foreign key constraints must be rebuilt (not sure about this). I have tried dropping the foreign keys and indexes, performing the deletes, then rebuilding the indexes and foreign keys. However, this process is equally as long (and maybe even longer) because dropping the foreign keys and indexes takes a long time, the delete seems to go pretty fast, and then rebuilding the indexes and foreign keys then takes a long time. This technique may be alright for deleting a large number of records, but for a small number it still takes a long time to drop and rebuild. I have tried deleting from the bottom up (i.e., deleting the child records first, then the parent records) to see if that would maybe bypass the FOREIGN KEY rebuild (if there is actually a rebuild for this, not sure) and speed up the process but it does not. It still takes a long time on a large number of deletes. So I am at a quandary as to how to make this delete process perform better. Thanks, -Randall Price -Original Message- From: Ian Simpson [mailto:i...@it.myjobgroup.co.uk] Sent: Thursday, March 18, 2010 10:11 AM To: Price, Randall Cc: Johan De Meersman; Ananda Kumar; [MySQL] Subject: RE: Question about DELETE Hi Randall, If you're talking about processes that are taking that long, then running SHOW PROCESSLIST several times during the operation should give you a rough idea what it is doing at each stage. Also, do you have an index on the id column? It could just be taking a long time to identify all the rows it needs to delete. On Thu, 2010-03-18 at 10:03 -0400, Price, Randall wrote: > Thanks for your responses on this. > > However, I suspect that the indexes are being rebuilt over and over during > the mass delete operation. > > If I delete a small number of records (i.e., DELETE FROM table WHERE id > BETWEEN 1 AND 5) it may only take a minute or so. > > If I delete a large number of records (i.e., DELETE FROM table WHERE id > BETWEEN 1 AND 500) it may take upwards of an hour or more. > > So what would cause this increased slowness the more records you delete, > unless the indexing is happening multiple times? > > Thanks, > > -Randall Price > > > From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De > Meersman > Sent: Thursday, March 18, 2010 6:48 AM > To: Ananda Kumar > Cc: Price, Randall; [MySQL] > Subject: Re: Question about DELETE > > Given that OP is talking about a single delete statement, I'm gonna be very > surprised if he manages to squeeze an intermediate commit in there :-) > > For a single-statement delete on a single table, the indexes will be rebuilt > only once. I'm not entirely sure what happens to cascaded deletes, though. > > On Thu, Mar 18, 2010 at 6:05 AM, Ananda Kumar > mailto:anan...@gmail.com>> wrote: > Hi, > It depends how frequently ur doing a commit. > If you have written a plsql, with loop and if you commit after each row is > deleted, then it get update for each row. Else if you commit at the end the > loop, it commits only once for all the rows deleted. > > regards > anandkl > On Thu, Mar 18, 2010 at 1:21 AM, Price, Randall > mailto:randall.pr...@vt.edu>>wrote: > > > Hello, > > > > I have a simple question about deleting records from INNODB tables. I have > > a master table with a few child tables linked via Foreign Key constraints. > > Each table has several indexes as well. > > > > My question is: if I delete many records in a single delete statem
RE: Question about DELETE
Hi Randall, If you're talking about processes that are taking that long, then running SHOW PROCESSLIST several times during the operation should give you a rough idea what it is doing at each stage. Also, do you have an index on the id column? It could just be taking a long time to identify all the rows it needs to delete. On Thu, 2010-03-18 at 10:03 -0400, Price, Randall wrote: > Thanks for your responses on this. > > However, I suspect that the indexes are being rebuilt over and over during > the mass delete operation. > > If I delete a small number of records (i.e., DELETE FROM table WHERE id > BETWEEN 1 AND 5) it may only take a minute or so. > > If I delete a large number of records (i.e., DELETE FROM table WHERE id > BETWEEN 1 AND 500) it may take upwards of an hour or more. > > So what would cause this increased slowness the more records you delete, > unless the indexing is happening multiple times? > > Thanks, > > -Randall Price > > > From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De > Meersman > Sent: Thursday, March 18, 2010 6:48 AM > To: Ananda Kumar > Cc: Price, Randall; [MySQL] > Subject: Re: Question about DELETE > > Given that OP is talking about a single delete statement, I'm gonna be very > surprised if he manages to squeeze an intermediate commit in there :-) > > For a single-statement delete on a single table, the indexes will be rebuilt > only once. I'm not entirely sure what happens to cascaded deletes, though. > > On Thu, Mar 18, 2010 at 6:05 AM, Ananda Kumar > mailto:anan...@gmail.com>> wrote: > Hi, > It depends how frequently ur doing a commit. > If you have written a plsql, with loop and if you commit after each row is > deleted, then it get update for each row. Else if you commit at the end the > loop, it commits only once for all the rows deleted. > > regards > anandkl > On Thu, Mar 18, 2010 at 1:21 AM, Price, Randall > mailto:randall.pr...@vt.edu>>wrote: > > > Hello, > > > > I have a simple question about deleting records from INNODB tables. I have > > a master table with a few child tables linked via Foreign Key constraints. > > Each table has several indexes as well. > > > > My question is: if I delete many records in a single delete statement > > (i.e., DELETE FROM table WHERE id = 1 AND id = 5 ... AND ID = 100) how many > > times are the foreign keys/indexes updated? > > > > Once for the entire DELETE operation or one time for each record that is > > deleted? > > > > Thanks, > > > > Randall Price > > > > > > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Question about DELETE
Thanks for your responses on this. However, I suspect that the indexes are being rebuilt over and over during the mass delete operation. If I delete a small number of records (i.e., DELETE FROM table WHERE id BETWEEN 1 AND 5) it may only take a minute or so. If I delete a large number of records (i.e., DELETE FROM table WHERE id BETWEEN 1 AND 500) it may take upwards of an hour or more. So what would cause this increased slowness the more records you delete, unless the indexing is happening multiple times? Thanks, -Randall Price From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Thursday, March 18, 2010 6:48 AM To: Ananda Kumar Cc: Price, Randall; [MySQL] Subject: Re: Question about DELETE Given that OP is talking about a single delete statement, I'm gonna be very surprised if he manages to squeeze an intermediate commit in there :-) For a single-statement delete on a single table, the indexes will be rebuilt only once. I'm not entirely sure what happens to cascaded deletes, though. On Thu, Mar 18, 2010 at 6:05 AM, Ananda Kumar mailto:anan...@gmail.com>> wrote: Hi, It depends how frequently ur doing a commit. If you have written a plsql, with loop and if you commit after each row is deleted, then it get update for each row. Else if you commit at the end the loop, it commits only once for all the rows deleted. regards anandkl On Thu, Mar 18, 2010 at 1:21 AM, Price, Randall mailto:randall.pr...@vt.edu>>wrote: > Hello, > > I have a simple question about deleting records from INNODB tables. I have > a master table with a few child tables linked via Foreign Key constraints. > Each table has several indexes as well. > > My question is: if I delete many records in a single delete statement > (i.e., DELETE FROM table WHERE id = 1 AND id = 5 ... AND ID = 100) how many > times are the foreign keys/indexes updated? > > Once for the entire DELETE operation or one time for each record that is > deleted? > > Thanks, > > Randall Price > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Question about DELETE
Given that OP is talking about a single delete statement, I'm gonna be very surprised if he manages to squeeze an intermediate commit in there :-) For a single-statement delete on a single table, the indexes will be rebuilt only once. I'm not entirely sure what happens to cascaded deletes, though. On Thu, Mar 18, 2010 at 6:05 AM, Ananda Kumar wrote: > Hi, > It depends how frequently ur doing a commit. > If you have written a plsql, with loop and if you commit after each row is > deleted, then it get update for each row. Else if you commit at the end the > loop, it commits only once for all the rows deleted. > > regards > anandkl > On Thu, Mar 18, 2010 at 1:21 AM, Price, Randall >wrote: > > > Hello, > > > > I have a simple question about deleting records from INNODB tables. I > have > > a master table with a few child tables linked via Foreign Key > constraints. > > Each table has several indexes as well. > > > > My question is: if I delete many records in a single delete statement > > (i.e., DELETE FROM table WHERE id = 1 AND id = 5 ... AND ID = 100) how > many > > times are the foreign keys/indexes updated? > > > > Once for the entire DELETE operation or one time for each record that is > > deleted? > > > > Thanks, > > > > Randall Price > > > > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Question about DELETE
Hi, It depends how frequently ur doing a commit. If you have written a plsql, with loop and if you commit after each row is deleted, then it get update for each row. Else if you commit at the end the loop, it commits only once for all the rows deleted. regards anandkl On Thu, Mar 18, 2010 at 1:21 AM, Price, Randall wrote: > Hello, > > I have a simple question about deleting records from INNODB tables. I have > a master table with a few child tables linked via Foreign Key constraints. > Each table has several indexes as well. > > My question is: if I delete many records in a single delete statement > (i.e., DELETE FROM table WHERE id = 1 AND id = 5 ... AND ID = 100) how many > times are the foreign keys/indexes updated? > > Once for the entire DELETE operation or one time for each record that is > deleted? > > Thanks, > > Randall Price > >
Question about DELETE
Hello, I have a simple question about deleting records from INNODB tables. I have a master table with a few child tables linked via Foreign Key constraints. Each table has several indexes as well. My question is: if I delete many records in a single delete statement (i.e., DELETE FROM table WHERE id = 1 AND id = 5 ... AND ID = 100) how many times are the foreign keys/indexes updated? Once for the entire DELETE operation or one time for each record that is deleted? Thanks, Randall Price
RE: Very slow delete for Master / Child tables with millions of rows
This isn't surprising, especially if you have foreign keys or indexes, as each DELETE will cascade and require a rebuild of the indexes (just as an INSERT does). Make sure that for each DELETE you are using LIMIT 1; if it's in a loop (and you're not deleting via PK, but it's a good habit to get into and won't hurt if using a PK). A trick I've done, is create another column called "delete_me" or something, then you loop through and set a flag to 1 for all records you want deleted. Then at the end (or during the night via crontab or something) you simply: DELETE FROM foo WHERE delete_me = 1; (you may need to adjust other code to ignore any records that are set for deletion in SELECTs) Depending on your schema you might also be able to do something like this, if you want to manage the foreign key deletes yourself. SET FOREIGN_KEY_CHECKS=0; DELETE FROM foo WHERE delete_me = 1; DELETE FROM bar WHERE delete_me = 1; SET FOREIGN_KEY_CHECKS=1; I thought there was a way to turn of indexes as well in a similar way. http://dev.mysql.com/doc/refman/5.0/en/delete.html Also look at the LOW_PRIORITY if you're using MYISAM tables and QUICK. Try the trick of INSERTing into a NEW table and using RENAME instead of DELETE all together. Store the ID's you want to delete in another table (maybe even a HEAP/MEMORY one), then: DELETE FROM LargeTable USING LargeTable INNER JOIN TemporarySmallTable ON LargeTable.ID = TemporarySmallTable.ID; Another idea I just had that may work is to use a VIEW as your SELECT table (where 'delete_me <> 1') then all your code points at the VIEW, and you can delete from the real main table whenever you like, or just keep it for archival purposes. Consider OPTIMIZE TABLE to reclaim unused space and reduce file sizes when done too. > -Original Message- > From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On > Behalf Of Johan De Meersman > Sent: Thursday, March 11, 2010 7:43 AM > To: Price, Randall > Cc: mysql@lists.mysql.com > Subject: Re: Very slow delete for Master / Child tables with > millions of rows > > If you really have to loop through the entire set deleting > record by record, > I'm not surprised it's slow. Could you change your application to loop > through the records doing "stuff" without deleting (maybe > even "do stuff" en > masse), and afterwards do a mass delete ? > > I also have a nagging suspicion (unfounded by any actual > experience, though) > that it might be faster to cut the explicit relations and do the child > deletes as a separate single-block execute, too. Easy enough > to test, I > suppose. > > > -- > 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
Very slow delete for Master / Child tables with millions of rows
Here is my.ini file - and I am using all Innodb [client] port=3306 [mysql] default-character-set=latin1 [mysqld] port=3306 skip-name-resolve basedir="W:/Applications/MySQL/MySQL Server 5.0/" datadir="W:/Applications/MySQL/MySQL Server 5.0/Data/" default-character-set=latin1 default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=100 query_cache_size=50M table_cache=512M tmp_table_size=103M thread_cache_size=8 #*** MyISAM Specific options myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G myisam_sort_buffer_size=205M key_buffer_size=512M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=2M #*** INNODB Specific options *** innodb_additional_mem_pool_size=7M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=3498K innodb_buffer_pool_size=512M innodb_log_file_size=170M innodb_thread_concurrency=10 query_cache_type=1 long_query_time=2 log-slow-queries=Slow.log innodb_file_per_table innodb_lock_wait_timeout=500 From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: Thursday, March 11, 2010 11:02 AM To: Price, Randall Cc: mysql@lists.mysql.com Subject: Re: Very slow delete for Master / Child tables with millions of rows Hi Randall, How much memory is allocated to innodb_buffer_pool_size. Please send your mysql configuration file (my.cnf) Thanks, Krishna On Thu, Mar 11, 2010 at 8:57 PM, Price, Randall mailto:randall.pr...@vt.edu>> wrote: I am experiencing very slow deletes when I delete a record from a master table and have cascading deletes on two detail tables. I have an application that looks for records in the master table that are older than "X" days and delete them. The cascasing deletes then handles deleting all the child records in the other tables. However, this process is very slow. Depending on how many records are found to delete, this process takes anywhere from 30-40 minutes to several hours. Due to the nature of my application, I must loop through the records to delete, do some stuff for each record, then delete it. I suspect at this point, each tables' indexes need to be rebuilt. There are several indexes and the ones for the tables with 4,000,000+ rows probably takes a while. My question is: What is the best way to handle deleting master/detail records in this scenario? I have a brief diagram of my tables and the CREATE TABLE statements follow. Thanks, Randall Price +---+ | tblwsusclientinfo | +-++---+ | tblwsusclients || ID| +-+| UpdateGUID| | SusClientId |<-oo| SusClientId | | ... | || ... | +-+ |+---+ ( ~ 3,000 rows) | (~ 4,000,000 rows ) | | |+-+ || tblwsusevents | |+-| || EventGUID | || ... | +--oo| EventAssociatedComputer | | ... | +-| (~ 4,300,000 rows ) CREATE TABLE `tblwsusclients` ( `SusClientId` varchar(36) NOT NULL default '', `DNSName` varchar(256) NOT NULL default '', `ServerGUID` varchar(36) NOT NULL default '', `IPAddress` varchar(15) NOT NULL default '', `LastReportTime` datetime NOT NULL default '-00-00 00:00:00', `LastSyncTime` datetime NOT NULL default '-00-00 00:00:00', `DetectionResult` varchar(256) default NULL, `ResponsiblePerson` varchar(16) default NULL, `TargetGroup` varchar(45) default NULL, `Affiliation` varchar(45) default NULL, `AddedDate` datetime default NULL, `IsActive` tinyint(1) default NULL, `UnRegisteredDate` datetime default NULL, `SCVersion` double default NULL, `BiosName` varchar(256) default NULL, `BiosVersion` varchar(45) default NULL, `OSVersion` varchar(45) default NULL, `SPVersion` varchar(45) default NULL, `Make` varchar(256) default NULL, `Model` varchar(256) default NULL, `ProcArchitecture` varchar(45) default NULL, `OSLongName` varchar(256) default NULL, `TimedOutDate` datetime default NULL, PRIMARY KEY (`SusClientId`), KEY `FK_tblwsusclients_1` (`ServerGUID`), KEY `IX_DNSName` (`DNSName`), KEY `IX_IsActive` (`IsActive`), CONSTRAINT `FK_tblwsusclients_1` FOREIGN KEY (`ServerGUID`) REFERENCES `tblwsusservers` (`ServerGUID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblwsusclientinfo` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `Updat
Re: Very slow delete for Master / Child tables with millions of rows
Hi Randall, How much memory is allocated to innodb_buffer_pool_size. Please send your mysql configuration file (my.cnf) Thanks, Krishna On Thu, Mar 11, 2010 at 8:57 PM, Price, Randall wrote: > I am experiencing very slow deletes when I delete a record from a master > table and have cascading deletes on two detail tables. > > I have an application that looks for records in the master table that are > older than "X" days and delete them. The cascasing deletes then handles > deleting all the child records in the other tables. However, this process > is very slow. Depending on how many records are found to delete, this > process takes anywhere from 30-40 minutes to several hours. > > Due to the nature of my application, I must loop through the records to > delete, do some stuff for each record, then delete it. I suspect at this > point, each tables' indexes need to be rebuilt. There are several indexes > and the ones for the tables with 4,000,000+ rows probably takes a while. > > My question is: What is the best way to handle deleting master/detail > records in this scenario? > > I have a brief diagram of my tables and the CREATE TABLE statements follow. > > Thanks, > > Randall Price > > > > +---+ > | tblwsusclientinfo | > +-++---+ > | tblwsusclients || ID| > +-+| UpdateGUID| > | SusClientId |<-oo| SusClientId | > | ... | || ... | > +-+ |+---+ > ( ~ 3,000 rows) | (~ 4,000,000 rows ) > | > | > |+-+ > || tblwsusevents | > |+-| > || EventGUID | > || ... | > +--oo| EventAssociatedComputer | > | ... | > +-| >(~ 4,300,000 rows ) > > > CREATE TABLE `tblwsusclients` ( > `SusClientId` varchar(36) NOT NULL default '', > `DNSName` varchar(256) NOT NULL default '', > `ServerGUID` varchar(36) NOT NULL default '', > `IPAddress` varchar(15) NOT NULL default '', > `LastReportTime` datetime NOT NULL default '-00-00 00:00:00', > `LastSyncTime` datetime NOT NULL default '-00-00 00:00:00', > `DetectionResult` varchar(256) default NULL, > `ResponsiblePerson` varchar(16) default NULL, > `TargetGroup` varchar(45) default NULL, > `Affiliation` varchar(45) default NULL, > `AddedDate` datetime default NULL, > `IsActive` tinyint(1) default NULL, > `UnRegisteredDate` datetime default NULL, > `SCVersion` double default NULL, > `BiosName` varchar(256) default NULL, > `BiosVersion` varchar(45) default NULL, > `OSVersion` varchar(45) default NULL, > `SPVersion` varchar(45) default NULL, > `Make` varchar(256) default NULL, > `Model` varchar(256) default NULL, > `ProcArchitecture` varchar(45) default NULL, > `OSLongName` varchar(256) default NULL, > `TimedOutDate` datetime default NULL, > PRIMARY KEY (`SusClientId`), > KEY `FK_tblwsusclients_1` (`ServerGUID`), > KEY `IX_DNSName` (`DNSName`), > KEY `IX_IsActive` (`IsActive`), > CONSTRAINT `FK_tblwsusclients_1` FOREIGN KEY (`ServerGUID`) REFERENCES > `tblwsusservers` (`ServerGUID`) ON DELETE CASCADE ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 > > > > CREATE TABLE `tblwsusclientinfo` ( > `ID` bigint(20) unsigned NOT NULL auto_increment, > `UpdateGUID` varchar(36) NOT NULL default '', > `SusClientId` varchar(36) NOT NULL default '', > `UpdateState` varchar(256) NOT NULL default '', > `LastTimeChanged` datetime default NULL, > PRIMARY KEY (`ID`), > KEY `IX_UpdateState` (`UpdateState`), > KEY `IX_SusClientId_UpdateState` (`SusClientId`,`UpdateState`), > KEY `FK_tblwsusclientinfo_1` (`UpdateGUID`), > KEY `FK_tblwsusclientinfo_2` (`SusClientId`), > CONSTRAINT `FK_tblwsusclientinfo_1` FOREIGN KEY (`UpdateGUID`) REFERENCES > `tblupdateinformation` (`UpdateGUID`) ON DELETE CASCADE ON UPDATE CASCADE, > CONSTRAINT `FK_tblwsusclientinfo_2` FOREIGN KEY (`SusClientId`) REFERENCES > `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 > > > > CREATE TABLE `tblwsusevents` ( > `EventGUID` varchar(36) NOT NULL def
Re: Very slow delete for Master / Child tables with millions of rows
If you really have to loop through the entire set deleting record by record, I'm not surprised it's slow. Could you change your application to loop through the records doing "stuff" without deleting (maybe even "do stuff" en masse), and afterwards do a mass delete ? I also have a nagging suspicion (unfounded by any actual experience, though) that it might be faster to cut the explicit relations and do the child deletes as a separate single-block execute, too. Easy enough to test, I suppose. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Very slow delete for Master / Child tables with millions of rows
I am experiencing very slow deletes when I delete a record from a master table and have cascading deletes on two detail tables. I have an application that looks for records in the master table that are older than "X" days and delete them. The cascasing deletes then handles deleting all the child records in the other tables. However, this process is very slow. Depending on how many records are found to delete, this process takes anywhere from 30-40 minutes to several hours. Due to the nature of my application, I must loop through the records to delete, do some stuff for each record, then delete it. I suspect at this point, each tables' indexes need to be rebuilt. There are several indexes and the ones for the tables with 4,000,000+ rows probably takes a while. My question is: What is the best way to handle deleting master/detail records in this scenario? I have a brief diagram of my tables and the CREATE TABLE statements follow. Thanks, Randall Price +---+ | tblwsusclientinfo | +-++---+ | tblwsusclients || ID| +-+| UpdateGUID| | SusClientId |<-oo| SusClientId | | ... | || ... | +-+ |+---+ ( ~ 3,000 rows) | (~ 4,000,000 rows ) | | |+-+ || tblwsusevents | |+-| || EventGUID | || ... | +--oo| EventAssociatedComputer | | ... | +-| (~ 4,300,000 rows ) CREATE TABLE `tblwsusclients` ( `SusClientId` varchar(36) NOT NULL default '', `DNSName` varchar(256) NOT NULL default '', `ServerGUID` varchar(36) NOT NULL default '', `IPAddress` varchar(15) NOT NULL default '', `LastReportTime` datetime NOT NULL default '-00-00 00:00:00', `LastSyncTime` datetime NOT NULL default '-00-00 00:00:00', `DetectionResult` varchar(256) default NULL, `ResponsiblePerson` varchar(16) default NULL, `TargetGroup` varchar(45) default NULL, `Affiliation` varchar(45) default NULL, `AddedDate` datetime default NULL, `IsActive` tinyint(1) default NULL, `UnRegisteredDate` datetime default NULL, `SCVersion` double default NULL, `BiosName` varchar(256) default NULL, `BiosVersion` varchar(45) default NULL, `OSVersion` varchar(45) default NULL, `SPVersion` varchar(45) default NULL, `Make` varchar(256) default NULL, `Model` varchar(256) default NULL, `ProcArchitecture` varchar(45) default NULL, `OSLongName` varchar(256) default NULL, `TimedOutDate` datetime default NULL, PRIMARY KEY (`SusClientId`), KEY `FK_tblwsusclients_1` (`ServerGUID`), KEY `IX_DNSName` (`DNSName`), KEY `IX_IsActive` (`IsActive`), CONSTRAINT `FK_tblwsusclients_1` FOREIGN KEY (`ServerGUID`) REFERENCES `tblwsusservers` (`ServerGUID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblwsusclientinfo` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `UpdateGUID` varchar(36) NOT NULL default '', `SusClientId` varchar(36) NOT NULL default '', `UpdateState` varchar(256) NOT NULL default '', `LastTimeChanged` datetime default NULL, PRIMARY KEY (`ID`), KEY `IX_UpdateState` (`UpdateState`), KEY `IX_SusClientId_UpdateState` (`SusClientId`,`UpdateState`), KEY `FK_tblwsusclientinfo_1` (`UpdateGUID`), KEY `FK_tblwsusclientinfo_2` (`SusClientId`), CONSTRAINT `FK_tblwsusclientinfo_1` FOREIGN KEY (`UpdateGUID`) REFERENCES `tblupdateinformation` (`UpdateGUID`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_tblwsusclientinfo_2` FOREIGN KEY (`SusClientId`) REFERENCES `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblwsusevents` ( `EventGUID` varchar(36) NOT NULL default '', `EventCreationDate` datetime NOT NULL default '-00-00 00:00:00', `EventMessage` text NOT NULL, `EventId` varchar(256) NOT NULL default '', `EventSource` varchar(45) NOT NULL default '', `EventErrorCode` varchar(45) default NULL, `EventIsError` tinyint(1) NOT NULL default '0', `EventAssociatedUpdate` varchar(36) default NULL, `EventAssociatedComputer` varchar(36) default NULL, `EventAssociatedWSUSServer` varchar(36) NOT NULL default '', PRIMARY KEY (`EventGUID`), KEY `IX_EventId` (`EventId`), KEY `IX_EventCreationDate` (`EventCreat
Re: ERROR 1442 (HY000) when delete inside trigger statement
> > That is correct. There is as far as I know no way in a MySQL trigger to > neither to do operations on the table the trigger belongs to (obviously > except the row that the trigger is operating on through the NEW variables) > nor reject an insert, update, or delete. > thanks jesper. ~viraj > It is of course possible to do a workaround in a stored procedure and use > permissions to ensure that the normal users cannot update the table directly. > I don't know whether that will be an acceptable solution in your case though. > > Jesper > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=kali...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ERROR 1442 (HY000) when delete inside trigger statement
--- Original Message --- > From: viraj > To: mysql@lists.mysql.com > Sent: 19/2/10, 05:48:41 > Subject: ERROR 1442 (HY000) when delete > inside trigger statement > issue: ERROR 1442 (HY000): Can't update table 'T1' in stored > function/trigger because it is already used by > statement which invoked > this stored function/trigger. > could somebody please confirm this is not possible!!! so i can think > about some other workaround :) -- That is correct. There is as far as I know no way in a MySQL trigger to neither to do operations on the table the trigger belongs to (obviously except the row that the trigger is operating on through the NEW variables) nor reject an insert, update, or delete. It is of course possible to do a workaround in a stored procedure and use permissions to ensure that the normal users cannot update the table directly. I don't know whether that will be an acceptable solution in your case though. Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ERROR 1442 (HY000) when delete inside trigger statement
i have two table, T1, T2. and 1 trigger. trigger is before update on T1 and it updates some values in T2. once it's done, the trigger tries to delete the subject row of T1 (delete from T1 where id = new.id) i tried with second trigger on T2 (after/before update) and with a procedure inside this trigger.. but, all the time i get.. issue: ERROR 1442 (HY000): Can't update table 'T1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. found http://forums.mysql.com/read.php?99,122354,122354#msg-122354 and many other articles which had ended up with no solution. (with before update, it's possible to set new values to NEW.*, but did not find anything useful to do a successful delete) could somebody please confirm this is not possible!!! so i can think about some other workaround :) thanks ~viraj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Replication Delete is not gettting replicated
Hi Manasi, That alone is the difference in this case. -- Thanks Suresh Kuna MySQL DBA On Tue, Jan 19, 2010 at 10:36 AM, Manasi Save < manasi.s...@artificialmachines.com> wrote: > Dear Carlos, > > Thanks for the response. But I haven't gave any privileges besides > repl_slave priv to user replication and replication2 respectively. > So does that amke any difference really? > > > Thanks in advance. > -- > Regards, > Manasi Save > > Quoting Carlos Proal : > >> >> I dont see anything unusual or missing on your config file and as the >> only thing missing are deletes, i think that might be a permission issue. >> Can you check out the grants for your replication users and see if they >> have full permissions granted ? >> >> mysql> show grants for x; >> >> where is x is replication and replication2 respectively. >> Carlos >> >> >> On 1/18/2010 1:35 AM, Manasi Save wrote: >> > Hi Anand, >> > >> > Please find below my configuration file of both the masters: >> > >> > ON MASTER 1: >> > >> > [mysqld] >> > datadir=/var/lib/mysql/ >> > socket=/var/lib/mysql/mysql.sock >> > old_passwords=1 >> > >> > log-bin=/usr/local/mysql/bin.log >> > #binlog-do-db= # input the database which should >> > be replicated >> > binlog-ignore-db=mysql# input the database that should be >> > ignored for replication >> > binlog-ignore-db=test >> > log-bin-index=/usr/local/mysql/log-bin.index >> > log_slave_updates >> > >> > server-id=2 >> > >> > auto_increment_increment=2 >> > auto_increment_offset=1 >> > >> > #information for becoming slave. > master-host = 192.168.1.1 >> > master-user = replication >> > master-password = replication >> > master-port = 3306 >> > >> > [mysql.server] >> > user=mysql >> > >> > [mysqld_safe] >> > err-log=/var/lib/mysql/mysql.log >> > pid-file=/var/lib/mysql/mysql.privatedns.com.pid >> > >> > ON MASTER 2: >> > >> > [mysqld] >> > datadir=/var/lib/mysql/ >> > socket=/var/lib/mysql/mysql.sock >> > old_passwords=1 >> > >> > log-bin=/usr/local/mysql/bin.log >> > #binlog-do-db= # input the database which should >> > be replicated >> > binlog-ignore-db=mysql# input the database that should be >> > ignored for replication >> > binlog-ignore-db=test >> > log-bin-index=/usr/local/mysql/log-bin.index >> > log_slave_updates >> > >> > server-id=1 >> > >> > auto_increment_increment=2 >> > auto_increment_offset=2 >> > >> > #information for becoming slave. > master-host = 192.168.1.2 >> > master-user = replication2 >> > master-password = replication2 >> > master-port = 3306 >> > >> > [mysql.server] >> > user=mysql >> > >> > [mysqld_safe] >> > err-log=/var/var/lib/mysql/mysql.log >> > pid-file=/var/lib/mysql/mysql.privatedns.com.pid >> > >> > Please let me know if I need to add any parameter to enable this >> > replication. > >> > Thanks in advance. > >> > -- >> > >> > Regards, >> > >> > Manasi Save >> > >> > >> > >> > Quoting Anand kumar : >> > >> > can you give us the configuration(.cnf) file from both the masters ? >> > --Anand >> > On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save >> > > > <mailto:manasi.s...@artificialmachines.com>> wrote: >> > >> > Hi All, >> > >> > >> > I have configured MySQL Master-Master Replication on my >> > servers. When I am inserting or updating any data in a regular >> > table the data is getting replicated. > >> > >> > But When I am doing delete on that same table. the data is >> > only getting deleted only on the server where I am doing >> > delete. but it is not getting replicated on its slave. > >> > >> > Even if I am doing truncate it is not getting replicated. Can >> > anyone provide any input on this? >> > >> > >> > Thanks in advance. > >> > >> > -- >> > >> > Regards, >> > >> > Manasi Save >> > >> > >> >> > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > >
Re: MySQL Replication Delete is not gettting replicated
Hi Manasi Yes, you only need the repl_slave_priv, the show grants should give you something like: GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY PASSWORD '...' If thats ok, have check your binlog and relay binlog to see if them contain the delete statements? Im trying to figure out whats wrong. Carlos On 1/18/2010 11:06 PM, Manasi Save wrote: Dear Carlos, Thanks for the response. But I haven't gave any privileges besides repl_slave priv to user replication and replication2 respectively. So does that amke any difference really? Thanks in advance. -- Regards, Manasi Save Quoting Carlos Proal : I dont see anything unusual or missing on your config file and as the only thing missing are deletes, i think that might be a permission issue. Can you check out the grants for your replication users and see if they have full permissions granted ? mysql> show grants for x; where is x is replication and replication2 respectively. Carlos On 1/18/2010 1:35 AM, Manasi Save wrote: > Hi Anand, > > Please find below my configuration file of both the masters: > > ON MASTER 1: > > [mysqld] > datadir=/var/lib/mysql/ > socket=/var/lib/mysql/mysql.sock > old_passwords=1 > > log-bin=/usr/local/mysql/bin.log > #binlog-do-db= # input the database which should > be replicated > binlog-ignore-db=mysql# input the database that should be > ignored for replication > binlog-ignore-db=test > log-bin-index=/usr/local/mysql/log-bin.index > log_slave_updates > > server-id=2 > > auto_increment_increment=2 > auto_increment_offset=1 > > #information for becoming slave. > master-host = 192.168.1.1 > master-user = replication > master-password = replication > master-port = 3306 > > [mysql.server] > user=mysql > > [mysqld_safe] > err-log=/var/lib/mysql/mysql.log > pid-file=/var/lib/mysql/mysql.privatedns.com.pid > > ON MASTER 2: > > [mysqld] > datadir=/var/lib/mysql/ > socket=/var/lib/mysql/mysql.sock > old_passwords=1 > > log-bin=/usr/local/mysql/bin.log > #binlog-do-db= # input the database which should > be replicated > binlog-ignore-db=mysql# input the database that should be > ignored for replication > binlog-ignore-db=test > log-bin-index=/usr/local/mysql/log-bin.index > log_slave_updates > > server-id=1 > > auto_increment_increment=2 > auto_increment_offset=2 > > #information for becoming slave. > master-host = 192.168.1.2 > master-user = replication2 > master-password = replication2 > master-port = 3306 > > [mysql.server] > user=mysql > > [mysqld_safe] > err-log=/var/var/lib/mysql/mysql.log > pid-file=/var/lib/mysql/mysql.privatedns.com.pid > > Please let me know if I need to add any parameter to enable this > replication. > > Thanks in advance. > > -- > > Regards, > > Manasi Save > > > > Quoting Anand kumar : > > can you give us the configuration(.cnf) file from both the masters ? > --Anand > On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save > <mailto:manasi.s...@artificialmachines.com>> wrote: > > Hi All, > > > I have configured MySQL Master-Master Replication on my > servers. When I am inserting or updating any data in a regular > table the data is getting replicated. > > > But When I am doing delete on that same table. the data is > only getting deleted only on the server where I am doing > delete. but it is not getting replicated on its slave. > > > Even if I am doing truncate it is not getting replicated. Can > anyone provide any input on this? > > > Thanks in advance. > > > -- > > Regards, > > Manasi Save > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Replication Delete is not gettting replicated
Dear Carlos, Thanks for the response. But I haven't gave any privileges besides repl_slave priv to user replication and replication2 respectively. So does that amke any difference really? Thanks in advance. -- Regards, Manasi Save Quoting Carlos Proal : I dont see anything unusual or missing on your config file and as the only thing missing are deletes, i think that might be a permission issue. Can you check out the grants for your replication users and see if they have full permissions granted ? mysql> show grants for x; where is x is replication and replication2 respectively. Carlos On 1/18/2010 1:35 AM, Manasi Save wrote: > Hi Anand, > > Please find below my configuration file of both the masters: > > ON MASTER 1: > > [mysqld] > datadir=/var/lib/mysql/ > socket=/var/lib/mysql/mysql.sock > old_passwords=1 > > log-bin=/usr/local/mysql/bin.log > #binlog-do-db= # input the database which should > be replicated > binlog-ignore-db=mysql# input the database that should be > ignored for replication > binlog-ignore-db=test > log-bin-index=/usr/local/mysql/log-bin.index > log_slave_updates > > server-id=2 > > auto_increment_increment=2 > auto_increment_offset=1 > > #information for becoming slave. > master-host = 192.168.1.1 > master-user = replication > master-password = replication > master-port = 3306 > > [mysql.server] > user=mysql > > [mysqld_safe] > err-log=/var/lib/mysql/mysql.log > pid-file=/var/lib/mysql/mysql.privatedns.com.pid > > ON MASTER 2: > > [mysqld] > datadir=/var/lib/mysql/ > socket=/var/lib/mysql/mysql.sock > old_passwords=1 > > log-bin=/usr/local/mysql/bin.log > #binlog-do-db= # input the database which should > be replicated > binlog-ignore-db=mysql# input the database that should be > ignored for replication > binlog-ignore-db=test > log-bin-index=/usr/local/mysql/log-bin.index > log_slave_updates > > server-id=1 > > auto_increment_increment=2 > auto_increment_offset=2 > > #information for becoming slave. > master-host = 192.168.1.2 > master-user = replication2 > master-password = replication2 > master-port = 3306 > > [mysql.server] > user=mysql > > [mysqld_safe] > err-log=/var/var/lib/mysql/mysql.log > pid-file=/var/lib/mysql/mysql.privatedns.com.pid > > Please let me know if I need to add any parameter to enable this > replication. > > Thanks in advance. > > -- > > Regards, > > Manasi Save > > > > Quoting Anand kumar : > > can you give us the configuration(.cnf) file from both the masters ? > --Anand > On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save > <mailto:manasi.s...@artificialmachines.com>> wrote: > > Hi All, > > > I have configured MySQL Master-Master Replication on my > servers. When I am inserting or updating any data in a regular > table the data is getting replicated. > > > But When I am doing delete on that same table. the data is > only getting deleted only on the server where I am doing > delete. but it is not getting replicated on its slave. > > > Even if I am doing truncate it is not getting replicated. Can > anyone provide any input on this? > > > Thanks in advance. > > > -- > > Regards, > > Manasi Save > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Replication Delete is not gettting replicated
I dont see anything unusual or missing on your config file and as the
only thing missing are deletes, i think that might be a permission issue.
Can you check out the grants for your replication users and see if they
have full permissions granted ?
mysql> show grants for x;
where is x is replication and replication2 respectively.
Carlos
On 1/18/2010 1:35 AM, Manasi Save wrote:
Hi Anand,
Please find below my configuration file of both the masters:
ON MASTER 1:
[mysqld]
datadir=/var/lib/mysql/
socket=/var/lib/mysql/mysql.sock
old_passwords=1
log-bin=/usr/local/mysql/bin.log
#binlog-do-db= # input the database which should
be replicated
binlog-ignore-db=mysql# input the database that should be
ignored for replication
binlog-ignore-db=test
log-bin-index=/usr/local/mysql/log-bin.index
log_slave_updates
server-id=2
auto_increment_increment=2
auto_increment_offset=1
#information for becoming slave.
master-host = 192.168.1.1
master-user = replication
master-password = replication
master-port = 3306
[mysql.server]
user=mysql
[mysqld_safe]
err-log=/var/lib/mysql/mysql.log
pid-file=/var/lib/mysql/mysql.privatedns.com.pid
ON MASTER 2:
[mysqld]
datadir=/var/lib/mysql/
socket=/var/lib/mysql/mysql.sock
old_passwords=1
log-bin=/usr/local/mysql/bin.log
#binlog-do-db= # input the database which should
be replicated
binlog-ignore-db=mysql# input the database that should be
ignored for replication
binlog-ignore-db=test
log-bin-index=/usr/local/mysql/log-bin.index
log_slave_updates
server-id=1
auto_increment_increment=2
auto_increment_offset=2
#information for becoming slave.
master-host = 192.168.1.2
master-user = replication2
master-password = replication2
master-port = 3306
[mysql.server]
user=mysql
[mysqld_safe]
err-log=/var/var/lib/mysql/mysql.log
pid-file=/var/lib/mysql/mysql.privatedns.com.pid
Please let me know if I need to add any parameter to enable this
replication.
Thanks in advance.
--
Regards,
Manasi Save
Quoting Anand kumar :
can you give us the configuration(.cnf) file from both the masters ?
--Anand
On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save
mailto:manasi.s...@artificialmachines.com>> wrote:
Hi All,
I have configured MySQL Master-Master Replication on my
servers. When I am inserting or updating any data in a regular
table the data is getting replicated.
But When I am doing delete on that same table. the data is
only getting deleted only on the server where I am doing
delete. but it is not getting replicated on its slave.
Even if I am doing truncate it is not getting replicated. Can
anyone provide any input on this?
Thanks in advance.
--
Regards,
Manasi Save
Re: MySQL Replication Delete is not gettting replicated
Hi Anand,Please find below my configuration file of both the masters:ON MASTER 1:[mysqld]datadir=/var/lib/mysql/socket=/var/lib/mysql/mysql.sockold_passwords=1log-bin=/usr/local/mysql/bin.log#binlog-do-db= # input the database which should be replicatedbinlog-ignore-db=mysql # input the database that should be ignored for replicationbinlog-ignore-db=testlog-bin-index=/usr/local/mysql/log-bin.indexlog_slave_updatesserver-id=2auto_increment_increment=2auto_increment_offset=1#information for becoming slave.master-host = 192.168.1.1master-user = replicationmaster-password = replicationmaster-port = 3306[mysql.server]user=mysql[mysqld_safe]err-log=/var/lib/mysql/mysql.logpid-file=/var/lib/mysql/mysql.privatedns.com.pidON MASTER 2:[mysqld]datadir=/var/lib/mysql/socket=/var/lib/mysql/mysql.sockold_passwords=1log-bin=/usr/local/mysql/bin.log#binlog-do-db= # input the database which should be replicatedbinlog-ignore-db=mysql # input the database that should be ignored for replicationbinlog-ignore-db=testlog-bin-index=/usr/local/mysql/log-bin.indexlog_slave_updatesserver-id=1auto_increment_increment=2auto_increment_offset=2#information for becoming slave.master-host = 192.168.1.2master-user = replication2master-password = replication2master-port = 3306[mysql.server]user=mysql[mysqld_safe]err-log=/var/var/lib/mysql/mysql.logpid-file=/var/lib/mysql/mysql.privatedns.com.pidPlease let me know if I need to add any parameter to enable this replication.Thanks in advance.--Regards, Manasi Save Quoting Anand kumar :can you give us the configuration(.cnf) file from both the masters ? --Anand On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save <manasi.s...@artificialmachines.com> wrote: Hi All, I have configured MySQL Master-Master Replication on my servers. When I am inserting or updating any data in a regular table the data is getting replicated. But When I am doing delete on that same table. the data is only getting deleted only on the server where I am doing delete. but it is not getting replicated on its slave. Even if I am doing truncate it is not getting replicated. Can anyone provide any input on this? Thanks in advance. -- Regards,Manasi Save
MySQL Replication Delete is not gettting replicated
Hi All,I have configured MySQL Master-Master Replication on my servers. When I am inserting or updating any data in a regular table the data is getting replicated.But When I am doing delete on that same table. the data is only getting deleted only on the server where I am doing delete. but it is not getting replicated on its slave.Even if I am doing truncate it is not getting replicated. Can anyone provide any input on this? Thanks in advance.--Regards, Manasi Save
Re: High Overhead On Active Insert And Delete Table
My first impression after looking at the table structure is
1 the number of indexes present are very huge and each insert or delete will
act as an extra insert or delete of each and every index created.
2 what type of select stmts are going to hit this table.
3 if it is a primary key select, drop all the remaining indexes.
4 as it is myisam, enable the concurrent insert value to 2, so it insert at the
last and do periodic analyze or optimize.
5 the final option is de normalization.
Thanks,
Suresh Kuna
Sent from BlackBerry® on Airtel
-Original Message-
From: Willy Mularto
Date: Sun, 3 Jan 2010 18:12:08
To:
Cc:
Subject: Re: High Overhead On Active Insert And Delete Table
The engine is using MyISAM. And here is the structure:
CREATE TABLE IF NOT EXISTS `ie_push` (
`sql_id` bigint(20) NOT NULL auto_increment,
`momt` enum('MO','MT','DLR') default NULL,
`sender` varchar(20) default NULL,
`receiver` varchar(20) default NULL,
`udhdata` blob,
`msgdata` text,
`time` bigint(20) default NULL,
`smsc_id` varchar(255) default NULL,
`service` varchar(255) default NULL,
`account` varchar(255) default NULL,
`id` bigint(20) default NULL,
`sms_type` bigint(20) default NULL,
`mclass` bigint(20) default NULL,
`mwi` bigint(20) default NULL,
`coding` bigint(20) default NULL,
`compress` bigint(20) default NULL,
`validity` bigint(20) default NULL,
`deferred` bigint(20) default NULL,
`dlr_mask` bigint(20) default NULL,
`dlr_url` varchar(255) default NULL,
`pid` bigint(20) default NULL,
`alt_dcs` bigint(20) default NULL,
`rpi` bigint(20) default NULL,
`charset` varchar(255) default NULL,
`boxc_id` varchar(255) default NULL,
`binfo` varchar(255) default NULL,
`priority` tinyint(4) default NULL,
`custom` varchar(255) default NULL,
`additional` varchar(255) default NULL,
`service_id` varchar(50) default NULL,
`fid` varchar(50) default NULL,
`msgid` varchar(50) default NULL,
`insertdate` datetime default NULL,
PRIMARY KEY (`sql_id`),
KEY `momt` (`momt`),
KEY `sender` (`sender`),
KEY `receiver` (`receiver`),
KEY `time` (`time`),
KEY `service` (`service`),
KEY `account` (`account`),
KEY `id` (`id`),
KEY `sms_type` (`sms_type`),
KEY `mclass` (`mclass`),
KEY `mwi` (`mwi`),
KEY `coding` (`coding`),
KEY `compress` (`compress`),
KEY `validity` (`validity`),
KEY `deferred` (`deferred`),
KEY `dlr_mask` (`dlr_mask`),
KEY `dlr_url` (`dlr_url`),
KEY `pid` (`pid`),
KEY `rpi` (`rpi`),
KEY `alt_dcs` (`alt_dcs`),
KEY `charset` (`charset`),
KEY `boxc_id` (`boxc_id`),
KEY `binfo` (`binfo`),
KEY `priority` (`priority`),
KEY `custom` (`custom`),
KEY `additional` (`additional`),
KEY `service_id` (`service_id`),
KEY `insertdate` (`insertdate`),
KEY `fid` (`fid`),
KEY `msgid` (`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Willy
sangpr...@gmail.com
On Jan 3, 2010, at 6:00 PM, sureshkumar...@gmail.com wrote:
> Hi Willy,
> What is the engine you are using for the table? Paste the table
> structure.
> Thanks,
> Suresh Kuna
> --Original Message--
> From: Willy Mularto
> To: mysql@lists.mysql.com
> Subject: High Overhead On Active Insert And Delete Table
> Sent: Jan 3, 2010 4:22 PM
>
> Hi list,
> I have a table which is very active in operation INSERT INTO and
> DELETE, approximately there will be around 2millions INSERT and DELETE
> operation per day. And I see the overhead is getting very high, I must
> do OPTIMIZE TABLE query every time. Is there any other option to solve
> this? TIA.
>
>
>
> Willy
> sangpr...@gmail.com
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com
>
>
>
> Sent from BlackBerry® on Airtel
Re: High Overhead On Active Insert And Delete Table
The engine is using MyISAM. And here is the structure:
CREATE TABLE IF NOT EXISTS `ie_push` (
`sql_id` bigint(20) NOT NULL auto_increment,
`momt` enum('MO','MT','DLR') default NULL,
`sender` varchar(20) default NULL,
`receiver` varchar(20) default NULL,
`udhdata` blob,
`msgdata` text,
`time` bigint(20) default NULL,
`smsc_id` varchar(255) default NULL,
`service` varchar(255) default NULL,
`account` varchar(255) default NULL,
`id` bigint(20) default NULL,
`sms_type` bigint(20) default NULL,
`mclass` bigint(20) default NULL,
`mwi` bigint(20) default NULL,
`coding` bigint(20) default NULL,
`compress` bigint(20) default NULL,
`validity` bigint(20) default NULL,
`deferred` bigint(20) default NULL,
`dlr_mask` bigint(20) default NULL,
`dlr_url` varchar(255) default NULL,
`pid` bigint(20) default NULL,
`alt_dcs` bigint(20) default NULL,
`rpi` bigint(20) default NULL,
`charset` varchar(255) default NULL,
`boxc_id` varchar(255) default NULL,
`binfo` varchar(255) default NULL,
`priority` tinyint(4) default NULL,
`custom` varchar(255) default NULL,
`additional` varchar(255) default NULL,
`service_id` varchar(50) default NULL,
`fid` varchar(50) default NULL,
`msgid` varchar(50) default NULL,
`insertdate` datetime default NULL,
PRIMARY KEY (`sql_id`),
KEY `momt` (`momt`),
KEY `sender` (`sender`),
KEY `receiver` (`receiver`),
KEY `time` (`time`),
KEY `service` (`service`),
KEY `account` (`account`),
KEY `id` (`id`),
KEY `sms_type` (`sms_type`),
KEY `mclass` (`mclass`),
KEY `mwi` (`mwi`),
KEY `coding` (`coding`),
KEY `compress` (`compress`),
KEY `validity` (`validity`),
KEY `deferred` (`deferred`),
KEY `dlr_mask` (`dlr_mask`),
KEY `dlr_url` (`dlr_url`),
KEY `pid` (`pid`),
KEY `rpi` (`rpi`),
KEY `alt_dcs` (`alt_dcs`),
KEY `charset` (`charset`),
KEY `boxc_id` (`boxc_id`),
KEY `binfo` (`binfo`),
KEY `priority` (`priority`),
KEY `custom` (`custom`),
KEY `additional` (`additional`),
KEY `service_id` (`service_id`),
KEY `insertdate` (`insertdate`),
KEY `fid` (`fid`),
KEY `msgid` (`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Willy
sangpr...@gmail.com
On Jan 3, 2010, at 6:00 PM, sureshkumar...@gmail.com wrote:
Hi Willy,
What is the engine you are using for the table? Paste the table
structure.
Thanks,
Suresh Kuna
--Original Message--
From: Willy Mularto
To: mysql@lists.mysql.com
Subject: High Overhead On Active Insert And Delete Table
Sent: Jan 3, 2010 4:22 PM
Hi list,
I have a table which is very active in operation INSERT INTO and
DELETE, approximately there will be around 2millions INSERT and DELETE
operation per day. And I see the overhead is getting very high, I must
do OPTIMIZE TABLE query every time. Is there any other option to solve
this? TIA.
Willy
sangpr...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com
Sent from BlackBerry® on Airtel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: High Overhead On Active Insert And Delete Table
Hi Willy, What is the engine you are using for the table? Paste the table structure. Thanks, Suresh Kuna --Original Message-- From: Willy Mularto To: mysql@lists.mysql.com Subject: High Overhead On Active Insert And Delete Table Sent: Jan 3, 2010 4:22 PM Hi list, I have a table which is very active in operation INSERT INTO and DELETE, approximately there will be around 2millions INSERT and DELETE operation per day. And I see the overhead is getting very high, I must do OPTIMIZE TABLE query every time. Is there any other option to solve this? TIA. Willy sangpr...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com Sent from BlackBerry® on Airtel
High Overhead On Active Insert And Delete Table
Hi list, I have a table which is very active in operation INSERT INTO and DELETE, approximately there will be around 2millions INSERT and DELETE operation per day. And I see the overhead is getting very high, I must do OPTIMIZE TABLE query every time. Is there any other option to solve this? TIA. Willy sangpr...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Delete table definition without .frm files.
No, if you were to try a create statement, mysql would notice the still-existing data in the InnoDB file. You just have to create a .frm file on the filesystem level, and assign it the correct permissions (on unix, ug=rw and owned by mysql:mysql or something similar). On Mon, Dec 14, 2009 at 2:13 PM, Manasi Save < manasi.s...@artificialmachines.com> wrote: > Thanks Johan, > > I tried doing this. When I try to delete that empty table it is giving me > an error saying Unknow table 'tblename'. > I have created empty .frm file at folder level. can I create one from > mysql. > Thanks in advance. > -- > Regards, > Manasi Save > > > > Quoting Johan De Meersman : > >> Heh. You'll need to just create an empty .frm file, and then issue the >> drop >> table statement. >> On Mon, Dec 14, 2009 at 6:11 AM, Manasi Save < >> manasi.s...@artificialmachines.com> wrote: >> >> > Hi All, >> > >> > >> > I really don't know how to elaborate this problem because it is quite >> > strange- >> > >> > >> > I have deleted .frm files but not by issuing Drop table command I >> deleted >> > the folder from backend at folder level as there was some problem. > >> > >> > Is there any way I can delete this definition from innodb datafile? >> > >> > >> > Thanks in advance. > >> > -- >> > >> > Regards, >> > Manasi Save >> > Artificial Machines Private Limited >> > >> > >> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be > >
Re: Delete table definition without .frm files.
Thanks Johan, I tried doing this. When I try to delete that empty table it is giving me an error saying Unknow table 'tblename'. I have created empty .frm file at folder level. can I create one from mysql. Thanks in advance. -- Regards, Manasi Save Quoting Johan De Meersman : Heh. You'll need to just create an empty .frm file, and then issue the drop table statement. On Mon, Dec 14, 2009 at 6:11 AM, Manasi Save < manasi.s...@artificialmachines.com> wrote: > Hi All, > > > I really don't know how to elaborate this problem because it is quite > strange- > > > I have deleted .frm files but not by issuing Drop table command I deleted > the folder from backend at folder level as there was some problem. > > > Is there any way I can delete this definition from innodb datafile? > > > Thanks in advance. > > -- > > Regards, > Manasi Save > Artificial Machines Private Limited > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Delete table definition without .frm files.
Heh. You'll need to just create an empty .frm file, and then issue the drop table statement. On Mon, Dec 14, 2009 at 6:11 AM, Manasi Save < manasi.s...@artificialmachines.com> wrote: > Hi All, > > > I really don't know how to elaborate this problem because it is quite > strange- > > > I have deleted .frm files but not by issuing Drop table command I deleted > the folder from backend at folder level as there was some problem. > > > Is there any way I can delete this definition from innodb datafile? > > > Thanks in advance. > > -- > > Regards, > Manasi Save > Artificial Machines Private Limited > >
Delete table definition without .frm files.
Hi All,I really don't know how to elaborate this problem because it is quite strange-I have deleted .frm files but not by issuing Drop table command I deleted the folder from backend at folder level as there was some problem.Is there any way I can delete this definition from innodb datafile? Thanks in advance. --Regards, Manasi Save Artificial Machines Private Limited
RE: DELETE DATA FROM TABLE
Hi Krishna, Drop partition should be very quick - much faster than doing a DELETE on the same amount of data. Internally, it will be the same as doing a drop table for that partition. Regards, Gavin Towey From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: Thursday, November 19, 2009 1:15 AM To: Gavin Towey Cc: MySQL Subject: Re: DELETE DATA FROM TABLE Hi Gavin, I am using innodb with file-per-table. I agree with you dropping a partition will reclaim disk space. alter table drop partition But, my concern is "alter table drop partition " on very big table would might take a lot of time. (Although, I haven't tested) Thanks for the immediate response. Thanks & Regard, Krishna Ch. Prajapati On Thu, Nov 19, 2009 at 2:22 PM, Gavin Towey mailto:gto...@ffn.com>> wrote: Assuming you're using either myisam tables, or innodb with file-per-table option turned on, then dropping a whole partition at a time will allow you to reclaim disk space. If you're using innodb with a single tablespace currently, then unfortunately, you would have to export all your data, shutdown mysql, change you're my.cnf & delete the tablespace & ib_log files, then restart and re-import all your data. If you need to do this, you should probably seek a bit more information about from this list or other sources. Regards, Gavin Towey -Original Message- From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com<mailto:prajapat...@gmail.com>] Sent: Thursday, November 19, 2009 12:13 AM To: MySQL Subject: DELETE DATA FROM TABLE Hi Experts, I have a crm table where 12 millions records inserted/day. We are running report queries on this table and using partitioning features for faster results. we have to maintain 45 days data means 540million records. As per my calculation 540 records will use 1.8 TB of disk space. Total disk space available is 2.3TB. Deleting data doesn't free up the disk space. So, I was thinking of rotating the table. But doesn't have enough disk space. Any Idea, how this task can be performed. Any idea or suggestion is highly appreciated. Thanks & Regards, Krishna Ch. Prajapati The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: DELETE DATA FROM TABLE
Hi Gavin, I am using innodb with file-per-table. I agree with you dropping a partition will reclaim disk space. alter table drop partition But, my concern is "alter table drop partition " on very big table would might take a lot of time. (Although, I haven't tested) Thanks for the immediate response. Thanks & Regard, Krishna Ch. Prajapati On Thu, Nov 19, 2009 at 2:22 PM, Gavin Towey wrote: > Assuming you're using either myisam tables, or innodb with file-per-table > option turned on, then dropping a whole partition at a time will allow you > to reclaim disk space. > > If you're using innodb with a single tablespace currently, then > unfortunately, you would have to export all your data, shutdown mysql, > change you're my.cnf & delete the tablespace & ib_log files, then restart > and re-import all your data. If you need to do this, you should probably > seek a bit more information about from this list or other sources. > > Regards, > Gavin Towey > > -Original Message- > From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] > Sent: Thursday, November 19, 2009 12:13 AM > To: MySQL > Subject: DELETE DATA FROM TABLE > > Hi Experts, > > I have a crm table where 12 millions records inserted/day. We are running > report queries on this table and using partitioning features for faster > results. we have to maintain 45 days data means 540million records. As per > my calculation 540 records will use 1.8 TB of disk space. Total disk space > available is 2.3TB. > > Deleting data doesn't free up the disk space. So, I was thinking of > rotating > the table. But doesn't have enough disk space. > > Any Idea, how this task can be performed. > > Any idea or suggestion is highly appreciated. > > Thanks & Regards, > Krishna Ch. Prajapati > > The information contained in this transmission may contain privileged and > confidential information. It is intended only for the use of the person(s) > named above. If you are not the intended recipient, you are hereby notified > that any review, dissemination, distribution or duplication of this > communication is strictly prohibited. If you are not the intended recipient, > please contact the sender by reply email and destroy all copies of the > original message. >
RE: DELETE DATA FROM TABLE
Assuming you're using either myisam tables, or innodb with file-per-table option turned on, then dropping a whole partition at a time will allow you to reclaim disk space. If you're using innodb with a single tablespace currently, then unfortunately, you would have to export all your data, shutdown mysql, change you're my.cnf & delete the tablespace & ib_log files, then restart and re-import all your data. If you need to do this, you should probably seek a bit more information about from this list or other sources. Regards, Gavin Towey -Original Message- From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: Thursday, November 19, 2009 12:13 AM To: MySQL Subject: DELETE DATA FROM TABLE Hi Experts, I have a crm table where 12 millions records inserted/day. We are running report queries on this table and using partitioning features for faster results. we have to maintain 45 days data means 540million records. As per my calculation 540 records will use 1.8 TB of disk space. Total disk space available is 2.3TB. Deleting data doesn't free up the disk space. So, I was thinking of rotating the table. But doesn't have enough disk space. Any Idea, how this task can be performed. Any idea or suggestion is highly appreciated. Thanks & Regards, Krishna Ch. Prajapati The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: DELETE DATA FROM TABLE
Krishna Chandra Prajapati wrote: Hi Experts, I have a crm table where 12 millions records inserted/day. We are running report queries on this table and using partitioning features for faster results. we have to maintain 45 days data means 540million records. As per my calculation 540 records will use 1.8 TB of disk space. Total disk space available is 2.3TB. Deleting data doesn't free up the disk space. So, I was thinking of rotating the table. But doesn't have enough disk space. Any Idea, how this task can be performed. Any idea or suggestion is highly appreciated. The space is freed inside the table space but is not seen on disk. Use "show table status" to show a tables "data_free" variable. If you prompt want to free the space so you can see it on the file system you can use "optimize table" command. But the operation can be slow and the table will be locked. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
DELETE DATA FROM TABLE
Hi Experts, I have a crm table where 12 millions records inserted/day. We are running report queries on this table and using partitioning features for faster results. we have to maintain 45 days data means 540million records. As per my calculation 540 records will use 1.8 TB of disk space. Total disk space available is 2.3TB. Deleting data doesn't free up the disk space. So, I was thinking of rotating the table. But doesn't have enough disk space. Any Idea, how this task can be performed. Any idea or suggestion is highly appreciated. Thanks & Regards, Krishna Ch. Prajapati
Re: Multiple Rows DELETE Fails on Replication
DELETE FROM table WHERE key LIKE '%100%' Also I tried to continously insert big numbers of new records (around 50 millions new records) and tried to delete 1 record on master, I checked the slave not synchronized. Is it just a lag? Willy On Fri, 2009-06-26 at 16:14 +0800, Moon's Father wrote: > > Could you tell me your detail statements? > On Wed, Jun 24, 2009 at 9:46 AM, sangprabv > wrote: > Hi, > I found every time I try to do multiple rows delete on > replicated MySQL > is always failed. Is there any explanation regarding this > issue and how > to solve it? TIA. > > > > Willy > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com > > > > > -- > David Yeung, > MySQL Senior Support Engineer, > Sun Gold Partner. > My Blog:http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Multiple Rows DELETE Fails on Replication
Could you tell me your detail statements? On Wed, Jun 24, 2009 at 9:46 AM, sangprabv wrote: > Hi, > I found every time I try to do multiple rows delete on replicated MySQL > is always failed. Is there any explanation regarding this issue and how > to solve it? TIA. > > > > Willy > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com > > -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
