Okay... but by looking in iostat, it looks like pretty low traffic. 1 to
2 MB/sec. A higher number of transfers per second, though.
You are right! Yes But the question is also, is there something else then...
A few ideas below. Sure not all apply for sure, but just to show you
that assuming it's the same setup and from 4.1 to 5.0 makes no
difference, or have no impact might not always be true.
Well... it's getting data from the master all the time, so I guess, it
will be in waiting for i/o all the time.
However, this is by "design" (if you like to speak of design in regards
to MySQL).
And still I should be able to connect to mysql and do a show slave
status\G quite fast (not waiting 6 seconds to complete that task...).
It gets even worse, if I try to do a select on some database. Yeah, the
database could be locked while I do that, but since there are 50 queries
/ second coming in, the database still should have enough time to answer
(in between being locked).
May be. But it may depend on many things including file system use too.
Does you Linux version actually writes the data to the drive, or to
cache and flush time to time. Meaning faster to process locks if you do
use any? If it crash, do you actually lost some data that were not
written to disk in that case? If it crash on OpenBSD, the data
will/should be there. I am not saying this is THE reason, but consider
it however.
Also some design in MySQL might affect you too if you do use locks and
you might here, I don't know the data you use: "WRITE locks normally
have higher priority than READ locks to ensure that updates are
processed as soon as possible. This means that if one thread obtains a
READ lock and then another thread requests a WRITE lock, subsequent READ
lock requests wait until the WRITE thread has gotten the lock and
released it. You can use LOW_PRIORITY WRITE locks to allow other threads
to obtain READ locks while the thread is waiting for the WRITE lock.
You should use LOW_PRIORITY WRITE locks only if you are sure that
eventually there will be a time when no threads have a READ lock."
Also something that may well apply to you as you refer to timezone table
that you do not replicate over. Did you consider this when mixing 4.1 to
5.0:
#If the master uses MySQL 4.1, the same system time zone should be set
for both master and slave. Otherwise some statements will not be
replicated properly, such as statements that use the NOW() or
FROM_UNIXTIME() functions. You can set the time zone in which MySQL
server runs by using the --timezone=timezone_name option of the
mysqld_safe script or by setting the TZ environment variable. Both
master and slave should also have the same default connection time zone
setting; that is, the --default-time-zone parameter should have the same
value for both master and slave. Note that this is not necessary when
the master is MySQL 5.0 or later.
Anyways, many others issues you should/need to consider when mixing, or
trying to mix version of master/slave 4.1 to 5.0:
http://mysql.speedbone.de/doc/refman/5.0/en/replication-features.html
Then do you use trigger as well? I am almost sure this doesn't apply to
you, but needs to be consider when mixing version for replications setup.
#Known issue: In MySQL 5.0.17, the syntax for CREATE TRIGGER changed to
include a DEFINER clause for specifying which access privileges to check
at trigger invocation time. (See Section 18.1, "CREATE TRIGGER Syntax",
for more information.) However, if you attempt to replicate from a
master server older than MySQL 5.0.17 to a slave running MySQL 5.0.17 or
up, replication of CREATE TRIGGER statements fails on the slave with a
Definer not fully qualified error. A workaround is to create triggers on
the master using a version-specific comment embedded in each CREATE
TRIGGER statement:
Some more issues with mixing 4/1 version as master to 5.0 as slave:
If the master uses MySQL 4.1, you must always use the same global
character set and collation on the master and the slave, regardless of
the MySQL version running on the slave. (These are controlled by the
--character-set-server and --collation-server options.) Otherwise, you
may get duplicate-key errors on the slave, because a key that is unique
in the master character set might not be unique in the slave character
set. Note that this is not a cause for concern when master and slave are
both MySQL 5.0 or later.
Also for speed improvements on slave:
http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
and a few more:
Q: How can I use replication to improve performance of my system?
A: You should set up one server as the master and direct all writes to
it. Then configure as many slaves as you have the budget and rackspace
for, and distribute the reads among the master and the slaves. You can
also start the slaves with the --skip-innodb, --skip-bdb,
--low-priority-updates, and --delay-key-write=ALL options to get speed
improvements on the slave end. In this case, the slave uses
non-transactional MyISAM tables instead of InnoDB and BDB tables to get
more speed by eliminating transactional overhead.
Okay, flame me, but, the same replication setup like it is in use here
on a Debian Woody with Linux 2.4.31 takes 0,00 seconds to do a select
count(*) foo; while OpenBSD needs from 0,83 to 7,56 seconds to complete
the request :-/
Strange...
I wouldn't say it's the same setup.
One use 4.1 and one use 5.0 and I can't speak to this as I haven't check
in a very long time, but does Woody and OpenBSd do use the same file
systems and that may be there isn't something else slowing you down
here. I only asked as I do not know the answer, but this is a
possibility. I know just to well that many on Linux use a different file
system for speed at the price of data integrity. Does this have any to
do with the speed difference you try to isolate. May be, then may be
not. I can't answer this with knowledge as I do not know for sure and I
wouldn't want to say something wrong either. But food for thought however.
mysql> select count(*) from foo;
I wouldn't expect to see that big of a difference, but any of the points
above apply to this setup here? To see .8 seconds and then almost 8
seconds later, but with just may be 100 records more might point you
else where, or some suggestion above might help as well. Some might
query from cache, or not written to disk yet and other might query from
real disk access after the lock is release...
I sure don't see this in any of my slave, but again all my volume insert
on the master are with delay insert. And if I do need, fast read access,
I can always use SELECT HIGH_PRIORITY as well. But never needed so far.
The Linux box is running the same hardware like the OpenBSD box. Only
difference is that the Linux box is running MySQL 4.1.14 whereas OpenBSD
runs 5.0.22
Might be a good amount of difference.
Granted, you can't compare those two systems.
On the other hand, the Linux box is in production, taking the 50 queries
/ second from replication while handling another 50 queries / second
due to being in production. Counts up to 100 queries per second avg.
Are you sure the configuration is the same for both.
May comparing the results of:
/usr/local/libexec/mysqld --verbose --help
(skip the top long part about the description of the variable, but
still good readin) then compare from both your linux and OpenBSD, you
might find interesting things. (:>
And also:
mysqladmin -p variable
and
mysqladmin extended-status
And by doing a real comparison between the three results, will you know
if the setup is really the same. After that, if they are exactly really
the same and you still see big difference between to identical setup
with the two different systems, but running the same version, then may
be there is something else that might be affecting it, like the file
system use, etc.
Just a thought to know if they are really the same...
Any more ideas? Should it be all related to the replication setup and
Disk I/O ?
May be, may be not. I wouldn't say for sure yet. If you really want to
know the exact answer as to why obviously.
I do know that MySQL is a bitch in regards to I/O and VM.
'tis no fun to handle huge files:
- - -bash-3.1$ ls -l /usr/local/mysql/data/*relay*
- - -rw-rw---- 1 _mysql _mysql 197288820 Sep 18 11:45
/usr/local/mysql/data/babelfish45-relay-bin.000032
- - -rw-rw---- 1 _mysql _mysql 31 Sep 18 09:59
/usr/local/mysql/data/babelfish45-relay-bin.index
- - -rw-rw---- 1 _mysql _mysql 72 Sep 18 11:45
/usr/local/mysql/data/relay-log.info
- - -bash-3.1$
Yeah, the relay binlog is _that_ big...
Nothing there out of this world. The limit on my log files is 1Gb and
are process no problem.
Hope this give you some to work with anyway.
If not, at a minimum a few things to think about and to look at if you
really want to get the answer.
Best,
Daniel
