MariaDB replication

[smontoya]

Hi

I've attempted to setup replication in my ViciBox server to a higher end server mostly to run reports, analysis and statistics of calls without disrupting dialing whenever we run large queries on the DB.

However when replication is active, calls will start to fail or lag.

Any suggestion to accomplish this? Or should I just go for a Asterisk & WEB+DB setup?

[Vince-0]

Sounds like you have a server load problem.

Writing replication logs can add stress to your disks. You should probably run Raid10. How many live agents are on your server?

[smontoya]

We have at most 30 agents in the server, 10 of them doing autodialing, the rest manual and inbound.

The server where Vici is running unfortunately does not support Raid10, it has Raid 0, 1 & 1E, which seems more like a RAID 5 probably.

The server which would be taking the role as slave does support RAID10.

Also, I'm using the default my.cnf provided by ViciBox but I ran the wizard at the Percona site and suggests the following configuration:

Code: Select all

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = MyISAM
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
;key-buffer-size                = 8G
myisam-recover                 = FORCE,BACKUP

# SAFETY #
;max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 4096

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 32M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 8M

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log


EDIT: Just to clarify I'm not using this configuration for MariaDB, this was suggested by Percona wizard tool, but currently the server is running the default my.cnf provided on ViciBox.

[Vince-0]

I'm not so sure about that my.cnf - rather use the Vicidial suggested one for 30 agents there won't be a problem.

I see no reason why you say there is a correlation between replication and call failures /lag other than the disks being under too much load writing replication binlogs.
It's never a good idea to run anything like RAID5 for your master server, it's not fast enough for your purposes.

[smontoya]

Got it, thank you. Will work something else out.

[okli]

Without going through the rest of the ny.conf settings, this one should be changed:

Code: Select all

sync-binlog                    = 1

to 0.
Otherwise each write to binlog MySQL will be synced to the disk which night have great impact on performance.

[Kumba]

Binary logs are enabled by default on ViciBox installs since V3. There shouldn't be any issue with a slave sync'ing to the master. Only time it might be a problem is if your disks are marginally overloaded and the extra reads from the slave are making them completely overloaded. I would expect you to have a bunch of other issues before that happens though.

Run iostat and see what kind of load your disks are under when it happens. If it's above 90% then you need faster disks. Either go with a paid of SSD's in a RAID-1 or get a RAID-10.

[williamconley]

Also, if you allow your binlogs to bloat, you will have problems in other areas as well. Back up regularly and dump those binary logs.

If you are using the slave as an emergency backup (ie: slave takes over if master dies), then your binary logs are redundant in the first place and could theoretically be turned off.

If, however, you have a large system and you are using the slave to run reports, I could see where turning of the binary log sync is a good idea. In that case, though, I'd probably put the original binary logs on a 2nd HD (not the same drive as the mysql database) so you can recover from the backup and binary logs in case the HD for mysql dies horribly.

[smontoya]

Without going through the rest of the ny.conf settings, this one should be changed:

Code: Select all

sync-binlog                    = 1

to 0.
Otherwise each write to binlog MySQL will be synced to the disk which night have great impact on performance.

I set it up like that and it is behaving much better, no issues whatsover with calls, just once in a while an error in the slave server will show up about being unable to do certain transaction probably every 1 or 2 days. Thanks!

Binary logs are enabled by default on ViciBox installs since V3. There shouldn't be any issue with a slave sync'ing to the master. Only time it might be a problem is if your disks are marginally overloaded and the extra reads from the slave are making them completely overloaded. I would expect you to have a bunch of other issues before that happens though.

Run iostat and see what kind of load your disks are under when it happens. If it's above 90% then you need faster disks. Either go with a paid of SSD's in a RAID-1 or get a RAID-10.

I ran iostat and there is not much info in % rather than CPU usage it seems.

This is what I got:

If, however, you have a large system and you are using the slave to run reports, I could see where turning of the binary log sync is a good idea. In that case, though, I'd probably put the original binary logs on a 2nd HD (not the same drive as the mysql database) so you can recover from the backup and binary logs in case the HD for mysql dies horribly.

This is basically the main usage of the server, running reports and a lot of custom MySQL queries for statistics on the vicidial database tables, a lot of joins, etc.

I will do that, I'll setup another set of hard drives just for the binlogs, even though the issues seems to have been mitigated with sync-binlog = 0.... this would really help more I think. Thanks!

[williamconley]

iostat -c -d -h -z -t -x 1 5

we've found that the "await" value suddenly jumping to triple digits (and staying there for a few seconds) tends to precede "HD death" and can also indicate a simple overload of the HD.

[smontoya]

iostat -c -d -h -z -t -x 1 5

we've found that the "await" value suddenly jumping to triple digits (and staying there for a few seconds) tends to precede "HD death" and can also indicate a simple overload of the HD.

Actually the await does jump a lot.



There was actually 1 second where there was no data at all.

[williamconley]

No data generally means the HD caught up (no wait to report). Obviously you should delve into the meanings and draw your own conclusion, but this seems to indicate that you need a faster HD that can keep up with demand.