Replication slaves quitting with binlog error
Hi all-- We have one master and 12 slaves replicating from it. Server is 4.0.16 (havn't wanted to take it down to upgrade), the slaves are 4.0.17, all running on RedHat AS. Lately, every few hours one of the machines caughs up this error, and quits replicating: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. It's complaining about a corrupted relay-log, but it's always only one machine at a time. When I run 'mysqlbinlog ' it gives me the following error: ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 396, event_type: 2 ERROR: Could not read entry at offset 60096864 : Error in log format or read error The offending log in the current case is _not_ the last one, so the IO thread is appearently still functional. The only fix I know is to blow away the data on the slaves and start again with a fresh snapshot (including master.info). As far as I know there's no disk problems (all servers less than a year old, and lots of space available). Any thoughts? -Matt- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql as cluster service, failover causes broken replication
Yes, the clients (appearently) read to the end of the previous file, and then sit there, while the server is writing to a new file. I was thinking this had to do with the "unclean" shutdown of MySQL-- perhapps it's something else. -Matt- > Matt Sturtz wrote: >> Hello-- >> >> We're using Red Hat's cluster manager (RH AS 2.1, MySQL 4.0.16 RPM). >> Due >> to a problem within the cluster software that we're working on with Red >> Hat, the cluster fails over from one node to the other sometimes when it >> shouldn't (one node will reboot, services will fail over-- at this point >> we think it's probably related to IO on the shared quorum partitions). >> >> When service is restored some seconds later, the slaves won't start >> replicating from the newly created binary-log, instead continuing to >> read >> from the previous one (IE db-bin.002 is created when MySQL is restarted, >> but the slaves keep reading from the old file, db-bin.001). The only >> fix >> seems to be CHANGE MASTER TO..., which seems somewhat error prone. >> >> Anybody else running MySQL in this type of environment have any words of >> wisdom? Thanks in advance for any info... > > They should keep reading from the old one until they catch up. Do they > fail to > roll over to the next one after finishing the old one? If yes, it would be > a bug. > > -- > Sasha Pachev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql as cluster service, failover causes broken replication
Hello-- We're using Red Hat's cluster manager (RH AS 2.1, MySQL 4.0.16 RPM). Due to a problem within the cluster software that we're working on with Red Hat, the cluster fails over from one node to the other sometimes when it shouldn't (one node will reboot, services will fail over-- at this point we think it's probably related to IO on the shared quorum partitions). When service is restored some seconds later, the slaves won't start replicating from the newly created binary-log, instead continuing to read from the previous one (IE db-bin.002 is created when MySQL is restarted, but the slaves keep reading from the old file, db-bin.001). The only fix seems to be CHANGE MASTER TO..., which seems somewhat error prone. Anybody else running MySQL in this type of environment have any words of wisdom? Thanks in advance for any info... -Matt Sturtz- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication causes high system load
Hello, I posted about this probably a year ago or so... We run a replication slave on each of our frontend web servers (4.0.17 RPM). When a large number of updates happen on the master, it kills the performance of the web servers while getting caught up. With our application, it doesn't matter if the slave is a few (or even more) minutes behind, so I'm willing to sacrafice replication speed for performance. Any way to throttle updates so that, say, the SQL thread will only do a certain number of inserts/updates per second? or maybe any other ideas? -Matt- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB on master, MyISAM on slaves?
Thanks for the quick reply-- > Yes, this is a common strategy, actually. Any tricks to getting the tables converted on the master without the slaves knowing about it (IE how can I do 'alter table' on the master without it being executed on the slaves)? > Transactions are not written to the binary log on the master until they > are committed, so that's already taken care of. (Rolled back transactions > never appear in the binary log.) -Matt- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB on master, MyISAM on slaves?
Hello, We run a master-slave configuration and are considering migrating a few tables to InnoDB to get transaction capability... Question is, can we keep the tables as MyISAM on the slaves to maintain the high-speed accesses? I suppose this would require the SQL not being written to the bin-log until it's committed, and we would need to convert the tables on the master without the slaves knowing about it... Anybody doing anything like this? Or am I simply barking up the wrong tree? -Matt- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very large table with broken index
Hello All-- We have a particularly large MyISAM table on an older MySQL (3.23.56) on an older Linux (RedHat 6.2). The table is always growing (data is never removed, except when we move it and start a new one). Yesterday one of the indexes started acting weird, so I took a look, and the .MYI file was 2.0G. I suspect it hit the 2G file size limit on Linux. We moved the broken table out of the way and started a new one with exactly the same spec. It's now in a "crashed" state... I've been running myisamchk with some optimization options, but the table has almost 7.88M rows. After nearly 24 hours, it's currently working on 3.4M rows, in the first of 5 indexes. Is there a simple way I can drop the indexes and get access to the table? We need to move the last 3 months of data (insert ... select ?) to the new table we started, and then we can 'myisampack' this one and archive it... Or am I stuck waiting for myisamchk to finish? myisamchk --force --fast --update-state -O key_buffer=128M -O sort_buffer=128M -O read_buffer=4M -O write_buffer=4M Any help appreciated... -Matt- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.15 on Red Hat Cluster
Hello, We're building a Red Hat cluster server for MySQL. Are there any gotchas, or any documentation I should read before setting up something like this? For starters, can I make MySQL believe it's proper hostname is that of the service-IP (that IP which is moved along with the service failover to the active node)? This looks important because the bin-log filenames, as well as the GRANT table, seem to use whatever the system returns from hostname(), which is 'cluster0' or 'cluster1' depending on where it's running. Any input appreciated... Thanks in advance... -Matt- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication priority / speed
>>>> Or, alternatly, is there a way to limit the slave thread to only "X" >>>> bin-log transactions per second? >>> >>> There is not. >> >> Any plan to add this feature? I would think it'd be useful... > > Wouldn't it be better to *solve* your problem instead of going > around it? Well, as this is how our software is designed, I'm going at solving it as best I can... The problem, as I see it, is the MySQL slaves consume the system (load average goes very high-- most likely I/O bound as someone else said) when there's a lot of updates to do at once. Therefor, I'm looking to MySQL resources to help solve the problem. >> MyTOP says our key efficiency is 97.35%, with an average of 1.24 q/sec >> (on the master-- most queries are done directly on the slave, with >> only updates happening on the master). We've optimized things as best >> we can. > > 1.24 q/sec doesn't sound loaded at all. Nope, it's not loaded up much at all, except when somebody makes a huge change or bulk-load (not terrably often, but expected to be more)... > How many queries/second do you see on your slave box? 13.75, according to MyTOP... > How many slow queries per hour? MyTOP says this particular slave has been up 47 days, 2 hours... It's had 56M queries, of those 4,559 were "slow"... Not being a MySQL expert, I'm not sure how to get slow queries/hour directly from MySQL. > You say you that most queries are done directly on the slave. > Why don't you spread the SELECTs across both boxes? Do you mean using a load balancer accross all the slaves? That would defeat the purpose of having a local slave on each web server-- that purpose being to return results as quickly as possible (network traffic is expensive compaired to local disk)... >> The problem is our customers are allowed to bulk-load keywords into >> our database, which causes about 4 large tables to be updated quite a >> bit. Whenever this happens, the slaves struggle to get caught back >> up... > > Have you tried enabling DELAY_KEY_WRITE on the 4 tables that > your bulk-loader updates? I'm using DELAY_KEY_WRITE on my slaves... I didn't know it, but I just looked and it says "ON" in 'show variables';... When looking through the docs on this, I also found "low_priority_updates", which I could set on the slaves-- would this help? > Why not spread the bulk-load in time, so that the keywords aren't > added instantly? We're looking into this now, as well-- some type of log that would keep track of the large updates, and insert/update/delete them later, a few at a time. The problem is we lose the real-time notification of success/failure... > Why not modify your software so that it doesn't read from the > slave while your bulk-loader runs? The bulk load can happen on any web server, how would it notify the others? Where would they query instead? > Can you give some more information on your master & slave config? > (hardware, OS, MySQL show variables, show status) RedHat 6.2 on i386, stock RPM install of MySQL-3.23.36-1... The only options we set up on the slaves are to connect to the master-- everything else is stock... Thanks again for the help, -Matt Sturtz- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication priority / speed
Hello, Jeremy, et al-- Thanks for the reply before... Further questions:
>> Is it possible to set either set the priority ('nice') of the Slave
>> thread down so it doesn't do that?
>
> The slave thread only? No, not really. You could nice MySQL when you
> start it up. But I'm not sure how much effect (positive or negative)
> that'd have.
When I run "show [full] processlist", there's an "Id" column, but it
doesn't corrospond with the Unix PID of the process (on OS's that use a
seperate PID for each thread-- like Linux does)... Is there any way
to(easilly) figure out which PID is handling the slave thread, so that I
might re-nice it after it's already been started up?
>> Or, alternatly, is there a way to limit the slave thread to only "X"
>> bin-log transactions per second?
>
> There is not.
Any plan to add this feature? I would think it'd be useful...
> Are your updates already well optimized? If you're doing enough work to
> cause noticeable speed problems, I'd double-check that if you
> haven't already.
MyTOP says our key efficiency is 97.35%, with an average of 1.24 q/sec (on
the master-- most queries are done directly on the slave, with only
updates happening on the master). We've optimized things as best we can.
The problem is our customers are allowed to bulk-load keywords into our
database, which causes about 4 large tables to be updated quite a bit.
Whenever this happens, the slaves struggle to get caught back up...
Thanks again,
-Matt Sturtz-
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication priority / speed
Hello,
We run several frontend servers (Linux/Apache/PHP) behind a load balancer.
Each frontend is also a MySQL slave, and all queries are done locally
(all changes go directly to the master). The problem is, whenever
somebody makes a lot of changes to the database at once (deletes, updates,
or adds a lot of rows), the slave thread loads up the server to the point
where Apache doesn't get much CPU anymore, and then our sites slow way
down...
Is it possible to set either set the priority ('nice') of the Slave thread
down so it doesn't do that? Or, alternatly, is there a way to limit the
slave thread to only "X" bin-log transactions per second? We don't much
care if the frondends aren't updated at exactly the same second (or even
the same minute, just so we have the appearance of real-time), so we'd
prefer to slow down the slave process in an effort to keep Apache fast...
Thanks for any advice,
-Matt Sturtz-
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
