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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
