search

krowinski / php-mysql-replication

Pure PHP Implementation of MySQL replication protocol. This allow you to receive event like insert, update, delete with their data and raw SQL queries.
MIT License
323 stars 98 forks source link

Question about performance on bulk updates (ex. UPDATE table SET value = value + 1) #103

Open cyppe opened 10 months ago

cyppe commented 10 months ago

Hello!

Maybe it's a general replication "thing", but when I run the benchmark script I get good performance.

18106 event by seconds (1365000 total) 18105 event by seconds (1366000 total) 18105 event by seconds (1367000 total) 18105 event by seconds (1368000 total) 18106 event by seconds (1369000 total) 18106 event by seconds (1370000 total) 18107 event by seconds (1371000 total) 18107 event by seconds (1372000 total)

But if I run a huge bulk update (UPDATE table SET value = value + 1) on say 2 million rows. Then I only seem to handle about 500 events per second.

Is that expected or is there something I could look into?

This is without any processing of the data, just how many events being fired.

cyppe commented 10 months ago

Or actually.. I just measured it it in wrong way. In bulk each event contains a lot of rows. So It's actually processing on average 40.000 rows for me. So all good.

cyppe commented 10 months ago

Last addition: For big tables, or rather, wide tables with a lot of columns it's pretty slow anyway. But it's more data so shuffle around so I guess it makes sense.

It's around 3000 rows per second in such cases. Maybe someone has some ideas to improve this. able has around 50 columns, so maybe it's the way it is.

But interesting if anyone has any ideas.

And again - it's not about data processing, the event dispatches a job to process it. I measure just the pure speed the event is called with the updated data object.