is it possible to run without binlog, or live update

[andycjw]

running binlog on my database is using too much resource, when it's doing mass update daily during data fetch in the background (10k inserts/sec), the meteor will become unresponsive as mysql spend all the cpu cycle doing master slave replication.

is it possible to disable live update, or the binlog listener during runtime? so that I can disable the listener when the database is doing the data fetch, then re-enable it when it's done.

using the settings minInterval and skipDiff doesn't help

right now, for publish, I set the trigger option 'table' for livemysql.select as '' empty string, so no trigger for update, but there's no live update anymore.

[numtel]

Pause/resume updates should be no problem to implement. Give a few days and I'll see what I can come up with.

[beveradb]

Thanks so much Ben, the work you're doing here is very much appreciated - I believe for Meteor to expand it will inevitably need to support relational databases alongside Mongo / Redis and you're the one person actually making it a reality.

Within the environment of my work however, I'm worried that just having MySQL's binlog enabled will have too much of an impact on the performance of our database server to be able to cope - the OP mentioned 10k inserts/sec during a daily data fetch, we're looking at 2 to 10k insert/updates per second during normal day to day operation of our application, and I can't easily test whether MySQL will be able to cope with the additional I/O of having the binlog enabled.

Do you have any idea what additional load a server should expect to see with binlog updates enabled, and how much of that load is just MySQL writing to the binlog file, versus how much of that load is caused by the processing of the binlog file by your code to extract changes and fire off events etc? If not, have you got any suggestions about how I could go about testing / benchmarking this? Thanks.

[numtel]

Without testing it conclusively, I would think that most of the delay is caused by the processing in Javascript, especially if those inserts/updates are occurring on tables that have live queries attached.

With meteor debug and/or node-inpector, you can profile the execution and log the heap for details on what is taking so long and using so much memory.

In addition to pause/resume, in order to measure and improve performance, I will convert mysql-live-select to inherit from pg-live-select.

• On query refreshes, row values are hashed in order to only retrieve new row data, resulting in much less data coming from the database server. With Postgres, measurements showed that this change more than doubled performance for tested queries.
• The existing load test application can be used to perform queries repeatedly over many clients and measure memory usage and response times.

[andycjw]

another possible delay is the firing of published query when each new insert is detected, this will cause slowdown especially if the query itself is slow, e.g. multiple join. I observe this from the 'show processlist;' that my slow query keep re-running when background insert happens.

[andycjw]

I have a suggestion to make the query to refresh run on debounce, so that it only re-query when the insert stop coming on user specified interval, e.g. 10 seconds, and live update will still work albeit delayed

[numtel]

@andycjw That feature already exists. See the minInterval configuration setting.

[andycjw]

Right now if I set minInterval as 10 seconds, it'll still re-query every 10 seconds, if there's insertion going on, it'll run non stop.

Is it possible to have it wait until 10 seconds that the insertion stop arriving anymore, and then only re-run the query to refresh the publish collection. Hope you get what I mean.

[numtel]

Let me know if I understand correctly, you would like a way to prevent a query from updating until no change affecting its results has occurred in the previous 10 seconds? e.g. If one change occurs to a query's results, then another happens 5 seconds later, the client will not be notified of any change for a minimum of 15 seconds from the first change?

You may be able to implement this type of strategy using the existing triggers settings interface with some creative use of fibers.

[andycjw]

you got the gist of it, but current existing trigger options still evaluate the condition callback on each row insert, it would be better performance wise if the LiveMysqlSelect.prototype.matchRowEvent handle this via 'throttling/debounce', if there's a first matchRowEvent() == true, the remaining update event from binlog will skip this select object without calling matchRowEvent, until no more events come in, then do the update(), put this select object back to _select

or maybe wrap select.update() in matchRowEvent in _.debounce, so that it only do the select.update() when binlog stop calling matchRowEvent for user-settings specified wait time

I'll try to modify the code to see if my if my idea works as expected, let me know what you think about this.

[numtel]

@andycjw Sorry this update took so long. I added pause/resume to mysql-live-select and have released version 0.1.8 of this package.

This update did not end up merging this code base with pg-live-select yet.

[vlasky]

@numtel Any idea when the updated version that inherits from pg-live-select will be released?

I am hoping that your algorithm that hashes row values will significantly reduce resource usage in my Meteor application.

P.S. Thanks for your excellent work on this package.

[numtel]

Hello @vlasky,

I do not know when I will have that feature implemented. If you would like to work out a schedule for me to put time into it, possibly as well as testing and simulations towards greater performance, please send me an email and maybe we can come up with a deal.

Thank you, Ben