> -----Original Message----- > From: Devananda [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 29, 2005 14:56 > To: Jeff > Cc: mysql@lists.mysql.com > Subject: Re: MyISAM to InnoDB > > > Jeff wrote: > > > > Well the applications with persistant connections is a > touchy subject. > > Our apps send and rec data over satelite links which are very > > expensive. The shorter the duration of the link the less it > costs us. > > So the pervailing theory is that with persistant > connections the apps > > will spend less time re-connecting/dis-connecting from the > db. Even > > fractions of a second counts when you're talking about thousands of > > connections a day and we are charged by the second for airtime. > > That's the whole driving force behind wanting to switch over to > > InnoDB. The thought is it would give us faster writes when > we have a > > hundred apps trying to write at or very near the same time > because of > > the record level locking as opposed to the MyISAM Table > level locking > > during writes and updates. > > > > Now, the question is, if we need to drop the persistant > connections in > > order to move to an InnoDB engine, will the speed benefit of record > > level locking outweigh what is lost by not having persistant > > connections? > > The only way to know is to test it in your environment. I > don't believe > anyone on the list could answer that question with certainty. > > Just out of curiosity, I wrote a couple scripts in perl to > very loosely > test this. > ------------------ > [EMAIL PROTECTED] - test]# cat loop.sh > #!/bin/bash > > for x in `seq 1 10`; do > $1 > done > ---------------------- > [EMAIL PROTECTED] - test]# cat con.pl > #!/usr/bin/perl > > use strict; > use warnings; > require DBI; > > print "Start\n"; > my $con = DBI->connect("DBI:mysql::db3-p", 'user', 'pass') > or die("Failed to connect!"); > print "Connected!\n"; > exit; > ---------------------- > [EMAIL PROTECTED] - test]# cat nocon.pl > #!/usr/bin/perl > > use strict; > use warnings; > require DBI; > > print "Start\n"; > #my $con = DBI->connect("DBI:mysql::db3-p", 'user', 'pass') > # or die("Failed to connect!"); > print "Skipped Connecting!\n"; > exit; > ------------------- > time ./loop.sh ; time ./loop.sh ./nocon.pl >/dev/null; time ./loop.sh > ./con.pl >/dev/null > > ((( bash script overhead ))) > real 0m0.004s > user 0m0.002s > sys 0m0.002s > ((( perl script with no connection ))) > real 0m0.595s > user 0m0.520s > sys 0m0.057s > ((( same perl script with connection ))) > real 0m0.781s > user 0m0.682s > sys 0m0.064s > > Now, I know this is *far* from an accurate test, and doesn't > demonstrate > any of the specifics of your servers, but it does show that, on my > servers, with perl, there is roughly a 0.02sec real and 0.007sec sys > overhead to make and close the connection. Take that for what > you will. > > > > That being said and having just looked at our connections > for the past > > 60 minutes during what is our roughly our peak time I only > see about > > 350 which is roughly one every 10 seconds with a rough avg > connection > > time of about 28 seconds most of which is transfer of data > and not db > > read/write/updates. So, I believe, from that information I > can make > > an educated guess that the MyISAM table locking is not the real > > bottleneck here and therefore it's probably not going to do > us a lot > > of good to switch to InnoDB, especially with our current > hardware and > > application behavior. Thoughts? > > With one connection every 10 seconds, I don't understand how > table lock > contention is a concern, unless your queries are so large > that they lock > the table for *that* long. If so, are they properly indexed? > > It doesn't sound like that is your problem though, so that's not a > reason to move to InnoDB. > > > > > At some point however, as our traffic grows we probably will hit a > > point where the db read/write/updates will start to become a > > bottleneck and we'll need to look at moving to a 64bit > arch, >2gig ram > > and the InnoDB engine. What status variables should I be > looking at > > to see if we have a lot of read/write/updates being delayed? > > > > See http://dev.mysql.com/doc/mysql/en/internal-locking.html > and http://dev.mysql.com/doc/mysql/en/show-status.html > > "Table_locks_immediate > > The number of times that a table lock was acquired > immediately. This variable was added as of MySQL 3.23.33. > > Table_locks_waited > > The number of times that a table lock could not be acquired > immediately and a wait was needed. If this is high, and you > have performance problems, you should first optimize your > queries, and then either split your table or tables or use > replication. This variable was added as of MySQL 3.23.33." > > For example, this is from our MyISAM server (uptime 200days, > 7% selects, > very un-optimized but still performs well enough), > > mysql> show status like 'table%'; > Table_locks_immediate 12810013 > Table_locks_waited 306450 > > > Hope that helps! > Devananda vdv >
All the information so far has helped! With our current Production DB server I'm seeing about 1 in 620 table locks that wait. +-----------------------+----------+ | Variable_name | Value | +-----------------------+----------+ | Table_locks_immediate | 66399221 | | Table_locks_waited | 106985 | +-----------------------+----------+ So I'm guessing that's not too bad. Given all the information that everyone here has contributed I don't think, at this point anyway, it's worth converting to InnoDB. From the reading I've done and all the info from everyone here I have a much better understanding of the InnoDB engine and setup though and that will certainly serve me in the future. I'd like to thank everyone for they're help and advice! Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
