Re: High cpu usage

2018-10-26 Thread shawn l.green

Hello Machiel,

(I am guessing you can only process top-posts?)

When you stop only the IO thread, you may leave the last event recorded 
to the Relay Log incomplete.


When it gets to that part of the Relay Log, the SQL thread may only be 
part-way through a transaction. It will keep that transaction alive 
waiting for the IO thread to finish downloading the rest of the event 
from the master's copy of the binary log. That partially-complete 
transaction is most likely blocking the ability of your other commands 
to operate more efficiently for several reasons:

 * MVCC
 * InnoDB history length
 * Incomplete transactions to secondary indexes forcing those commands 
to scan the table instead of using the index (related to MVCC)



We made the SQL thread wait so that intermittent networks (a real thing 
years ago) would not "break" replication. We would wait for connectivity 
to resume so that replication could continue.


A safer plan is to stop both threads at the same time. Just use the 
basic STOP SLAVE command instead of the more specific STOP SLAVE IO_THREAD.


Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.




On 10/26/2018 2:09 AM, Machiel Richards wrote:

Hi Shawn


Thank you for the response.


 In order to pause the slave , the stop the sql_io_thread, and to unpause 
they simply start the thread.


  I have run "show engine innodb status" yes and the threads show 90% as 
sleeping and then a few selects , all from the same table as it does a lot of 
authentications for dial outs.


 I will have a look at the results from SELECT * FROM 
information_schema.INNODB_TRX; during the day as we get this issue regularly 
and will provide feedback.



Regards





(earlier thread below... )




From: shawn l.green 
Sent: Thursday, 25 October 2018 9:54:10 PM
To: mysql@lists.mysql.com
Subject: Re: High cpu usage

Hello Machiel,

On 10/25/2018 6:09 AM, Machiel Richards wrote:

Good day all


 Hoping this mail finds you well.


 I am hoping someone can perhaps give us some guidance here as we now seem 
to be stuck on a problem and have not been able to find a solution after more 
than a month.


 We are running an opensips server on Centos 6.5 , using mysql 5.7.13 which 
was installed via Tarball file.


 The server is setup as a slave and master and receives updates from 
opensips config nodes as well as registrations from workers.


 Replication is paused during the day and forward replication (master) is 
disabled at the moment.


 However , we are getting an issue every day on mysql side in terms of 
mysql pushing up server load.



  During the day the server is running fine with a load avg not going above 
1.5 during peak times.


  However in the evening , replication is unpaused, and completes 
processing and catchup within about 15 minutes and is paused again about 30 
minutes after the unpause.



Give or take 45 minutes to an hour after the replication is paused 
again, mysql starts to cause high cpu usage with no apparent processes running 
as can be seen on full processlist (maybe one or two selects which completes 
fairly quickly)


 The higher load, causes queries to slow down however and opensips to 
start timing out on db connections, causing clients to resubmit.


   The resubmits , then obviously causes even more load spiking the mysql 
load to increase as well as the server load and eventually opensips kills 
itself.



  I have looked at the disks, iowaits, memory usage, all is fine.


  We do not see any strange queries or stick queries, no deadlocks, etc... 
only the increase in selects after mysql starts to push up the cpu load.



  We have added all indexes we can find, but even this has made no 
difference at all.


   Currently we are at a loss so I am hoping someone else can assist in 
explaining how else we can find out why mysql is eating up the cpu ...



 The same behaviour can also be seen the moment any new feature is added to 
the server that requires mysql processing to be done, so this does not seem to 
be specifically related to replication, however it does seem like the current 
load from replication causes mysql to act up.


the server is currently running on SSD (recently replaced) , and 8Gb of 
memory with 1 x quadcore CPU.



  should any more info be required, please feel free to ask.




When you say pause replication, what command are you executing on the
slave?

Which end of the system is experiencing the high CPU usage: the master
or the slave?

Have you checked these resources to see what the InnoDB main or
background threads are doing when your CPU starts to spike? (you could
be in a massive rollback)

SHOW ENG

Re: High cpu usage

2018-10-26 Thread Machiel Richards
Hi Shawn


   Thank you for the response.


In order to pause the slave , the stop the sql_io_thread, and to unpause 
they simply start the thread.


 I have run "show engine innodb status" yes and the threads show 90% as 
sleeping and then a few selects , all from the same table as it does a lot of 
authentications for dial outs.


I will have a look at the results from SELECT * FROM 
information_schema.INNODB_TRX; during the day as we get this issue regularly 
and will provide feedback.



Regards



From: shawn l.green 
Sent: Thursday, 25 October 2018 9:54:10 PM
To: mysql@lists.mysql.com
Subject: Re: High cpu usage

Hello Machiel,

On 10/25/2018 6:09 AM, Machiel Richards wrote:
> Good day all
>
>
> Hoping this mail finds you well.
>
>
> I am hoping someone can perhaps give us some guidance here as we now seem 
> to be stuck on a problem and have not been able to find a solution after more 
> than a month.
>
>
> We are running an opensips server on Centos 6.5 , using mysql 5.7.13 
> which was installed via Tarball file.
>
>
> The server is setup as a slave and master and receives updates from 
> opensips config nodes as well as registrations from workers.
>
>
> Replication is paused during the day and forward replication (master) is 
> disabled at the moment.
>
>
> However , we are getting an issue every day on mysql side in terms of 
> mysql pushing up server load.
>
>
>
>  During the day the server is running fine with a load avg not going 
> above 1.5 during peak times.
>
>
>  However in the evening , replication is unpaused, and completes 
> processing and catchup within about 15 minutes and is paused again about 30 
> minutes after the unpause.
>
>
>
>Give or take 45 minutes to an hour after the replication is paused 
> again, mysql starts to cause high cpu usage with no apparent processes 
> running as can be seen on full processlist (maybe one or two selects which 
> completes fairly quickly)
>
>
> The higher load, causes queries to slow down however and opensips to 
> start timing out on db connections, causing clients to resubmit.
>
>
>   The resubmits , then obviously causes even more load spiking the mysql 
> load to increase as well as the server load and eventually opensips kills 
> itself.
>
>
>
>  I have looked at the disks, iowaits, memory usage, all is fine.
>
>
>  We do not see any strange queries or stick queries, no deadlocks, etc... 
> only the increase in selects after mysql starts to push up the cpu load.
>
>
>
>  We have added all indexes we can find, but even this has made no 
> difference at all.
>
>
>   Currently we are at a loss so I am hoping someone else can assist in 
> explaining how else we can find out why mysql is eating up the cpu ...
>
>
>
> The same behaviour can also be seen the moment any new feature is added 
> to the server that requires mysql processing to be done, so this does not 
> seem to be specifically related to replication, however it does seem like the 
> current load from replication causes mysql to act up.
>
>
>the server is currently running on SSD (recently replaced) , and 8Gb of 
> memory with 1 x quadcore CPU.
>
>
>
>  should any more info be required, please feel free to ask.
>
>

When you say pause replication, what command are you executing on the
slave?

Which end of the system is experiencing the high CPU usage: the master
or the slave?

Have you checked these resources to see what the InnoDB main or
background threads are doing when your CPU starts to spike? (you could
be in a massive rollback)

SHOW ENGINE INNODB STATUS
SELECT * FROM information_schema.INNODB_TRX;

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc.

Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.


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



Re: High cpu usage

2018-10-25 Thread shawn l.green

Hello Machiel,

On 10/25/2018 6:09 AM, Machiel Richards wrote:

Good day all


Hoping this mail finds you well.


I am hoping someone can perhaps give us some guidance here as we now seem 
to be stuck on a problem and have not been able to find a solution after more 
than a month.


We are running an opensips server on Centos 6.5 , using mysql 5.7.13 which 
was installed via Tarball file.


The server is setup as a slave and master and receives updates from 
opensips config nodes as well as registrations from workers.


Replication is paused during the day and forward replication (master) is 
disabled at the moment.


However , we are getting an issue every day on mysql side in terms of mysql 
pushing up server load.



 During the day the server is running fine with a load avg not going above 
1.5 during peak times.


 However in the evening , replication is unpaused, and completes processing 
and catchup within about 15 minutes and is paused again about 30 minutes after 
the unpause.



   Give or take 45 minutes to an hour after the replication is paused 
again, mysql starts to cause high cpu usage with no apparent processes running 
as can be seen on full processlist (maybe one or two selects which completes 
fairly quickly)


The higher load, causes queries to slow down however and opensips to 
start timing out on db connections, causing clients to resubmit.


  The resubmits , then obviously causes even more load spiking the mysql 
load to increase as well as the server load and eventually opensips kills 
itself.



 I have looked at the disks, iowaits, memory usage, all is fine.


 We do not see any strange queries or stick queries, no deadlocks, etc... 
only the increase in selects after mysql starts to push up the cpu load.



 We have added all indexes we can find, but even this has made no 
difference at all.


  Currently we are at a loss so I am hoping someone else can assist in 
explaining how else we can find out why mysql is eating up the cpu ...



The same behaviour can also be seen the moment any new feature is added to 
the server that requires mysql processing to be done, so this does not seem to 
be specifically related to replication, however it does seem like the current 
load from replication causes mysql to act up.


   the server is currently running on SSD (recently replaced) , and 8Gb of 
memory with 1 x quadcore CPU.



 should any more info be required, please feel free to ask.




When you say pause replication, what command are you executing on the 
slave?


Which end of the system is experiencing the high CPU usage: the master 
or the slave?


Have you checked these resources to see what the InnoDB main or 
background threads are doing when your CPU starts to spike? (you could 
be in a massive rollback)


SHOW ENGINE INNODB STATUS
SELECT * FROM information_schema.INNODB_TRX;

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc.

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.



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