On 7/19/06, Daniel Kinzler daniel@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.