MySQL Slave is almost 1 day behind

Good day :)

We've had our master/slave server running for a while now, and just
yesterday, we started getting behind.
Not entirely sure what happened, but it is getting further and furhter
behind.

(master server)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000280
Position: 58090245
Binlog_Do_DB: admin_server,baf,freeradius,radius
Binlog_Ignore_DB:
1 row in set (0.00 sec)


(slave server)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.7.101
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000280
Read_Master_Log_Pos: 55208258
Relay_Log_File: backup-relay-bin.000530
Relay_Log_Pos: 96663109
Relay_Master_Log_File: mysql-bin.000259
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: admin_server,baf,freeradius,radius
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 96662972
Relay_Log_Space: 2211376614
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 77473
1 row in set (0.00 sec)

Now, we are logging the freeradius packets into mysql, and like I said, it
has been running fine, up until yesterday. Any idea how the slave would
get this far behind, and not be generating any errors?

It is my understanding, that the slave only does update/insert/delete
queries, so even if there was a lot of "select" queries on the master, the
slave wouldn't see them. We are not running any queries on the slave (it
was set up for backup purposes, so we could stop the slave and backup
completely), and we haven't done a backup on the slave in a couple of days
(yeah, i know... bad bad) so there is really no reason for this.

Can anyone help/assist/point me in the right direction to figure out how to
catch the slave back up to the master? The master is not being overloaded,
it is keeping up no problem, and the backup server is 8x the server than the
application server, so it shoulnd't even be an i/o or cpu issue.

Please help! :)


Thanks in advance
Steven Staples


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL Slave is almost 1 day behind

--001636833a444284a70482b55b6c
Content-Type: text/plain; charset=ISO-8859-1

A few things to keep in mind:

1: the master may have several threads feeding into the binlog at a time,
but a slave only executes in a single thread. Are you throwing more stuff
at the slave in multiple mysql threads?

2: is there something else going on with the slave box? some big backup or
gzip or something that would chew up cycles? any big mysql query or update
going on?

3: have you checked the disks on your slave. Whenever I notice a slave
falling behind for an extended period of time, I ask the sys admins to check
the disk drives - if you're using some kind of RAID, they can become
degraded.

4: you might also check the slave's mysql error log to see if there's any
hint there.





On Fri, Mar 26, 2010 at 9:45 AM, Steven Staples wrote:

> Good day :)
>
> We've had our master/slave server running for a while now, and just
> yesterday, we started getting behind.
> Not entirely sure what happened, but it is getting further and furhter
> behind.
>
> (master server)
> mysql> show master status\G
> *************************** 1. row ***************************
> File: mysql-bin.000280
> Position: 58090245
> Binlog_Do_DB: admin_server,baf,freeradius,radius
> Binlog_Ignore_DB:
> 1 row in set (0.00 sec)
>
>
> (slave server)
> mysql> show slave status\G
> *************************** 1. row ***************************
> Slave_IO_State: Waiting for master to send event
> Master_Host: 192.168.7.101
> Master_User: slave_user
> Master_Port: 3306
> Connect_Retry: 60
> Master_Log_File: mysql-bin.000280
> Read_Master_Log_Pos: 55208258
> Relay_Log_File: backup-relay-bin.000530
> Relay_Log_Pos: 96663109
> Relay_Master_Log_File: mysql-bin.000259
> Slave_IO_Running: Yes
> Slave_SQL_Running: Yes
> Replicate_Do_DB: admin_server,baf,freeradius,radius
> Replicate_Ignore_DB:
> Replicate_Do_Table:
> Replicate_Ignore_Table:
> Replicate_Wild_Do_Table:
> Replicate_Wild_Ignore_Table:
> Last_Errno: 0
> Last_Error:
> Skip_Counter: 0
> Exec_Master_Log_Pos: 96662972
> Relay_Log_Space: 2211376614
> Until_Condition: None
> Until_Log_File:
> Until_Log_Pos: 0
> Master_SSL_Allowed: No
> Master_SSL_CA_File:
> Master_SSL_CA_Path:
> Master_SSL_Cert:
> Master_SSL_Cipher:
> Master_SSL_Key:
> Seconds_Behind_Master: 77473
> 1 row in set (0.00 sec)
>
> Now, we are logging the freeradius packets into mysql, and like I said, it
> has been running fine, up until yesterday. Any idea how the slave would
> get this far behind, and not be generating any errors?
>
> It is my understanding, that the slave only does update/insert/delete
> queries, so even if there was a lot of "select" queries on the master, the
> slave wouldn't see them. We are not running any queries on the slave (it
> was set up for backup purposes, so we could stop the slave and backup
> completely), and we haven't done a backup on the slave in a couple of days
> (yeah, i know... bad bad) so there is really no reason for this.
>
> Can anyone help/assist/point me in the right direction to figure out how to
> catch the slave back up to the master? The master is not being overloaded,
> it is keeping up no problem, and the backup server is 8x the server than
> the
> application server, so it shoulnd't even be an i/o or cpu issue.
>
> Please help! :)
>
>
> Thanks in advance
> Steven Staples
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4435@gmail.com
>
>


--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

--001636833a444284a70482b55b6c--

Re: MySQL Slave is almost 1 day behind

--000e0cd32ed8e2b8840482b68af2
Content-Type: text/plain; charset=ISO-8859-1

Also,
run "SHOW PROCESSLIST", to see what sql's are running on slave.
This will give u any idea if any huge dml was run on master and its getting
sync'd with slave.

regards
anandkl

On Fri, Mar 26, 2010 at 8:25 PM, Jim Lyons wrote:

> A few things to keep in mind:
>
> 1: the master may have several threads feeding into the binlog at a time,
> but a slave only executes in a single thread. Are you throwing more stuff
> at the slave in multiple mysql threads?
>
> 2: is there something else going on with the slave box? some big backup or
> gzip or something that would chew up cycles? any big mysql query or update
> going on?
>
> 3: have you checked the disks on your slave. Whenever I notice a slave
> falling behind for an extended period of time, I ask the sys admins to
> check
> the disk drives - if you're using some kind of RAID, they can become
> degraded.
>
> 4: you might also check the slave's mysql error log to see if there's any
> hint there.
>
>
>
>
>
> On Fri, Mar 26, 2010 at 9:45 AM, Steven Staples wrote:
>
> > Good day :)
> >
> > We've had our master/slave server running for a while now, and just
> > yesterday, we started getting behind.
> > Not entirely sure what happened, but it is getting further and furhter
> > behind.
> >
> > (master server)
> > mysql> show master status\G
> > *************************** 1. row ***************************
> > File: mysql-bin.000280
> > Position: 58090245
> > Binlog_Do_DB: admin_server,baf,freeradius,radius
> > Binlog_Ignore_DB:
> > 1 row in set (0.00 sec)
> >
> >
> > (slave server)
> > mysql> show slave status\G
> > *************************** 1. row ***************************
> > Slave_IO_State: Waiting for master to send event
> > Master_Host: 192.168.7.101
> > Master_User: slave_user
> > Master_Port: 3306
> > Connect_Retry: 60
> > Master_Log_File: mysql-bin.000280
> > Read_Master_Log_Pos: 55208258
> > Relay_Log_File: backup-relay-bin.000530
> > Relay_Log_Pos: 96663109
> > Relay_Master_Log_File: mysql-bin.000259
> > Slave_IO_Running: Yes
> > Slave_SQL_Running: Yes
> > Replicate_Do_DB: admin_server,baf,freeradius,radius
> > Replicate_Ignore_DB:
> > Replicate_Do_Table:
> > Replicate_Ignore_Table:
> > Replicate_Wild_Do_Table:
> > Replicate_Wild_Ignore_Table:
> > Last_Errno: 0
> > Last_Error:
> > Skip_Counter: 0
> > Exec_Master_Log_Pos: 96662972
> > Relay_Log_Space: 2211376614
> > Until_Condition: None
> > Until_Log_File:
> > Until_Log_Pos: 0
> > Master_SSL_Allowed: No
> > Master_SSL_CA_File:
> > Master_SSL_CA_Path:
> > Master_SSL_Cert:
> > Master_SSL_Cipher:
> > Master_SSL_Key:
> > Seconds_Behind_Master: 77473
> > 1 row in set (0.00 sec)
> >
> > Now, we are logging the freeradius packets into mysql, and like I said,
> it
> > has been running fine, up until yesterday. Any idea how the slave would
> > get this far behind, and not be generating any errors?
> >
> > It is my understanding, that the slave only does update/insert/delete
> > queries, so even if there was a lot of "select" queries on the master,
> the
> > slave wouldn't see them. We are not running any queries on the slave (it
> > was set up for backup purposes, so we could stop the slave and backup
> > completely), and we haven't done a backup on the slave in a couple of
> days
> > (yeah, i know... bad bad) so there is really no reason for this.
> >
> > Can anyone help/assist/point me in the right direction to figure out how
> to
> > catch the slave back up to the master? The master is not being
> overloaded,
> > it is keeping up no problem, and the backup server is 8x the server than
> > the
> > application server, so it shoulnd't even be an i/o or cpu issue.
> >
> > Please help! :)
> >
> >
> > Thanks in advance
> > Steven Staples
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=jlyons4435@gmail.com
> >
> >
>
>
> --
> Jim Lyons
> Web developer / Database administrator
> http://www.weblyons.com
>

--000e0cd32ed8e2b8840482b68af2--