Re: Replace delayed locks table
Replace deletes and inserts. ? what do you mean? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
matt ryan wrote: Replace deletes and inserts. ? what do you mean? Replace does a delete followed by an insert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
Replace does a delete followed by an insert. Ahh, I'm testing innodb on our tables with this problem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
matt ryan wrote: Replace does a delete followed by an insert. Ahh, I'm testing innodb on our tables with this problem I've switched to innodb but performance isnt very good while the insert runs, here's what I get for performance select count(*) from rondon; 1 row in .13 sec select count(*) from rondon; 1 row in 21.88 sec select count(*) from rondon; 1 row in 42.47 sec select count(*) from rondon; 1 row in 1 min 47.69 sec not sure why the first was so fast, the rest SUCK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
matt ryan wrote: matt ryan wrote: Replace does a delete followed by an insert. Ahh, I'm testing innodb on our tables with this problem I've switched to innodb but performance isnt very good while the insert runs, here's what I get for performance select count(*) from rondon; 1 row in .13 sec select count(*) from rondon; 1 row in 21.88 sec select count(*) from rondon; 1 row in 42.47 sec select count(*) from rondon; 1 row in 1 min 47.69 sec not sure why the first was so fast, the rest SUCK Well, this a particularly bad command to use to test innodb performance. With MyISAM table, the record count is immediately available. With Innodb, the server has to actually count records. Different users will get different counts depending on the transaction isolation mode and number of uncommitted records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
matt ryan wrote: Replace does a delete followed by an insert. Ahh, I'm testing innodb on our tables with this problem Doh another problem innodb has no merge option, I have too much data, and the only way to deal with it, is partition the data and then tie it together with merge views. Unfortunatly innodb will not work for me :( Anybody know if SQL Server desktop supports what I need? I know oracle does, but the cost is an issue, maxdb costs too much too. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
I hate to pry (snoop) but my curiosity is just going nuts! If this is a sensitive issue, please ignore my questions What are you doing that requires you to mass-replace so many records so often? Are they design or processing requirements (or both) that require this kind of bulk exchange of records? How open are you to the idea of possibly changing the way you deal with these records? I would feel horrible if after all this time and all of the brains that subscribe to this list that you would end this thread without some kind of improvement to your situation. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine matt ryan [EMAIL PROTECTED] wrote on 08/13/2004 03:26:57 PM: matt ryan wrote: Replace does a delete followed by an insert. Ahh, I'm testing innodb on our tables with this problem Doh another problem innodb has no merge option, I have too much data, and the only way to deal with it, is partition the data and then tie it together with merge views. Unfortunatly innodb will not work for me :( Anybody know if SQL Server desktop supports what I need? I know oracle does, but the cost is an issue, maxdb costs too much too. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replace delayed locks table
SQL is, replace delayed into table c1, c1, c3 select c1, c2, c3 from temp table. This takes 2 hours to comlete, the temp table is rather large. The table being updated is locked, the whole time, all web requests are locked and the pages time out. Is there any way to get this to run without locking the whole table? I thought with myisam it would only lock a table if you delete records, and insert records, it locks it to fill the gaps. If I need to switch to another table type it's an option, having locked tables is NOT an option. Thanks in advance Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
matt ryan wrote: SQL is, replace delayed into table c1, c1, c3 select c1, c2, c3 from temp table. This takes 2 hours to comlete, the temp table is rather large. The table being updated is locked, the whole time, all web requests are locked and the pages time out. Is there any way to get this to run without locking the whole table? I thought with myisam it would only lock a table if you delete records, and insert records, it locks it to fill the gaps. Replace deletes and inserts. If I need to switch to another table type it's an option, having locked tables is NOT an option. Thanks in advance Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replace delayed locks table
If you are using a MyISAM table type, the entire table is either locked or it isn't. Normally this isn't a problem most statements happen quickly. However, yours takes a couple of hours to finish so the entire table has to stay locked until your statement completes. The way I see it, you have only a few options: 1) Redesign your update to happen in smaller batches (scripting maybe?). This gives the engine a chance to handle other requests between the updates/replaces. 2) Change your update/replace so that it doen't need to work on so many records. That will help it to finish sooner and not lock you down for so long. 3) Change to an InnoDB table type. It supports row-level locking. Shawn Green Database Administrator Unimin Corporation - Spruce Pine matt ryan [EMAIL PROTECTED] wrote on 08/12/2004 01:59:42 PM: SQL is, replace delayed into table c1, c1, c3 select c1, c2, c3 from temp table. This takes 2 hours to comlete, the temp table is rather large. The table being updated is locked, the whole time, all web requests are locked and the pages time out. Is there any way to get this to run without locking the whole table? I thought with myisam it would only lock a table if you delete records, and insert records, it locks it to fill the gaps. If I need to switch to another table type it's an option, having locked tables is NOT an option. Thanks in advance Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
