On 7/19/06, Daniel Kinzler <daniel(a)brightbyte.de> wrote:
The replag
paranoia is out of hand...
It would help to have more than one person looking at it.
There is no reason that a select should be
blocking other reads no
matter what the isolation level is.... Mysql had that sort of
behavior with ISAM tables, but it should not exist with innodb..
If this is the case, can we *please* ask mysql to fix their software?
If we find the exact problem, sure.
[snip]
We know whats happening.. the system is IO starved and has been for a
long time. The system sits and thrashes on the disk while only doing
<6mbytes/sec of work. There might be additional complications, but it
is hard to troubleshoot something that is burried under another
problem.
Weither this is a pure hardware issue (lack of IO capacity) or
something that can be partially adressed through tunables or checking
for FS fragmentation (have updates to page and *links fragmented the
disk? I'm not sure. Does mysql support tablespaces? How does inno
store updates? Does solaris behave smartly with interleaved appends to
multiple files?) is not something I'm sure of..
It is clear that replication playback even with no users on the system
is just able to catch up.. in other words, it'sunacceptably slow... so
why all the cycles wasted on user oriented solutions?
As far as read uncommited goes, any time you join one of the updated
tables (page or *links) to another table on a live, you'll risk
getting bogus results because of the referenced rows on the other side
may not yet exist.
In any case, MySQL AB claims that with innodb tables *readers never
block writers*. If thats not true it is a pretty serious problem.