RE: io thread very slow copying binlogs

[Michael Dykman]

If you are confident that it's not the network, that pretty much leaves RAM or 
disk as the source of your problem. Assuming that you are seeing no performance 
issues on the master (I expect you would have mentioned), let's focus on the 
slave.

The behaviour you describe sounds to me like you have entered swap space: 
everythingthing is working fine, just in incredibly slow motion.
What does vmstat report?  Try someithng like 
  $ vmstat 2 60
and see if any swap traffic can be observed?

How about cat /proc//status ? What does VmSwap suggest?

 - michael dykman

-Original Message-
From: Brad Barnett [mailto:mysql-general-l...@l8r.net] 
Sent: Friday, January 20, 2017 10:45 AM
To: mysql@lists.mysql.com
Subject: Re: io thread very slow copying binlogs



Hey Morgan,

Thanks for the tip.  Might come in handy.

But, I'm positive it's not a disconnect / reconnect thing.  Or, at least not 
one affected by that timeout.

I can do a watch ls -lh in the binlog dir, and see the relay log increasing in 
size by a M every 4 or 5 seconds or so.  About 200kbyte/sec / 1.6Mbit/sec right 
now.

It seems very steady too.  As in, if I look at bytes, they're constantly 
increasing.. just, slow..

On Fri, 20 Jan 2017 10:19:57 -0500
"Morgan Tocker" <morgan.toc...@oracle.com> wrote:

> Hi Brad,
> 
> > MySQL community edition 5.6.29, running Linux.
> > 
> > Binlogs never seem to get caught up on slaves.
> > 
> > I've done all I can, to validate that this isn't network or disk 
> > related.
> > 
> > Disk tests (using iostat and other methods) show lots of bandwidth 
> > left on the slave and master.
> > 
> > Network tests, such as:
> > 
> > - using scp to copy binlogs directly
> > - using different NICs to copy binlogs
> > - using mysqlbinlog to snag logs (the most 'real' way I can think to
> >   simulate the replication thread copying binlogs from the master)
> > 
> > All seem to show that network speed is blazingly fast.
> > 
> > Yet, MySQL is barely getting 4mbit/sec across the network, and onto 
> > the
> disk.
> > And that's on a good day.
> > 
> > Any immediate suggestions here?  This seems very weird, and SQL 
> > thread is constantly running out of stuff to process.
> 
> Networking is not my strong-suit, but I have a suggestion:
> 
> Try lowering slave-net-timeout
> http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#
> option
> _mysqld_slave-net-timeout
> 
> I remember that we lowered the default in MySQL 5.7 (from 1hr to 60
> seconds) so that the connection between master/slave would be 
> considered broken faster.  If you have the throughput on a graph it 
> might better explain if it is a constant 4mbit/sec or more broken.
> 
> 
> - Morgan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: io thread very slow copying binlogs

[Brad Barnett]

Hey Morgan,

Thanks for the tip.  Might come in handy.

But, I'm positive it's not a disconnect / reconnect thing.  Or, at least
not one affected by that timeout.

I can do a watch ls -lh in the binlog dir, and see the relay log
increasing in size by a M every 4 or 5 seconds or so.  About
200kbyte/sec / 1.6Mbit/sec right now.

It seems very steady too.  As in, if I look at bytes, they're constantly
increasing.. just, slow..

On Fri, 20 Jan 2017 10:19:57 -0500
"Morgan Tocker"  wrote:

> Hi Brad,
> 
> > MySQL community edition 5.6.29, running Linux.
> > 
> > Binlogs never seem to get caught up on slaves.
> > 
> > I've done all I can, to validate that this isn't network or disk
> > related.
> > 
> > Disk tests (using iostat and other methods) show lots of bandwidth
> > left on the slave and master.
> > 
> > Network tests, such as:
> > 
> > - using scp to copy binlogs directly
> > - using different NICs to copy binlogs
> > - using mysqlbinlog to snag logs (the most 'real' way I can think to
> >   simulate the replication thread copying binlogs from the master)
> > 
> > All seem to show that network speed is blazingly fast.
> > 
> > Yet, MySQL is barely getting 4mbit/sec across the network, and onto
> > the
> disk.
> > And that's on a good day.
> > 
> > Any immediate suggestions here?  This seems very weird, and SQL
> > thread is constantly running out of stuff to process.
> 
> Networking is not my strong-suit, but I have a suggestion:
> 
> Try lowering slave-net-timeout
> http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option
> _mysqld_slave-net-timeout
> 
> I remember that we lowered the default in MySQL 5.7 (from 1hr to 60
> seconds) so that the connection between master/slave would be
> considered broken faster.  If you have the throughput on a graph it
> might better explain if it is a constant 4mbit/sec or more broken.
> 
> 
> - Morgan

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: io thread very slow copying binlogs

[Brad Barnett]

I should add that I've turned the SQL thread off, it makes no difference
from what I can see...

On Fri, 20 Jan 2017 10:31:38 -0500
Brad Barnett  wrote:

> 
> 
> 
> On Fri, 20 Jan 2017 10:30:09 -0500
> Brad Barnett  wrote:
> 
> > 
> > 
> > Hey Morgan,
> > 
> > Thanks for the tip.  Might come in handy.
> > 
> > But, I'm positive it's not a disconnect / reconnect thing.  Or, at
> > least not one affected by that timeout.
> > 
> > I can do a watch ls -lh in the binlog dir, and see the relay log
> > increasing in size by a M every 4 or 5 seconds or so.  About
> > 200kbyte/sec / 1.6Mbit/sec right now.
> > 
> > It seems very steady too.  As in, if I look at bytes, they're
> > constantly increasing.. just, slow..
> > 
> > On Fri, 20 Jan 2017 10:19:57 -0500
> > "Morgan Tocker"  wrote:
> > 
> > > Hi Brad,
> > > 
> > > > MySQL community edition 5.6.29, running Linux.
> > > > 
> > > > Binlogs never seem to get caught up on slaves.
> > > > 
> > > > I've done all I can, to validate that this isn't network or disk
> > > > related.
> > > > 
> > > > Disk tests (using iostat and other methods) show lots of bandwidth
> > > > left on the slave and master.
> > > > 
> > > > Network tests, such as:
> > > > 
> > > > - using scp to copy binlogs directly
> > > > - using different NICs to copy binlogs
> > > > - using mysqlbinlog to snag logs (the most 'real' way I can think
> > > > to simulate the replication thread copying binlogs from the
> > > > master)
> > > > 
> > > > All seem to show that network speed is blazingly fast.
> > > > 
> > > > Yet, MySQL is barely getting 4mbit/sec across the network, and
> > > > onto the
> > > disk.
> > > > And that's on a good day.
> > > > 
> > > > Any immediate suggestions here?  This seems very weird, and SQL
> > > > thread is constantly running out of stuff to process.
> > > 
> > > Networking is not my strong-suit, but I have a suggestion:
> > > 
> > > Try lowering slave-net-timeout
> > > http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option
> > > _mysqld_slave-net-timeout
> > > 
> > > I remember that we lowered the default in MySQL 5.7 (from 1hr to 60
> > > seconds) so that the connection between master/slave would be
> > > considered broken faster.  If you have the throughput on a graph it
> > > might better explain if it is a constant 4mbit/sec or more broken.
> > > 
> > > 
> > > - Morgan

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: io thread very slow copying binlogs

[Morgan Tocker]

Hi Brad,

> MySQL community edition 5.6.29, running Linux.
> 
> Binlogs never seem to get caught up on slaves.
> 
> I've done all I can, to validate that this isn't network or disk related.
> 
> Disk tests (using iostat and other methods) show lots of bandwidth left on
> the slave and master.
> 
> Network tests, such as:
> 
> - using scp to copy binlogs directly
> - using different NICs to copy binlogs
> - using mysqlbinlog to snag logs (the most 'real' way I can think to
>   simulate the replication thread copying binlogs from the master)
> 
> All seem to show that network speed is blazingly fast.
> 
> Yet, MySQL is barely getting 4mbit/sec across the network, and onto the
disk.
> And that's on a good day.
> 
> Any immediate suggestions here?  This seems very weird, and SQL thread is
> constantly running out of stuff to process.

Networking is not my strong-suit, but I have a suggestion:

Try lowering slave-net-timeout
http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option
_mysqld_slave-net-timeout

I remember that we lowered the default in MySQL 5.7 (from 1hr to 60 seconds)
so that the connection between master/slave would be considered broken
faster.  If you have the throughput on a graph it might better explain if it
is a constant 4mbit/sec or more broken.


- Morgan


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql