Slave Lag MySQLCE 5.5.54

[Markus Bauer]

Hi Guys,

after googling around for ages i thought i might aswell post here:

I have a master-slave setup for a monitoring system and whilst importing all the data took ages (8hrs to be precise) i'm having trouble getting the replication to work properly.

Slave_IO and SQL are both on running and the different Log Positions are updating (Read_Master_Log, Relay_Log_Space) according to the master, but Exec_Master_Log_Pos is stuck. It seemed to have increased about 150k entries from the original position but now it's been at the same count for about 2 days. Logs are showing now errors, neither does "Show slave status\G". However in encountered the infamous "invalidating query cache entries (table)" that i've read about quite often on my journey through google. So i disabled query caching and its related variables however that didn't bring the effect i had hoped for.

In fact it changed nothing at all. Apart from the "state"-message of the process where it now says null.

ID User Host db Command Time State Info
4 | system user | | NULL | Connect | 293112 | NULL | NULL

And no the host hasn't been up for 293112 seconds.


The setup:

master and slave have 8 vCPUs each + 7.5GB of RAM

data is stored on a SAN

the data:

three databases, two of which have data that is in sum less than 10GB, the last database has one myisam table that is 18GB for the .myi file and 22GB for the .myd file (probably the source of my issues and something i dearly hate but can't change). Most of the other tables are a lot smaller and about overall i'd say 90% are already InnoDB. Replication typ is row based as i was told that statement based is not advisable for this kind of data (there are hashes beeing generated and stored).

The slave my.cnf
The part that is commented out beneath "Replication Master" is the current config as it is on the master. All other settings are identical (other than query caching beeing still enabled on the master but not causing any issues there)

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table=1


skip-external-locking
key_buffer = 384M
max_allowed_packet = 32M
table_cache = 3600
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 2M
thread_cache_size = 32

query_cache_size= 0 #disable query cache for better slave performance
query_cache_type=0

max_heap_table_size=256M
tmp_table_size=512M
thread_concurrency = 8
skip-name-resolve
innodb_buffer_pool_size = 4G
innodb_log_file_size = 250M
innodb_log_buffer_size = 16M
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 2
tmpdir = /var/lib/centreon/repair/



general_log_file= /var/log/mysql/mysql_general.log
general_log=1
slow_query_log_file = /var/log/mysql/mysql_slow.log
slow_query_log = 1
log_queries_not_using_indexes = 0
long_query_time = 0.5
min_examined_row_limit = 100

##### Replication SLAVE ##### COPY THIS PART INTO THE MY.CNF OF A NEW SLAVE IF NECCESSARY
server-id=2 #Needs to be unique and not 1 for slave status
binlog-format=row #Needs to be enabled so replication works
relay-log=mysqld-relay-bin #Specify Log
skip-slave-start=true #Prevent slave start
#
#
####### Replication MASTER ##### !!CURRENTLY ACTIVE!!
#
#
#log-bin=mysql-bin #Enable binlog so slaves know what to do
#server-id=1 #Master needs to be 1 and unique
#binlog-format=row #Specify binlog format - needs to the same on slaves
#log_bin_trust_function_creators = 1
#binlog-do-db=centreon #Specify DBs that log into binlog
#binlog-do-db=centreon_status #These 3 are needed for centreon
#binlog-do-db=centreon_storage
#sync_binlog=1
expire_logs_days=3 #Expire entries older than 7 days






[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid





I'll happily provide any additional data if neccessary. Also feel free to roast me for any bad configurations as i didn't set this up initially and my MySQL admin powers are rather limited.

Note: I tested the replication setup on a small scale database that worked just fine.