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.
------------------
[deva@o1 - test]# cat loop.sh
#!/bin/bash
for x in `seq 1 10`; do
$1
done
----------------------
[deva@o1 - 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;
----------------------
[deva@o1 - 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
