K
K
kiranananda2019-08-05 17:49:43
linux
kiranananda, 2019-08-05 17:49:43

Is mysql behind the replica or is the server running very slowly for some reason?

Hello! Some strange situation. There is mysql (mariadb) master. At the moment, a slave has been raised from it on a virtual machine and everything works well, no lags. We bought a new piece of hardware, which, according to its characteristics, is no worse than the master, raised a second slave on it, and I get a delay of the replica by an average of 1-3 hours. The configs are the same, nothing new... This supermicroserver, at the beginning, came across a trouble that the processors were working in energy-saving mode, fixed it. Maybe they have some other restrictions muddied there, I don’t understand.
But the load on interrupts is strangely large, although the amount of write data is very small.
atop

PRC | sys    5.00s | user  26.52s | #proc    575 | #trun      6 |  #tslpi  1071 | #tslpu     0 | #zombie    0 | clones     7 | #exit      0 |
CPU | sys      37% | user    218% | irq      21% | idle   3693% |  wait     34% | steal     0% | guest     0% | curf 2.15GHz | curscal  97% |
CPL | avg1    3.95 | avg5    3.72 | avg15   3.73 |              |  csw   360872 |              | intr  364166 |              | numcpu    40 |
MEM | tot   125.8G | free   49.5G | cache  61.5G | buff    1.3G |  slab    1.5G | shmem  99.5M | vmbal   0.0M | hptot   0.0M | hpuse   0.0M |
SWP | tot     8.0G | free    7.7G |              |              |               |              |              | vmcom  55.1G | vmlim  70.9G |
DSK |          sda | busy     96% | read      17 | write   4984 |  KiB/r     20 | KiB/w     12 | MBr/s    0.0 | MBw/s    4.9 | avio 2.33 ms |
NFS | rpc        1 | cread      0 | cwrit      0 | MBcr/s   0.0 |  MBcw/s   0.0 | nettcp     1 | netudp     0 | badaut     0 | badcln     0 |
NET | transport    | tcpi  195613 | tcpo  252297 | udpi      53 |  udpo      67 | tcpao   3723 | tcppo   3136 | tcprs    217 | udpie      0 |
NET | network      | ipi   195667 | ipo   206104 | ipfrw      0 |  deliv 195667 |              |              | icmpi      0 | icmpo      0 |
NET | eno1     11% | pcki  225328 | pcko  206800 | sp 1000 Mbps |  si  119 Mbps | so   68 Mbps | erri       0 | erro       0 | drpo       0 |
NET | lo      ---- | pcki   45782 | pcko   45782 | sp    0 Mbps |  si 9524 Kbps | so 9524 Kbps | erri       0 | erro       0 | drpo       0 |

  PID                 TID                 RDDSK                 WRDSK                WCANCL                 DSK                CMD        1/6
 1640                   -                  352K                33076K                  388K                100%                mysqld
  692                   -                    0K                   24K                    0K                  0%                jbd2/sda2-8
20006                   -                    0K                    8K                    0K                  0%                apache2

iotop
Total DISK READ :      30.16 K/s | Total DISK WRITE :       3.75 M/s
Actual DISK READ:      30.16 K/s | Actual DISK WRITE:       5.84 M/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                       
 1706 be/4 mysql       0.00 B/s  644.67 K/s  0.00 % 93.35 % mysqld
 1693 be/4 mysql      30.16 K/s    3.77 K/s  0.00 %  2.31 % mysqld
  692 be/3 root        0.00 B/s    0.00 B/s  0.00 %  1.54 % [jbd2/sda2-8]
 1697 be/4 mysql       0.00 B/s    3.06 M/s  0.00 %  0.95 % mysqld
 1676 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  0.80 % mysqld
 1705 be/4 mysql       0.00 B/s   52.78 K/s  0.00 %  0.01 % mysqld
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init maybe-ubiquity
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
    4 be/0 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0H]

Installed 4 SSD hard drives and megaraid controller. 10 raids.

Answer the question

In order to leave comments, you need to log in

3 answer(s)
K
kiranananda, 2019-08-08
@kiranananda

All decided. The problem was the slow write speed to the disk. Demolished linux, rebuilt the raid, installed debian 10 this time and everything was fine :).
Many thanks to everyone who helped!

P
podguzovvasily, 2019-08-06
@podguzovvasily

Here, consul, nomad. There is an excellent solution Master-Master Real Time.

V
Victor Taran, 2019-08-06
@shambler81

Statements
1. on your server, the clock frequency per processor is not lower than on the old one. (I don’t need about the number)
2. your master and slave are located in the same data center, and are connected well either by one shrunka or special routing.
3. When accessing the database, you write the domain name instead of localhost
4. iotop - oka in 10 minutes it did not rise above 60% iO
5. you have NvME, I hope in 19 you do not use something else.
6. Does the size of your database correspond to the settings in my.conf and do all tables get into the cache well, etc.?
7. You know for sure that a socket connection is faster than usual
9. You understand that localhost and 83.32.113. 32 work on a different algorithm.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question