@Marostegui ok! So the T125135 auto-increment thing is a very small piece of this larger issue.

Let's see if we can hammer out a way to use regular MySQL replication before we think about making eventlogging_sync.sh better.

Regular mysql replication is too slow and unsuitable for large batches of data

Is the issue large batch inserts, or is the issue just too many inserts? We can do either. IIRC, EventLogging was optimized to do large batch inserts to make things easier on the master. There's no reason we couldn't revert to doing individual inserts, or smaller batch inserts, if that would help. Would it?

In T124307#2987587, @Ottomata wrote:

@Marostegui ok! So the T125135 auto-increment thing is a very small piece of this larger issue.

Let's see if we can hammer out a way to use regular MySQL replication before we think about making eventlogging_sync.sh better.

It would be great :-)

Regular mysql replication is too slow and unsuitable for large batches of data

Is the issue large batch inserts, or is the issue just too many inserts? We can do either. IIRC, EventLogging was optimized to do large batch inserts to make things easier on the master. There's no reason we couldn't revert to doing individual inserts, or smaller batch inserts, if that would help. Would it?

The issue is normally larg batch of inserts (not sure if you also do DELETEs, but that is even harder for MySQL, big DELETES normally cause replication lag). Normally high number of INSERTS is fine (as the the master will probably have all in memory), but depending on what "high number" means it could become a problem but I guess we are not at that point just yet.

As Jaime mentions in the original post, using LOAD DATA can also be a benefit here for large amount of data inserting.

@mforns, can you comment about large DELETES? Do they happen often? How large are they when it happens?

@Marostegui , Would LOAD DATA actually help replication?

In T124307#2989663, @Ottomata wrote:

@Marostegui , Would LOAD DATA actually help replication?

If you need to do massive data imports into the DB, it will help if you have the file ordered by PK and can be loaded into the DB it will be a lot faster than parsing all the SQL commands (as Jaime also stated on the original post).

EventLogging is a stream of data. We can do batching because the data is consumed from Kafka, and then inserted into MySQL via a python MySQL client. So we could consume periodically, or wait until N messages are consumed before inserting. Constructing a file and ordering by primary key (I'm not sure what primary key would be, other than an auto-increment id) would be a little hacky. But I'm confused. LOAD DATA is inherently batch, right? Wouldn't that hinder the replication process?

LOAD DATA is a lot faster to bulk lots of data in the DB, there is a lot less overhead in parsing SQL statements and all the processes around that parsing.

This is an interesting blog post from @jcrespo where you can see some performance benchmarks: https://dbahire.com/testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results/

@Ottomata: we do not delete data from eventlogging (other than the purging that it should happen after 90 days) the system just inserts batches of records. cc @mforns

purging that it should happen after 90 days

How do you implement purging? That surely must run deletes or some kind of updates?

Change 345646 had a related patch set uploaded (by Ottomata):
[operations/puppet@production] Improvements to eventlogging_sync.sh script

https://gerrit.wikimedia.org/r/345646

ping @Marostegui, in case you didn't see it: https://gerrit.wikimedia.org/r/345646

Change 345646 merged by Ottomata:
[operations/puppet@production] Improvements to eventlogging_sync.sh script

https://gerrit.wikimedia.org/r/345646

Change 346541 had a related patch set uploaded (by Ottomata):
[operations/puppet@production] Properly default to master database name when slave database not given

https://gerrit.wikimedia.org/r/346541

Change 346541 merged by Ottomata:
[operations/puppet@production] Properly default to master database name when slave database not given

https://gerrit.wikimedia.org/r/346541