Re: Concurrency Question
Joshua, - Original Message - From: Joshua Chamas [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, July 12, 2004 11:33 PM Subject: RE: Concurrency Question Quoting Marvin Wright [EMAIL PROTECTED]: Hi, It suggests below to bundle transactions into one commit, at what point does this become unecessary ? For example I have 2 threads each doing 12,000 inserts in 1 commit each. Would I really gain any performance if I did these 24,000 inserts in 1 commit only ? I would not think this would make a difference, in fact you should test this, as it may be that in fact bundling 24,000 at a time is slower than 12,000 at a time. I know that things can slow down when doing too much in one transaction in Oracle for example, and InnoDB/MySQL might be similar. big transactions do not slow down InnoDB. But, of course, if the application is multithreaded and the clients compete for row locks, then having big transactions will increase the risk of deadlocks and lock waits. Regards, Josh please address these general MySQL questions to [EMAIL PROTECTED] Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Concurrency Question
Hi, It suggests below to bundle transactions into one commit, at what point does this become unecessary ? For example I have 2 threads each doing 12,000 inserts in 1 commit each. Would I really gain any performance if I did these 24,000 inserts in 1 commit only ? Additionally, what performance should I be able to acheive with innodb ? My server is currently Linux RH, 2 CPU's 3.2Ghz, 4 Gig of ram and SCSI drives. I think my queires are optimised, I've had as much as 8000-9000 inserts per second but this is when I start to run into machine load issues. Am I asking to much of the machine ? Marvin. -Original Message- From: Josh Chamas [mailto:[EMAIL PROTECTED] Sent: 05 July 2004 20:23 To: Javier Diaz Cc: [EMAIL PROTECTED] Subject: Re: Concurrency Question Javier Diaz wrote: Hi We have changed all our tables to InnoDB and now the server is not able to handle the load, even when we are not running the SELECTs statements against these tables yet. As I mentioned in my email we make a lots of INSERTS and UPDATES in these tables (more than 3000 per second). So far using MyISAM everything was OK, but now when we moved the tables to InnoDB (to be able to make Read/Write operations in these tables) the performance was down completely and the server can not handle it. Does anyone have a rough idea when you change from MyISAM to InnoDB how the performance is affected? I would appreciate any ideas you can have, we really need this ASAP. I would suggest load/capacity testing things very carefully in a test environment before moving your system to InnoDB. InnoDB has very different locking / disk i/o behavior than MyISAM as you have discovered. It also seems to use about 2x the disk space for my tables as it has something like a 19 byte overhead per record in the table. Like others suggested, make sure you bundle as many transactions as possible in one commit. Each commit will end up doing a disk write, so using an auto-commit mode ( without BEGIN WORK ) will result in one disk write per transaction. If you bundle many SQL statements in one transaction, you can get a relative performance improvement, say if you can get an average of 3 insert/updates per transaction, then you have just increased your performance by a factor of 3 if your operations were disk bound in the first place which is likely at 3000 inserts/updates per second. That said, I also found that InnoDB can do some non-intuitive row level locking that can result in dead locks, so when moving to many SQL operations per transaction, you also have to test this carefully under load to make sure that your code does not result in any dead locks. Regards, Josh Josh Chamas, Founder| NodeWorks - http://www.nodeworks.com Chamas Enterprises Inc. | NodeWorks Directory - http://dir.nodeworks.com http://www.chamas.com | Apache::ASP - http://www.apache-asp.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Concurrency Question
Quoting Marvin Wright [EMAIL PROTECTED]: Hi, It suggests below to bundle transactions into one commit, at what point does this become unecessary ? For example I have 2 threads each doing 12,000 inserts in 1 commit each. Would I really gain any performance if I did these 24,000 inserts in 1 commit only ? I would not think this would make a difference, in fact you should test this, as it may be that in fact bundling 24,000 at a time is slower than 12,000 at a time. I know that things can slow down when doing too much in one transaction in Oracle for example, and InnoDB/MySQL might be similar. With regards to performance gains by bundling, this is more about not committing after one insert but committing after 10 or 100, as there will be certain performance gains here. Additionally, what performance should I be able to acheive with innodb ? My server is currently Linux RH, 2 CPU's 3.2Ghz, 4 Gig of ram and SCSI drives. I think my queires are optimised, I've had as much as 8000-9000 inserts per second but this is when I start to run into machine load issues. Am I asking to much of the machine ? Each platform/hardware/OS/etc. will have different performance than another, but I would think that you are doing pretty well at 8000-9000 inserts per second. With a dual CPU system, you could also try doing the inserts in parallel for further speed, for example in a 2nd forked or threaded job, so as to make sure the system is taking full advantage of that 2nd processor, otherwise a process that is executing serially/sequentially will just use up one CPU. When it comes to benchmarking performance tuning, make sure to establish a level as which performance is good enough ahead of time, otherwise you can spend all your time doing these things with only marginal gains. Regards, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concurrency Question
Javier Diaz [EMAIL PROTECTED] wrote: 3- Replication of these tables to another server ?? This is classical solution to the problem and probably the easiest to provide. Just do SELECTs on the slave server. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Concurrency Question
Hi We have changed all our tables to InnoDB and now the server is not able to handle the load, even when we are not running the SELECTs statements against these tables yet. As I mentioned in my email we make a lots of INSERTS and UPDATES in these tables (more than 3000 per second). So far using MyISAM everything was OK, but now when we moved the tables to InnoDB (to be able to make Read/Write operations in these tables) the performance was down completely and the server can not handle it. Does anyone have a rough idea when you change from MyISAM to InnoDB how the performance is affected? I would appreciate any ideas you can have, we really need this ASAP. Thanks Javier` -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 02 July 2004 10:42 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Concurrency Question Javier Diaz [EMAIL PROTECTED] wrote on 02/07/2004 10:28:32: We have some tables to record different data of the activity in our website. The number of INSERT, DELETE and UPDATE operations in these tables is huge (it could be more than 3000 a second). So far we don't have any speed problems, all these tables are optimised using the right indexes and everything is working ok. But now we are writing a Report Tool to get some stats and figures from these tables. We have been doing a few tests and any SELECT query taking more than one second or a few simultaneous SELECT, and we have a real mess, lots of LOCKS. We definitely can not afford to slow down the web site, and we have been thinking in a few possible solutions 1- Create a duplicate once a day of each of the tables we need to connect from the Report Tool. We can do this in the period of less activity in the site. This a safest solution because we will be running all the SELECT's against the duplicates and there are no risks to cause problems in the site but we will have the inconvenience that we can not get latest figures only the previous days. Sounds like a nasty kludge to me. I really wouldn't be keen on it at all. 2- Use InnoDB instead of MyISAM, but we are not sure is this will be good enough It strikes me that this is what InnoDB is designed for. In your situation, this is the first thing I would try. 3- Replication of these tables to another server ?? This would also work: it just uses more resources (another computer of nearly equal power) and more development time (scripts to stop and start replication. If you can borrow the replicated machine, you could test the InnoDB solution on that: change the tables on the replicated server to InnoDB and try running your report generator on that. If the replications doesn't fall behind, the main server will probably handle it. Alec This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concurrency Question
On Mon, 5 Jul 2004 16:07:58 +0100 , Javier Diaz [EMAIL PROTECTED] wrote: We have changed all our tables to InnoDB and now the server is not able to handle the load, even when we are not running the SELECTs statements against these tables yet. As I mentioned in my email we make a lots of INSERTS and UPDATES in these tables (more than 3000 per second). So far using MyISAM everything was OK, but now when we moved the tables to InnoDB (to be able to make Read/Write operations in these tables) the performance was down completely and the server can not handle it. Does anyone have a rough idea when you change from MyISAM to InnoDB how the performance is affected? That all depends on how you are using transactions. If you are trying to do each of these operations in a separate transaction, then definitely that will be a problem since transactions inherently have a certain cost to them since they need to commit changes to durable storage. If this is the case, then a horribly ugly now you don't have durability any more in your transactions hack you could try is setting innodb_flush_log_at_trx_commit to 2, see the docs for details. Be warned that doing so means you can loose committed transactions if the machine crashes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Concurrency Question
Hi I really don't like the idea to set innodb_flush_log_at_trx_commit to 2, the information in these tables is important. On the other hand there is nothing I can do from the point of view of the number of transactions. Each process run its own set of INSERTs and UPDATEs statements, so I can not reduce the number of transactions being executed. Looking to the MySQL documentation: Since the rotation speed of a disk is typically at most 167 revolutions/second, that constrains the number of commits to the same 167th/second if the disk does not fool the operating system And that we are doing a LOT MORE INSERTs by second, I'm afraid maybe the only solution is go back to MyISAM :-( By the way this figure of 167 revolutions/second is based on what kind of hard disk? thanks Javier -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: 05 July 2004 17:58 To: Javier Diaz Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Concurrency Question On Mon, 5 Jul 2004 16:07:58 +0100 , Javier Diaz [EMAIL PROTECTED] wrote: We have changed all our tables to InnoDB and now the server is not able to handle the load, even when we are not running the SELECTs statements against these tables yet. As I mentioned in my email we make a lots of INSERTS and UPDATES in these tables (more than 3000 per second). So far using MyISAM everything was OK, but now when we moved the tables to InnoDB (to be able to make Read/Write operations in these tables) the performance was down completely and the server can not handle it. Does anyone have a rough idea when you change from MyISAM to InnoDB how the performance is affected? That all depends on how you are using transactions. If you are trying to do each of these operations in a separate transaction, then definitely that will be a problem since transactions inherently have a certain cost to them since they need to commit changes to durable storage. If this is the case, then a horribly ugly now you don't have durability any more in your transactions hack you could try is setting innodb_flush_log_at_trx_commit to 2, see the docs for details. Be warned that doing so means you can loose committed transactions if the machine crashes. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Concurrency Question
At 18:48 +0100 7/5/04, Javier Diaz wrote: Hi I really don't like the idea to set innodb_flush_log_at_trx_commit to 2, the information in these tables is important. On the other hand there is nothing I can do from the point of view of the number of transactions. Each process run its own set of INSERTs and UPDATEs statements, so I can not reduce the number of transactions being executed. Looking to the MySQL documentation: Since the rotation speed of a disk is typically at most 167 revolutions/second, that constrains the number of commits to the same 167th/second if the disk does not fool the operating system And that we are doing a LOT MORE INSERTs by second, I'm afraid maybe the only solution is go back to MyISAM :-( By the way this figure of 167 revolutions/second is based on what kind of hard disk? Presumably one that spins at 1 RPM. (167 * 60 is approximately 1) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concurrency Question
On Mon, 5 Jul 2004 18:48:50 +0100 , Javier Diaz [EMAIL PROTECTED] wrote: I really don't like the idea to set innodb_flush_log_at_trx_commit to 2, the information in these tables is important. On the other hand there is nothing I can do from the point of view of the number of transactions. Each process run its own set of INSERTs and UPDATEs statements, so I can not reduce the number of transactions being executed. Looking to the MySQL documentation: Since the rotation speed of a disk is typically at most 167 revolutions/second, that constrains the number of commits to the same 167th/second if the disk does not fool the operating system And that we are doing a LOT MORE INSERTs by second, I'm afraid maybe the only solution is go back to MyISAM :-( By the way this figure of 167 revolutions/second is based on what kind of hard disk? Well, if you are using myisam you already have even fewer guarantees about transactional integrity than innodb with innodb_flush_log_at_trx_commit set to 2. That is the only reason that myisam can perform as it does in the manner you are using it. So if that is all that is worrying you, no reason not to try innodb setup that way. You may want to look more closely at how you may be able to re architect your system to not require so many transactions, such as by having a middle tier that can aggregate information before committing it. Unfortunately, myisam tricks people into thinking disk based databases can safely handle the sort of operation you are doing, then leaves them in an unfortunate situation when they realize that myisam has no durability guarantees. A ballpark figure that applies to disk based databases is that you can do approximately one write operation per rotation, which translates into one transaction per rotation. This logic makes some assumptions and isn't exact with modern disks, but is a reasonable ballpark. 167 revolutions per second is a 10k RPM drive. You can improve this with the right type of RAID, you can improve it with faster disks, but it is still a fairly small number. You can improve it further with a battery backed disk controller that can cache writes, although the reliability of some of the cheaper options there isn't great. You can improve it with a database that doesn't commit to disk, such as mysql cluster however that is a whole different ballpark and a ways from being ready for prime time and has all sorts of issues of its own. Some databases can be smart and coalesce commits from multiple connections into one write to disk, but this is a fairly uncommon feature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concurrency Question
Javier Diaz wrote: Hi We have changed all our tables to InnoDB and now the server is not able to handle the load, even when we are not running the SELECTs statements against these tables yet. As I mentioned in my email we make a lots of INSERTS and UPDATES in these tables (more than 3000 per second). So far using MyISAM everything was OK, but now when we moved the tables to InnoDB (to be able to make Read/Write operations in these tables) the performance was down completely and the server can not handle it. Does anyone have a rough idea when you change from MyISAM to InnoDB how the performance is affected? I would appreciate any ideas you can have, we really need this ASAP. I would suggest load/capacity testing things very carefully in a test environment before moving your system to InnoDB. InnoDB has very different locking / disk i/o behavior than MyISAM as you have discovered. It also seems to use about 2x the disk space for my tables as it has something like a 19 byte overhead per record in the table. Like others suggested, make sure you bundle as many transactions as possible in one commit. Each commit will end up doing a disk write, so using an auto-commit mode ( without BEGIN WORK ) will result in one disk write per transaction. If you bundle many SQL statements in one transaction, you can get a relative performance improvement, say if you can get an average of 3 insert/updates per transaction, then you have just increased your performance by a factor of 3 if your operations were disk bound in the first place which is likely at 3000 inserts/updates per second. That said, I also found that InnoDB can do some non-intuitive row level locking that can result in dead locks, so when moving to many SQL operations per transaction, you also have to test this carefully under load to make sure that your code does not result in any dead locks. Regards, Josh Josh Chamas, Founder| NodeWorks - http://www.nodeworks.com Chamas Enterprises Inc. | NodeWorks Directory - http://dir.nodeworks.com http://www.chamas.com | Apache::ASP - http://www.apache-asp.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Concurrency Question
Hi everyone We have some tables to record different data of the activity in our website. The number of INSERT, DELETE and UPDATE operations in these tables is huge (it could be more than 3000 a second). So far we don't have any speed problems, all these tables are optimised using the right indexes and everything is working ok. But now we are writing a Report Tool to get some stats and figures from these tables. We have been doing a few tests and any SELECT query taking more than one second or a few simultaneous SELECT, and we have a real mess, lots of LOCKS. We definitely can not afford to slow down the web site, and we have been thinking in a few possible solutions 1- Create a duplicate once a day of each of the tables we need to connect from the Report Tool. We can do this in the period of less activity in the site. This a safest solution because we will be running all the SELECT's against the duplicates and there are no risks to cause problems in the site but we will have the inconvenience that we can not get latest figures only the previous days. 2- Use InnoDB instead of MyISAM, but we are not sure is this will be good enough 3- Replication of these tables to another server ?? We would appreciate a lot any ideas Thanks a lot Javier This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concurrency Question
Javier Diaz [EMAIL PROTECTED] wrote on 02/07/2004 10:28:32: We have some tables to record different data of the activity in our website. The number of INSERT, DELETE and UPDATE operations in these tables is huge (it could be more than 3000 a second). So far we don't have any speed problems, all these tables are optimised using the right indexes and everything is working ok. But now we are writing a Report Tool to get some stats and figures from these tables. We have been doing a few tests and any SELECT query taking more than one second or a few simultaneous SELECT, and we have a real mess, lots of LOCKS. We definitely can not afford to slow down the web site, and we have been thinking in a few possible solutions 1- Create a duplicate once a day of each of the tables we need to connect from the Report Tool. We can do this in the period of less activity in the site. This a safest solution because we will be running all the SELECT's against the duplicates and there are no risks to cause problems in the site but we will have the inconvenience that we can not get latest figures only the previous days. Sounds like a nasty kludge to me. I really wouldn't be keen on it at all. 2- Use InnoDB instead of MyISAM, but we are not sure is this will be good enough It strikes me that this is what InnoDB is designed for. In your situation, this is the first thing I would try. 3- Replication of these tables to another server ?? This would also work: it just uses more resources (another computer of nearly equal power) and more development time (scripts to stop and start replication. If you can borrow the replicated machine, you could test the InnoDB solution on that: change the tables on the replicated server to InnoDB and try running your report generator on that. If the replications doesn't fall behind, the main server will probably handle it. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
