Slave не догоняет master. В чем может быть проблема?

уверены, что эта команда из под mysql root подается ?

Проверять лучше с помощью show slave status;

Вообще, mysql slave может отставать просто потому что может отставать, а не потому что неверно настроен.

show slave status;

show slave status;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: **********

Master_User: *******

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000177

Read_Master_Log_Pos: 33395470

Relay_Log_File: mysqld2-relay-bin.000002

Relay_Log_Pos: 19206

Relay_Master_Log_File: mysql-bin.000177

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: 19609659

Relay_Log_Space: 13805175

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: 13282

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: 55

1 row in set (0,00 sec)

Exec_Master_Log_Pos и Relay_Log_Space бегут, но Seconds_Behind_Master все больше и больше. На практике такое первый раз и это ненормально.....

Так slave просто не успевает. Ситуация вполне объяснимая.

Насколько slave по ресурсам хуже чем master ?

Show processlist по-моему должен что-то показывать.

Если видимой активности не заметно - ну сделайте рестарт slave сервера что-ли.

Попробуйте перезалить снэпшот с мастера на слейв, обычно помогает. А лучше пользуйтесь синхронной репликацией.

Слейв может отставать изза того, что на нем выполняюся запросы в 1 поток.

Для слейва желательно иметь проц большей производительности на ядро при высоконагруженном мастере. А вообще надо смотреть на чем тупит слейв и исправлять, возможно на мастере придется править код сайта/скриптов.

Базу перезаливали на слэйв много раз, не помогает!

Слэйв существенно мощнее мастера, из под root на слеэйве:

show processlist;

+----+-------------+-----------+------+---------+-------+----------------------------------+------------------+-----------+---------------+-----------+

| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |

+----+-------------+-----------+------+---------+-------+----------------------------------+------------------+-----------+---------------+-----------+

| 2 | system user | | NULL | Connect | 40685 | Waiting for master to send event | NULL | 0 | 0 | 0 |

| 3 | system user | | NULL | Connect | 42875 | Reading event from the relay log | NULL | 0 | 1 | 1 |

| 7 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0 | 0 | 0 |

+----+-------------+-----------+------+---------+-------+----------------------------------+------------------+-----------+---------------+-----------+

Ну давайте вывод pt-summary с ДВУХ серверов. Посмотрим насколько мощнее.

Не помешал бы и вывод pt-mysql-summary.

тут синтаксис может показаться неочевидным, запускать нужно так :

pt-mysql-summary --user=root --password=XX

MASTER:

pt-summary:

# Percona Toolkit System Summary Report ######################

Date | 2015-06-17 14:21:14 UTC (local TZ: MSK +0300)

Hostname | *******************

Uptime | ********************

System | System manufacturer; System Product Name; vSystem Version (Desktop)

Service Tag | System Serial Number

Platform | Linux

Release | Debian GNU/Linux 7.8 (wheezy) (wheezy)

Kernel | 2.6.32-5-amd64

Architecture | CPU = 64-bit, OS = 64-bit

Threading | NPTL 2.13

SELinux | No SELinux detected

Virtualized | No virtualization detected

# Processor ##################################################

Processors | physical = 1, cores = 4, virtual = 8, hyperthreading = yes

Speeds | 8x1600.000

Models | 8xIntel(R) Core(TM) i7-3770 CPU @ 3.40GHz

Caches | 8x8192 KB

# Memory #####################################################

Total | 15.4G

Free | 103.6M

Used | physical = 15.3G, swap allocated = 8.0G, swap used = 908.1M, virtual = 16.2G

Buffers | 381.7M

Caches | 11.7G

Dirty | 244 kB

UsedRSS | 3.9G

Swappiness | 60

DirtyPolicy | 20, 10

DirtyStatus | 0, 0

Locator Size Speed Form Factor Type Type Detail

========= ======== ================= ============= ============= ===========

ChannelA-DIMM0 4096 MB 1333 MHz DIMM DDR3 Synchronous

ChannelA-DIMM1 4096 MB 1333 MHz DIMM DDR3 Synchronous

ChannelB-DIMM0 4096 MB 1333 MHz DIMM DDR3 Synchronous

ChannelB-DIMM1 4096 MB 1333 MHz DIMM DDR3 Synchronous

# Mounted Filesystems ########################################

Filesystem Size Used Type Opts Mountpoint

/dev/md1 496M 12% ext3 rw /boot

/dev/md2 1016G 9% ext4 rw /

/dev/md3 1,8T 96% ext4 rw /home

# Disk Partioning ############################################

Device Type Start End Size

============ ==== ========== ========== ==================

/dev/md0 Disk 8588873728

/dev/md1 Disk 536858624

/dev/md2 Disk 1099510439936

/dev/md3 Disk 1891951246336

# Kernel Inode State #########################################

dentry-state | 46401 39451 45 0 0 0

file-nr | 1856 0 1614292

inode-nr | 48519 7346

# Network Config #############################################

Controller | Realtek Semiconductor Co., Ltd. RTL8111/8168B PCI Express Gigabit Ethernet controller (rev 09)

FIN Timeout | 60

Port Range | 61000

# Interface Statistics #######################################

interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors

========= ========= ========== ========== ========== ========== ==========

lo 3500000000 300000000 0 3500000000 300000000 0

eth0 2000000000 4500000000 0 2000000000 35000000 0

# Network Devices ############################################

Device Speed Duplex

========= ========= =========

eth0 1000Mb/s Full

# Network Connections ########################################

Connections from remote IP addresses

States of connections

ESTABLISHED 15

FIN_WAIT1 1

LISTEN 15

SYN_RECV 1

TIME_WAIT 100

# Top Processes ##############################################

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

****** mysql 20 0 848m 226m 8168 S 6,2 1,4 37:46.47 mysqld

****** root 20 0 24404 1636 1084 R 6,2 0,0 0:00.01 top

1 root 20 0 10604 692 576 S 0,0 0,0 3:39.32 init

2 root 20 0 0 0 0 S 0,0 0,0 0:02.37 kthreadd

3 root rt 0 0 0 0 S 0,0 0,0 0:00.65 migration/0

4 root 20 0 0 0 0 S 0,0 0,0 6:22.36 ksoftirqd/0

5 root rt 0 0 0 0 S 0,0 0,0 0:00.00 watchdog/0

6 root rt 0 0 0 0 S 0,0 0,0 0:00.11 migration/1

7 root 20 0 0 0 0 S 0,0 0,0 1:04.94 ksoftirqd/1

# Notable Processes ##########################################

PID OOM COMMAND

**** -17 sshd

**** -17 udevd

**** -17 udevd

**** -17 udevd

# The End ####################################################

pt-summary SLAVE:

# Percona Toolkit System Summary Report ######################

Date | 2015-06-17 14:26:17 UTC (******)

Hostname | ******

Uptime | ******

System | ASUSTeK Computer INC.; KGP(M)E-D16; vSystem Version (Desktop)

Service Tag | ******

Platform | Linux

Release | Debian GNU/Linux 7.8 (wheezy) (wheezy)

Kernel | 3.2.0-4-amd64

Architecture | CPU = 64-bit, OS = 64-bit

Threading | NPTL 2.13

SELinux | No SELinux detected

Virtualized | No virtualization detected

# Processor ##################################################

Processors | physical = 2, cores = 16, virtual = 32, hyperthreading = yes

Speeds | 16x1400.000, 16x2100.000

Models | 32xAMD Opteron(TM) Processor 6272

Caches | 32x2048 KB

# Memory #####################################################

Total | 94.6G

Free | 46.2G

Used | physical = 48.4G, swap allocated = 0.0, swap used = 0.0, virtual = 48.4G

Buffers | 230.5M

Caches | 34.0G

Dirty | 3192 kB

UsedRSS | 13.2G

Swappiness | 60

DirtyPolicy | 20, 10

DirtyStatus | 0, 0

Locator Size Speed Form Factor Type Type Detail

========= ======== ================= ============= ============= ===========

DIMM_D1 16384 MB 1333 MHz DIMM DDR3 Synchronous

DIMM_D2 16384 MB 1333 MHz DIMM DDR3 Synchronous

DIMM_G1 16384 MB 1333 MHz DIMM DDR3 Synchronous

DIMM_G2 16384 MB 1333 MHz DIMM DDR3 Synchronous

DIMM_H1 16384 MB 1333 MHz DIMM DDR3 Synchronous

DIMM_H2 16384 MB 1333 MHz DIMM DDR3 Synchronous

DIMM_A1 {EMPTY} Unknown {OUT OF SPEC} DDR3 Synchronous

DIMM_A2 {EMPTY} Unknown {OUT OF SPEC} DDR3 Synchronous

DIMM_B1 {EMPTY} Unknown {OUT OF SPEC} DDR3 Synchronous

DIMM_B2 {EMPTY} Unknown {OUT OF SPEC} DDR3 Synchronous

DIMM_C1 {EMPTY} Unknown {OUT OF SPEC} DDR3 Synchronous

DIMM_C2 {EMPTY} Unknown {OUT OF SPEC} DDR3 Synchronous

DIMM_E1 {EMPTY} Unknown {OUT OF SPEC} DDR3 Synchronous

DIMM_E2 {EMPTY} Unknown {OUT OF SPEC} DDR3 Synchronous

DIMM_F1 {EMPTY} Unknown {OUT OF SPEC} DDR3 Synchronous

DIMM_F2 {EMPTY} Unknown {OUT OF SPEC} DDR3 Synchronous

# Disk Partioning ############################################

Device Type Start End Size

============ ==== ========== ========== ==================

******

# Kernel Inode State #########################################

dentry-state | 19702 15518 45 0 0 0

file-nr | 1600 0 9911237

inode-nr | 16998 294

# LVM Volumes ################################################

Unable to collect information

# LVM Volume Groups ##########################################

Unable to collect information

# RAID Controller ############################################

Controller | Fusion-MPT SAS

# Network Config #############################################

******

FIN Timeout | 60

Port Range | 61000

# Interface Statistics #######################################

interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors

========= ========= ========== ========== ========== ========== ==========

******

# Network Connections ########################################

Connections from remote IP addresses

******

Connections to local IP addresses

******

Connections to top 10 local ports

******

States of connections

ESTABLISHED 40

FIN_WAIT1 1

LISTEN 70

TIME_WAIT 3000

# The End ####################################################

pt-mysql-summary MASTER

# Percona Toolkit MySQL Summary Report #######################

System time | 2015-06-17 12:14:41

# Instances ##################################################

Port Data Directory Nice OOM Socket

===== ========================== ==== === ======

3306 /var/lib/mysql 0 0 /var/run/mysqld/mysqld.sock

# MySQL Executable ###########################################

Path to executable | /usr/sbin/mysqld

Has symbols | No

# Report On Port 3306 ########################################

User | root@localhost

Time | 2015-06-17 17:14:41 (MSK)

Hostname | ******

Version | 5.5.43-37.2-log Percona Server (GPL), Release 37.2, Revision 6202fee

Built On | debian-linux-gnu x86_64

Started | 2015-06-16 19:09 (up 0+22:04:57)

Databases | 24

Datadir | ******

Processes | 70 connected, 3 running

Replication | Is not a slave, has 1 slaves connected

Pidfile | /var/run/mysqld/mysqld.pid (exists)

# Processlist ################################################

Command COUNT(*) Working SUM(Time) MAX(Time)

------------------------------ -------- ------- --------- ---------

Binlog Dump 1 1 1000 1000

Query 2 2 0 0

Sleep 60 0 60000 12500

User COUNT(*) Working SUM(Time) MAX(Time)

------------------------------ -------- ------- --------- ---------

root 1 1 0 0

slave 1 1 1000 1000

***** 60 1 0 0

Host COUNT(*) Working SUM(Time) MAX(Time)

------------------------------ -------- ------- --------- ---------

slave IP 1 1 1000 1000

localhost 60 2 0 0

db COUNT(*) Working SUM(Time) MAX(Time)

------------------------------ -------- ------- --------- ---------

NULL 2 2 1000 1000

***** 60 1 0 0

State COUNT(*) Working SUM(Time) MAX(Time)

------------------------------ -------- ------- --------- ---------

60 0 0 0

Master has sent all binlog to 1 1 1000 1000

NULL 2 2 0 0

# Table cache ################################################

Size | 400

Usage | 100%

# Key Percona Server features ################################

Table & Index Stats | Disabled

Multiple I/O Threads | Enabled

Corruption Resilient | Enabled

Durable Replication | Disabled

Import InnoDB Tables | Disabled

Fast Server Restarts | Disabled

Enhanced Logging | Disabled

Replica Perf Logging | Disabled

Response Time Hist. | Disabled

Smooth Flushing | Enabled

HandlerSocket NoSQL | Not Supported

Fast Hash UDFs | Unknown

# Percona XtraDB Cluster #####################################

# Plugins ####################################################

InnoDB compression | ACTIVE

# Query cache ################################################

query_cache_type | ON

Size | 16.0M

Usage | 80%

HitToInsertRatio | 600%

# Schema #####################################################

Specify --databases or --all-databases to dump and summarize schemas

# Noteworthy Technologies ####################################

SSL | No

Explicit LOCK TABLES | No

Delayed Insert | No

XA Transactions | No

NDB Cluster | No

Prepared Statements | No

Prepared statement count | 0

# InnoDB #####################################################

Version | 5.5.43-37.2

Buffer Pool Size | 128.0M

Buffer Pool Fill | 100%

Buffer Pool Dirty | 25%

File Per Table | OFF

Page Size | 16k

Log File Size | 2 * 5.0M = 10.0M

Log Buffer Size | 8M

Flush Method |

Flush Log At Commit | 1

XA Support | ON

Checksums | ON

Doublewrite | ON

R/W I/O Threads | 4 4

I/O Capacity | 200

Thread Concurrency | 0

Concurrency Tickets | 500

Commit Concurrency | 0

Txn Isolation Level | REPEATABLE-READ

Adaptive Flushing | ON

Adaptive Checkpoint |

Checkpoint Age | 4M

InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue

Oldest Transaction | 0 Seconds

History List Len | 4285

Read Views | 1

Undo Log Entries | 0 transactions, 0 total undo, 0 max undo

Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads

Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (1 log, 0 chkp); 0 pwrites

Pending I/O Flushes | 0 buf pool, 1 log

Transaction States | 64xnot started, 1xnot started flushing log

# MyISAM #####################################################

Key Cache | 16.0M

Pct Used | 25%

Unflushed | 0%

# Security ###################################################

Users | 31 users, 0 anon, 0 w/o pw, 0 old pw

Old Passwords | OFF

# Binary Logging #############################################

Binlogs | 80

Zero-Sized | 0

Total Size | 6.3G

binlog_format | MIXED

expire_logs_days | 10

sync_binlog | 0

server_id | 55

binlog_do_db |

binlog_ignore_db |

# Noteworthy Variables #######################################

Auto-Inc Incr/Offset | 1/1

default_storage_engine | InnoDB

flush_time | 0

init_connect |

init_file |

sql_mode |

join_buffer_size | 128k

sort_buffer_size | 2M

read_buffer_size | 128k

read_rnd_buffer_size | 256k

bulk_insert_buffer | 0.00

max_heap_table_size | 16M

tmp_table_size | 16M

max_allowed_packet | 16M

thread_stack | 192k

log | OFF

log_error | /var/log/mysql/mysql.log

log_warnings | 1

log_slow_queries | OFF

log_queries_not_using_indexes | OFF

log_slave_updates | OFF

# The End ####################################################