performance – Why MySQL 5.7 replication is verry slow on my new server

I have a problem with MySQL replication and I wanted to find advice or information on it.

Let me show you the infrastructure.

Master: This is our main MySQL server. I want to replace it by Slave B.

Slave A: This is our replication server connected to the Master. There are no issues with this server.

Slave B: This is our MySQL server that will replace the Master. For the moment this server is configured as replication to prepare the transfer.

Goal:
I have two MySQL Servers (Master / Slave) that work well. I want to replace our Master server with a new more efficient server (Slave B). To do this, we have set up a second Slave B replication to prepare for the transfer, but I have a problem with it. I tried to investigate the problem but I am not an expert on MySQL and I would like to ask for help to find the problem.

PS: I installed the Percona Monitoring Management and sysbench tools on Slave B. So if needed I can easily provide information from these tools to complete my post.

Hardware detail of the machines:

Master:

HOST
Host OS Xenserver
CPU Intel(R) Xeon(R) CPU D-1541 @ 2.10GHz
RAM on Host 128 Go
Space on Host RAID 10 SSD 4×744,687Go (1,454 TB)
VM (MySQL)
VM OS Ubuntu 16.04
RAM on VM 120 Go
Space on VM 1,3TB

SLAVE A

HOST
Host OS Xenserver
CPU Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz
RAM on Host 128 Go
Space on Host RAID 1 HDD (2×2,728TB) 2,728TB
VM (MySQL)
VM OS Ubuntu 16.04
RAM on VM 60 Go
Space on VM 1,7TB

SLAVE B

HOST (MySQL)
Host OS Ubuntu 18.04
CPU 2x Intel(R) Xeon(R) Silver 4214R CPU @ 2.40GHz
RAM on Host 192 Go
Space on Host RAID 10 SSD (4×3,492TB) 6,984TB

https://versus.com/en/intel-xeon-e5-2650-vs-intel-xeon-silver-4214r

Observation of delays in replication:

Master:

Master work on the file mysql-bin.024080

show master statusG
*************************** 1. row ***************************
             File: mysql-bin.024080
         Position: 62186738

Slave B:

Slave B has all files (mysql-bin.024080) and it works on the file mysql-bin.024075. The gap between these files increases over time. Currently SQL_THREAD is behind 5 binary logs.

show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.55.55.21
                  Master_User: slave_replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.024080
          Read_Master_Log_Pos: 60111446
               Relay_Log_File: mysql-relay-bin.002952
                Relay_Log_Pos: 154669142
        Relay_Master_Log_File: mysql-bin.024075
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 154668929
              Relay_Log_Space: 2631436265
              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: 68158
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 195390
                  Master_UUID: 361633ab-6bb7-11e7-829c-1a76636a64a1
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

Slave A:

Slave A is up to date.

show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.55.55.21
                  Master_User: slave_replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.024080
          Read_Master_Log_Pos: 88327716
               Relay_Log_File: mysql-relay-bin.417046
                Relay_Log_Pos: 88327835
        Relay_Master_Log_File: mysql-bin.024080
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 88327716
              Relay_Log_Space: 88328255
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 195390
                  Master_UUID: 361633ab-6bb7-11e7-829c-1a76636a64a1
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

There are 3 ways that I identified

1: MySQL configuration problem
The many parameters of MySQL can influence server performance hence the importance of optimizing MySQL.
When I vary the sync_binlog = 0 and innodb_flush_log_at_trx_commit = 2 parameters the server performance improves greatly and allows the replication of Slave B to catch up. However, I want to leave sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1.
Do you know any other parameter that could influence the slowness of replication?

2: Single-threaded CPU problem
MySQL performs replication on a single process so only one CPU is engaged.
As the CPU of Slave B (2.40GHz) is slower than that of Slave A (2.60GHz).
The MySQL replication process on Slave A doesn’t have any problem with the workload whereas Slave B can’t support it.
How can I verify this ?

3: Hardware problem
A hardware problem can be considered. How can I find the bottleneck ?

kernel – ata1: link is slow to respond, please be patient err

I am new to this group. I am working with an embedded system with linux kernel version 2.6.32 (MontaVista Linux G++ 4.4-111220) ) on cavium processor. I have a boot CF card, removable CF and an SD card interface. I see after cold reboot the boot CF card is not able to detect by the kernel (not always). The kernel logs shows below warnings and errors during this problematic condition. In the good case I am not seeing the below logs from kernel. Can someone help send some clue or root cause. The Boot CF card seems to be working. Is this issue between kernel and the driver interface ?

2020 Nov 11 13:19:26+00:00 wglng-10204 kernel (warning) ata1: device not ready (errno=-16), forcing hardreset
2020 Nov 11 13:19:26+00:00 wglng-10204 kernel (warning) (ready=0)
2020 Nov 11 13:19:26+00:00 wglng-10204 kernel (err) ata1: SRST failed (errno=-16)
2020 Nov 11 13:19:26+00:00 wglng-10204 kernel (warning) ata1: link is slow to respond, please be patient (ready=0)
2020 Nov 11 13:19:26+00:00 wglng-10204 kernel (err) ata1: SRST failed (errno=-16)
2020 Nov 11 13:19:26+00:00 wglng-10204 kernel (warning) ata1: link is slow to respond, please be patient (ready=0)
2020 Nov 11 13:19:26+00:00 wglng-10204 kernel (err) ata1: SRST failed (errno=-16)
2020 Nov 11 13:19:26+00:00 wglng-10204 kernel (err) ata1: SRST failed (errno=-16)
2020 Nov 11 13:19:26+00:00 wglng-10204 kernel (err) ata1: reset failed, giving up

Here is the logs from a success case.
The ata1.00 logs and ATA CE08TLQVR-FD137- 2013 PQ: 0 ANSI: 5 logs ae missing in failed case.

2021 Jan 22 22:31:36+00:00 wglng-10204 kernel (info) ata1.00: CFA: CE08TLQVR-FD137-D, 20130426, max PIO4
2021 Jan 22 22:31:36+00:00 wglng-10204 kernel (info) ata1.00: 15662304 sectors, multi 0: LBA48
2021 Jan 22 22:31:36+00:00 wglng-10204 kernel (info) ata1.00: configured for PIO4
2021 Jan 22 22:31:36+00:00 wglng-10204 kernel (notice) scsi 0:0:0:0: Direct-Access ATA CE08TLQVR-FD137- 2013 PQ: 0 ANSI: 5
2021 Jan 22 22:31:36+00:00 wglng-10204 kernel (notice) sd 0:0:0:0: Attached scsi generic sg0 type 0
2021 Jan 22 22:31:36+00:00 wglng-10204 kernel (notice) sd 0:0:0:0: (sda) 15662304 512-byte logical blocks: (8.01 GB/7.46 GiB)
2021 Jan 22 22:31:36+00:00 wglng-10204 kernel (notice) sd 0:0:0:0: (sda) Write Protect is off
2021 Jan 22 22:31:36+00:00 wglng-10204 kernel (notice) sd 0:0:0:0: (sda) Write cache: disabled, read cache: enabled, doesn’t support DPO or FUA
2021 Jan 22 22:31:36+00:00 wglng-10204 kernel (info) ehci_hcd 0000:00:02.2: EHCI Host Controller
2021 Jan 22 22:31:36+00:00 wglng-10204 kernel (info) ehci_hcd 0000:00:02.2: new USB bus registered, assigned bus number 1
2021 Jan 22 22:31:36+00:00 wglng-10204 kernel (warning) ehci_hcd 0000:00:02.2: Enabling legacy PCI PM
2021 Jan 22 22:31:36+00:00 wglng-10204 kernel (info) sda: sda1 sda2

spam filter – Postfix `smtp_destination_rate_delay=1s` makes sending emails too slow

Several years ago, I guess for the reason that our emails get delivered (we have opt-in, opt-our email list with currently 640K subscribers), we have added 1s pause between emails to the same domain. However, over time gmail grew and it is currently 70% of our email subscribers so now sending of our newsletter takes ~8 days and it would most likely increase in the future… and comes to the point, when we have to speed it up, I think.

Postfix main.cf is configured with:

smtp_destination_concurrency_limit = 2
smtp_destination_rate_delay = 1s
smtp_extra_recipient_limit = 10

I’m thinking with the simple approach, to change smtp_destination_rate_delay=0
BUT there was a reason we introduced this smtp_destination_rate_delay, but I don’t remember, it was years ago. Obviously, some hosts didn’t like number of emails received from us in a short time span. (I don’t remember which one).

Another option to try would be a specific rule for gmail.

By searching Internet I didn’t find some good examples and it seems that postfix doesn’t support fractional values like 0.1.

Any suggestion on how to speed up sending with the postfix and not to raise false amount alarm by major mail providers?

To note, we are not sending spam, otherwise, we would not care much about it (or would figure out the numbers which work, by experiments).

We do send a few newsletters per year so couldn’t figure out this by experiments.

development process – How do developers show progress when it’s a “slow week”?

I’m working as the web developer with some friends who aren’t coders themselves, but understand what goes on in the process. So, if it’s been a week where I’m tackling a certain issue that I’m really confused by, I try to stay productive while attempting to piece together the new concepts by refactoring existing code, my friends understand.

At a real company, do other teams only care about pushing out new features, or is most sustainable code equally weighed even though there’s less to show off?

My role right now is like a one-man-dev-team so I handle everything and some weeks I’m on a roll, but then others it takes a bit to learn how to implement a feature. In the process though, I take a lot of notes, ask questions that I want answered, and try to gain a strong foundation to build on. In reality, do most dev teams try to promote this approach as well?

Thanks!

Slow query on WordPress site

I have an ongoing issues with slow queries killing my WordPress site.

# Query_time: 4066.175238  Lock_time: 0.000487  Rows_sent: 0  Rows_examined: 11814357687
# Rows_affected: 0  Bytes_sent: 0
SET timestamp=1611144443;
SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)  LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id)  LEFT JOIN wp_term_relationships AS tt2 ON (wp_posts.ID = tt2.object_id)  LEFT JOIN wp_term_relationships AS tt3 ON (wp_posts.ID = tt3.object_id) INNER JOIN wp_reorder_post_rel ON wp_posts.ID = wp_reorder_post_rel.post_id and incl = 1   LEFT JOIN wp_term_relationships tr ON wp_posts.ID = tr.object_id LEFT JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id=tr.term_taxonomy_id LEFT JOIN wp_terms tm ON tm.term_id = tt.term_id LEFT JOIN wp_term_relationships tr_v ON wp_posts.ID = tr_v.object_id LEFT JOIN wp_term_taxonomy tt_v ON ( tt_v.term_taxonomy_id=tr_v.term_taxonomy_id AND tt_v.taxonomy LIKE 'product_visibility' AND tt_v.term_taxonomy_id NOT IN ( 2349 ) ) LEFT JOIN wp_terms tm_v ON tm_v.term_id = tt_v.term_id WHERE 1=1  AND (
                wp_posts.ID NOT IN (
                SELECT object_id
                FROM wp_term_relationships
                WHERE term_taxonomy_id IN (2349)
            )
) AND wp_posts.post_status = 'publish'  AND (  ( LOWER( wp_posts.post_title ) REGEXP  'photo?(.*)frame')  OR  (( LOWER(tm.name) REGEXP 'photo?(.*)frame' OR LOWER(tm.slug) REGEXP 'photo?(.*)frame') AND tt.taxonomy IN ('product_cat'))  )  AND wp_posts.post_type IN ('product' ) AND wp_reorder_post_rel.category_id = '1441' GROUP BY wp_posts.ID ORDER BY wp_reorder_post_rel.id ASC LIMIT 0, 24;

Can anybody decifer and direct me?

Thanks

networking – My internet is so slow to far away servers. Why?

I have 240mb/60mb FTTH connection. When I do speedtest, I get full speeds to my ISP all the time. However, when I do speedtest outside country to another region, I get fixed download/upload speed of just 6.25mb using single-stream test. when I use multi-stream test, I get 25mb which makes sense because 6.25×4=25mb

It is always this fixed number to all servers in that area, so does that mean my ISP perhaps intentionally limit how much I can get to far away servers? If so, what can I do to achieve higher speeds to far away servers?

networking – My wifi working slow on Ubuntu and fast on other devices

I have been using my wifi for over a month now and have not encountered any problem. Recently after a long time I went back to my Ubuntu 20.04 and went to update the kernel. What I instantly noticed that my internet speed was extremely slow. For cross checking, I checked my mobile device for the same and speeds were normally fast.
The crazy part is that when I connected Ubuntu with my phone’s data connection, the speeds were normal. So it seems that the problem is with my wifi broadband connection on ubuntu .
Can anyone please suggest a possible fix.
I tried following:-

  1. Disbling wifi powersave
  2. Stopping ipv6 use.
  3. Preventing 802.11
    None of the above worked for me.

Output for lspci -knn | grep Net -A3

03:00.0 Network controller (0280): Qualcomm Atheros QCA9377 802.11ac Wireless Network Adapter (168c:0042) (rev 31)
    Subsystem: Lite-On Communications Inc Qualcomm Atheros QCA9377 802.11ac Wireless Network Adapter (11ad:08a6)
    Kernel driver in use: ath10k_pci
    Kernel modules: ath10k_pci